SQLite

Check-in [74cb0b032f]
Login

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

Overview
Comment:Fix the LIKE optimization so that it finds BLOB entries in addition to text entries. Ticket [05f43be8fdda9f].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 74cb0b032fcf598537fae04412771450124ae712
User & Date: drh 2015-03-07 13:56:48.044
Context
2015-03-09
10:40
Increase the version number to 3.8.9 (check-in: e5da5e7d5d user: drh tags: trunk)
2015-03-07
20:32
Fix another problem with the LIKE optimization. (check-in: 465bfc72d2 user: drh tags: like-opt-fix)
13:56
Fix the LIKE optimization so that it finds BLOB entries in addition to text entries. Ticket [05f43be8fdda9f]. (check-in: 74cb0b032f user: drh tags: trunk)
12:58
New test cases for LIKE and GLOB with BLOB left-hand side values. (Closed-Leaf check-in: 50fa3c5fae user: drh tags: like-opt-fix)
2015-03-06
04:37
Clearification of some documentation text. Added requirements marks. (check-in: 8c1e85aab9 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/func.c.
1646
1647
1648
1649
1650
1651
1652





1653
1654
1655
1656
1657
1658
1659

/*
** pExpr points to an expression which implements a function.  If
** it is appropriate to apply the LIKE optimization to that function
** then set aWc[0] through aWc[2] to the wildcard characters and
** return TRUE.  If the function is not a LIKE-style function then
** return FALSE.





*/
int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
  FuncDef *pDef;
  if( pExpr->op!=TK_FUNCTION 
   || !pExpr->x.pList 
   || pExpr->x.pList->nExpr!=2
  ){







>
>
>
>
>







1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664

/*
** pExpr points to an expression which implements a function.  If
** it is appropriate to apply the LIKE optimization to that function
** then set aWc[0] through aWc[2] to the wildcard characters and
** return TRUE.  If the function is not a LIKE-style function then
** return FALSE.
**
** *pIsNocase is set to true if uppercase and lowercase are equivalent for
** the function (default for LIKE).  If the function makes the distinction
** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
** false.
*/
int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
  FuncDef *pDef;
  if( pExpr->op!=TK_FUNCTION 
   || !pExpr->x.pList 
   || pExpr->x.pList->nExpr!=2
  ){
Changes to src/select.c.
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
  if( pSelect->iLimit ){
    int addr1, addr2;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;
    }
    addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit); VdbeCoverage(v);
    sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
    addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addr1);
    sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
    sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
    sqlite3VdbeJumpHere(v, addr2);
  }
}

/*
** Add code to implement the OFFSET
*/
static void codeOffset(







|






<
<
|
<


|







559
560
561
562
563
564
565
566
567
568
569
570
571
572


573

574
575
576
577
578
579
580
581
582
583
  if( pSort->sortFlags & SORTFLAG_UseSorter ){
    op = OP_SorterInsert;
  }else{
    op = OP_IdxInsert;
  }
  sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
  if( pSelect->iLimit ){
    int addr;
    int iLimit;
    if( pSelect->iOffset ){
      iLimit = pSelect->iOffset+1;
    }else{
      iLimit = pSelect->iLimit;
    }


    addr = sqlite3VdbeAddOp3(v, OP_IfNotZero, iLimit, 0, -1); VdbeCoverage(v);

    sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
    sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
    sqlite3VdbeJumpHere(v, addr);
  }
}

/*
** Add code to implement the OFFSET
*/
static void codeOffset(
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
  }

  /* Jump to the end of the loop if the LIMIT is reached.  Except, if
  ** there is a sorter, in which case the sorter has already limited
  ** the output for us.
  */
  if( pSort==0 && p->iLimit ){
    sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v);
  }
}

/*
** Allocate a KeyInfo object sufficient for an index of N key columns and
** X extra columns.
*/







|







966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
  }

  /* Jump to the end of the loop if the LIMIT is reached.  Except, if
  ** there is a sorter, in which case the sorter has already limited
  ** the output for us.
  */
  if( pSort==0 && p->iLimit ){
    sqlite3VdbeAddOp2(v, OP_DecrJumpZero, p->iLimit, iBreak); VdbeCoverage(v);
  }
}

/*
** Allocate a KeyInfo object sufficient for an index of N key columns and
** X extra columns.
*/
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
      }else if( n>=0 && p->nSelectRow>(u64)n ){
        p->nSelectRow = n;
      }
    }else{
      sqlite3ExprCode(pParse, p->pLimit, iLimit);
      sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeCoverage(v);
      VdbeComment((v, "LIMIT counter"));
      sqlite3VdbeAddOp2(v, OP_IfZero, iLimit, iBreak); VdbeCoverage(v);
    }
    if( p->pOffset ){
      p->iOffset = iOffset = ++pParse->nMem;
      pParse->nMem++;   /* Allocate an extra register for limit+offset */
      sqlite3ExprCode(pParse, p->pOffset, iOffset);
      sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset); VdbeCoverage(v);
      VdbeComment((v, "OFFSET counter"));







|







1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
      }else if( n>=0 && p->nSelectRow>(u64)n ){
        p->nSelectRow = n;
      }
    }else{
      sqlite3ExprCode(pParse, p->pLimit, iLimit);
      sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit); VdbeCoverage(v);
      VdbeComment((v, "LIMIT counter"));
      sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, iBreak); VdbeCoverage(v);
    }
    if( p->pOffset ){
      p->iOffset = iOffset = ++pParse->nMem;
      pParse->nMem++;   /* Allocate an extra register for limit+offset */
      sqlite3ExprCode(pParse, p->pOffset, iOffset);
      sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset); VdbeCoverage(v);
      VdbeComment((v, "OFFSET counter"));
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055

  /* Output the single row in Current */
  addrCont = sqlite3VdbeMakeLabel(v);
  codeOffset(v, regOffset, addrCont);
  selectInnerLoop(pParse, p, p->pEList, iCurrent,
      0, 0, pDest, addrCont, addrBreak);
  if( regLimit ){
    sqlite3VdbeAddOp3(v, OP_IfZero, regLimit, addrBreak, -1);
    VdbeCoverage(v);
  }
  sqlite3VdbeResolveLabel(v, addrCont);

  /* Execute the recursive SELECT taking the single row in Current as
  ** the value for the recursive-table. Store the results in the Queue.
  */







