SQLite

Check-in [564b8fe794]
Login

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

Overview
Comment:Fix problems with reverse order sorting and indexes in the LIKE optimization.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | like-opt-fix
Files: files | file ages | folders
SHA1: 564b8fe79475d7584a21078e6098840b8ce6a6e1
User & Date: drh 2015-03-07 02:51:59.332
Context
2015-03-07
03:02
Remove some code that is commented out. (check-in: 55ff429177 user: drh tags: like-opt-fix)
02:51
Fix problems with reverse order sorting and indexes in the LIKE optimization. (check-in: 564b8fe794 user: drh tags: like-opt-fix)
00:57
Refactor some jump opcodes in the VDBE. Add JumpZeroIncr and DecrJumpZero. Fix the LIKE optimization to work with DESC sort order. (check-in: 26cb5145bf user: drh tags: like-opt-fix)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
3366
3367
3368
3369
3370
3371
3372


3373


3374
3375
3376
3377
3378
3379
3380

      pRangeEnd = pLoop->aLTerm[j++];
      nExtraReg = 1;
      if( pRangeStart
       && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0
       && (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
      ){
        pLevel->iLikeRepCntr = ++pParse->nMem;


        sqlite3VdbeAddOp2(v, OP_Integer, 0, pLevel->iLikeRepCntr);


        VdbeComment((v, "LIKE loop counter"));
        pLevel->addrLikeRep = sqlite3VdbeCurrentAddr(v);
      }
      if( pRangeStart==0
       && (j = pIdx->aiColumn[nEq])>=0 
       && pIdx->pTable->aCol[j].notNull==0
      ){








>
>
|
>
>








3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384

      pRangeEnd = pLoop->aLTerm[j++];
      nExtraReg = 1;
      if( pRangeStart
       && (pRangeStart->wtFlags & TERM_LIKEOPT)!=0
       && (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0
      ){
        pLevel->iLikeRepCntr = ++pParse->nMem;
        testcase( bRev );
        testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
        sqlite3VdbeAddOp2(v, OP_Integer,
                          bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
                          pLevel->iLikeRepCntr);
        VdbeComment((v, "LIKE loop counter"));
        pLevel->addrLikeRep = sqlite3VdbeCurrentAddr(v);
      }
      if( pRangeStart==0
       && (j = pIdx->aiColumn[nEq])>=0 
       && pIdx->pTable->aCol[j].notNull==0
      ){
︙︙
3397
3398
3399
3400
3401
3402
3403

3404
3405
3406

3407
3408
3409
3410
3411
3412
3413

    ** start and end terms (pRangeStart and pRangeEnd).
    */
    if( (nEq<pIdx->nKeyCol && bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC))
     || (bRev && pIdx->nKeyCol==nEq)
    ){
      SWAP(WhereTerm *, pRangeEnd, pRangeStart);
      SWAP(u8, bSeekPastNull, bStopAtNull);

      if( pLevel->addrLikeRep ){
        sqlite3VdbeChangeP1(v, pLevel->addrLikeRep-1, 1);
      }

    }

    testcase( pRangeStart && (pRangeStart->eOperator & WO_LE)!=0 );
    testcase( pRangeStart && (pRangeStart->eOperator & WO_GE)!=0 );
    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 );
    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_GE)!=0 );
    startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);








>



>








3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419

    ** start and end terms (pRangeStart and pRangeEnd).
    */
    if( (nEq<pIdx->nKeyCol && bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC))
     || (bRev && pIdx->nKeyCol==nEq)
    ){
      SWAP(WhereTerm *, pRangeEnd, pRangeStart);
      SWAP(u8, bSeekPastNull, bStopAtNull);
#if 0
      if( pLevel->addrLikeRep ){
        sqlite3VdbeChangeP1(v, pLevel->addrLikeRep-1, 1);
      }
#endif
    }

    testcase( pRangeStart && (pRangeStart->eOperator & WO_LE)!=0 );
    testcase( pRangeStart && (pRangeStart->eOperator & WO_GE)!=0 );
    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 );
    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_GE)!=0 );
    startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);
︙︙
6678
6679
6680
6681
6682
6683
6684

6685
6686



6687
6688
6689
6690
6691
6692
6693
6694

    if( pLevel->addrSkip ){
      sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrSkip);
      VdbeComment((v, "next skip-scan on %s", pLoop->u.btree.pIndex->zName));
      sqlite3VdbeJumpHere(v, pLevel->addrSkip);
      sqlite3VdbeJumpHere(v, pLevel->addrSkip-2);
    }
    if( pLevel->addrLikeRep ){

      sqlite3VdbeAddOp2(v,
           pLevel->op==OP_Prev ? OP_DecrJumpZero : OP_JumpZeroIncr,



           pLevel->iLikeRepCntr, pLevel->addrLikeRep);
      VdbeCoverage(v);
    }
    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 ){








>
|
|
>
>
>
|








6684
6685
6686
6687
6688
6689
6690
6691
6692
6693
6694
6695
6696
6697
6698
6699
6700
6701
6702
6703
6704

    if( pLevel->addrSkip ){
      sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrSkip);
      VdbeComment((v, "next skip-scan on %s", pLoop->u.btree.pIndex->zName));
      sqlite3VdbeJumpHere(v, pLevel->addrSkip);
      sqlite3VdbeJumpHere(v, pLevel->addrSkip-2);
    }
    if( pLevel->addrLikeRep ){
      int op;
      if( sqlite3VdbeGetOp(v, pLevel->addrLikeRep-1)->p1 ){
        op = OP_DecrJumpZero;
      }else{
        op = OP_JumpZeroIncr;
      }
      sqlite3VdbeAddOp2(v, op, pLevel->iLikeRepCntr, pLevel->addrLikeRep);
      VdbeCoverage(v);
    }
    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 ){
︙︙
Changes to test/like3.test.
︙︙
65
66
67
68
69
70
71


























72
73
74

} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-3.1 {
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC;
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-3.1ck {
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC;
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}




























finish_test








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




65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-3.1 {
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC;
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-3.1ck {
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC;
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-3.2 {
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-3.2ck {
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}

do_execsql_test like3-4.0 {
  CREATE TABLE t4(x TEXT COLLATE nocase);
  CREATE INDEX t4x ON t4(x DESC);
  INSERT INTO t4(x) SELECT x FROM t3;
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-4.1 {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC;
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-4.1ck {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC;
} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
do_execsql_test like3-4.2 {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
do_execsql_test like3-4.2ck {
  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}



finish_test