Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not move WHERE clause terms inside OR expressions that are contained within an ON clause of a LEFT JOIN. Fix for ticket [f2369304e47167e3e]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1128575d0ab24f7023a0f6e6ce4828b9 |
User & Date: | drh 2013-05-09 14:20:11.944 |
Context
2013-05-09
| ||
18:12 | Add assert()s to the implementation of xRead() in the built-in VFSes to verify that the offset parameter is always non-negative. (check-in: cf5c364224 user: drh tags: trunk) | |
14:20 | Do not move WHERE clause terms inside OR expressions that are contained within an ON clause of a LEFT JOIN. Fix for ticket [f2369304e47167e3e]. (check-in: 1128575d0a user: drh tags: trunk) | |
00:40 | Refine and reform all Windows OSTRACE macro usage. Replace all usage of sqlite3TestErrorName() with sqlite3ErrName() and add missing return codes. (check-in: 610425f190 user: mistachkin tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 | ** That way, terms in y that are factored into the disjunction will ** be picked up by the recursive calls to sqlite3WhereBegin() below. ** ** Actually, each subexpression is converted to "xN AND w" where w is ** the "interesting" terms of z - terms that did not originate in the ** ON or USING clause of a LEFT JOIN, and terms that are usable as ** indices. */ if( pWC->nTerm>1 ){ int iTerm; for(iTerm=0; iTerm<pWC->nTerm; iTerm++){ Expr *pExpr = pWC->a[iTerm].pExpr; if( ExprHasProperty(pExpr, EP_FromJoin) ) continue; if( pWC->a[iTerm].wtFlags & (TERM_VIRTUAL|TERM_ORINFO) ) continue; | > > > > | 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 | ** That way, terms in y that are factored into the disjunction will ** be picked up by the recursive calls to sqlite3WhereBegin() below. ** ** Actually, each subexpression is converted to "xN AND w" where w is ** the "interesting" terms of z - terms that did not originate in the ** ON or USING clause of a LEFT JOIN, and terms that are usable as ** indices. ** ** This optimization also only applies if the (x1 OR x2 OR ...) term ** is not contained in the ON clause of a LEFT JOIN. ** See ticket http://www.sqlite.org/src/info/f2369304e4 */ if( pWC->nTerm>1 ){ int iTerm; for(iTerm=0; iTerm<pWC->nTerm; iTerm++){ Expr *pExpr = pWC->a[iTerm].pExpr; if( ExprHasProperty(pExpr, EP_FromJoin) ) continue; if( pWC->a[iTerm].wtFlags & (TERM_VIRTUAL|TERM_ORINFO) ) continue; |
︙ | ︙ | |||
4743 4744 4745 4746 4747 4748 4749 | } for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ Expr *pOrExpr = pOrTerm->pExpr; | | | 4747 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 | } for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ Expr *pOrExpr = pOrTerm->pExpr; if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){ pAndExpr->pLeft = pOrExpr; pOrExpr = pAndExpr; } /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY, iCovCur); |
︙ | ︙ |
Changes to test/where9.test.
︙ | ︙ | |||
888 889 890 891 892 893 894 895 896 | db eval { SELECT * FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83 WHERE c==p OR d==p ORDER BY +a; } } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55} finish_test | > > > > > > > > > > > > > > > > > > > > > | 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 | db eval { SELECT * FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83 WHERE c==p OR d==p ORDER BY +a; } } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55} # Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7] # Incorrect results when OR is used in the ON clause of a LEFT JOIN # do_test where9-9.1 { db eval { CREATE TABLE t91(x); INSERT INTO t91 VALUES(1); CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b); INSERT INTO t92 VALUES(1,2,3); SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3; SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3; SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL; SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL; CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3; SELECT 5 FROM x9 WHERE y IS NULL; SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL; SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL; SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL; SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL; } } {1 2 3 4 8 9} finish_test |