/ Check-in [86734732]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix for the "(x AND y) OR z" bug backported to version 3.6.21.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | branch-3.6.21
Files: files | file ages | folders
SHA1: 867347323b0c095159411de36cbd87b09c1b2863
User & Date: drh 2009-12-16 23:28:32
Context
2009-12-16
23:28
Fix for the "(x AND y) OR z" bug backported to version 3.6.21. Leaf check-in: 86734732 user: drh tags: branch-3.6.21
2009-12-07
16:39
Version 3.6.21 check-in: 1ed88e9d user: drh tags: trunk, release
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -3.6.21
            1  +3.6.21.1

Changes to src/sqliteInt.h.

  1890   1890   #define WHERE_ORDERBY_MIN      0x0001 /* ORDER BY processing for min() func */
  1891   1891   #define WHERE_ORDERBY_MAX      0x0002 /* ORDER BY processing for max() func */
  1892   1892   #define WHERE_ONEPASS_DESIRED  0x0004 /* Want to do one-pass UPDATE/DELETE */
  1893   1893   #define WHERE_DUPLICATES_OK    0x0008 /* Ok to return a row more than once */
  1894   1894   #define WHERE_OMIT_OPEN        0x0010 /* Table cursor are already open */
  1895   1895   #define WHERE_OMIT_CLOSE       0x0020 /* Omit close of table & index cursors */
  1896   1896   #define WHERE_FORCE_TABLE      0x0040 /* Do not use an index-only search */
         1897  +#define WHERE_ONETABLE_ONLY    0x0080 /* Only code the 1st table in pTabList */
  1897   1898   
  1898   1899   /*
  1899   1900   ** The WHERE clause processing routine has two halves.  The
  1900   1901   ** first part does the start of the WHERE loop and the second
  1901   1902   ** half does the tail of the WHERE loop.  An instance of
  1902   1903   ** this structure is returned by the first half and passed
  1903   1904   ** into the second half to give some continuity.
  1904   1905   */
  1905   1906   struct WhereInfo {
  1906   1907     Parse *pParse;       /* Parsing and code generating context */
  1907   1908     u16 wctrlFlags;      /* Flags originally passed to sqlite3WhereBegin() */
  1908   1909     u8 okOnePass;        /* Ok to use one-pass algorithm for UPDATE or DELETE */
         1910  +  u8 untestedTerms;    /* Not all WHERE terms resolved by outer loop */
  1909   1911     SrcList *pTabList;             /* List of tables in the join */
  1910   1912     int iTop;                      /* The very beginning of the WHERE loop */
  1911   1913     int iContinue;                 /* Jump here to continue with next record */
  1912   1914     int iBreak;                    /* Jump here to break out of the loop */
  1913   1915     int nLevel;                    /* Number of nested loop */
  1914   1916     struct WhereClause *pWC;       /* Decomposition of the WHERE clause */
  1915   1917     WhereLevel a[1];               /* Information about each nest loop in WHERE */

Changes to src/where.c.

  3261   3261       **          Return     2                # Jump back to the Gosub
  3262   3262       **
  3263   3263       **       B: <after the loop>
  3264   3264       **
  3265   3265       */
  3266   3266       WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
  3267   3267       WhereTerm *pFinal;     /* Final subterm within the OR-clause. */
  3268         -    SrcList oneTab;        /* Shortened table list */
         3268  +    SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
  3269   3269   
  3270   3270       int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
  3271   3271       int regRowset = 0;                        /* Register for RowSet object */
  3272   3272       int regRowid = 0;                         /* Register holding rowid */
  3273   3273       int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
  3274   3274       int iRetInit;                             /* Address of regReturn init */
         3275  +    int untestedTerms = 0;             /* Some terms not completely tested */
  3275   3276       int ii;
  3276   3277      
  3277   3278       pTerm = pLevel->plan.u.pTerm;
  3278   3279       assert( pTerm!=0 );
  3279   3280       assert( pTerm->eOperator==WO_OR );
  3280   3281       assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
  3281   3282       pOrWc = &pTerm->u.pOrInfo->wc;
  3282   3283       pFinal = &pOrWc->a[pOrWc->nTerm-1];
         3284  +    pLevel->op = OP_Return;
         3285  +    pLevel->p1 = regReturn;
  3283   3286   
  3284         -    /* Set up a SrcList containing just the table being scanned by this loop. */
  3285         -    oneTab.nSrc = 1;
  3286         -    oneTab.nAlloc = 1;
  3287         -    oneTab.a[0] = *pTabItem;
         3287  +    /* Set up a new SrcList ni pOrTab containing the table being scanned
         3288  +    ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
         3289  +    ** This becomes the SrcList in the recursive call to sqlite3WhereBegin().
         3290  +    */
         3291  +    if( pWInfo->nLevel>1 ){
         3292  +      int nNotReady;                 /* The number of notReady tables */
         3293  +      struct SrcList_item *origSrc;     /* Original list of tables */
         3294  +      nNotReady = pWInfo->nLevel - iLevel - 1;
         3295  +      pOrTab = sqlite3StackAllocRaw(pParse->db,
         3296  +                            sizeof(*pOrTab)+ nNotReady*sizeof(pOrTab->a[0]));
         3297  +      if( pOrTab==0 ) return notReady;
         3298  +      pOrTab->nSrc = pOrTab->nAlloc = nNotReady + 1;
         3299  +      memcpy(pOrTab->a, pTabItem, sizeof(*pTabItem));
         3300  +      origSrc = pWInfo->pTabList->a;
         3301  +      for(k=1; k<=nNotReady; k++){
         3302  +        memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k]));
         3303  +      }
         3304  +    }else{
         3305  +      pOrTab = pWInfo->pTabList;
         3306  +    }
  3288   3307   
  3289   3308       /* Initialize the rowset register to contain NULL. An SQL NULL is 
  3290   3309       ** equivalent to an empty rowset.
  3291   3310       **
  3292   3311       ** Also initialize regReturn to contain the address of the instruction 
  3293   3312       ** immediately following the OP_Return at the bottom of the loop. This
  3294   3313       ** is required in a few obscure LEFT JOIN cases where control jumps
................................................................................
  3305   3324       iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);
  3306   3325   
  3307   3326       for(ii=0; ii<pOrWc->nTerm; ii++){
  3308   3327         WhereTerm *pOrTerm = &pOrWc->a[ii];
  3309   3328         if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
  3310   3329           WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
  3311   3330           /* Loop through table entries that match term pOrTerm. */
  3312         -        pSubWInfo = sqlite3WhereBegin(pParse, &oneTab, pOrTerm->pExpr, 0,
  3313         -                        WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE);
         3331  +        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0,
         3332  +                        WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
         3333  +                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
  3314   3334           if( pSubWInfo ){
  3315   3335             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3316   3336               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  3317   3337               int r;
  3318   3338               r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
  3319   3339                                            regRowid, 0);
  3320   3340               sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
  3321   3341                                    sqlite3VdbeCurrentAddr(v)+2, r, iSet);
  3322   3342             }
  3323   3343             sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);
         3344  +
         3345  +          /* The pSubWInfo->untestedTerms flag means that this OR term
         3346  +          ** contained one or more AND term from a notReady table.  The
         3347  +          ** terms from the notReady table could not be tested and will
         3348  +          ** need to be tested later.
         3349  +          */
         3350  +          if( pSubWInfo->untestedTerms ) untestedTerms = 1;
  3324   3351   
  3325   3352             /* Finish the loop through table entries that match term pOrTerm. */
  3326   3353             sqlite3WhereEnd(pSubWInfo);
  3327   3354           }
  3328   3355         }
  3329   3356       }
  3330   3357       sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
  3331         -    /* sqlite3VdbeAddOp2(v, OP_Null, 0, regRowset); */
  3332   3358       sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
  3333   3359       sqlite3VdbeResolveLabel(v, iLoopBody);
  3334   3360   
  3335         -    pLevel->op = OP_Return;
  3336         -    pLevel->p1 = regReturn;
  3337         -    disableTerm(pLevel, pTerm);
         3361  +    if( pWInfo->nLevel>1 ) sqlite3StackFree(pParse->db, pOrTab);
         3362  +    if( !untestedTerms ) disableTerm(pLevel, pTerm);
  3338   3363     }else
  3339   3364   #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  3340   3365   
  3341   3366     {
  3342   3367       /* Case 5:  There is no usable index.  We must do a complete
  3343   3368       **          scan of the entire table.
  3344   3369       */
................................................................................
  3358   3383     */
  3359   3384     k = 0;
  3360   3385     for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  3361   3386       Expr *pE;
  3362   3387       testcase( pTerm->wtFlags & TERM_VIRTUAL );
  3363   3388       testcase( pTerm->wtFlags & TERM_CODED );
  3364   3389       if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  3365         -    if( (pTerm->prereqAll & notReady)!=0 ) continue;
         3390  +    if( (pTerm->prereqAll & notReady)!=0 ){
         3391  +      testcase( pWInfo->untestedTerms==0
         3392  +               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
         3393  +      pWInfo->untestedTerms = 1;
         3394  +      continue;
         3395  +    }
  3366   3396       pE = pTerm->pExpr;
  3367   3397       assert( pE!=0 );
  3368   3398       if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
  3369   3399         continue;
  3370   3400       }
  3371   3401       sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
  3372   3402       k = 1;
................................................................................
  3381   3411       sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin);
  3382   3412       VdbeComment((v, "record LEFT JOIN hit"));
  3383   3413       sqlite3ExprCacheClear(pParse);
  3384   3414       for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){
  3385   3415         testcase( pTerm->wtFlags & TERM_VIRTUAL );
  3386   3416         testcase( pTerm->wtFlags & TERM_CODED );
  3387   3417         if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  3388         -      if( (pTerm->prereqAll & notReady)!=0 ) continue;
         3418  +      if( (pTerm->prereqAll & notReady)!=0 ){
         3419  +        assert( pWInfo->untestedTerms );
         3420  +        continue;
         3421  +      }
  3389   3422         assert( pTerm->pExpr );
  3390   3423         sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL);
  3391   3424         pTerm->wtFlags |= TERM_CODED;
  3392   3425       }
  3393   3426     }
  3394   3427     sqlite3ReleaseTempReg(pParse, iReleaseReg);
  3395   3428   
