/ Check-in [26348556]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:26348556d824c032851e409ac510cddb55c200bf
User & Date: drh 2007-03-28 14:30:07
Context
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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