SQLite

Check-in [eca35871c3]
Login

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

Overview
Comment:Merge trunk fixes for "x IN (?)" handling.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | orderby-planning
Files: files | file ages | folders
SHA1: eca35871c34374ca9189c7c9b6d490ac3c30357f
User & Date: drh 2014-03-20 20:56:49.315
Context
2014-03-21
15:24
New test case for block-sorting. (check-in: e70cfa28aa user: drh tags: orderby-planning)
2014-03-20
20:56
Merge trunk fixes for "x IN (?)" handling. (check-in: eca35871c3 user: drh tags: orderby-planning)
19:04
Remove a testcase() that is now always true due to the "x IN (?)" optimization. Add an ALWAYS() around a conditional in the parser that cannot be false. (check-in: d5a1530bdc user: drh tags: trunk)
2014-03-19
23:42
Merge the vdbesort.c optimization from trunk. (check-in: e4bfffb988 user: drh tags: orderby-planning)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
29
30
31
32
33
34
35

36
37
38
39
40
41
42
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
char sqlite3ExprAffinity(Expr *pExpr){
  int op;
  pExpr = sqlite3ExprSkipCollate(pExpr);

  op = pExpr->op;
  if( op==TK_SELECT ){
    assert( pExpr->flags&EP_xIsSelect );
    return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){







>







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
char sqlite3ExprAffinity(Expr *pExpr){
  int op;
  pExpr = sqlite3ExprSkipCollate(pExpr);
  if( pExpr->flags & EP_Generic ) return SQLITE_AFF_NONE;
  op = pExpr->op;
  if( op==TK_SELECT ){
    assert( pExpr->flags&EP_xIsSelect );
    return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
61
62
63
64
65
66
67
68




69
70
71
72
73
74
75
** Set the collating sequence for expression pExpr to be the collating
** sequence named by pToken.   Return a pointer to a new Expr node that
** implements the COLLATE operator.
**
** If a memory allocation error occurs, that fact is recorded in pParse->db
** and the pExpr parameter is returned unchanged.
*/
Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr *pExpr, Token *pCollName){




  if( pCollName->n>0 ){
    Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLLATE, pCollName, 1);
    if( pNew ){
      pNew->pLeft = pExpr;
      pNew->flags |= EP_Collate|EP_Skip;
      pExpr = pNew;
    }







|
>
>
>
>







62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
** Set the collating sequence for expression pExpr to be the collating
** sequence named by pToken.   Return a pointer to a new Expr node that
** implements the COLLATE operator.
**
** If a memory allocation error occurs, that fact is recorded in pParse->db
** and the pExpr parameter is returned unchanged.
*/
Expr *sqlite3ExprAddCollateToken(
  Parse *pParse,           /* Parsing context */
  Expr *pExpr,             /* Add the "COLLATE" clause to this expression */
  const Token *pCollName   /* Name of collating sequence */
){
  if( pCollName->n>0 ){
    Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLLATE, pCollName, 1);
    if( pNew ){
      pNew->pLeft = pExpr;
      pNew->flags |= EP_Collate|EP_Skip;
      pExpr = pNew;
    }
114
115
116
117
118
119
120

121
122
123
124
125
126
127
*/
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
  sqlite3 *db = pParse->db;
  CollSeq *pColl = 0;
  Expr *p = pExpr;
  while( p ){
    int op = p->op;

    if( op==TK_CAST || op==TK_UPLUS ){
      p = p->pLeft;
      continue;
    }
    if( op==TK_COLLATE || (op==TK_REGISTER && p->op2==TK_COLLATE) ){
      pColl = sqlite3GetCollSeq(pParse, ENC(db), 0, p->u.zToken);
      break;







>







119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
*/
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
  sqlite3 *db = pParse->db;
  CollSeq *pColl = 0;
  Expr *p = pExpr;
  while( p ){
    int op = p->op;
    if( p->flags & EP_Generic ) break;
    if( op==TK_CAST || op==TK_UPLUS ){
      p = p->pLeft;
      continue;
    }
    if( op==TK_COLLATE || (op==TK_REGISTER && p->op2==TK_COLLATE) ){
      pColl = sqlite3GetCollSeq(pParse, ENC(db), 0, p->u.zToken);
      break;
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
    u32 savedNQueryLoop = pParse->nQueryLoop;
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    }else{
      testcase( pParse->nQueryLoop>0 );
      pParse->nQueryLoop = 0;
      if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
        eType = IN_INDEX_ROWID;
      }
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;







<







1630
1631
1632
1633
1634
1635
1636

1637
1638
1639
1640
1641
1642
1643
    u32 savedNQueryLoop = pParse->nQueryLoop;
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    }else{

      pParse->nQueryLoop = 0;
      if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
        eType = IN_INDEX_ROWID;
      }
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
Changes to src/parse.y.
1016
1017
1018
1019
1020
1021
1022



























1023
1024
1025
1026
1027
1028
1029
      **      expr1 NOT IN ()
      **
      ** simplify to constants 0 (false) and 1 (true), respectively,
      ** regardless of the value of expr1.
      */
      A.pExpr = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[N]);
      sqlite3ExprDelete(pParse->db, X.pExpr);



























    }else{
      A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
      if( A.pExpr ){
        A.pExpr->x.pList = Y;
        sqlite3ExprSetHeight(pParse, A.pExpr);
      }else{
        sqlite3ExprListDelete(pParse->db, Y);







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







1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
      **      expr1 NOT IN ()
      **
      ** simplify to constants 0 (false) and 1 (true), respectively,
      ** regardless of the value of expr1.
      */
      A.pExpr = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[N]);
      sqlite3ExprDelete(pParse->db, X.pExpr);
    }else if( Y->nExpr==1 ){
      /* Expressions of the form:
      **
      **      expr1 IN (?1)
      **      expr1 NOT IN (?2)
      **
      ** with exactly one value on the RHS can be simplified to something
      ** like this:
      **
      **      expr1 == ?1
      **      expr1 <> ?2
      **
      ** But, the RHS of the == or <> is marked with the EP_Generic flag
      ** so that it may not contribute to the computation of comparison
      ** affinity or the collating sequence to use for comparison.  Otherwise,
      ** the semantics would be subtly different from IN or NOT IN.
      */
      Expr *pRHS = Y->a[0].pExpr;
      Y->a[0].pExpr = 0;
      sqlite3ExprListDelete(pParse->db, Y);
      /* pRHS cannot be NULL because a malloc error would have been detected
      ** before now and control would have never reached this point */
      if( ALWAYS(pRHS) ){
        pRHS->flags &= ~EP_Collate;
        pRHS->flags |= EP_Generic;
      }
      A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
    }else{
      A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
      if( A.pExpr ){
        A.pExpr->x.pList = Y;
        sqlite3ExprSetHeight(pParse, A.pExpr);
      }else{
        sqlite3ExprListDelete(pParse->db, Y);
Changes to src/shell.c.
1191
1192
1193
1194
1195
1196
1197
1198

1199
1200
1201
1202
1203
1204
1205
static void explain_data_prepare(struct callback_data *p, sqlite3_stmt *pSql){
  const char *zSql;               /* The text of the SQL statement */
  const char *z;                  /* Used to check if this is an EXPLAIN */
  int *abYield = 0;               /* True if op is an OP_Yield */
  int nAlloc = 0;                 /* Allocated size of p->aiIndent[], abYield */
  int iOp;                        /* Index of operation in p->aiIndent[] */

  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", 0 };

  const char *azYield[] = { "Yield", "SeekLt", "SeekGt", "RowSetRead", "Rewind", 0 };
  const char *azGoto[] = { "Goto", 0 };

  /* Try to figure out if this is really an EXPLAIN statement. If this
  ** cannot be verified, return early.  */
  zSql = sqlite3_sql(pSql);
  if( zSql==0 ) return;







|
>







1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
static void explain_data_prepare(struct callback_data *p, sqlite3_stmt *pSql){
  const char *zSql;               /* The text of the SQL statement */
  const char *z;                  /* Used to check if this is an EXPLAIN */
  int *abYield = 0;               /* True if op is an OP_Yield */
  int nAlloc = 0;                 /* Allocated size of p->aiIndent[], abYield */
  int iOp;                        /* Index of operation in p->aiIndent[] */

  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext",
                           "NextIfOpen", "PrevIfOpen", 0 };
  const char *azYield[] = { "Yield", "SeekLt", "SeekGt", "RowSetRead", "Rewind", 0 };
  const char *azGoto[] = { "Goto", 0 };

  /* Try to figure out if this is really an EXPLAIN statement. If this
  ** cannot be verified, return early.  */
  zSql = sqlite3_sql(pSql);
  if( zSql==0 ) return;
Changes to src/sqliteInt.h.
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
#define EP_Agg       0x000002 /* Contains one or more aggregate functions */
#define EP_Resolved  0x000004 /* IDs have been resolved to COLUMNs */
#define EP_Error     0x000008 /* Expression contains one or more errors */
#define EP_Distinct  0x000010 /* Aggregate function with DISTINCT keyword */
#define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */
#define EP_DblQuoted 0x000040 /* token.z was originally in "..." */
#define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */
#define EP_Collate   0x000100 /* Tree contains a TK_COLLATE opeartor */
      /* unused      0x000200 */
#define EP_IntValue  0x000400 /* Integer value contained in u.iValue */
#define EP_xIsSelect 0x000800 /* x.pSelect is valid (otherwise x.pList is) */
#define EP_Skip      0x001000 /* COLLATE, AS, or UNLIKELY */
#define EP_Reduced   0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
#define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
#define EP_Static    0x008000 /* Held in memory not obtained from malloc() */
#define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */







|
|







1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
#define EP_Agg       0x000002 /* Contains one or more aggregate functions */
#define EP_Resolved  0x000004 /* IDs have been resolved to COLUMNs */
#define EP_Error     0x000008 /* Expression contains one or more errors */
#define EP_Distinct  0x000010 /* Aggregate function with DISTINCT keyword */
#define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */
#define EP_DblQuoted 0x000040 /* token.z was originally in "..." */
#define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */
#define EP_Collate   0x000100 /* Tree contains a TK_COLLATE operator */
#define EP_Generic   0x000200 /* Ignore COLLATE or affinity on this tree */
#define EP_IntValue  0x000400 /* Integer value contained in u.iValue */
#define EP_xIsSelect 0x000800 /* x.pSelect is valid (otherwise x.pList is) */
#define EP_Skip      0x001000 /* COLLATE, AS, or UNLIKELY */
#define EP_Reduced   0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
#define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
#define EP_Static    0x008000 /* Held in memory not obtained from malloc() */
#define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
#endif

const char *sqlite3ErrStr(int);
int sqlite3ReadSchema(Parse *pParse);
CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char*,int);
CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char*zName);
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr);
Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, Token*);
Expr *sqlite3ExprAddCollateString(Parse*,Expr*,const char*);
Expr *sqlite3ExprSkipCollate(Expr*);
int sqlite3CheckCollSeq(Parse *, CollSeq *);
int sqlite3CheckObjectName(Parse *, const char *);
void sqlite3VdbeSetChanges(sqlite3 *, int);
int sqlite3AddInt64(i64*,i64);
int sqlite3SubInt64(i64*,i64);







|







3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
#endif

const char *sqlite3ErrStr(int);
int sqlite3ReadSchema(Parse *pParse);
CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char*,int);
CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char*zName);
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr);
Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, const Token*);
Expr *sqlite3ExprAddCollateString(Parse*,Expr*,const char*);
Expr *sqlite3ExprSkipCollate(Expr*);
int sqlite3CheckCollSeq(Parse *, CollSeq *);
int sqlite3CheckObjectName(Parse *, const char *);
void sqlite3VdbeSetChanges(sqlite3 *, int);
int sqlite3AddInt64(i64*,i64);
int sqlite3SubInt64(i64*,i64);
Changes to src/vdbesort.c.
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
        **
        ** Alternatively, if pIter2 contains the smaller of the two values,
        ** set aTree[i] to its index and update pIter1. If vdbeSorterCompare()
        ** was actually called above, then pSorter->pUnpacked now contains
        ** a value equivalent to pIter2. So set pKey2 to NULL to prevent
        ** vdbeSorterCompare() from decoding pIter2 again.  */
        if( iRes<=0 ){
          pSorter->aTree[i] = (pIter1 - pSorter->aIter);
          pIter2 = &pSorter->aIter[ pSorter->aTree[i ^ 0x0001] ];
          pKey2 = pIter2->aKey;
        }else{
          if( pIter1->pFile ) pKey2 = 0;
          pSorter->aTree[i] = (pIter2 - pSorter->aIter);
          pIter1 = &pSorter->aIter[ pSorter->aTree[i ^ 0x0001] ];
        }

      }
      *pbEof = (pSorter->aIter[pSorter->aTree[1]].pFile==0);
    }
  }else{







|




|







1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
        **
        ** Alternatively, if pIter2 contains the smaller of the two values,
        ** set aTree[i] to its index and update pIter1. If vdbeSorterCompare()
        ** was actually called above, then pSorter->pUnpacked now contains
        ** a value equivalent to pIter2. So set pKey2 to NULL to prevent
        ** vdbeSorterCompare() from decoding pIter2 again.  */
        if( iRes<=0 ){
          pSorter->aTree[i] = (int)(pIter1 - pSorter->aIter);
          pIter2 = &pSorter->aIter[ pSorter->aTree[i ^ 0x0001] ];
          pKey2 = pIter2->aKey;
        }else{
          if( pIter1->pFile ) pKey2 = 0;
          pSorter->aTree[i] = (int)(pIter2 - pSorter->aIter);
          pIter1 = &pSorter->aIter[ pSorter->aTree[i ^ 0x0001] ];
        }

      }
      *pbEof = (pSorter->aIter[pSorter->aTree[1]].pFile==0);
    }
  }else{
Changes to src/where.c.
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
      pLevel->op = OP_Noop;
    }else if( bRev ){
      pLevel->op = OP_Prev;
    }else{
      pLevel->op = OP_Next;
    }
    pLevel->p1 = iIdxCur;
    assert( (WHERE_UNQ_WANTED>>16)==1 );
    pLevel->p3 = (pLoop->wsFlags>>16)&1;
    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }else{
      assert( pLevel->p5==0 );
    }
  }else








