Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow vector IN(SELECT ...) expressions to use an index if either all the indexed columns are declared NOT NULL or if there is no difference between the expression evaluating to 0 and NULL (as in a WHERE clause). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rowvalue |
Files: | files | file ages | folders |
SHA1: |
e2fd6f49b1b145bec09c581cc982b894 |
User & Date: | dan 2016-07-23 20:24:06.382 |
Context
2016-07-26
| ||
18:06 | Fix where.c handling of "IN (SELECT ...)" expressions when the SELECT returns more than one result column. Also error handling for other row value constructor cases. (check-in: 061b800603 user: dan tags: rowvalue) | |
2016-07-23
| ||
20:24 | Allow vector IN(SELECT ...) expressions to use an index if either all the indexed columns are declared NOT NULL or if there is no difference between the expression evaluating to 0 and NULL (as in a WHERE clause). (check-in: e2fd6f49b1 user: dan tags: rowvalue) | |
2016-07-22
| ||
17:58 | Merge latest trunk changes with this branch. (check-in: 60fed5cdd4 user: dan tags: rowvalue) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
319 320 321 322 323 324 325 326 327 328 329 330 331 332 | if( (pExpr->flags & EP_Vector)==0 ) return 1; if( pExpr->flags & EP_xIsSelect ){ return pExpr->x.pSelect->pEList->nExpr; } return pExpr->x.pList->nExpr; } static Expr *exprVectorField(Expr *pVector, int i){ if( (pVector->flags & EP_Vector)==0 ){ assert( i==0 ); return pVector; }else if( pVector->flags & EP_xIsSelect ){ return pVector->x.pSelect->pEList->a[i].pExpr; } | > > > > > > | 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 | if( (pExpr->flags & EP_Vector)==0 ) return 1; if( pExpr->flags & EP_xIsSelect ){ return pExpr->x.pSelect->pEList->nExpr; } return pExpr->x.pList->nExpr; } /* ** If the expression passed as the first argument is a TK_VECTOR, return ** a pointer to the i'th field of the vector. Or, if the first argument ** points to a sub-select, return a pointer to the i'th returned column ** value. Otherwise, return a copy of the first argument. */ static Expr *exprVectorField(Expr *pVector, int i){ if( (pVector->flags & EP_Vector)==0 ){ assert( i==0 ); return pVector; }else if( pVector->flags & EP_xIsSelect ){ return pVector->x.pSelect->pEList->a[i].pExpr; } |
︙ | ︙ | |||
1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 | /* ** pX is the RHS of an IN operator. If pX is a SELECT statement ** that can be simplified to a direct table access, then return ** a pointer to the SELECT statement. If pX is not a SELECT statement, ** or if the SELECT statement needs to be manifested into a transient ** table, then return NULL. */ #ifndef SQLITE_OMIT_SUBQUERY static Select *isCandidateForInOpt(Expr *pX, int bNullSensitive){ Select *p; SrcList *pSrc; ExprList *pEList; Table *pTab; | > > > > > > > | 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 | /* ** pX is the RHS of an IN operator. If pX is a SELECT statement ** that can be simplified to a direct table access, then return ** a pointer to the SELECT statement. If pX is not a SELECT statement, ** or if the SELECT statement needs to be manifested into a transient ** table, then return NULL. ** ** If parameter bNullSensitive is 0, then this operation will be ** used in a context in which there is no difference between a result ** of 0 and one of NULL. For example: ** ** ... WHERE (?,?) IN (SELECT ...) ** */ #ifndef SQLITE_OMIT_SUBQUERY static Select *isCandidateForInOpt(Expr *pX, int bNullSensitive){ Select *p; SrcList *pSrc; ExprList *pEList; Table *pTab; |
︙ | ︙ | |||
1866 1867 1868 1869 1870 1871 1872 | ** ** If a register is allocated and its location stored in *prRhsHasNull, then ** the value in that register will be NULL if the b-tree contains one or more ** NULL values, and it will be some non-NULL value if the b-tree contains no ** NULL values. */ #ifndef SQLITE_OMIT_SUBQUERY | | > > > > > > > < | 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 | ** ** If a register is allocated and its location stored in *prRhsHasNull, then ** the value in that register will be NULL if the b-tree contains one or more ** NULL values, and it will be some non-NULL value if the b-tree contains no ** NULL values. */ #ifndef SQLITE_OMIT_SUBQUERY int sqlite3FindInIndex( Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull, int *aiMap ){ Select *p; /* SELECT to the right of IN operator */ int eType = 0; /* Type of RHS table. IN_INDEX_* */ int iTab = pParse->nTab++; /* Cursor of the RHS table */ int mustBeUnique; /* True if RHS must be unique */ Vdbe *v = sqlite3GetVdbe(pParse); /* Virtual machine being coded */ assert( pX->op==TK_IN ); mustBeUnique = (inFlags & IN_INDEX_LOOP)!=0; /* Check to see if an existing table or index can be used to ** satisfy the query. This is preferable to generating a new ** ephemeral table. */ if( pParse->nErr==0 && (p = isCandidateForInOpt(pX, prRhsHasNull!=0))!=0 ){ sqlite3 *db = pParse->db; /* Database connection */ Table *pTab; /* Table <table>. */ i16 iDb; /* Database idx for pTab */ ExprList *pEList = p->pEList; int nExpr = pEList->nExpr; assert( p->pEList!=0 ); /* Because of isCandidateForInOpt(p) */ assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */ assert( p->pSrc!=0 ); /* Because of isCandidateForInOpt(p) */ pTab = p->pSrc->a[0].pTab; /* Code an OP_Transaction and OP_TableLock for <table>. */ |
︙ | ︙ | |||
1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 | for(j=0; j<nExpr; j++){ if( pIdx->aiColumn[j]!=pRhs->iColumn ) continue; assert( pIdx->azColl[j] ); if( sqlite3StrICmp(pReq->zName, pIdx->azColl[j])!=0 ) continue; break; } if( j==nExpr ) break; } if( i==nExpr ){ int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIdx); VdbeComment((v, "%s", pIdx->zName)); | > | 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 | for(j=0; j<nExpr; j++){ if( pIdx->aiColumn[j]!=pRhs->iColumn ) continue; assert( pIdx->azColl[j] ); if( sqlite3StrICmp(pReq->zName, pIdx->azColl[j])!=0 ) continue; break; } if( j==nExpr ) break; if( aiMap ) aiMap[i] = j; } if( i==nExpr ){ int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIdx); VdbeComment((v, "%s", pIdx->zName)); |
︙ | ︙ | |||
2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 | *prRhsHasNull = rMayHaveNull = ++pParse->nMem; } sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID); pParse->nQueryLoop = savedNQueryLoop; }else{ pX->iTable = iTab; } return eType; } #endif static char *exprINAffinity(Parse *pParse, Expr *pExpr){ Expr *pLeft = pExpr->pLeft; int nVal = sqlite3ExprVectorSize(pLeft); | > > > > > > | 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 | *prRhsHasNull = rMayHaveNull = ++pParse->nMem; } sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID); pParse->nQueryLoop = savedNQueryLoop; }else{ pX->iTable = iTab; } if( aiMap && eType!=IN_INDEX_INDEX_ASC && eType!=IN_INDEX_INDEX_DESC ){ int i, n; n = sqlite3ExprVectorSize(pX->pLeft); for(i=0; i<n; i++) aiMap[i] = i; } return eType; } #endif static char *exprINAffinity(Parse *pParse, Expr *pExpr){ Expr *pLeft = pExpr->pLeft; int nVal = sqlite3ExprVectorSize(pLeft); |
︙ | ︙ | |||
2305 2306 2307 2308 2309 2310 2311 | } sqlite3ExprCachePop(pParse); return rReg; } #endif /* SQLITE_OMIT_SUBQUERY */ | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 | } sqlite3ExprCachePop(pParse); return rReg; } #endif /* SQLITE_OMIT_SUBQUERY */ #ifndef SQLITE_OMIT_SUBQUERY /* ** Generate code for an IN expression. ** ** x IN (SELECT ...) ** x IN (value, value, ...) ** |
︙ | ︙ | |||
2399 2400 2401 2402 2403 2404 2405 | static void sqlite3ExprCodeIN( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* The IN expression */ int destIfFalse, /* Jump here if LHS is not contained in the RHS */ int destIfNull /* Jump here if the results are unknown due to NULLs */ ){ int rRhsHasNull = 0; /* Register that is true if RHS contains NULL values */ | < | > > > | < > | > > > > > > | > | < > | | | < < | | > > > > > > > > > | > > > | > > > > > > > > > | 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 | static void sqlite3ExprCodeIN( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* The IN expression */ int destIfFalse, /* Jump here if LHS is not contained in the RHS */ int destIfNull /* Jump here if the results are unknown due to NULLs */ ){ int rRhsHasNull = 0; /* Register that is true if RHS contains NULL values */ int eType; /* Type of the RHS */ int r1; /* Temporary use register */ Vdbe *v; /* Statement under construction */ int *aiMap = 0; /* Map from vector field to index column */ char *zAff = 0; /* Affinity string for comparisons */ int nVector; /* Size of vectors for this IN(...) op */ int regSelect = 0; Expr *pLeft = pExpr->pLeft; int i; nVector = sqlite3ExprVectorSize(pExpr->pLeft); aiMap = (int*)sqlite3DbMallocZero( pParse->db, nVector*(sizeof(int) + sizeof(char)) + 1 ); if( !aiMap ) return; zAff = (char*)&aiMap[nVector]; /* Attempt to compute the RHS. After this step, if anything other than ** IN_INDEX_NOOP is returned, the table opened ith cursor pExpr->iTable ** contains the values that make up the RHS. If IN_INDEX_NOOP is returned, ** the RHS has not yet been coded. */ v = pParse->pVdbe; assert( v!=0 ); /* OOM detected prior to this routine */ VdbeNoopComment((v, "begin IN expr")); eType = sqlite3FindInIndex(pParse, pExpr, IN_INDEX_MEMBERSHIP | IN_INDEX_NOOP_OK, destIfFalse==destIfNull ? 0 : &rRhsHasNull, aiMap); assert( pParse->nErr || nVector==1 || eType==IN_INDEX_EPH || eType==IN_INDEX_INDEX_ASC || eType==IN_INDEX_INDEX_DESC ); /* Code the LHS, the <expr> from "<expr> IN (...)". If the LHS is a ** vector, then it is stored in an array of nVector registers starting ** at r1. */ r1 = sqlite3GetTempRange(pParse, nVector); sqlite3ExprCachePush(pParse); if( nVector>1 && (pLeft->flags & EP_xIsSelect) ){ regSelect = sqlite3CodeSubselect(pParse, pLeft, 0, 0); } for(i=0; i<nVector; i++){ int iCol = aiMap[i]; Expr *pLhs = exprVectorField(pLeft, i); if( regSelect ){ sqlite3VdbeAddOp3(v, OP_Copy, regSelect+i, r1+iCol, 0); }else{ sqlite3ExprCode(pParse, pLhs, r1+iCol); } zAff[iCol] = sqlite3ExprAffinity(pLhs); if( pExpr->flags & EP_xIsSelect ){ zAff[iCol] = sqlite3CompareAffinity( pExpr->x.pSelect->pEList->a[iCol].pExpr, zAff[iCol] ); } } /* If sqlite3FindInIndex() did not find or create an index that is ** suitable for evaluating the IN operator, then evaluate using a ** sequence of comparisons. */ if( eType==IN_INDEX_NOOP ){ ExprList *pList = pExpr->x.pList; |
︙ | ︙ | |||
2456 2457 2458 2459 2460 2461 2462 | sqlite3VdbeAddOp3(v, OP_BitAnd, regCkNull, r2, regCkNull); } if( ii<pList->nExpr-1 || destIfNull!=destIfFalse ){ sqlite3VdbeAddOp4(v, OP_Eq, r1, labelOk, r2, (void*)pColl, P4_COLLSEQ); VdbeCoverageIf(v, ii<pList->nExpr-1); VdbeCoverageIf(v, ii==pList->nExpr-1); | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > | > | 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 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 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 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 | sqlite3VdbeAddOp3(v, OP_BitAnd, regCkNull, r2, regCkNull); } if( ii<pList->nExpr-1 || destIfNull!=destIfFalse ){ sqlite3VdbeAddOp4(v, OP_Eq, r1, labelOk, r2, (void*)pColl, P4_COLLSEQ); VdbeCoverageIf(v, ii<pList->nExpr-1); VdbeCoverageIf(v, ii==pList->nExpr-1); sqlite3VdbeChangeP5(v, zAff[0]); }else{ assert( destIfNull==destIfFalse ); sqlite3VdbeAddOp4(v, OP_Ne, r1, destIfFalse, r2, (void*)pColl, P4_COLLSEQ); VdbeCoverage(v); sqlite3VdbeChangeP5(v, zAff[0] | SQLITE_JUMPIFNULL); } sqlite3ReleaseTempReg(pParse, regToFree); } if( regCkNull ){ sqlite3VdbeAddOp2(v, OP_IsNull, regCkNull, destIfNull); VdbeCoverage(v); sqlite3VdbeGoto(v, destIfFalse); } sqlite3VdbeResolveLabel(v, labelOk); sqlite3ReleaseTempReg(pParse, regCkNull); }else{ /* If the LHS is NULL, then the result is either false or NULL depending ** on whether the RHS is empty or not, respectively. */ if( nVector==1 && sqlite3ExprCanBeNull(pExpr->pLeft) ){ if( destIfNull==destIfFalse ){ /* Shortcut for the common case where the false and NULL outcomes are ** the same. */ sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v); }else{ int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); VdbeCoverage(v); sqlite3VdbeGoto(v, destIfNull); sqlite3VdbeJumpHere(v, addr1); } } if( eType==IN_INDEX_ROWID ){ /* In this case, the RHS is the ROWID of table b-tree */ sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, r1); VdbeCoverage(v); }else if( nVector>1 && eType==IN_INDEX_EPH ){ int regNull = sqlite3GetTempReg(pParse); int r2 = sqlite3GetTempReg(pParse); int r3 = sqlite3GetTempReg(pParse); int r4 = sqlite3GetTempReg(pParse); int addrNext; int addrIf; if( destIfFalse!=destIfNull ){ sqlite3VdbeAddOp2(v, OP_Integer, 0, regNull); } addrNext = sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); for(i=0; i<nVector; i++){ Expr *p; CollSeq *pColl; p = exprVectorField(pLeft, i); pColl = sqlite3ExprCollSeq(pParse, p); sqlite3VdbeAddOp3(v, OP_Column, pExpr->iTable, i, r2); sqlite3VdbeAddOp4(v, OP_Eq, r1+i, i?r3:r4, r2, (void*)pColl,P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_STOREP2); if( i!=0 ){ sqlite3VdbeAddOp3(v, OP_And, r3, r4, r4); } } addrIf = sqlite3VdbeAddOp1(v, OP_If, r4); if( destIfNull!=destIfFalse ){ sqlite3VdbeAddOp2(v, OP_IfNot, r4, sqlite3VdbeCurrentAddr(v)+2); sqlite3VdbeAddOp2(v, OP_Integer, 1, regNull); } sqlite3VdbeAddOp2(v, OP_Next, pExpr->iTable, addrNext+1); if( destIfNull!=destIfFalse ){ sqlite3VdbeAddOp2(v, OP_If, regNull, destIfNull); } sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse); sqlite3VdbeChangeP2(v, addrIf, sqlite3VdbeCurrentAddr(v)); sqlite3ReleaseTempReg(pParse, regNull); sqlite3ReleaseTempReg(pParse, r2); sqlite3ReleaseTempReg(pParse, r3); sqlite3ReleaseTempReg(pParse, r4); }else{ /* In this case, the RHS is an index b-tree. */ sqlite3VdbeAddOp4(v, OP_Affinity, r1, nVector, 0, zAff, nVector); /* If the set membership test fails, then the result of the ** "x IN (...)" expression must be either 0 or NULL. If the set ** contains no NULL values, then the result is 0. If the set ** contains one or more NULL values, then the result of the ** expression is also NULL. */ assert( destIfFalse!=destIfNull || rRhsHasNull==0 ); if( rRhsHasNull==0 ){ /* This branch runs if it is known at compile time that the RHS ** cannot contain NULL values. This happens as the result ** of a "NOT NULL" constraint in the database schema. ** ** Also run this branch if NULL is equivalent to FALSE ** for this particular IN operator. */ sqlite3VdbeAddOp4Int( v, OP_NotFound, pExpr->iTable, destIfFalse, r1, nVector ); VdbeCoverage(v); }else{ /* In this branch, the RHS of the IN might contain a NULL and ** the presence of a NULL on the RHS makes a difference in the ** outcome. */ int addr1; |
︙ | ︙ | |||
2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 | sqlite3VdbeGoto(v, destIfFalse); sqlite3VdbeJumpHere(v, addr1); } } } sqlite3ReleaseTempReg(pParse, r1); sqlite3ExprCachePop(pParse); VdbeComment((v, "end IN expr")); } #endif /* SQLITE_OMIT_SUBQUERY */ #ifndef SQLITE_OMIT_FLOATING_POINT /* ** Generate an instruction that will put the floating point | > | 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 | sqlite3VdbeGoto(v, destIfFalse); sqlite3VdbeJumpHere(v, addr1); } } } sqlite3ReleaseTempReg(pParse, r1); sqlite3ExprCachePop(pParse); sqlite3DbFree(pParse->db, aiMap); VdbeComment((v, "end IN expr")); } #endif /* SQLITE_OMIT_SUBQUERY */ #ifndef SQLITE_OMIT_FLOATING_POINT /* ** Generate an instruction that will put the floating point |
︙ | ︙ | |||
4006 4007 4008 4009 4010 4011 4012 | assert( pExpr->op!=TK_NE || op==OP_Eq ); assert( pExpr->op!=TK_EQ || op==OP_Ne ); assert( pExpr->op!=TK_LT || op==OP_Ge ); assert( pExpr->op!=TK_LE || op==OP_Gt ); assert( pExpr->op!=TK_GT || op==OP_Le ); assert( pExpr->op!=TK_GE || op==OP_Lt ); | | | 4034 4035 4036 4037 4038 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 | assert( pExpr->op!=TK_NE || op==OP_Eq ); assert( pExpr->op!=TK_EQ || op==OP_Ne ); assert( pExpr->op!=TK_LT || op==OP_Ge ); assert( pExpr->op!=TK_LE || op==OP_Gt ); assert( pExpr->op!=TK_GT || op==OP_Le ); assert( pExpr->op!=TK_GE || op==OP_Lt ); switch( pExpr->op ){ case TK_AND: { testcase( jumpIfNull==0 ); sqlite3ExprIfFalse(pParse, pExpr->pLeft, dest, jumpIfNull); sqlite3ExprCachePush(pParse); sqlite3ExprIfFalse(pParse, pExpr->pRight, dest, jumpIfNull); sqlite3ExprCachePop(pParse); break; |
︙ | ︙ | |||
4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 | /* Fall thru */ case TK_LT: case TK_LE: case TK_GT: case TK_GE: case TK_NE: case TK_EQ: { testcase( jumpIfNull==0 ); r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, jumpIfNull); assert(TK_LT==OP_Lt); testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt); assert(TK_LE==OP_Le); testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le); | > > | 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 | /* Fall thru */ case TK_LT: case TK_LE: case TK_GT: case TK_GE: case TK_NE: case TK_EQ: { if( pExpr->pLeft->flags & EP_Vector ) goto default_expr; testcase( jumpIfNull==0 ); r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, ®Free2); codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, r1, r2, dest, jumpIfNull); assert(TK_LT==OP_Lt); testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt); assert(TK_LE==OP_Le); testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le); |
︙ | ︙ | |||
4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101 4102 | sqlite3ExprCodeIN(pParse, pExpr, dest, destIfNull); sqlite3VdbeResolveLabel(v, destIfNull); } break; } #endif default: { if( exprAlwaysFalse(pExpr) ){ sqlite3VdbeGoto(v, dest); }else if( exprAlwaysTrue(pExpr) ){ /* no-op */ }else{ r1 = sqlite3ExprCodeTemp(pParse, pExpr, ®Free1); sqlite3VdbeAddOp3(v, OP_IfNot, r1, dest, jumpIfNull!=0); | > | 4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 4129 4130 4131 4132 4133 | sqlite3ExprCodeIN(pParse, pExpr, dest, destIfNull); sqlite3VdbeResolveLabel(v, destIfNull); } break; } #endif default: { default_expr: if( exprAlwaysFalse(pExpr) ){ sqlite3VdbeGoto(v, dest); }else if( exprAlwaysTrue(pExpr) ){ /* no-op */ }else{ r1 = sqlite3ExprCodeTemp(pParse, pExpr, ®Free1); sqlite3VdbeAddOp3(v, OP_IfNot, r1, dest, jumpIfNull!=0); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
4130 4131 4132 4133 4134 4135 4136 | #define IN_INDEX_NOOP 5 /* No table available. Use comparisons */ /* ** Allowed flags for the 3rd parameter to sqlite3FindInIndex(). */ #define IN_INDEX_NOOP_OK 0x0001 /* OK to return IN_INDEX_NOOP */ #define IN_INDEX_MEMBERSHIP 0x0002 /* IN operator used for membership test */ #define IN_INDEX_LOOP 0x0004 /* IN operator used as a loop */ | | | 4130 4131 4132 4133 4134 4135 4136 4137 4138 4139 4140 4141 4142 4143 4144 | #define IN_INDEX_NOOP 5 /* No table available. Use comparisons */ /* ** Allowed flags for the 3rd parameter to sqlite3FindInIndex(). */ #define IN_INDEX_NOOP_OK 0x0001 /* OK to return IN_INDEX_NOOP */ #define IN_INDEX_MEMBERSHIP 0x0002 /* IN operator used for membership test */ #define IN_INDEX_LOOP 0x0004 /* IN operator used as a loop */ int sqlite3FindInIndex(Parse *, Expr *, u32, int*, int*); int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int); int sqlite3JournalSize(sqlite3_vfs *); #ifdef SQLITE_ENABLE_ATOMIC_WRITE int sqlite3JournalCreate(sqlite3_file *); #endif |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
375 376 377 378 379 380 381 | ){ testcase( iEq==0 ); testcase( bRev ); bRev = !bRev; } assert( pX->op==TK_IN ); iReg = iTarget; | | | 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 | ){ testcase( iEq==0 ); testcase( bRev ); bRev = !bRev; } assert( pX->op==TK_IN ); iReg = iTarget; eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0); if( eType==IN_INDEX_INDEX_DESC ){ testcase( bRev ); bRev = !bRev; } iTab = pX->iTable; sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0); VdbeCoverageIf(v, bRev); |
︙ | ︙ |
Added test/rowvalue3.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 | # 2016 June 17 # # 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 "(...) IN (SELECT ...)" expressions # where the SELECT statement returns more than one column. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix rowvalue3 do_execsql_test 1.0 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a, b); INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t1 VALUES(7, 8, 9); } foreach {tn sql res} { 1 "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)" 1 2 "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)" {} 3 "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)" {} 4 "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)" 1 5 "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1 6 "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0 7 "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)" 1 8 "SELECT (5, 4) IN (SELECT +b, +a FROM t1)" 1 9 "SELECT (1, 2) IN (SELECT rowid, b FROM t1)" 1 10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)" 1 11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)" {} } { do_execsql_test 1.$tn $sql $res } #explain_i { SELECT (4, NULL) IN (SELECT a, b FROM t1) } #do_execsql_test 2 { SELECT (4, NULL) IN (SELECT a, b FROM t1) } {} finish_test |