Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Correctly handle NULLs in IN operators. Ticket #2273. The changes in where.c and in the WhereLevel.aInLoop structure are not strictly necessary to fix this problem - they just make the code easier to read. Only the change in OP_Next/OP_Prev operator of vdbe.c is required. (CVS 3735) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
26348556d824c032851e409ac510cddb |
User & Date: | drh 2007-03-28 14:30:07.000 |
Context
2007-03-28
| ||
18:04 | Improvements to the XFER Optimization of the INSERT statement. (CVS 3736) (check-in: 53fff7d1f2 user: drh tags: trunk) | |
14:30 | Correctly handle NULLs in IN operators. Ticket #2273. The changes in where.c and in the WhereLevel.aInLoop structure are not strictly necessary to fix this problem - they just make the code easier to read. Only the change in OP_Next/OP_Prev operator of vdbe.c is required. (CVS 3735) (check-in: 26348556d8 user: drh tags: trunk) | |
13:07 | Update comments in sqlite3.h. No changes to code. (CVS 3734) (check-in: 1c2656fdf6 user: drh tags: trunk) | |
Changes
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.546 2007/03/28 14:30:07 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ #if defined(SQLITE_TCL) || defined(TCLSH) # include <tcl.h> #endif |
︙ | ︙ | |||
1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 | int flags; /* Flags associated with this level */ int iMem; /* First memory cell used by this level */ int iLeftJoin; /* Memory cell used to implement LEFT OUTER JOIN */ Index *pIdx; /* Index used. NULL if no index */ int iTabCur; /* The VDBE cursor used to access the table */ int iIdxCur; /* The VDBE cursor used to acesss pIdx */ int brk; /* Jump here to break out of the loop */ int cont; /* Jump here to continue with the next loop cycle */ int top; /* First instruction of interior of the loop */ int op, p1, p2; /* Opcode used to terminate the loop */ int nEq; /* Number of == or IN constraints on this loop */ int nIn; /* Number of IN operators constraining this loop */ | > > > > | | 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 | int flags; /* Flags associated with this level */ int iMem; /* First memory cell used by this level */ int iLeftJoin; /* Memory cell used to implement LEFT OUTER JOIN */ Index *pIdx; /* Index used. NULL if no index */ int iTabCur; /* The VDBE cursor used to access the table */ int iIdxCur; /* The VDBE cursor used to acesss pIdx */ int brk; /* Jump here to break out of the loop */ int nxt; /* Jump here to start the next IN combination */ int cont; /* Jump here to continue with the next loop cycle */ int top; /* First instruction of interior of the loop */ int op, p1, p2; /* Opcode used to terminate the loop */ int nEq; /* Number of == or IN constraints on this loop */ int nIn; /* Number of IN operators constraining this loop */ struct InLoop { int iCur; /* The VDBE cursor used by this IN operator */ int topAddr; /* Top of the IN loop */ } *aInLoop; /* Information about each nested IN operator */ sqlite3_index_info *pBestIdx; /* Index information for this level */ /* The following field is really not part of the current level. But ** we need a place to cache index information for each table in the ** FROM clause and the WhereLevel structure is a convenient place. */ sqlite3_index_info *pIdxInfo; /* Index info for n-th source table */ |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.595 2007/03/28 14:30:07 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
3673 3674 3675 3676 3677 3678 3679 | case OP_Next: { /* no-push */ Cursor *pC; BtCursor *pCrsr; CHECK_FOR_INTERRUPT; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; | | > > | 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 | case OP_Next: { /* no-push */ Cursor *pC; BtCursor *pCrsr; CHECK_FOR_INTERRUPT; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; if( pC==0 ){ break; /* See ticket #2273 */ } if( (pCrsr = pC->pCursor)!=0 ){ int res; if( pC->nullRow ){ res = 1; }else{ assert( pC->deferredMoveto==0 ); rc = pOp->opcode==OP_Next ? sqlite3BtreeNext(pCrsr, &res) : |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.242 2007/03/28 14:30:09 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
1683 1684 1685 1686 1687 1688 1689 | ** For a constraint of the form X=expr, the expression is evaluated and its ** result is left on the stack. For constraints of the form X IN (...) ** this routine sets up a loop that will iterate over all values of X. */ static void codeEqualityTerm( Parse *pParse, /* The parsing context */ WhereTerm *pTerm, /* The term of the WHERE clause to be coded */ | < | > > > | | | | | | > | 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 | ** For a constraint of the form X=expr, the expression is evaluated and its ** result is left on the stack. For constraints of the form X IN (...) ** this routine sets up a loop that will iterate over all values of X. */ static void codeEqualityTerm( Parse *pParse, /* The parsing context */ WhereTerm *pTerm, /* The term of the WHERE clause to be coded */ WhereLevel *pLevel /* When level of the FROM clause we are working on */ ){ Expr *pX = pTerm->pExpr; Vdbe *v = pParse->pVdbe; if( pX->op==TK_EQ ){ sqlite3ExprCode(pParse, pX->pRight); }else if( pX->op==TK_ISNULL ){ sqlite3VdbeAddOp(v, OP_Null, 0, 0); #ifndef SQLITE_OMIT_SUBQUERY }else{ int iTab; struct InLoop *pIn; assert( pX->op==TK_IN ); sqlite3CodeSubselect(pParse, pX); iTab = pX->iTable; sqlite3VdbeAddOp(v, OP_Rewind, iTab, 0); VdbeComment((v, "# %.*s", pX->span.n, pX->span.z)); if( pLevel->nIn==0 ){ pLevel->nxt = sqlite3VdbeMakeLabel(v); } pLevel->nIn++; pLevel->aInLoop = sqliteReallocOrFree(pLevel->aInLoop, sizeof(pLevel->aInLoop[0])*pLevel->nIn); pIn = pLevel->aInLoop; if( pIn ){ pIn += pLevel->nIn - 1; pIn->iCur = iTab; pIn->topAddr = sqlite3VdbeAddOp(v, OP_Column, iTab, 0); sqlite3VdbeAddOp(v, OP_IsNull, -1, 0); }else{ pLevel->nIn = 0; } #endif } disableTerm(pLevel, pTerm); } |
︙ | ︙ | |||
1745 1746 1747 1748 1749 1750 1751 | ** this routine allocates an additional nEq memory cells for internal ** use. */ static void codeAllEqualityTerms( Parse *pParse, /* Parsing context */ WhereLevel *pLevel, /* Which nested loop of the FROM we are coding */ WhereClause *pWC, /* The WHERE clause */ | | < | 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 | ** this routine allocates an additional nEq memory cells for internal ** use. */ static void codeAllEqualityTerms( Parse *pParse, /* Parsing context */ WhereLevel *pLevel, /* Which nested loop of the FROM we are coding */ WhereClause *pWC, /* The WHERE clause */ Bitmask notReady /* Which parts of FROM have not yet been coded */ ){ int nEq = pLevel->nEq; /* The number of == or IN constraints to code */ int termsInMem = 0; /* If true, store value in mem[] cells */ Vdbe *v = pParse->pVdbe; /* The virtual machine under construction */ Index *pIdx = pLevel->pIdx; /* The index being used for this loop */ int iCur = pLevel->iTabCur; /* The cursor of the table */ WhereTerm *pTerm; /* A single constraint term */ |
︙ | ︙ | |||
1775 1776 1777 1778 1779 1780 1781 | */ assert( pIdx->nColumn>=nEq ); for(j=0; j<nEq; j++){ int k = pIdx->aiColumn[j]; pTerm = findTerm(pWC, iCur, k, notReady, pLevel->flags, pIdx); if( pTerm==0 ) break; assert( (pTerm->flags & TERM_CODED)==0 ); | | | | | 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 | */ assert( pIdx->nColumn>=nEq ); for(j=0; j<nEq; j++){ int k = pIdx->aiColumn[j]; pTerm = findTerm(pWC, iCur, k, notReady, pLevel->flags, pIdx); if( pTerm==0 ) break; assert( (pTerm->flags & TERM_CODED)==0 ); codeEqualityTerm(pParse, pTerm, pLevel); if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){ sqlite3VdbeAddOp(v, OP_IsNull, termsInMem ? -1 : -(j+1), pLevel->brk); } if( termsInMem ){ sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem+j+1, 1); } } /* Make sure all the constraint values are on the top of the stack |
︙ | ︙ | |||
2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 | ** program. */ notReady = ~(Bitmask)0; for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){ int j; int iCur = pTabItem->iCursor; /* The VDBE cursor for the table */ Index *pIdx; /* The index we will be using */ int iIdxCur; /* The VDBE cursor for the index */ int omitTable; /* True if we use the index only */ int bRev; /* True if we need to scan in reverse order */ pTabItem = &pTabList->a[pLevel->iFrom]; iCur = pTabItem->iCursor; pIdx = pLevel->pIdx; iIdxCur = pLevel->iIdxCur; bRev = (pLevel->flags & WHERE_REVERSE)!=0; omitTable = (pLevel->flags & WHERE_IDX_ONLY)!=0; /* Create labels for the "break" and "continue" instructions ** for the current loop. Jump to brk to break out of a loop. ** Jump to cont to go immediately to the next iteration of the ** loop. */ | > > > > > > | | 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 | ** program. */ notReady = ~(Bitmask)0; for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){ int j; int iCur = pTabItem->iCursor; /* The VDBE cursor for the table */ Index *pIdx; /* The index we will be using */ int nxt; /* Where to jump to continue with the next IN case */ int iIdxCur; /* The VDBE cursor for the index */ int omitTable; /* True if we use the index only */ int bRev; /* True if we need to scan in reverse order */ pTabItem = &pTabList->a[pLevel->iFrom]; iCur = pTabItem->iCursor; pIdx = pLevel->pIdx; iIdxCur = pLevel->iIdxCur; bRev = (pLevel->flags & WHERE_REVERSE)!=0; omitTable = (pLevel->flags & WHERE_IDX_ONLY)!=0; /* Create labels for the "break" and "continue" instructions ** for the current loop. Jump to brk to break out of a loop. ** Jump to cont to go immediately to the next iteration of the ** loop. ** ** When there is an IN operator, we also have a "nxt" label that ** means to continue with the next IN value combination. When ** there are no IN operators in the constraints, the "nxt" label ** is the same as "brk". */ brk = pLevel->brk = pLevel->nxt = sqlite3VdbeMakeLabel(v); cont = pLevel->cont = sqlite3VdbeMakeLabel(v); /* If this is the right table of a LEFT OUTER JOIN, allocate and ** initialize a memory cell that records if this table matches any ** row of the left table of the join. */ if( pLevel->iFrom>0 && (pTabItem[0].jointype & JT_LEFT)!=0 ){ |
︙ | ︙ | |||
2262 2263 2264 2265 2266 2267 2268 | ** construct. */ pTerm = findTerm(&wc, iCur, -1, notReady, WO_EQ|WO_IN, 0); assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( pTerm->leftCursor==iCur ); assert( omitTable==0 ); | | > | | | 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 | ** construct. */ pTerm = findTerm(&wc, iCur, -1, notReady, WO_EQ|WO_IN, 0); assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( pTerm->leftCursor==iCur ); assert( omitTable==0 ); codeEqualityTerm(pParse, pTerm, pLevel); nxt = pLevel->nxt; sqlite3VdbeAddOp(v, OP_MustBeInt, 1, nxt); sqlite3VdbeAddOp(v, OP_NotExists, iCur, nxt); VdbeComment((v, "pk")); pLevel->op = OP_Noop; }else if( pLevel->flags & WHERE_ROWID_RANGE ){ /* Case 2: We have an inequality comparison against the ROWID field. */ int testOp = OP_Noop; int start; |
︙ | ︙ | |||
2343 2344 2345 2346 2347 2348 2349 | int testOp; int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0; int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0; /* Generate code to evaluate all constraint terms using == or IN ** and level the values of those terms on the stack. */ | | | 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 | int testOp; int topLimit = (pLevel->flags & WHERE_TOP_LIMIT)!=0; int btmLimit = (pLevel->flags & WHERE_BTM_LIMIT)!=0; /* Generate code to evaluate all constraint terms using == or IN ** and level the values of those terms on the stack. */ codeAllEqualityTerms(pParse, pLevel, &wc, notReady); /* Duplicate the equality term values because they will all be ** used twice: once to make the termination key and once to make the ** start key. */ for(j=0; j<nEq; j++){ sqlite3VdbeAddOp(v, OP_Dup, nEq-1, 0); |
︙ | ︙ | |||
2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 | /* Generate the termination key. This is the key value that ** will end the search. There is no termination key if there ** are no equality terms and no "X<..." term. ** ** 2002-Dec-04: On a reverse-order scan, the so-called "termination" ** key computed here really ends up being the start key. */ if( topLimit ){ Expr *pX; int k = pIdx->aiColumn[j]; pTerm = findTerm(&wc, iCur, k, notReady, topOp, pIdx); assert( pTerm!=0 ); pX = pTerm->pExpr; assert( (pTerm->flags & TERM_CODED)==0 ); sqlite3ExprCode(pParse, pX->pRight); | > | | | 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 | /* Generate the termination key. This is the key value that ** will end the search. There is no termination key if there ** are no equality terms and no "X<..." term. ** ** 2002-Dec-04: On a reverse-order scan, the so-called "termination" ** key computed here really ends up being the start key. */ nxt = pLevel->nxt; if( topLimit ){ Expr *pX; int k = pIdx->aiColumn[j]; pTerm = findTerm(&wc, iCur, k, notReady, topOp, pIdx); assert( pTerm!=0 ); pX = pTerm->pExpr; assert( (pTerm->flags & TERM_CODED)==0 ); sqlite3ExprCode(pParse, pX->pRight); sqlite3VdbeAddOp(v, OP_IsNull, -(nEq+1), nxt); topEq = pTerm->eOperator & (WO_LE|WO_GE); disableTerm(pLevel, pTerm); testOp = OP_IdxGE; }else{ testOp = nEq>0 ? OP_IdxGE : OP_Noop; topEq = 1; } if( testOp!=OP_Noop ){ int nCol = nEq + topLimit; pLevel->iMem = pParse->nMem++; buildIndexProbe(v, nCol, pIdx); if( bRev ){ int op = topEq ? OP_MoveLe : OP_MoveLt; sqlite3VdbeAddOp(v, op, iIdxCur, nxt); }else{ sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 1); } }else if( bRev ){ sqlite3VdbeAddOp(v, OP_Last, iIdxCur, brk); } |
︙ | ︙ | |||
2421 2422 2423 2424 2425 2426 2427 | Expr *pX; int k = pIdx->aiColumn[j]; pTerm = findTerm(&wc, iCur, k, notReady, btmOp, pIdx); assert( pTerm!=0 ); pX = pTerm->pExpr; assert( (pTerm->flags & TERM_CODED)==0 ); sqlite3ExprCode(pParse, pX->pRight); | | | | | 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 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 | Expr *pX; int k = pIdx->aiColumn[j]; pTerm = findTerm(&wc, iCur, k, notReady, btmOp, pIdx); assert( pTerm!=0 ); pX = pTerm->pExpr; assert( (pTerm->flags & TERM_CODED)==0 ); sqlite3ExprCode(pParse, pX->pRight); sqlite3VdbeAddOp(v, OP_IsNull, -(nEq+1), nxt); btmEq = pTerm->eOperator & (WO_LE|WO_GE); disableTerm(pLevel, pTerm); }else{ btmEq = 1; } if( nEq>0 || btmLimit ){ int nCol = nEq + btmLimit; buildIndexProbe(v, nCol, pIdx); if( bRev ){ pLevel->iMem = pParse->nMem++; sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 1); testOp = OP_IdxLT; }else{ int op = btmEq ? OP_MoveGe : OP_MoveGt; sqlite3VdbeAddOp(v, op, iIdxCur, nxt); } }else if( bRev ){ testOp = OP_Noop; }else{ sqlite3VdbeAddOp(v, OP_Rewind, iIdxCur, brk); } /* Generate the the top of the loop. If there is a termination ** key we have to test for that key and abort at the top of the ** loop. */ start = sqlite3VdbeCurrentAddr(v); if( testOp!=OP_Noop ){ sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0); sqlite3VdbeAddOp(v, testOp, iIdxCur, nxt); if( (topEq && !bRev) || (!btmEq && bRev) ){ sqlite3VdbeChangeP3(v, -1, "+", P3_STATIC); } } if( topLimit | btmLimit ){ sqlite3VdbeAddOp(v, OP_Column, iIdxCur, nEq); sqlite3VdbeAddOp(v, OP_IsNull, 1, cont); |
︙ | ︙ | |||
2480 2481 2482 2483 2484 2485 2486 | */ int start; int nEq = pLevel->nEq; /* Generate code to evaluate all constraint terms using == or IN ** and leave the values of those terms on the stack. */ | | > | | | | | | 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 | */ int start; int nEq = pLevel->nEq; /* Generate code to evaluate all constraint terms using == or IN ** and leave the values of those terms on the stack. */ codeAllEqualityTerms(pParse, pLevel, &wc, notReady); nxt = pLevel->nxt; /* Generate a single key that will be used to both start and terminate ** the search */ buildIndexProbe(v, nEq, pIdx); sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 0); /* Generate code (1) to move to the first matching element of the table. ** Then generate code (2) that jumps to "nxt" after the cursor is past ** the last matching element of the table. The code (1) is executed ** once to initialize the search, the code (2) is executed before each ** iteration of the scan to see if the scan has finished. */ if( bRev ){ /* Scan in reverse order */ sqlite3VdbeAddOp(v, OP_MoveLe, iIdxCur, nxt); start = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0); sqlite3VdbeAddOp(v, OP_IdxLT, iIdxCur, nxt); pLevel->op = OP_Prev; }else{ /* Scan in the forward order */ sqlite3VdbeAddOp(v, OP_MoveGe, iIdxCur, nxt); start = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0); sqlite3VdbeOp3(v, OP_IdxGE, iIdxCur, nxt, "+", P3_STATIC); pLevel->op = OP_Next; } if( !omitTable ){ sqlite3VdbeAddOp(v, OP_IdxRowid, iIdxCur, 0); sqlite3VdbeAddOp(v, OP_MoveGe, iCur, 0); } pLevel->p1 = iIdxCur; |
︙ | ︙ | |||
2636 2637 2638 2639 2640 2641 2642 | */ for(i=pTabList->nSrc-1; i>=0; i--){ pLevel = &pWInfo->a[i]; sqlite3VdbeResolveLabel(v, pLevel->cont); if( pLevel->op!=OP_Noop ){ sqlite3VdbeAddOp(v, pLevel->op, pLevel->p1, pLevel->p2); } | < | > | > | | > | 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 | */ for(i=pTabList->nSrc-1; i>=0; i--){ pLevel = &pWInfo->a[i]; sqlite3VdbeResolveLabel(v, pLevel->cont); if( pLevel->op!=OP_Noop ){ sqlite3VdbeAddOp(v, pLevel->op, pLevel->p1, pLevel->p2); } if( pLevel->nIn ){ struct InLoop *pIn; int j; sqlite3VdbeResolveLabel(v, pLevel->nxt); for(j=pLevel->nIn, pIn=&pLevel->aInLoop[j-1]; j>0; j--, pIn--){ sqlite3VdbeJumpHere(v, pIn->topAddr+1); sqlite3VdbeAddOp(v, OP_Next, pIn->iCur, pIn->topAddr); sqlite3VdbeJumpHere(v, pIn->topAddr-1); } sqliteFree(pLevel->aInLoop); } sqlite3VdbeResolveLabel(v, pLevel->brk); if( pLevel->iLeftJoin ){ int addr; addr = sqlite3VdbeAddOp(v, OP_IfMemPos, pLevel->iLeftJoin, 0); sqlite3VdbeAddOp(v, OP_NullRow, pTabList->a[i].iCursor, 0); if( pLevel->iIdxCur>=0 ){ sqlite3VdbeAddOp(v, OP_NullRow, pLevel->iIdxCur, 0); } |
︙ | ︙ |
Changes to test/where4.test.
︙ | ︙ | |||
11 12 13 14 15 16 17 | # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clauses. # This file was created when support for optimizing IS NULL phrases # was added. And so the principle purpose of this file is to test # that IS NULL phrases are correctly optimized. But you can never # have too many tests, so some other tests are thrown in as well. # | | | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clauses. # This file was created when support for optimizing IS NULL phrases # was added. And so the principle purpose of this file is to test # that IS NULL phrases are correctly optimized. But you can never # have too many tests, so some other tests are thrown in as well. # # $Id: where4.test,v 1.3 2007/03/28 14:30:09 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test where4-1.0 { |
︙ | ︙ | |||
169 170 171 172 173 174 175 | } {} do_test where4-4.4 { execsql { SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 WHERE t1.col1 IS NULL; } } {} | | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | } {} do_test where4-4.4 { execsql { SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 WHERE t1.col1 IS NULL; } } {} # Ticket #2273. Problems with IN operators and NULLs. # do_test where4-5.1 { execsql { CREATE TABLE t4(x,y,z,PRIMARY KEY(x,y)); } execsql { SELECT * FROM t2 LEFT JOIN t4 b1 LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y); } } {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}} do_test where4-5.2 { execsql { INSERT INTO t4 VALUES(1,1,11); INSERT INTO t4 VALUES(1,2,12); INSERT INTO t4 VALUES(1,3,13); INSERT INTO t4 VALUES(2,2,22); SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13; } } {1 2 4} do_test where4-5.3 { execsql { SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13; } } {1 2 4} do_test where4-6.1 { execsql { CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f)); INSERT INTO t5 VALUES(1,1,1,1,1,11111); INSERT INTO t5 VALUES(2,2,2,2,2,22222); INSERT INTO t5 VALUES(1,2,3,4,5,12345); INSERT INTO t5 VALUES(2,3,4,5,6,23456); } execsql { SELECT rowid FROM t5 WHERE a IN (1,9,2) AND b=2 AND c IN (1,2,3,4) AND d>0 } } {3 2} do_test where4-6.2 { execsql { SELECT rowid FROM t5 WHERE a IN (1,NULL,2) AND b=2 AND c IN (1,2,3,4) AND d>0 } } {3 2} do_test where4-7.1 { execsql { CREATE TABLE t6(y,z,PRIMARY KEY(y,z)); } execsql { SELECT * FROM t6 WHERE y=NULL AND z IN ('hello'); } } {} integrity_check {where4-99.0} finish_test |