<
|







3207
3208
3209
3210
3211
3212
3213

3214
3215
3216
3217
3218
3219
3220
3221
      pLevel->op = OP_Noop;
    }else if( bRev ){
      pLevel->op = OP_Prev;
    }else{
      pLevel->op = OP_Next;
    }
    pLevel->p1 = iIdxCur;

    pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }else{
      assert( pLevel->p5==0 );
    }
  }else

4009
4010
4011
4012
4013
4014
4015


4016
4017
4018
4019
4020
4021
4022
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        /* "x IN (SELECT ...)":  TUNING: the SELECT returns 25 rows */
        nIn = 46;  assert( 46==sqlite3LogEst(25) );
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
      }


      pNew->rRun += nIn;
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul + nIn;
    }else if( pTerm->eOperator & (WO_EQ) ){
      assert(
        (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN|WHERE_SKIPSCAN))!=0
        || nInMul==0







>
>







4008
4009
4010
4011
4012
4013
4014
4015
4016
4017
4018
4019
4020
4021
4022
4023
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        /* "x IN (SELECT ...)":  TUNING: the SELECT returns 25 rows */
        nIn = 46;  assert( 46==sqlite3LogEst(25) );
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
      }
      assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
                        ** changes "x IN (?)" into "x=?". */
      pNew->rRun += nIn;
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul + nIn;
    }else if( pTerm->eOperator & (WO_EQ) ){
      assert(
        (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN|WHERE_SKIPSCAN))!=0
        || nInMul==0
Changes to test/in4.test.
154
155
156
157
158
159
160

















































































































































































161
162
} {}
do_test in4-3.11 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
} {1 1 1}
do_test in4-3.12 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
} {}


















































































































































