|







2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052

  /* Output the single row in Current */
  addrCont = sqlite3VdbeMakeLabel(v);
  codeOffset(v, regOffset, addrCont);
  selectInnerLoop(pParse, p, p->pEList, iCurrent,
      0, 0, pDest, addrCont, addrBreak);
  if( regLimit ){
    sqlite3VdbeAddOp2(v, OP_DecrJumpZero, regLimit, addrBreak);
    VdbeCoverage(v);
  }
  sqlite3VdbeResolveLabel(v, addrCont);

  /* Execute the recursive SELECT taking the single row in Current as
  ** the value for the recursive-table. Store the results in the Queue.
  */
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
      if( rc ){
        goto multi_select_end;
      }
      p->pPrior = 0;
      p->iLimit = pPrior->iLimit;
      p->iOffset = pPrior->iOffset;
      if( p->iLimit ){
        addr = sqlite3VdbeAddOp1(v, OP_IfZero, p->iLimit); VdbeCoverage(v);
        VdbeComment((v, "Jump ahead if LIMIT reached"));
      }
      explainSetInteger(iSub2, pParse->iNextSelectId);
      rc = sqlite3Select(pParse, p, &dest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;







|







2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
      if( rc ){
        goto multi_select_end;
      }
      p->pPrior = 0;
      p->iLimit = pPrior->iLimit;
      p->iOffset = pPrior->iOffset;
      if( p->iLimit ){
        addr = sqlite3VdbeAddOp1(v, OP_IfNot, p->iLimit); VdbeCoverage(v);
        VdbeComment((v, "Jump ahead if LIMIT reached"));
      }
      explainSetInteger(iSub2, pParse->iNextSelectId);
      rc = sqlite3Select(pParse, p, &dest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
      break;
    }
  }

  /* Jump to the end of the loop if the LIMIT is reached.
  */
  if( p->iLimit ){
    sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v);
  }

  /* Generate the subroutine return
  */
  sqlite3VdbeResolveLabel(v, iContinue);
  sqlite3VdbeAddOp1(v, OP_Return, regReturn);








|







2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
      break;
    }
  }

  /* Jump to the end of the loop if the LIMIT is reached.
  */
  if( p->iLimit ){
    sqlite3VdbeAddOp2(v, OP_DecrJumpZero, p->iLimit, iBreak); VdbeCoverage(v);
  }

  /* Generate the subroutine return
  */
  sqlite3VdbeResolveLabel(v, iContinue);
  sqlite3VdbeAddOp1(v, OP_Return, regReturn);

Changes to src/vdbe.c.
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
}
#endif

/* Opcode: String8 * P2 * P4 *
** Synopsis: r[P2]='P4'
**
** P4 points to a nul terminated UTF-8 string. This opcode is transformed 
** into a String before it is executed for the first time.  During
** this transformation, the length of string P4 is computed and stored
** as the P1 parameter.
*/
case OP_String8: {         /* same as TK_STRING, out2-prerelease */
  assert( pOp->p4.z!=0 );
  pOp->opcode = OP_String;
  pOp->p1 = sqlite3Strlen30(pOp->p4.z);







|







1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
}
#endif

/* Opcode: String8 * P2 * P4 *
** Synopsis: r[P2]='P4'
**
** P4 points to a nul terminated UTF-8 string. This opcode is transformed 
** into a String opcode before it is executed for the first time.  During
** this transformation, the length of string P4 is computed and stored
** as the P1 parameter.
*/
case OP_String8: {         /* same as TK_STRING, out2-prerelease */
  assert( pOp->p4.z!=0 );
  pOp->opcode = OP_String;
  pOp->p1 = sqlite3Strlen30(pOp->p4.z);
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053





1054
1055
1056
1057
1058
1059
1060
1061







1062
1063
1064
1065
1066
1067
1068
#endif
  if( pOp->p1>db->aLimit[SQLITE_LIMIT_LENGTH] ){
    goto too_big;
  }
  /* Fall through to the next case, OP_String */
}
  
/* Opcode: String P1 P2 * P4 *
** Synopsis: r[P2]='P4' (len=P1)
**
** The string value P4 of length P1 (bytes) is stored in register P2.





*/
case OP_String: {          /* out2-prerelease */
  assert( pOp->p4.z!=0 );
  pOut->flags = MEM_Str|MEM_Static|MEM_Term;
  pOut->z = pOp->p4.z;
  pOut->n = pOp->p1;
  pOut->enc = encoding;
  UPDATE_MAX_BLOBSIZE(pOut);







  break;
}

/* Opcode: Null P1 P2 P3 * *
** Synopsis:  r[P2..P3]=NULL
**
** Write a NULL into registers P2.  If P3 greater than P2, then also write







|



>
>
>
>
>








>
>
>
>
>
>
>







1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
#endif
  if( pOp->p1>db->aLimit[SQLITE_LIMIT_LENGTH] ){
    goto too_big;
  }
  /* Fall through to the next case, OP_String */
}
  
/* Opcode: String P1 P2 P3 P4 P5
** Synopsis: r[P2]='P4' (len=P1)
**
** The string value P4 of length P1 (bytes) is stored in register P2.
**
** If P5!=0 and the content of register P3 is greater than zero, then
** the datatype of the register P2 is converted to BLOB.  The content is
** the same sequence of bytes, it is merely interpreted as a BLOB instead
** of a string, as if it had been CAST.
*/
case OP_String: {          /* out2-prerelease */
  assert( pOp->p4.z!=0 );
  pOut->flags = MEM_Str|MEM_Static|MEM_Term;
  pOut->z = pOp->p4.z;
  pOut->n = pOp->p1;
  pOut->enc = encoding;
  UPDATE_MAX_BLOBSIZE(pOut);
  if( pOp->p5 ){
    assert( pOp->p3>0 );
    assert( pOp->p3<=(p->nMem-p->nCursor) );
    pIn3 = &aMem[pOp->p3];
    assert( pIn3->flags & MEM_Int );
    if( pIn3->u.i ) pOut->flags = MEM_Blob|MEM_Static|MEM_Term;
  }
  break;
}

