/ Check-in [e7b9bc67]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | leftjoin-or-fix
Files: files | file ages | folders
SHA1: e7b9bc678ecb75c594d9d3ade12a99a8d551cdc9
User & Date: dan 2016-10-26 15:46:09
Context
2016-10-26
16:05
If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. Fix for ticket [34a579141b2c5ac] check-in: ec9dab80 user: dan tags: trunk
15:46
If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. Closed-Leaf check-in: e7b9bc67 user: dan tags: leftjoin-or-fix
13:58
Merge the SQLITE_ENABLE_URI_00_ERROR compile-time option. check-in: 86675ae0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

4856
4857
4858
4859
4860
4861
4862

4863
4864
4865
4866
4867
4868
4869


4870
4871
4872
4873
4874
4875
4876
    if( pLevel->addrLikeRep ){
      sqlite3VdbeAddOp2(v, OP_DecrJumpZero, (int)(pLevel->iLikeRepCntr>>1),
                        pLevel->addrLikeRep);
      VdbeCoverage(v);
    }
#endif
    if( pLevel->iLeftJoin ){

      addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v);
      assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
           || (pLoop->wsFlags & WHERE_INDEXED)!=0 );
      if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);
      }
      if( pLoop->wsFlags & WHERE_INDEXED ){


        sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur);
      }
      if( pLevel->op==OP_Return ){
        sqlite3VdbeAddOp2(v, OP_Gosub, pLevel->p1, pLevel->addrFirst);
      }else{
        sqlite3VdbeGoto(v, pLevel->addrFirst);
      }







>

<
|
|


|
>
>







4856
4857
4858
4859
4860
4861
4862
4863
4864

4865
4866
4867
4868
4869
4870
4871
4872
4873
4874
4875
4876
4877
4878
    if( pLevel->addrLikeRep ){
      sqlite3VdbeAddOp2(v, OP_DecrJumpZero, (int)(pLevel->iLikeRepCntr>>1),
                        pLevel->addrLikeRep);
      VdbeCoverage(v);
    }
#endif
    if( pLevel->iLeftJoin ){
      int ws = pLoop->wsFlags;
      addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v);

      assert( (ws & WHERE_IDX_ONLY)==0 || (ws & WHERE_INDEXED)!=0 );
      if( (ws & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);
      }
      if( (ws & WHERE_INDEXED) 
       || ((ws & WHERE_MULTI_OR) && pLevel->u.pCovidx) 
      ){
        sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur);
      }
      if( pLevel->op==OP_Return ){
        sqlite3VdbeAddOp2(v, OP_Gosub, pLevel->p1, pLevel->addrFirst);
      }else{
        sqlite3VdbeGoto(v, pLevel->addrFirst);
      }

Changes to test/whereD.test.

332
333
334
335
336
337
338
339













































































340
} {3 7 11 search 7}
do_searchcount_test 6.6.3 {
  SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6 
} {11 3 7 search 7}
do_searchcount_test 6.6.4 {
  SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1
} {7 11 3 search 7}














































































finish_test








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

332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
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
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
} {3 7 11 search 7}
do_searchcount_test 6.6.3 {
  SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6 
} {11 3 7 search 7}
do_searchcount_test 6.6.4 {
  SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1
} {7 11 3 search 7}

#-------------------------------------------------------------------------
#
do_execsql_test 7.0 {
  CREATE TABLE y1(a, b);
  CREATE TABLE y2(x, y);
  CREATE INDEX y2xy ON y2(x, y);
  INSERT INTO y1 VALUES(1, 1);
  INSERT INTO y2 VALUES(3, 3);
}

do_execsql_test 7.1 {
  SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b))
} {1 1 {} {}}

do_execsql_test 7.3 {
  CREATE TABLE foo (Id INTEGER PRIMARY KEY, fa INTEGER, fb INTEGER); 
  CREATE TABLE bar (Id INTEGER PRIMARY KEY, ba INTEGER, bb INTEGER);

  INSERT INTO foo VALUES(1, 1, 1);
  INSERT INTO foo VALUES(2, 1, 2);
  INSERT INTO foo VALUES(3, 1, 3);
  INSERT INTO foo VALUES(4, 1, 4);
  INSERT INTO foo VALUES(5, 1, 5);
  INSERT INTO foo VALUES(6, 1, 6);
  INSERT INTO foo VALUES(7, 1, 7);
  INSERT INTO foo VALUES(8, 1, 8);
  INSERT INTO foo VALUES(9, 1, 9);

  INSERT INTO bar VALUES(NULL, 1, 1);
  INSERT INTO bar VALUES(NULL, 2, 2);
  INSERT INTO bar VALUES(NULL, 3, 3);
  INSERT INTO bar VALUES(NULL, 1, 4);
  INSERT INTO bar VALUES(NULL, 2, 5);
  INSERT INTO bar VALUES(NULL, 3, 6);
  INSERT INTO bar VALUES(NULL, 1, 7);
  INSERT INTO bar VALUES(NULL, 2, 8);
  INSERT INTO bar VALUES(NULL, 3, 9);
}

do_execsql_test 7.4 {
  SELECT 
    bar.Id, bar.ba, bar.bb, foo.fb
    FROM foo LEFT JOIN bar
           ON (bar.ba = 1 AND bar.bb = foo.fb)
           OR (bar.ba = 5 AND bar.bb = foo.fb);
} {
  1 1 1 1 
  {} {} {} 2 
  {} {} {} 3 
  4 1 4 4 
  {} {} {} 5 
  {} {} {} 6 
  7 1 7 7 
  {} {} {} 8 
  {} {} {} 9
}

do_execsql_test 7.5 {
  CREATE INDEX idx_bar ON bar(ba, bb);
  SELECT 
    bar.Id, bar.ba, bar.bb, foo.fb
    FROM foo LEFT JOIN bar
           ON (bar.ba = 1 AND bar.bb = foo.fb)
           OR (bar.ba = 5 AND bar.bb = foo.fb);
} {
  1 1 1 1 
  {} {} {} 2 
  {} {} {} 3 
  4 1 4 4 
  {} {} {} 5 
  {} {} {} 6 
  7 1 7 7 
  {} {} {} 8 
  {} {} {} 9
}


finish_test