finish_test







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


154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
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
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
} {}
do_test in4-3.11 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
} {1 1 1}
do_test in4-3.12 {
  execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
} {}

# Tests for "... IN (?)" and "... NOT IN (?)".  In other words, tests
# for when the RHS of IN is a single expression.  This should work the
# same as the == and <> operators.
#
do_execsql_test in4-3.21 {
  SELECT * FROM t3 WHERE x=10 AND y IN (10);
} {10 10 10}
do_execsql_test in4-3.22 {
  SELECT * FROM t3 WHERE x IN (10) AND y=10;
} {10 10 10}
do_execsql_test in4-3.23 {
  SELECT * FROM t3 WHERE x IN (10) AND y IN (10);
} {10 10 10}
do_execsql_test in4-3.24 {
  SELECT * FROM t3 WHERE x=1 AND y NOT IN (10);
} {1 1 1}
do_execsql_test in4-3.25 {
  SELECT * FROM t3 WHERE x  NOT IN (10) AND y=1;
} {1 1 1}
do_execsql_test in4-3.26 {
  SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10);
} {1 1 1}

# The query planner recognizes that "x IN (?)" only generates a
# single match and can use this information to optimize-out ORDER BY
# clauses.
#
do_execsql_test in4-3.31 {
  DROP INDEX t3i1;
  CREATE UNIQUE INDEX t3xy ON t3(x,y);

  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x=10 AND A.y IN (10)
     AND B.x=1 AND B.y IN (1);
} {10 10 10 1 1 1 |}
do_execsql_test in4-3.32 {
  EXPLAIN QUERY PLAN
  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x=10 AND A.y IN (10)
     AND B.x=1 AND B.y IN (1);
} {~/B-TREE/}  ;# No separate sorting pass
do_execsql_test in4-3.33 {
  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x IN (10) AND A.y=10
     AND B.x IN (1) AND B.y=1;
} {10 10 10 1 1 1 |}
do_execsql_test in4-3.34 {
  EXPLAIN QUERY PLAN
  SELECT *, '|' FROM t3 A, t3 B
   WHERE A.x IN (10) AND A.y=10
     AND B.x IN (1) AND B.y=1;
} {~/B-TREE/}  ;# No separate sorting pass

