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: |
eca35871c34374ca9189c7c9b6d490ac |
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
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 | ** 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. */ | | > > > > | 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 | u32 savedNQueryLoop = pParse->nQueryLoop; int rMayHaveNull = 0; eType = IN_INDEX_EPH; if( prNotFound ){ *prNotFound = rMayHaveNull = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); }else{ | < | 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 | 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[] */ | | > | 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 | #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 */ | | | | 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 | #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); | | | 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 | ** ** 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 ){ | | | | 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 | pLevel->op = OP_Noop; }else if( bRev ){ pLevel->op = OP_Prev; }else{ pLevel->op = OP_Next; } pLevel->p1 = iIdxCur; | < | | 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 |