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