# An expression of the form "x IN (?,?)" creates an ephemeral table to
# hold the list of values on the RHS.  But "x IN (?)" does not create
# an ephemeral table.
#
do_execsql_test in4-3.41 {
  SELECT * FROM t3 WHERE x IN (10,11);
} {10 10 10}
do_execsql_test in4-3.42 {
  EXPLAIN
  SELECT * FROM t3 WHERE x IN (10,11);
} {/OpenEphemeral/}
do_execsql_test in4-3.43 {
  SELECT * FROM t3 WHERE x IN (10);
} {10 10 10}
do_execsql_test in4-3.44 {
  EXPLAIN
  SELECT * FROM t3 WHERE x IN (10);
} {~/OpenEphemeral/}
do_execsql_test in4-3.45 {
  SELECT * FROM t3 WHERE x NOT IN (10,11);
} {1 1 1}
do_execsql_test in4-3.46 {
  EXPLAIN
  SELECT * FROM t3 WHERE x NOT IN (10,11);
} {/OpenEphemeral/}
do_execsql_test in4-3.47 {
  SELECT * FROM t3 WHERE x NOT IN (10);
} {1 1 1}
do_execsql_test in4-3.48 {
  EXPLAIN
  SELECT * FROM t3 WHERE x NOT IN (10);
} {~/OpenEphemeral/}