/* Opcode: Null P1 P2 P3 * *
** Synopsis:  r[P2..P3]=NULL
**
** Write a NULL into registers P2.  If P3 greater than P2, then also write
5569
5570
5571
5572
5573
5574
5575

5576

5577
5578
5579

5580
5581
5582
5583
5584
5585
5586
  break;
}
#endif /* SQLITE_OMIT_AUTOINCREMENT */

/* Opcode: IfPos P1 P2 * * *
** Synopsis: if r[P1]>0 goto P2
**

** If the value of register P1 is 1 or greater, jump to P2.

**
** It is illegal to use this instruction on a register that does
** not contain an integer.  An assertion fault will result if you try.

*/
case OP_IfPos: {        /* jump, in1 */
  pIn1 = &aMem[pOp->p1];
  assert( pIn1->flags&MEM_Int );
  VdbeBranchTaken( pIn1->u.i>0, 2);
  if( pIn1->u.i>0 ){
     pc = pOp->p2 - 1;







>
|
>

|
<
>







5581
5582
5583
5584
5585
5586
5587
5588
5589
5590
5591
5592

5593
5594
5595
5596
5597
5598
5599
5600
  break;
}
#endif /* SQLITE_OMIT_AUTOINCREMENT */

/* Opcode: IfPos P1 P2 * * *
** Synopsis: if r[P1]>0 goto P2
**
** Register P1 must contain an integer.
** If the value of register P1 is 1 or greater, jump to P2 and
** add the literal value P3 to register P1.
**
** If the initial value of register P1 is less than 1, then the

** value is unchanged and control passes through to the next instruction.
*/
case OP_IfPos: {        /* jump, in1 */
  pIn1 = &aMem[pOp->p1];
  assert( pIn1->flags&MEM_Int );
  VdbeBranchTaken( pIn1->u.i>0, 2);
  if( pIn1->u.i>0 ){
     pc = pOp->p2 - 1;
5601
5602
5603
5604
5605
5606
5607
5608
5609
5610
5611
5612

5613
5614
5615
5616


5617















5618
5619
5620
5621
5622
5623
5624


















5625
5626
5627
5628
5629
5630
5631
  VdbeBranchTaken(pIn1->u.i<0, 2);
  if( pIn1->u.i<0 ){
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: IfZero P1 P2 P3 * *
** Synopsis: r[P1]+=P3, if r[P1]==0 goto P2
**
** The register P1 must contain an integer.  Add literal P3 to the
** value in register P1.  If the result is exactly 0, jump to P2. 

*/
case OP_IfZero: {        /* jump, in1 */
  pIn1 = &aMem[pOp->p1];
  assert( pIn1->flags&MEM_Int );


  pIn1->u.i += pOp->p3;















  VdbeBranchTaken(pIn1->u.i==0, 2);
  if( pIn1->u.i==0 ){
     pc = pOp->p2 - 1;
  }
  break;
}



















/* Opcode: AggStep * P2 P3 P4 P5
** Synopsis: accum=r[P3] step(r[P2@P5])
**
** Execute the step function for an aggregate.  The
** function has P5 arguments.   P4 is a pointer to the FuncDef
** structure that specifies the function.  Use register
** P3 as the accumulator.







|
|

|
|
>

|


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







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







5615
5616
5617
5618
5619
5620
5621
5622
5623
5624
5625
5626
5627
5628
5629
5630
5631
5632
5633
5634
5635
5636
5637
5638
5639
5640
5641
5642
5643
5644
5645
5646
5647
5648
5649
5650
5651
5652
5653
5654
5655
5656
5657
5658
5659
5660
5661
5662
5663
5664
5665
5666
5667
5668
5669
5670
5671
5672
5673
5674
5675
5676
5677
5678
5679
5680
5681
  VdbeBranchTaken(pIn1->u.i<0, 2);
  if( pIn1->u.i<0 ){
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: IfNotZero P1 P2 P3 * *
** Synopsis: if r[P1]!=0 then r[P1]+=P3, goto P2
**
** Register P1 must contain an integer.  If the content of register P1 is
** initially nonzero, then add P3 to P1 and jump to P2.  If register P1 is
** initially zero, leave it unchanged and fall through.
*/
case OP_IfNotZero: {        /* jump, in1 */
  pIn1 = &aMem[pOp->p1];
  assert( pIn1->flags&MEM_Int );
  VdbeBranchTaken(pIn1->u.i<0, 2);
  if( pIn1->u.i ){
     pIn1->u.i += pOp->p3;
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: DecrJumpZero P1 P2 * * *
** Synopsis: if (--r[P1])==0 goto P2
**
** Register P1 must hold an integer.  Decrement the value in register P1
** then jump to P2 if the new value is exactly zero.
*/
case OP_DecrJumpZero: {      /* jump, in1 */
  pIn1 = &aMem[pOp->p1];
  assert( pIn1->flags&MEM_Int );
  pIn1->u.i--;
  VdbeBranchTaken(pIn1->u.i==0, 2);
  if( pIn1->u.i==0 ){
     pc = pOp->p2 - 1;
  }
  break;
}


/* Opcode: JumpZeroIncr P1 P2 * * *
** Synopsis: if (r[P1]++)==0 ) goto P2
**
** The register P1 must contain an integer.  If register P1 is initially
** zero, then jump to P2.  Increment register P1 regardless of whether or
** not the jump is taken.
*/
case OP_JumpZeroIncr: {        /* jump, in1 */
  pIn1 = &aMem[pOp->p1];
  assert( pIn1->flags&MEM_Int );
  VdbeBranchTaken(pIn1->u.i==0, 2);
  if( (pIn1->u.i++)==0 ){
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: AggStep * P2 P3 P4 P5
** Synopsis: accum=r[P3] step(r[P2@P5])
**
** Execute the step function for an aggregate.  The
** function has P5 arguments.   P4 is a pointer to the FuncDef
** structure that specifies the function.  Use register
** P3 as the accumulator.
Changes to src/where.c.
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
** This is true even if this routine fails to allocate a new WhereTerm.
**
** WARNING:  This routine might reallocate the space used to store
** WhereTerms.  All pointers to WhereTerms should be invalidated after
** calling this routine.  Such pointers may be reinitialized by referencing
** the pWC->a[] array.
*/
static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){
  WhereTerm *pTerm;
  int idx;
  testcase( wtFlags & TERM_VIRTUAL );
  if( pWC->nTerm>=pWC->nSlot ){
    WhereTerm *pOld = pWC->a;
    sqlite3 *db = pWC->pWInfo->pParse->db;
    pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );







|







198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
** This is true even if this routine fails to allocate a new WhereTerm.
**
** WARNING:  This routine might reallocate the space used to store
** WhereTerms.  All pointers to WhereTerms should be invalidated after
** calling this routine.  Such pointers may be reinitialized by referencing
** the pWC->a[] array.
*/
static int whereClauseInsert(WhereClause *pWC, Expr *p, u16 wtFlags){
  WhereTerm *pTerm;
  int idx;
  testcase( wtFlags & TERM_VIRTUAL );
  if( pWC->nTerm>=pWC->nSlot ){
    WhereTerm *pOld = pWC->a;
    sqlite3 *db = pWC->pWInfo->pParse->db;
    pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );
623
624
625
626
627
628
629
630




631
632
633
634
635
636
637
#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
/*
** Check to see if the given expression is a LIKE or GLOB operator that
** can be optimized using inequality constraints.  Return TRUE if it is
** so and false if not.
**
** In order for the operator to be optimizible, the RHS must be a string
** literal that does not begin with a wildcard.  




*/
static int isLikeOrGlob(
  Parse *pParse,    /* Parsing and code generating context */
  Expr *pExpr,      /* Test this expression */
  Expr **ppPrefix,  /* Pointer to TK_STRING expression with pattern prefix */
  int *pisComplete, /* True if the only wildcard is % in the last character */
  int *pnoCase      /* True if uppercase is equivalent to lowercase */







|
>
>
>
>







623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
/*
** Check to see if the given expression is a LIKE or GLOB operator that
** can be optimized using inequality constraints.  Return TRUE if it is
** so and false if not.
**
** In order for the operator to be optimizible, the RHS must be a string
** literal that does not begin with a wildcard.  The LHS must be a column
** that may only be NULL, a string, or a BLOB, never a number. (This means
** that virtual tables cannot participate in the LIKE optimization.)  If the
** collating sequence for the column on the LHS must be appropriate for
** the operator.
*/
static int isLikeOrGlob(
  Parse *pParse,    /* Parsing and code generating context */
  Expr *pExpr,      /* Test this expression */
  Expr **ppPrefix,  /* Pointer to TK_STRING expression with pattern prefix */
  int *pisComplete, /* True if the only wildcard is % in the last character */
  int *pnoCase      /* True if uppercase is equivalent to lowercase */
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
#ifdef SQLITE_EBCDIC
  if( *pnoCase ) return 0;
#endif
  pList = pExpr->x.pList;
  pLeft = pList->a[1].pExpr;
  if( pLeft->op!=TK_COLUMN 
   || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
   || IsVirtual(pLeft->pTab)
  ){
    /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
    ** be the name of an indexed column with TEXT affinity. */
    return 0;
  }
  assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */








|







656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
#ifdef SQLITE_EBCDIC
  if( *pnoCase ) return 0;
#endif
  pList = pExpr->x.pList;
  pLeft = pList->a[1].pExpr;
  if( pLeft->op!=TK_COLUMN 
   || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT 
   || IsVirtual(pLeft->pTab)  /* Value might be numeric */
  ){
    /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
    ** be the name of an indexed column with TEXT affinity. */
    return 0;
  }
  assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */

1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
  WhereMaskSet *pMaskSet;          /* Set of table index masks */
  Expr *pExpr;                     /* The expression to be analyzed */
  Bitmask prereqLeft;              /* Prerequesites of the pExpr->pLeft */
  Bitmask prereqAll;               /* Prerequesites of pExpr */
  Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* LIKE/GLOB distinguishes case */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWInfo->pParse;  /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */

  if( db->mallocFailed ){
    return;
  }







|







1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
  WhereMaskSet *pMaskSet;          /* Set of table index masks */
  Expr *pExpr;                     /* The expression to be analyzed */
  Bitmask prereqLeft;              /* Prerequesites of the pExpr->pLeft */
  Bitmask prereqAll;               /* Prerequesites of pExpr */
  Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* uppercase equivalent to lowercase */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWInfo->pParse;  /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */

  if( db->mallocFailed ){
    return;
  }
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251



1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262

1263
1264
1265















1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
  }
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */

#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
  /* Add constraints to reduce the search space on a LIKE or GLOB
  ** operator.
  **
  ** A like pattern of the form "x LIKE 'abc%'" is changed into constraints
  **
  **          x>='abc' AND x<'abd' AND x LIKE 'abc%'
  **
  ** The last character of the prefix "abc" is incremented to form the
  ** termination condition "abd".



  */
  if( pWC->op==TK_AND 
   && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase)
  ){
    Expr *pLeft;       /* LHS of LIKE/GLOB operator */
    Expr *pStr2;       /* Copy of pStr1 - RHS of LIKE/GLOB operator */
    Expr *pNewExpr1;
    Expr *pNewExpr2;
    int idxNew1;
    int idxNew2;
    Token sCollSeqName;  /* Name of collating sequence */


    pLeft = pExpr->x.pList->a[1].pExpr;
    pStr2 = sqlite3ExprDup(db, pStr1, 0);















    if( !db->mallocFailed ){
      u8 c, *pC;       /* Last character before the first wildcard */
      pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1];
      c = *pC;
      if( noCase ){
        /* The point is to increment the last character before the first
        ** wildcard.  But if we increment '@', that will push it into the
        ** alphabetic range where case conversions will mess up the 
        ** inequality.  To avoid this, make sure to also run the full
        ** LIKE on all candidate expressions by clearing the isComplete flag
        */
        if( c=='A'-1 ) isComplete = 0;
        c = sqlite3UpperToLower[c];
      }
      *pC = c + 1;
    }
    sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
    sCollSeqName.n = 6;
    pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
    pNewExpr1 = sqlite3PExpr(pParse, TK_GE, 
           sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName),
           pStr1, 0);
    transferJoinMarkings(pNewExpr1, pExpr);
    idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
    testcase( idxNew1==0 );
    exprAnalyze(pSrc, pWC, idxNew1);
    pNewExpr2 = sqlite3ExprDup(db, pLeft, 0);
    pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
           sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName),
           pStr2, 0);
    transferJoinMarkings(pNewExpr2, pExpr);
    idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
    testcase( idxNew2==0 );
    exprAnalyze(pSrc, pWC, idxNew2);
    pTerm = &pWC->a[idxTerm];
    if( isComplete ){
      markTermAsChild(pWC, idxNew1, idxTerm);
      markTermAsChild(pWC, idxNew2, idxTerm);
    }







|

|


|
>
>
>











>



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



















|



|







|







1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
  }
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */

#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
  /* Add constraints to reduce the search space on a LIKE or GLOB
  ** operator.
  **
  ** A like pattern of the form "x LIKE 'aBc%'" is changed into constraints
  **
  **          x>='ABC' AND x<'abd' AND x LIKE 'aBc%'
  **
  ** The last character of the prefix "abc" is incremented to form the
  ** termination condition "abd".  If case is not significant (the default
  ** for LIKE) then the lower-bound is made all uppercase and the upper-
  ** bound is made all lowercase so that the bounds also work when comparing
  ** BLOBs.
  */
  if( pWC->op==TK_AND 
   && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase)
  ){
    Expr *pLeft;       /* LHS of LIKE/GLOB operator */
    Expr *pStr2;       /* Copy of pStr1 - RHS of LIKE/GLOB operator */
    Expr *pNewExpr1;
    Expr *pNewExpr2;
    int idxNew1;
    int idxNew2;
    Token sCollSeqName;  /* Name of collating sequence */
    const u16 wtFlags = TERM_LIKEOPT | TERM_VIRTUAL | TERM_DYNAMIC;

    pLeft = pExpr->x.pList->a[1].pExpr;
    pStr2 = sqlite3ExprDup(db, pStr1, 0);

    /* Convert the lower bound to upper-case and the upper bound to
    ** lower-case (upper-case is less than lower-case in ASCII) so that
    ** the range constraints also work for BLOBs
    */
    if( noCase && !pParse->db->mallocFailed ){
      int i;
      char c;
      pTerm->wtFlags |= TERM_LIKE;
      for(i=0; (c = pStr1->u.zToken[i])!=0; i++){
        pStr1->u.zToken[i] = sqlite3Toupper(c);
        pStr2->u.zToken[i] = sqlite3Tolower(c);
      }
    }

    if( !db->mallocFailed ){
      u8 c, *pC;       /* Last character before the first wildcard */
      pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1];
      c = *pC;
      if( noCase ){
        /* The point is to increment the last character before the first
        ** wildcard.  But if we increment '@', that will push it into the
        ** alphabetic range where case conversions will mess up the 
        ** inequality.  To avoid this, make sure to also run the full
        ** LIKE on all candidate expressions by clearing the isComplete flag
        */
        if( c=='A'-1 ) isComplete = 0;
        c = sqlite3UpperToLower[c];
      }
      *pC = c + 1;
    }
    sCollSeqName.z = noCase ? "NOCASE" : "BINARY";
    sCollSeqName.n = 6;
    pNewExpr1 = sqlite3ExprDup(db, pLeft, 0);
    pNewExpr1 = sqlite3PExpr(pParse, TK_GE,
           sqlite3ExprAddCollateToken(pParse,pNewExpr1,&sCollSeqName),
           pStr1, 0);
    transferJoinMarkings(pNewExpr1, pExpr);
    idxNew1 = whereClauseInsert(pWC, pNewExpr1, wtFlags);
    testcase( idxNew1==0 );
    exprAnalyze(pSrc, pWC, idxNew1);
    pNewExpr2 = sqlite3ExprDup(db, pLeft, 0);
    pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
           sqlite3ExprAddCollateToken(pParse,pNewExpr2,&sCollSeqName),
           pStr2, 0);
    transferJoinMarkings(pNewExpr2, pExpr);
    idxNew2 = whereClauseInsert(pWC, pNewExpr2, wtFlags);
    testcase( idxNew2==0 );
    exprAnalyze(pSrc, pWC, idxNew2);
    pTerm = &pWC->a[idxTerm];
    if( isComplete ){
      markTermAsChild(pWC, idxNew1, idxTerm);
      markTermAsChild(pWC, idxNew2, idxTerm);
    }
2465
2466
2467
2468
2469
2470
2471



















2472
2473

2474
2475
2476
2477
2478



2479

2480
2481
2482
2483
2484
2485

2486
2487
2488
2489
2490
2491
2492
** Disabling a term causes that term to not be tested in the inner loop
** of the join.  Disabling is an optimization.  When terms are satisfied
** by indices, we disable them to prevent redundant tests in the inner
** loop.  We would get the correct results if nothing were ever disabled,
** but joins might run a little slower.  The trick is to disable as much
** as we can without disabling too much.  If we disabled in (1), we'd get
** the wrong answer.  See ticket #813.



















*/
static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){

  if( pTerm
      && (pTerm->wtFlags & TERM_CODED)==0
      && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
      && (pLevel->notReady & pTerm->prereqAll)==0
  ){



    pTerm->wtFlags |= TERM_CODED;

    if( pTerm->iParent>=0 ){
      WhereTerm *pOther = &pTerm->pWC->a[pTerm->iParent];
      if( (--pOther->nChild)==0 ){
        disableTerm(pLevel, pOther);
      }
    }

  }
}

/*
** Code an OP_Affinity opcode to apply the column affinity string zAff
** to the n registers starting at base. 
**







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


>
|




>
>
>
|
>
|
|
|
|
<
<
>







2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530


2531
2532
2533
2534
2535
2536
2537
2538
** Disabling a term causes that term to not be tested in the inner loop
** of the join.  Disabling is an optimization.  When terms are satisfied
** by indices, we disable them to prevent redundant tests in the inner
** loop.  We would get the correct results if nothing were ever disabled,
** but joins might run a little slower.  The trick is to disable as much
** as we can without disabling too much.  If we disabled in (1), we'd get
** the wrong answer.  See ticket #813.
**
** If all the children of a term are disabled, then that term is also
** automatically disabled.  In this way, terms get disabled if derived
** virtual terms are tested first.  For example:
**
**      x GLOB 'abc*' AND x>='abc' AND x<'acd'
**      \___________/     \______/     \_____/
**         parent          child1       child2
**
** Only the parent term was in the original WHERE clause.  The child1
** and child2 terms were added by the LIKE optimization.  If both of
** the virtual child terms are valid, then testing of the parent can be 
** skipped.
**
** Usually the parent term is marked as TERM_CODED.  But if the parent
** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead.
** The TERM_LIKECOND marking indicates that the term should be coded inside
** a conditional such that is only evaluated on the second pass of a
** LIKE-optimization loop, when scanning BLOBs instead of strings.
*/
static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
  int nLoop = 0;
  while( pTerm
      && (pTerm->wtFlags & TERM_CODED)==0
      && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
      && (pLevel->notReady & pTerm->prereqAll)==0
  ){
    if( nLoop && (pTerm->wtFlags & TERM_LIKE)!=0 ){
      pTerm->wtFlags |= TERM_LIKECOND;
    }else{
      pTerm->wtFlags |= TERM_CODED;
    }
    if( pTerm->iParent<0 ) break;
    pTerm = &pTerm->pWC->a[pTerm->iParent];
    pTerm->nChild--;
    if( pTerm->nChild!=0 ) break;


    nLoop++;
  }
}

/*
** Code an OP_Affinity opcode to apply the column affinity string zAff
** to the n registers starting at base. 
**
2962
2963
2964
2965
2966
2967
2968


















2969

2970
2971
2972
2973
2974
2975
2976
      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
  );
}
#else
# define addScanStatus(a, b, c, d) ((void)d)
#endif






















/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
static Bitmask codeOneLoopStart(
  WhereInfo *pWInfo,   /* Complete information about the WHERE clause */







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







3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
  );
}
#else
# define addScanStatus(a, b, c, d) ((void)d)
#endif

