/ Check-in [aeb694e3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Do not attempt to use terms from the WHERE clause to drive indexes on the right table of a LEFT JOIN. Fix for ticket [4ba5abf65c5b0f9a96a7a40cd18b]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: aeb694e3f787f1f8b55650c17f90c197eee3f7f9b890a88f458c33e43009a082
User & Date: drh 2018-04-24 17:34:03
Context
2018-04-24
18:53
Fix a problem with processing "LEFT JOIN tbl ON tbl.a = ? AND (tbl.b=? OR tbl.c=?)" in cases where there are indexes on both tbl(a, b) and tbl(a, c). check-in: ce35e39c user: dan tags: trunk
17:34
Do not attempt to use terms from the WHERE clause to drive indexes on the right table of a LEFT JOIN. Fix for ticket [4ba5abf65c5b0f9a96a7a40cd18b] check-in: aeb694e3 user: drh tags: trunk
16:51
Update the expert extension test cases to account for the fact that EXPLAIN QUERY PLAN now shows the start of each trigger in its output. check-in: 8acb42f4 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5539   5539   
  5540   5540       /* Sometimes the code for a subquery will be generated more than
  5541   5541       ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
  5542   5542       ** for example.  In that case, do not regenerate the code to manifest
  5543   5543       ** a view or the co-routine to implement a view.  The first instance
  5544   5544       ** is sufficient, though the subroutine to manifest the view does need
  5545   5545       ** to be invoked again. */
  5546         -    if( pItem->addrFillSub ){
         5546  +    if( NEVER(pItem->addrFillSub) ){
  5547   5547         if( pItem->fg.viaCoroutine==0 ){
  5548   5548           /* The subroutine that manifests the view might be a one-time routine,
  5549   5549           ** or it might need to be rerun on each iteration because it
  5550   5550           ** encodes a correlated subquery. */
  5551   5551           testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
  5552   5552           sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
  5553   5553         }

Changes to src/where.c.

  2416   2416       }
  2417   2417       if( pTerm->prereqRight & pNew->maskSelf ) continue;
  2418   2418   
  2419   2419       /* Do not allow the upper bound of a LIKE optimization range constraint
  2420   2420       ** to mix with a lower range bound from some other source */
  2421   2421       if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
  2422   2422   
  2423         -    /* Do not allow IS constraints from the WHERE clause to be used by the
         2423  +    /* Do not allow constraints from the WHERE clause to be used by the
  2424   2424       ** right table of a LEFT JOIN.  Only constraints in the ON clause are
  2425   2425       ** allowed */
  2426   2426       if( (pSrc->fg.jointype & JT_LEFT)!=0
  2427   2427        && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
  2428         -     && (eOp & (WO_IS|WO_ISNULL))!=0
  2429   2428       ){
  2430         -      testcase( eOp & WO_IS );
  2431         -      testcase( eOp & WO_ISNULL );
  2432   2429         continue;
  2433   2430       }
  2434   2431   
  2435   2432       if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
  2436   2433         pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
  2437   2434       }else{
  2438   2435         pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;

Changes to test/where.test.

  1374   1374     CREATE INDEX t191a ON t1(a);
  1375   1375     CREATE INDEX t191b ON t1(b);
  1376   1376     CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);
  1377   1377   
  1378   1378     EXPLAIN QUERY PLAN
  1379   1379     SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
  1380   1380   } {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/}
         1381  +
         1382  +# 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a]
         1383  +# Index on expressions leads to an incorrect answer for a LEFT JOIN
         1384  +#
         1385  +do_execsql_test where-20.0 {
         1386  +  CREATE TABLE t201(x);
         1387  +  CREATE TABLE t202(y, z);
         1388  +  INSERT INTO t201 VALUES('key');
         1389  +  INSERT INTO t202 VALUES('key', -1);
         1390  +  CREATE INDEX t202i ON t202(y, ifnull(z, 0));
         1391  +  SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0;
         1392  +} {0}
  1381   1393   
  1382   1394   
  1383   1395   finish_test