# Make sure that when "x IN (?)" is converted into "x==?" that collating
# sequence and affinity computations do not get messed up.
#
do_execsql_test in4-4.1 {
  CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c);
  INSERT INTO t4a VALUES('ABC','abc',1);
  INSERT INTO t4a VALUES('def','xyz',2);
  INSERT INTO t4a VALUES('ghi','ghi',3);
  SELECT c FROM t4a WHERE a=b ORDER BY c;
} {3}
do_execsql_test in4-4.2 {
  SELECT c FROM t4a WHERE b=a ORDER BY c;
} {1 3}
do_execsql_test in4-4.3 {
  SELECT c FROM t4a WHERE (a||'')=b ORDER BY c;
} {1 3}
do_execsql_test in4-4.4 {
  SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c;
} {3}
do_execsql_test in4-4.5 {
  SELECT c FROM t4a WHERE a IN (b) ORDER BY c;
} {3}
do_execsql_test in4-4.6 {
  SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c;
} {3}


do_execsql_test in4-4.11 {
  CREATE TABLE t4b(a TEXT, b NUMERIC, c);
  INSERT INTO t4b VALUES('1.0',1,4);
  SELECT c FROM t4b WHERE a=b;
} {4}
do_execsql_test in4-4.12 {
  SELECT c FROM t4b WHERE b=a;
} {4}
do_execsql_test in4-4.13 {
  SELECT c FROM t4b WHERE +a=b;
} {4}
do_execsql_test in4-4.14 {
  SELECT c FROM t4b WHERE a=+b;
} {}
do_execsql_test in4-4.15 {
  SELECT c FROM t4b WHERE +b=a;
} {}
do_execsql_test in4-4.16 {
  SELECT c FROM t4b WHERE b=+a;
} {4}
do_execsql_test in4-4.17 {
  SELECT c FROM t4b WHERE a IN (b);
} {}
do_execsql_test in4-4.18 {
  SELECT c FROM t4b WHERE b IN (a);
} {4}
do_execsql_test in4-4.19 {
  SELECT c FROM t4b WHERE +b IN (a);
} {}