/*
** Look at the last instruction coded.  If that instruction is OP_String8
** and if pLoop->iLikeRepCntr is non-zero, then change the P3 to be
** pLoop->iLikeRepCntr and set P5.
**
** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
** expression: "x>='ABC' AND x<'abd'".  But this requires that the range
** scan loop run twice, once for strings and a second time for BLOBs.
** The OP_String opcodes on the second pass convert the upper and lower
** bound string contants to blobs.  This routine makes the necessary changes
** to the OP_String opcodes for that to happen.
*/
static void whereLikeOptimizationStringFixup(Vdbe *v, WhereLevel *pLevel){
  VdbeOp *pOp;
  pOp = sqlite3VdbeGetOp(v, -1);
  if( pLevel->iLikeRepCntr && pOp->opcode==OP_String8 ){
    pOp->p3 = pLevel->iLikeRepCntr;
    pOp->p5 = 1;
  }
}

/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
static Bitmask codeOneLoopStart(
  WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
3296
3297
3298
3299
3300
3301
3302













3303
3304
3305
3306
3307
3308
3309
    if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
      pRangeStart = pLoop->aLTerm[j++];
      nExtraReg = 1;
    }
    if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
      pRangeEnd = pLoop->aLTerm[j++];
      nExtraReg = 1;













      if( pRangeStart==0
       && (j = pIdx->aiColumn[nEq])>=0 
       && pIdx->pTable->aCol[j].notNull==0
      ){
        bSeekPastNull = 1;
      }
    }







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







