Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
d2f0b2e859a8a413ee4e4bd7040e450c |
| User & Date: | dan 2017-07-18 17:13:41 |
Context
|
2017-07-18
| ||
| 17:34 | Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. Because it prevents the planner from creating an automatic index on the results of the sub-query, the flattening optimization sometimes slows things down in this case. (check-in: 9dbae1df user: dan tags: trunk) | |
| 17:13 | Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b]. (check-in: d2f0b2e8 user: dan tags: trunk) | |
| 14:41 | Change the default command-line shell history depth to 2000 lines. (check-in: 0b69aa7e user: drh tags: trunk) | |
Changes
Changes to src/where.c.
604 605 606 607 608 609 610 611 612 613 614 615 616 617 |
WhereTerm *pTerm, /* WHERE clause term to check */
struct SrcList_item *pSrc, /* Table we are trying to access */
Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
if( pTerm->u.leftColumn<0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
testcase( pTerm->pExpr->op==TK_IS );
return 1;
}
|
> > > > > > > > > |
604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 |
WhereTerm *pTerm, /* WHERE clause term to check */
struct SrcList_item *pSrc, /* Table we are trying to access */
Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
if( (pSrc->fg.jointype & JT_LEFT)
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
&& (pTerm->eOperator & WO_IS)
){
/* Cannot use an IS term from the WHERE clause as an index driver for
** the RHS of a LEFT JOIN. Such a term can only be used if it is from
** the ON clause. */
return 0;
}
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
if( pTerm->u.leftColumn<0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
testcase( pTerm->pExpr->op==TK_IS );
return 1;
}
|
Changes to test/autoindex1.test.
515 516 517 518 519 520 521 522 523 |
# 2015-04-15: A NULL CollSeq pointer in automatic index creation.
#
do_execsql_test autoindex1-920 {
CREATE TABLE t920(x);
INSERT INTO t920 VALUES(3),(4),(5);
SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
} {5 0 9 5 0 9 5 0 9}
finish_test
|
> > > > > > > > > > > > > > > > > > |
515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 |
# 2015-04-15: A NULL CollSeq pointer in automatic index creation.
#
do_execsql_test autoindex1-920 {
CREATE TABLE t920(x);
INSERT INTO t920 VALUES(3),(4),(5);
SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
} {5 0 9 5 0 9 5 0 9}
#-------------------------------------------------------------------------
# An IS term from the WHERE clause of a LEFT JOIN cannot be used as an
# index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
# the following SELECT count(*) would incorrectly return 1.
#
do_execsql_test autoindex1-1010 {
CREATE TABLE t11(w);
CREATE TABLE t12(y);
INSERT INTO t11 VALUES(NULL);
INSERT INTO t12 VALUES('notnull');
}
do_execsql_test autoindex1-1020 {
SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
} 0
finish_test
|