/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

5539
5540
5541
5542
5543
5544
5545
5546
5547
5548
5549
5550
5551
5552
5553

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







|







5539
5540
5541
5542
5543
5544
5545
5546
5547
5548
5549
5550
5551
5552
5553

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

Changes to src/where.c.

2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
    }
    if( pTerm->prereqRight & pNew->maskSelf ) continue;

    /* Do not allow the upper bound of a LIKE optimization range constraint
    ** to mix with a lower range bound from some other source */
    if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;

    /* Do not allow IS constraints from the WHERE clause to be used by the
    ** right table of a LEFT JOIN.  Only constraints in the ON clause are
    ** allowed */
    if( (pSrc->fg.jointype & JT_LEFT)!=0
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
     && (eOp & (WO_IS|WO_ISNULL))!=0
    ){
      testcase( eOp & WO_IS );
      testcase( eOp & WO_ISNULL );
      continue;
    }

    if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
      pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
    }else{
      pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;







|




<

<
<







2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427

2428


2429
2430
2431
2432
2433
2434
2435
    }
    if( pTerm->prereqRight & pNew->maskSelf ) continue;

    /* Do not allow the upper bound of a LIKE optimization range constraint
    ** to mix with a lower range bound from some other source */
    if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;

    /* Do not allow constraints from the WHERE clause to be used by the
    ** right table of a LEFT JOIN.  Only constraints in the ON clause are
    ** allowed */
    if( (pSrc->fg.jointype & JT_LEFT)!=0
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)

    ){


      continue;
    }

    if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){
      pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
    }else{
      pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;

Changes to test/where.test.

1374
1375
1376
1377
1378
1379
1380












1381
1382
1383
  CREATE INDEX t191a ON t1(a);
  CREATE INDEX t191b ON t1(b);
  CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);

  EXPLAIN QUERY PLAN
  SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
} {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/}














finish_test







>
>
>
>
>
>
>
>
>
>
>
>



1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
  CREATE INDEX t191a ON t1(a);
  CREATE INDEX t191b ON t1(b);
  CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);

  EXPLAIN QUERY PLAN
  SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
} {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/}

# 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a]
# Index on expressions leads to an incorrect answer for a LEFT JOIN
#
do_execsql_test where-20.0 {
  CREATE TABLE t201(x);
  CREATE TABLE t202(y, z);
  INSERT INTO t201 VALUES('key');
  INSERT INTO t202 VALUES('key', -1);
  CREATE INDEX t202i ON t202(y, ifnull(z, 0));
  SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0;
} {0}


finish_test