/ Check-in [0dc4cb93]
Login

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

Overview
Comment:Candidate fix for the optimizer problem described in ticket [b7c8682cc17f3] which can causes a LEFT JOIN to be changed into a INNER JOIN if there are OR terms in the WHERE clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0dc4cb935514131c99172175d57feec3a1743aa9
User & Date: drh 2012-03-09 22:02:08
Context
2012-03-15
22:54
Add the sqlite3_db_readonly() API to trunk. check-in: fed134a5 user: drh tags: trunk
21:28
Add the sqlite3_db_readonly() interface. This is still tentative, pending a closer look at other ideas to accomplish the same thing. Closed-Leaf check-in: 254f99ea user: drh tags: db-readonly-api
2012-03-09
22:02
Candidate fix for the optimizer problem described in ticket [b7c8682cc17f3] which can causes a LEFT JOIN to be changed into a INNER JOIN if there are OR terms in the WHERE clause. check-in: 0dc4cb93 user: drh tags: trunk
2012-03-08
20:28
On Windows, make sure the returned test current directory value does not contain any backslashes. check-in: efee39e6 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4338
4339
4340
4341
4342
4343
4344





4345
4346







4347

4348


4349
4350
4351
4352
4353
4354
4355
....
4383
4384
4385
4386
4387
4388
4389


4390

4391
4392
4393
4394
4395
4396
4397
    }
    iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);

    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
    ** Then for every term xN, evaluate as the subexpression: xN AND z
    ** That way, terms in y that are factored into the disjunction will
    ** be picked up by the recursive calls to sqlite3WhereBegin() below.





    */
    if( pWC->nTerm>1 ){







      pAndExpr = sqlite3ExprAlloc(pParse->db, TK_AND, 0, 0);

      pAndExpr->pRight = pWhere;


    }

    for(ii=0; ii<pOrWc->nTerm; ii++){
      WhereTerm *pOrTerm = &pOrWc->a[ii];
      if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
        WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
        Expr *pOrExpr = pOrTerm->pExpr;
................................................................................
          if( pSubWInfo->untestedTerms ) untestedTerms = 1;

          /* Finish the loop through table entries that match term pOrTerm. */
          sqlite3WhereEnd(pSubWInfo);
        }
      }
    }


    sqlite3DbFree(pParse->db, pAndExpr);

    sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
    sqlite3VdbeResolveLabel(v, iLoopBody);

    if( pWInfo->nLevel>1 ) sqlite3StackFree(pParse->db, pOrTab);
    if( !untestedTerms ) disableTerm(pLevel, pTerm);
  }else







>
>
>
>
>


>
>
>
>
>
>
>
|
>
|
>
>







 







>
>
|
>







4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348
4349
4350
4351
4352
4353
4354
4355
4356
4357
4358
4359
4360
4361
4362
4363
4364
4365
4366
4367
4368
4369
4370
....
4398
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
    }
    iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);

    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
    ** Then for every term xN, evaluate as the subexpression: xN AND z
    ** 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 ii;
      for(ii=0; ii<pWC->nTerm; ii++){
        Expr *pExpr = pWC->a[ii].pExpr;
        if( ExprHasProperty(pExpr, EP_FromJoin) ) continue;
        if( pWC->a[ii].wtFlags & (TERM_VIRTUAL|TERM_ORINFO) ) continue;
        if( (pWC->a[ii].eOperator & WO_ALL)==0 ) continue;
        pExpr = sqlite3ExprDup(pParse->db, pExpr, 0);
        pAndExpr = sqlite3ExprAnd(pParse->db, pAndExpr, pExpr);
      }
      if( pAndExpr ){
        pAndExpr = sqlite3PExpr(pParse, TK_AND, 0, pAndExpr, 0);
      }
    }

    for(ii=0; ii<pOrWc->nTerm; ii++){
      WhereTerm *pOrTerm = &pOrWc->a[ii];
      if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
        WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
        Expr *pOrExpr = pOrTerm->pExpr;
................................................................................
          if( pSubWInfo->untestedTerms ) untestedTerms = 1;

          /* Finish the loop through table entries that match term pOrTerm. */
          sqlite3WhereEnd(pSubWInfo);
        }
      }
    }
    if( pAndExpr ){
      pAndExpr->pLeft = 0;
      sqlite3ExprDelete(pParse->db, pAndExpr);
    }
    sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
    sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
    sqlite3VdbeResolveLabel(v, iLoopBody);

    if( pWInfo->nLevel>1 ) sqlite3StackFree(pParse->db, pOrTab);
    if( !untestedTerms ) disableTerm(pLevel, pTerm);
  }else

Changes to test/where7.test.

23335
23336
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348
    FROM t302 JOIN t301 ON t302.c8 = t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~5 rows)} 
  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test







|






23335
23336
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348
    FROM t302 JOIN t301 ON t302.c8 = t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test

Changes to test/where9.test.

360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
...
852
853
854
855
856
857
858


859


















860
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
................................................................................
} {79 81 scan 0 sort 1}
do_test where9-7.3.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81}






















finish_test







|









|







 







|
|







 







>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
...
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~3 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~3 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
................................................................................
} {79 81 scan 0 sort 1}
do_test where9-7.3.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81}

# Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
# "Incorrect result from LEFT JOIN with OR in the WHERE clause"
#
do_test where9-8.1 {
  db eval {
    CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
    CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
    CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
    
    INSERT INTO t81 VALUES(2,3,4,5);
    INSERT INTO t81 VALUES(3,4,5,6);
    INSERT INTO t82 VALUES(2,4);
    INSERT INTO t83 VALUES(5,55);
    
    SELECT *
      FROM t81 LEFT JOIN 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