Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | When optimizing expressions of the form "x IN (SELECT ...)" make sure that the subquery is not correlated. Fix for ticket [5e3c886796e5512]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1ed6b06ea3c432f920fb2b66b6042be9 |
User & Date: | drh 2016-03-09 15:09:22 |
Context
2016-03-09
| ||
15:14 | Add another test case for bug [5e3c8867]. (check-in: d91e57e4 user: dan tags: trunk) | |
15:09 | When optimizing expressions of the form "x IN (SELECT ...)" make sure that the subquery is not correlated. Fix for ticket [5e3c886796e5512]. (check-in: 1ed6b06e user: drh tags: trunk) | |
13:39 | Automatically disable SQLITE_ENABLE_EXPLAIN_COMMENTS if SQLITE_OMIT_EXPLAIN is defined. (check-in: 8d4b6b2b user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 | ** errors have been found. */ #ifndef SQLITE_OMIT_SUBQUERY static int isCandidateForInOpt(Select *p){ SrcList *pSrc; ExprList *pEList; Table *pTab; if( p==0 ) return 0; /* right-hand side of IN is SELECT */ if( p->pPrior ) return 0; /* Not a compound SELECT */ if( p->selFlags & (SF_Distinct|SF_Aggregate) ){ testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ); testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate ); return 0; /* No DISTINCT keyword and no aggregate functions */ } | > | 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 | ** errors have been found. */ #ifndef SQLITE_OMIT_SUBQUERY static int isCandidateForInOpt(Select *p){ SrcList *pSrc; ExprList *pEList; Table *pTab; Expr *pRes; /* Result expression */ if( p==0 ) return 0; /* right-hand side of IN is SELECT */ if( p->pPrior ) return 0; /* Not a compound SELECT */ if( p->selFlags & (SF_Distinct|SF_Aggregate) ){ testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ); testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate ); return 0; /* No DISTINCT keyword and no aggregate functions */ } |
︙ | ︙ | |||
1601 1602 1603 1604 1605 1606 1607 | if( pSrc->a[0].pSelect ) return 0; /* FROM is not a subquery or view */ pTab = pSrc->a[0].pTab; if( NEVER(pTab==0) ) return 0; assert( pTab->pSelect==0 ); /* FROM clause is not a view */ if( IsVirtual(pTab) ) return 0; /* FROM clause not a virtual table */ pEList = p->pEList; if( pEList->nExpr!=1 ) return 0; /* One column in the result set */ | > | > | 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 | if( pSrc->a[0].pSelect ) return 0; /* FROM is not a subquery or view */ pTab = pSrc->a[0].pTab; if( NEVER(pTab==0) ) return 0; assert( pTab->pSelect==0 ); /* FROM clause is not a view */ if( IsVirtual(pTab) ) return 0; /* FROM clause not a virtual table */ pEList = p->pEList; if( pEList->nExpr!=1 ) return 0; /* One column in the result set */ pRes = pEList->a[0].pExpr; if( pRes->op!=TK_COLUMN ) return 0; /* Result is a column */ if( pRes->iTable!=pSrc->a[0].iCursor ) return 0; /* Not a correlated subq */ return 1; } #endif /* SQLITE_OMIT_SUBQUERY */ /* ** Code an OP_Once instruction and allocate space for its flag. Return the ** address of the new instruction. |
︙ | ︙ |
Changes to test/where2.test.
︙ | ︙ | |||
760 761 762 763 764 765 766 767 768 | # do_execsql_test where2-13.1 { CREATE TABLE t13(a,b); CREATE INDEX t13a ON t13(a); INSERT INTO t13 VALUES(4,5); SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4; } {4 5} finish_test | > > > > > > > > > > > | 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 | # do_execsql_test where2-13.1 { CREATE TABLE t13(a,b); CREATE INDEX t13a ON t13(a); INSERT INTO t13 VALUES(4,5); SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4; } {4 5} # https://www.sqlite.org/src/info/5e3c886796e5512e (2016-03-09) # Correlated subquery on the RHS of an IN operator # do_execsql_test where2-14.1 { CREATE TABLE t14a(x INTEGER PRIMARY KEY); INSERT INTO t14a(x) VALUES(1),(2),(3),(4); CREATE TABLE t14b(y INTEGER PRIMARY KEY); INSERT INTO t14b(y) VALUES(1); SELECT x FROM t14a WHERE x NOT IN (SELECT x FROM t14b); } {} finish_test |