do_execsql_test in4-5.1 {
  CREATE TABLE t5(c INTEGER PRIMARY KEY, d TEXT COLLATE nocase);
  INSERT INTO t5 VALUES(17, 'fuzz');
  SELECT 1 FROM t5 WHERE 'fuzz' IN (d);  -- match
  SELECT 2 FROM t5 WHERE 'FUZZ' IN (d);  -- no match
  SELECT 3 FROM t5 WHERE d IN ('fuzz');  -- match
  SELECT 4 FROM t5 WHERE d IN ('FUZZ');  -- match
} {1 3 4}

# An expression of the form "x IN (y)" can be used as "x=y" by the
# query planner when computing transitive constraints or to run the
# query using an index on y.
#
do_execsql_test in4-6.1 {
  CREATE TABLE t6a(a INTEGER PRIMARY KEY, b);
  INSERT INTO t6a VALUES(1,2),(3,4),(5,6);
  CREATE TABLE t6b(c INTEGER PRIMARY KEY, d);
  INSERT INTO t6b VALUES(4,44),(5,55),(6,66);

  SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c);
} {3 4 4 44}
do_execsql_test in4-6.1-eqp {
  EXPLAIN QUERY PLAN
  SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c);
} {~/SCAN/}
do_execsql_test in4-6.2 {
  SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b);
} {3 4 4 44}
do_execsql_test in4-6.2-eqp {
  EXPLAIN QUERY PLAN
  SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b);
} {~/SCAN/}


finish_test