................................................................................
  3524   3557     SrcList *pTabList,    /* A list of all tables to be scanned */
  3525   3558     Expr *pWhere,         /* The WHERE clause */
  3526   3559     ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */
  3527   3560     u16 wctrlFlags        /* One of the WHERE_* flags defined in sqliteInt.h */
  3528   3561   ){
  3529   3562     int i;                     /* Loop counter */
  3530   3563     int nByteWInfo;            /* Num. bytes allocated for WhereInfo struct */
         3564  +  int nTabList;              /* Number of elements in pTabList */
  3531   3565     WhereInfo *pWInfo;         /* Will become the return value of this function */
  3532   3566     Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  3533   3567     Bitmask notReady;          /* Cursors that are not yet positioned */
  3534   3568     WhereMaskSet *pMaskSet;    /* The expression mask set */
  3535   3569     WhereClause *pWC;               /* Decomposition of the WHERE clause */
  3536   3570     struct SrcList_item *pTabItem;  /* A single entry from pTabList */
  3537   3571     WhereLevel *pLevel;             /* A single level in the pWInfo list */
................................................................................
  3542   3576     /* The number of tables in the FROM clause is limited by the number of
  3543   3577     ** bits in a Bitmask 
  3544   3578     */
  3545   3579     if( pTabList->nSrc>BMS ){
  3546   3580       sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
  3547   3581       return 0;
  3548   3582     }
         3583  +
         3584  +  /* This function normally generates a nested loop for all tables in 
         3585  +  ** pTabList.  But if the WHERE_ONETABLE_ONLY flag is set, then we should
         3586  +  ** only generate code for the first table in pTabList and assume that
         3587  +  ** any cursors associated with subsequent tables are uninitialized.
         3588  +  */
         3589  +  nTabList = (wctrlFlags & WHERE_ONETABLE_ONLY) ? 1 : pTabList->nSrc;
  3549   3590   
  3550   3591     /* Allocate and initialize the WhereInfo structure that will become the
  3551   3592     ** return value. A single allocation is used to store the WhereInfo
  3552   3593     ** struct, the contents of WhereInfo.a[], the WhereClause structure
  3553   3594     ** and the WhereMaskSet structure. Since WhereClause contains an 8-byte
  3554   3595     ** field (type Bitmask) it must be aligned on an 8-byte boundary on
  3555   3596     ** some architectures. Hence the ROUND8() below.
  3556   3597     */
  3557   3598     db = pParse->db;
  3558         -  nByteWInfo = ROUND8(sizeof(WhereInfo)+(pTabList->nSrc-1)*sizeof(WhereLevel));
         3599  +  nByteWInfo = ROUND8(sizeof(WhereInfo)+(nTabList-1)*sizeof(WhereLevel));
  3559   3600     pWInfo = sqlite3DbMallocZero(db, 
  3560   3601         nByteWInfo + 
  3561   3602         sizeof(WhereClause) +
  3562   3603         sizeof(WhereMaskSet)
  3563   3604     );
  3564   3605     if( db->mallocFailed ){
  3565   3606       goto whereBeginError;
  3566   3607     }
  3567         -  pWInfo->nLevel = pTabList->nSrc;
         3608  +  pWInfo->nLevel = nTabList;
  3568   3609     pWInfo->pParse = pParse;
  3569   3610     pWInfo->pTabList = pTabList;
  3570   3611     pWInfo->iBreak = sqlite3VdbeMakeLabel(v);
  3571   3612     pWInfo->pWC = pWC = (WhereClause *)&((u8 *)pWInfo)[nByteWInfo];
  3572   3613     pWInfo->wctrlFlags = wctrlFlags;
  3573   3614     pMaskSet = (WhereMaskSet*)&pWC[1];
  3574   3615   
................................................................................
  3579   3620     whereClauseInit(pWC, pParse, pMaskSet);
  3580   3621     sqlite3ExprCodeConstants(pParse, pWhere);
  3581   3622     whereSplit(pWC, pWhere, TK_AND);
  3582   3623       
  3583   3624     /* Special case: a WHERE clause that is constant.  Evaluate the
  3584   3625     ** expression and either jump over all of the code or fall thru.
  3585   3626     */
  3586         -  if( pWhere && (pTabList->nSrc==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
         3627  +  if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
  3587   3628       sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
  3588   3629       pWhere = 0;
  3589   3630     }
  3590   3631   
  3591   3632     /* Assign a bit from the bitmask to every term in the FROM clause.
  3592   3633     **
  3593   3634     ** When assigning bitmask values to FROM clause cursors, it must be
................................................................................
  3599   3640     ** bitmask for all tables to the left of the join.  Knowing the bitmask
  3600   3641     ** for all tables to the left of a left join is important.  Ticket #3015.
  3601   3642     **
  3602   3643     ** Configure the WhereClause.vmask variable so that bits that correspond
  3603   3644     ** to virtual table cursors are set. This is used to selectively disable 
  3604   3645     ** the OR-to-IN transformation in exprAnalyzeOrTerm(). It is not helpful 
  3605   3646     ** with virtual tables.
         3647  +  **
         3648  +  ** Note that bitmasks are created for all pTabList->nSrc tables in
         3649  +  ** pTabList, not just the first nTabList tables.  nTabList is normally
         3650  +  ** equal to pTabList->nSrc but might be shortened to 1 if the
         3651  +  ** WHERE_ONETABLE_ONLY flag is set.
  3606   3652     */
  3607   3653     assert( pWC->vmask==0 && pMaskSet->n==0 );
  3608   3654     for(i=0; i<pTabList->nSrc; i++){
  3609   3655       createMask(pMaskSet, pTabList->a[i].iCursor);
  3610   3656   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3611   3657       if( ALWAYS(pTabList->a[i].pTab) && IsVirtual(pTabList->a[i].pTab) ){
  3612   3658         pWC->vmask |= ((Bitmask)1 << i);
................................................................................
  3650   3696     ** clause.
  3651   3697     */
  3652   3698     notReady = ~(Bitmask)0;
  3653   3699     pTabItem = pTabList->a;
  3654   3700     pLevel = pWInfo->a;
  3655   3701     andFlags = ~0;
  3656   3702     WHERETRACE(("*** Optimizer Start ***\n"));
  3657         -  for(i=iFrom=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
         3703  +  for(i=iFrom=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){
  3658   3704       WhereCost bestPlan;         /* Most efficient plan seen so far */
  3659   3705       Index *pIdx;                /* Index for FROM table at pTabItem */
  3660   3706       int j;                      /* For looping over FROM tables */
  3661   3707       int bestJ = -1;             /* The value of j */
  3662   3708       Bitmask m;                  /* Bitmask value for j or bestJ */
  3663   3709       int isOptimal;              /* Iterator for optimal/non-optimal search */
  3664   3710   
................................................................................
  3695   3741       ** However, since the cost of a linear scan through table t2 is the same 
  3696   3742       ** as the cost of a linear scan through table t1, a simple greedy 
  3697   3743       ** algorithm may choose to use t2 for the outer loop, which is a much
  3698   3744       ** costlier approach.
  3699   3745       */
  3700   3746       for(isOptimal=1; isOptimal>=0 && bestJ<0; isOptimal--){
  3701   3747         Bitmask mask = (isOptimal ? 0 : notReady);
  3702         -      assert( (pTabList->nSrc-iFrom)>1 || isOptimal );
  3703         -      for(j=iFrom, pTabItem=&pTabList->a[j]; j<pTabList->nSrc; j++, pTabItem++){
         3748  +      assert( (nTabList-iFrom)>1 || isOptimal );
         3749  +      for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){
  3704   3750           int doNotReorder;    /* True if this table should not be reordered */
  3705   3751           WhereCost sCost;     /* Cost information from best[Virtual]Index() */
  3706   3752           ExprList *pOrderBy;  /* ORDER BY clause for index to optimize */
  3707   3753     
  3708   3754           doNotReorder =  (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0;
  3709   3755           if( j!=iFrom && doNotReorder ) break;
  3710   3756           m = getMask(pMaskSet, pTabItem->iCursor);
................................................................................
  3793   3839       pWInfo->a[0].plan.wsFlags &= ~WHERE_IDX_ONLY;
  3794   3840     }
  3795   3841   
  3796   3842     /* Open all tables in the pTabList and any indices selected for
  3797   3843     ** searching those tables.
  3798   3844     */
  3799   3845     sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  3800         -  for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
         3846  +  for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){
  3801   3847       Table *pTab;     /* Table to open */
  3802   3848       int iDb;         /* Index of database containing table/index */
  3803   3849   
  3804   3850   #ifndef SQLITE_OMIT_EXPLAIN
  3805   3851       if( pParse->explain==2 ){
  3806   3852         char *zMsg;
  3807   3853         struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
................................................................................
  3872   3918     pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
  3873   3919   
  3874   3920     /* Generate the code to do the search.  Each iteration of the for
  3875   3921     ** loop below generates code for a single nested loop of the VM
  3876   3922     ** program.
  3877   3923     */
  3878   3924     notReady = ~(Bitmask)0;
  3879         -  for(i=0; i<pTabList->nSrc; i++){
         3925  +  for(i=0; i<nTabList; i++){
  3880   3926       notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady);
  3881   3927       pWInfo->iContinue = pWInfo->a[i].addrCont;
  3882   3928     }
  3883   3929   
  3884   3930   #ifdef SQLITE_TEST  /* For testing and debugging use only */
  3885   3931     /* Record in the query plan information about the current table
  3886   3932     ** and the index used to access it (if any).  If the table itself
  3887   3933     ** is not used, its name is just '{}'.  If no index is used
  3888   3934     ** the index is listed as "{}".  If the primary key is used the
  3889   3935     ** index name is '*'.
  3890   3936     */
  3891         -  for(i=0; i<pTabList->nSrc; i++){
         3937  +  for(i=0; i<nTabList; i++){
  3892   3938       char *z;
  3893   3939       int n;
  3894   3940       pLevel = &pWInfo->a[i];
  3895   3941       pTabItem = &pTabList->a[pLevel->iFrom];
  3896   3942       z = pTabItem->zAlias;
  3897   3943       if( z==0 ) z = pTabItem->pTab->zName;
  3898   3944       n = sqlite3Strlen30(z);
................................................................................
  3952   3998     WhereLevel *pLevel;
  3953   3999     SrcList *pTabList = pWInfo->pTabList;
  3954   4000     sqlite3 *db = pParse->db;
  3955   4001   
  3956   4002     /* Generate loop termination code.
  3957   4003     */
  3958   4004     sqlite3ExprCacheClear(pParse);
  3959         -  for(i=pTabList->nSrc-1; i>=0; i--){
         4005  +  for(i=pWInfo->nLevel-1; i>=0; i--){
  3960   4006       pLevel = &pWInfo->a[i];
  3961   4007       sqlite3VdbeResolveLabel(v, pLevel->addrCont);
  3962   4008       if( pLevel->op!=OP_Noop ){
  3963   4009         sqlite3VdbeAddOp2(v, pLevel->op, pLevel->p1, pLevel->p2);
  3964   4010         sqlite3VdbeChangeP5(v, pLevel->p5);
  3965   4011       }
  3966   4012       if( pLevel->plan.wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
................................................................................
  3998   4044     /* The "break" point is here, just past the end of the outer loop.
  3999   4045     ** Set it.
  4000   4046     */
  4001   4047     sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
  4002   4048   
  4003   4049     /* Close all of the cursors that were opened by sqlite3WhereBegin.
  4004   4050     */
  4005         -  for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
         4051  +  assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc );
         4052  +  for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
  4006   4053       struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
  4007   4054       Table *pTab = pTabItem->pTab;
  4008   4055       assert( pTab!=0 );
  4009   4056       if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ) continue;
  4010   4057       if( (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0 ){
  4011   4058         if( !pWInfo->okOnePass && (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0 ){
  4012   4059           sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);

Added test/tkt-31338dca7e.test.

            1  +# 2009 December 16
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests to verify that ticket [31338dca7e] has been
           14  +# fixed.  Ticket [31338dca7e] demonstrates problems with the OR-clause
           15  +# optimization in joins where the WHERE clause is of the form
           16  +#
           17  +#     (x AND y) OR z
           18  +#
           19  +# And the x and y subterms from from different tables of the join.
           20  +#
           21  +
           22  +set testdir [file dirname $argv0]
           23  +source $testdir/tester.tcl
           24  +
           25  +do_test tkt-31338-1.1 {
           26  +  db eval {
           27  +    CREATE TABLE t1(x);
           28  +    CREATE TABLE t2(y);
           29  +    INSERT INTO t1 VALUES(111);
           30  +    INSERT INTO t1 VALUES(222);
           31  +    INSERT INTO t2 VALUES(333);
           32  +    INSERT INTO t2 VALUES(444);
           33  +    SELECT * FROM t1, t2
           34  +     WHERE (x=111 AND y!=444) OR x=222
           35  +     ORDER BY x, y;
           36  +  }
           37  +} {111 333 222 333 222 444}
           38  +
           39  +do_test tkt-31338-1.2 {
           40  +  db eval {
           41  +    CREATE INDEX t1x ON t1(x);
           42  +    SELECT * FROM t1, t2
           43  +     WHERE (x=111 AND y!=444) OR x=222
           44  +     ORDER BY x, y;
           45  +  }
           46  +} {111 333 222 333 222 444}
           47  +
           48  +do_test tkt-31338-2.1 {
           49  +  db eval {
           50  +    CREATE TABLE t3(v,w);
           51  +    CREATE TABLE t4(x,y);
           52  +    CREATE TABLE t5(z);
           53  +    INSERT INTO t3 VALUES(111,222);
           54  +    INSERT INTO t3 VALUES(333,444);
           55  +    INSERT INTO t4 VALUES(222,333);
           56  +    INSERT INTO t4 VALUES(444,555);
           57  +    INSERT INTO t5 VALUES(888);
           58  +    INSERT INTO t5 VALUES(999);
           59  +    
           60  +    SELECT * FROM t3, t4, t5
           61  +     WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444)
           62  +     ORDER BY v, w, x, y, z;
           63  +  }
           64  +} {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999}
           65  +
           66  +do_test tkt-31338-2.2 {
           67  +  db eval {
           68  +   CREATE INDEX t3v ON t3(v);
           69  +   CREATE INDEX t4x ON t4(x);
           70  +    SELECT * FROM t3, t4, t5
           71  +     WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444)
           72  +     ORDER BY v, w, x, y, z;
           73  +  }
           74  +} {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999}
           75  +
           76  +
           77  +finish_test

Changes to test/where8.test.

   394    394       INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine');
   395    395       INSERT INTO t4 VALUES('from', 'of', 'all');
   396    396       INSERT INTO t4 VALUES(0938446095, 'same', NULL);
   397    397       INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL);
   398    398       INSERT INTO t4 VALUES('his', 'of', 378678316.5);
   399    399       INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647);
   400    400       INSERT INTO t4 VALUES('hills', 'all', 'peak');
          401  +    CREATE TABLE t5(s);
          402  +    INSERT INTO t5 VALUES('tab-t5');
          403  +    CREATE TABLE t6(t);
          404  +    INSERT INTO t6 VALUES(123456);
   401    405       COMMIT;
   402    406     }
   403    407   } {}
   404    408   
   405    409   catch {unset results}
   406    410   catch {unset A}
   407    411   catch {unset B}
................................................................................
   635    639   193  { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' }
   636    640   194  { SELECT * FROM t3, t4 WHERE 'remarkably' < g }
   637    641   195  { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f }
   638    642   196  { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b }
   639    643   197  { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g }
   640    644   198  { SELECT * FROM t3, t4 WHERE g < 8979323846 }
   641    645   199  { SELECT * FROM t3, t4 WHERE 'are' <= b }
          646  +200  { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825)
          647  +                               OR (a=6939937510 AND f=2643383279) }
          648  +201  { SELECT * FROM t3, t4, t5, t6
          649  +        WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5)
          650  +           OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) }
          651  +202  { SELECT * FROM t3, t4, t5, t6
          652  +        WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5)
          653  +           OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) }
   642    654   
   643    655     } {
   644    656       do_test where8-4.$A.$B.1 {
   645    657         unset -nocomplain R
   646    658         set R [execsql $sql]
   647    659         if {![info exists results($B)]} {
   648    660           set results($B) $R