3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
    if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
      pRangeStart = pLoop->aLTerm[j++];
      nExtraReg = 1;
    }
    if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
      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
      ){
        bSeekPastNull = 1;
      }
    }
3338
3339
3340
3341
3342
3343
3344

3345
3346
3347
3348
3349
3350
3351
    start_constraints = pRangeStart || nEq>0;

    /* Seek the index cursor to the start of the range. */
    nConstraint = nEq;
    if( pRangeStart ){
      Expr *pRight = pRangeStart->pExpr->pRight;
      sqlite3ExprCode(pParse, pRight, regBase+nEq);

      if( (pRangeStart->wtFlags & TERM_VNULL)==0
       && sqlite3ExprCanBeNull(pRight)
      ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
        VdbeCoverage(v);
      }
      if( zStartAff ){







>







3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
    start_constraints = pRangeStart || nEq>0;

    /* Seek the index cursor to the start of the range. */
    nConstraint = nEq;
    if( pRangeStart ){
      Expr *pRight = pRangeStart->pExpr->pRight;
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      whereLikeOptimizationStringFixup(v, pLevel);
      if( (pRangeStart->wtFlags & TERM_VNULL)==0
       && sqlite3ExprCanBeNull(pRight)
      ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
        VdbeCoverage(v);
      }
      if( zStartAff ){
3383
3384
3385
3386
3387
3388
3389

3390
3391
3392
3393
3394
3395
3396
    ** range (if any).
    */
    nConstraint = nEq;
    if( pRangeEnd ){
      Expr *pRight = pRangeEnd->pExpr->pRight;
      sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
      sqlite3ExprCode(pParse, pRight, regBase+nEq);

      if( (pRangeEnd->wtFlags & TERM_VNULL)==0
       && sqlite3ExprCanBeNull(pRight)
      ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
        VdbeCoverage(v);
      }
      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE







>







3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
    ** range (if any).
    */
    nConstraint = nEq;
    if( pRangeEnd ){
      Expr *pRight = pRangeEnd->pExpr->pRight;
      sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
      sqlite3ExprCode(pParse, pRight, regBase+nEq);
      whereLikeOptimizationStringFixup(v, pLevel);
      if( (pRangeEnd->wtFlags & TERM_VNULL)==0
       && sqlite3ExprCanBeNull(pRight)
      ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
        VdbeCoverage(v);
      }
      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
3773
3774
3775
3776
3777
3778
3779

3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793





3794

3795
3796
3797
3798
3799
3800
3801
#endif

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.
  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;

    testcase( pTerm->wtFlags & TERM_VIRTUAL );
    testcase( pTerm->wtFlags & TERM_CODED );
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
      testcase( pWInfo->untestedTerms==0
               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
      pWInfo->untestedTerms = 1;
      continue;
    }
    pE = pTerm->pExpr;
    assert( pE!=0 );
    if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
      continue;
    }





    sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);

    pTerm->wtFlags |= TERM_CODED;
  }

  /* Insert code to test for implied constraints based on transitivity
  ** of the "==" operator.
  **
  ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"







>














>
>
>
>
>

>







3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
3884
3885
3886
3887
3888
#endif

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.
  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;
    int skipLikeAddr = 0;
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
    testcase( pTerm->wtFlags & TERM_CODED );
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
      testcase( pWInfo->untestedTerms==0
               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
      pWInfo->untestedTerms = 1;
      continue;
    }
    pE = pTerm->pExpr;
    assert( pE!=0 );
    if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
      continue;
    }
    if( pTerm->wtFlags & TERM_LIKECOND ){
      assert( pLevel->iLikeRepCntr>0 );
      skipLikeAddr = sqlite3VdbeAddOp1(v, OP_IfNot, pLevel->iLikeRepCntr);
      VdbeCoverage(v);
    }
    sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
    if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
    pTerm->wtFlags |= TERM_CODED;
  }

  /* Insert code to test for implied constraints based on transitivity
  ** of the "==" operator.
  **
  ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
6590
6591
6592
6593
6594
6595
6596










6597
6598
6599
6600
6601
6602
6603
    }
    sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
    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->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);







>
>
>
>
>
>
>
>
>
>







6677
6678
6679
6680
6681
6682
6683
6684
6685
6686
6687
6688
6689
6690
6691
6692
6693
6694
6695
6696
6697
6698
6699
6700
    }
    sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
    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 ){
        sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);
Changes to src/whereInt.h.
65
66
67
68
69
70
71


72
73
74
75
76
77
78
  int iIdxCur;          /* The VDBE cursor used to access pIdx */
  int addrBrk;          /* Jump here to break out of the loop */
  int addrNxt;          /* Jump here to start the next IN combination */
  int addrSkip;         /* Jump here for next iteration of skip-scan */
  int addrCont;         /* Jump here to continue with the next loop cycle */
  int addrFirst;        /* First instruction of interior of the loop */
  int addrBody;         /* Beginning of the body of this loop */


  u8 iFrom;             /* Which entry in the FROM clause */
  u8 op, p3, p5;        /* Opcode, P3 & P5 of the opcode that ends the loop */
  int p1, p2;           /* Operands of the opcode used to ends the loop */
  union {               /* Information that depends on pWLoop->wsFlags */
    struct {
      int nIn;              /* Number of entries in aInLoop[] */
      struct InLoop {







>
>







65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
  int iIdxCur;          /* The VDBE cursor used to access pIdx */
  int addrBrk;          /* Jump here to break out of the loop */
  int addrNxt;          /* Jump here to start the next IN combination */
  int addrSkip;         /* Jump here for next iteration of skip-scan */
  int addrCont;         /* Jump here to continue with the next loop cycle */
  int addrFirst;        /* First instruction of interior of the loop */
  int addrBody;         /* Beginning of the body of this loop */
  int iLikeRepCntr;     /* LIKE range processing counter register */
  int addrLikeRep;      /* LIKE range processing address */
  u8 iFrom;             /* Which entry in the FROM clause */
  u8 op, p3, p5;        /* Opcode, P3 & P5 of the opcode that ends the loop */
  int p1, p2;           /* Operands of the opcode used to ends the loop */
  union {               /* Information that depends on pWLoop->wsFlags */
    struct {
      int nIn;              /* Number of entries in aInLoop[] */
      struct InLoop {
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
  union {
    int leftColumn;         /* Column number of X in "X <op> <expr>" */
    WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
    WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
  } u;
  LogEst truthProb;       /* Probability of truth for this expression */
  u16 eOperator;          /* A WO_xx value describing <op> */
  u8 wtFlags;             /* TERM_xxx bit flags.  See below */
  u8 nChild;              /* Number of children that must disable us */
  WhereClause *pWC;       /* The clause this term is part of */
  Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
  Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
};

/*







|







251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
  union {
    int leftColumn;         /* Column number of X in "X <op> <expr>" */
    WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
    WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
  } u;
  LogEst truthProb;       /* Probability of truth for this expression */
  u16 eOperator;          /* A WO_xx value describing <op> */
  u16 wtFlags;            /* TERM_xxx bit flags.  See below */
  u8 nChild;              /* Number of children that must disable us */
  WhereClause *pWC;       /* The clause this term is part of */
  Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
  Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
};

/*
271
272
273
274
275
276
277



278
279
280
281
282
283
284
#define TERM_ANDINFO    0x20   /* Need to free the WhereTerm.u.pAndInfo obj */
#define TERM_OR_OK      0x40   /* Used during OR-clause processing */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
#  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
#else
#  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
#endif




/*
** An instance of the WhereScan object is used as an iterator for locating
** terms in the WHERE clause that are useful to the query planner.
*/
struct WhereScan {
  WhereClause *pOrigWC;      /* Original, innermost WhereClause */







>
>
>







273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
#define TERM_ANDINFO    0x20   /* Need to free the WhereTerm.u.pAndInfo obj */
#define TERM_OR_OK      0x40   /* Used during OR-clause processing */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
#  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
#else
#  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
#endif
#define TERM_LIKEOPT    0x100  /* Virtual terms from the LIKE optimization */
#define TERM_LIKECOND   0x200  /* Conditionally this LIKE operator term */
#define TERM_LIKE       0x400  /* The original LIKE operator */

/*
** An instance of the WhereScan object is used as an iterator for locating
** terms in the WHERE clause that are useful to the query planner.
*/
struct WhereScan {
  WhereClause *pOrigWC;      /* Original, innermost WhereClause */
Changes to test/analyze3.test.
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}}
do_eqp_test analyze3-2.3 {
  SELECT count(a) FROM t1 WHERE b LIKE '%a'
} {0 0 0 {SCAN TABLE t1}}

do_test analyze3-2.4 {
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
} {101 0 100}
do_test analyze3-2.5 {
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
} {999 999 100}

do_test analyze3-2.4 {
  set like "a%"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 100}
do_test analyze3-2.5 {
  set like "%a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}
do_test analyze3-2.6 {
  set like "a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 0}
do_test analyze3-2.7 {
  set like "ab"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {11 0 0}
do_test analyze3-2.8 {
  set like "abc"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {2 0 1}
do_test analyze3-2.9 {
  set like "a_c"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 10}


#-------------------------------------------------------------------------
# This block of tests checks that statements are correctly marked as
# expired when the values bound to any parameters that may affect the 
# query plan are modified.
#







|




|


|
|



|


|
|


|
|


|
|


|







277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}}
do_eqp_test analyze3-2.3 {
  SELECT count(a) FROM t1 WHERE b LIKE '%a'
} {0 0 0 {SCAN TABLE t1}}

do_test analyze3-2.4 {
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
} {102 0 100}
do_test analyze3-2.5 {
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
} {999 999 100}

do_test analyze3-2.6 {
  set like "a%"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {102 0 100}
do_test analyze3-2.7 {
  set like "%a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}
do_test analyze3-2.8 {
  set like "a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {102 0 0}
do_test analyze3-2.9 {
  set like "ab"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {12 0 0}
do_test analyze3-2.10 {
  set like "abc"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {3 0 1}
do_test analyze3-2.11 {
  set like "a_c"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {102 0 10}


#-------------------------------------------------------------------------
# This block of tests checks that statements are correctly marked as
# expired when the values bound to any parameters that may affect the 
# query plan are modified.
#
Changes to test/like.test.
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
      SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.5 {
    count {
      SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.6 {
    count {
      SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.10 {
    execsql {







|







745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
      SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.5 {
    count {
      SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 4 like 0}
  do_test like-10.6 {
    count {
      SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.10 {
    execsql {
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
      SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.14 {
    count {
      SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.15 {
    count {
      SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
}








|







786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
      SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.14 {
    count {
      SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 4 like 0}
  do_test like-10.15 {
    count {
      SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
    }
  } {12 123 scan 5 like 6}
}

Added test/like3.test.
































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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
101
102
103
104
105
106
107
108
109
110
111
112
# 2015-03-06
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIKE and GLOB operators and
# in particular the optimizations that occur to help those operators
# run faster and that those optimizations work correctly when there
# are both strings and blobs being tested.
#
# Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following
# SQL was not working correctly:
#
#     CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase);
#     INSERT INTO t1(x) VALUES(x'616263');
#     SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%';
#     SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%';
#
# This script verifies that it works right now.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test like3-1.1 {
  PRAGMA encoding=UTF8;
  CREATE TABLE t1(a,b TEXT COLLATE nocase);
  INSERT INTO t1(a,b)
     VALUES(1,'abc'),
           (2,'ABX'),
           (3,'BCD'),
           (4,x'616263'),
           (5,x'414258'),
           (6,x'424344');
  CREATE INDEX t1ba ON t1(b,a);

  SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a;
} {1 abc 2 ABX 4 abc 5 ABX}
do_execsql_test like3-1.2 {
  SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a;
} {1 abc 2 ABX 4 abc 5 ABX}

do_execsql_test like3-2.0 {
  CREATE TABLE t2(a, b TEXT);
  INSERT INTO t2 SELECT a, b FROM t1;
  CREATE INDEX t2ba ON t2(b,a);
  SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a;
} {1 abc 4 abc}
do_execsql_test like3-2.1 {
  SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;
} {1 abc 4 abc}
do_execsql_test like3-2.2 {
  SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*'
} {4 abc}
do_execsql_test like3-2.3 {
  SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*'
} {4 abc}
do_execsql_test like3-2.4 {
  SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162'
} {4 abc}
do_execsql_test like3-2.5 {
  SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162'
} {4 abc}

do_execsql_test like3-3.0 {
  CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase);
  INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
  INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3;
  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x;
} {'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
Changes to test/where8.test.
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

do_test where8-1.3 { 
  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
} {IX X II 0 0 6}

do_test where8-1.4 { 
  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
} {IX X III II 0 0 9}

do_test where8-1.5 { 
  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
} {IX X V IV 0 0 9}

do_test where8-1.6 { 
  execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
} {I III 0 1}

do_test where8-1.7 { 
  execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }







|



|







62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

do_test where8-1.3 { 
  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
} {IX X II 0 0 6}

do_test where8-1.4 { 
  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
} {IX X III II 0 0 10}

do_test where8-1.5 { 
  execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
} {IX X V IV 0 0 10}

do_test where8-1.6 { 
  execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
} {I III 0 1}

do_test where8-1.7 { 
  execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }