/ Check-in [e72186f2]
Login

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

Overview
Comment:If a binary operator in a WHERE clause that should be performed with no affinity conversions applied to its operands (see http://www.sqlite.org/datatype3.html) is optimized by index lookup, do not apply any conversions to the key value before looking it up in the index. Fix for [93fb9f89d6].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e72186f2d68d28c2e0c32894f9adb28c155b5f63
User & Date: dan 2009-08-13 19:21:17
Original Comment: If a binary operator in a WHERE clause that should be performed with no affinity conversions applied to its operands (see http://www.sqlite.org/datatype3.html) is optimized by index lookup, do not apply any conversions to the key value before looking it up in the index. Fix for 93fb9f89d6.
References
2016-09-03
15:24 Ticket [199df416] Different answer with and without index on IN operator with type mismatch status still Open with 6 other changes artifact: 6a413497 user: drh
2009-08-19
15:21 Fixed ticket [93fb9f89]: Index causes incorrect WHERE clause evaluation plus 3 other changes artifact: 10bc6825 user: drh
Context
2009-08-13
19:54
Tweak to the new whereB.test file to make it more consistent. check-in: 06098505 user: drh tags: trunk
19:21
If a binary operator in a WHERE clause that should be performed with no affinity conversions applied to its operands (see http://www.sqlite.org/datatype3.html) is optimized by index lookup, do not apply any conversions to the key value before looking it up in the index. Fix for [93fb9f89d6]. check-in: e72186f2 user: dan tags: trunk
18:14
Enhancements to the whereB.test to check more affinity corner cases. check-in: 10484598 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   618    618       }else{
   619    619         sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j);
   620    620         sqlite3ColumnDefault(v, pTab, idx, -1);
   621    621       }
   622    622     }
   623    623     if( doMakeRec ){
   624    624       sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut);
   625         -    sqlite3IndexAffinityStr(v, pIdx);
          625  +    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
   626    626       sqlite3ExprCacheAffinityChange(pParse, regBase, nCol+1);
   627    627     }
   628    628     sqlite3ReleaseTempRange(pParse, regBase, nCol+1);
   629    629     return regBase;
   630    630   }
   631    631   
   632    632   /* Make sure "isView" gets undefined in case this file becomes part of
   633    633   ** the amalgamation - so that subsequent files do not see isView as a
   634    634   ** macro. */
   635    635   #undef isView

Changes to src/insert.c.

    33     33     sqlite3TableLock(p, iDb, pTab->tnum, (opcode==OP_OpenWrite)?1:0, pTab->zName);
    34     34     sqlite3VdbeAddOp3(v, opcode, iCur, pTab->tnum, iDb);
    35     35     sqlite3VdbeChangeP4(v, -1, SQLITE_INT_TO_PTR(pTab->nCol), P4_INT32);
    36     36     VdbeComment((v, "%s", pTab->zName));
    37     37   }
    38     38   
    39     39   /*
    40         -** Set P4 of the most recently inserted opcode to a column affinity
    41         -** string for index pIdx. A column affinity string has one character
    42         -** for each column in the table, according to the affinity of the column:
           40  +** Return a pointer to the column affinity string associated with index
           41  +** pIdx. A column affinity string has one character for each column in 
           42  +** the table, according to the affinity of the column:
    43     43   **
    44     44   **  Character      Column affinity
    45     45   **  ------------------------------
    46     46   **  'a'            TEXT
    47     47   **  'b'            NONE
    48     48   **  'c'            NUMERIC
    49     49   **  'd'            INTEGER
    50     50   **  'e'            REAL
    51     51   **
    52     52   ** An extra 'b' is appended to the end of the string to cover the
    53     53   ** rowid that appears as the last column in every index.
           54  +**
           55  +** Memory for the buffer containing the column index affinity string
           56  +** is managed along with the rest of the Index structure. It will be
           57  +** released when sqlite3DeleteIndex() is called.
    54     58   */
    55         -void sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
           59  +const char *sqlite3IndexAffinityStr(Vdbe *v, Index *pIdx){
    56     60     if( !pIdx->zColAff ){
    57     61       /* The first time a column affinity string for a particular index is
    58     62       ** required, it is allocated and populated here. It is then stored as
    59     63       ** a member of the Index structure for subsequent use.
    60     64       **
    61     65       ** The column affinity string will eventually be deleted by
    62     66       ** sqliteDeleteIndex() when the Index structure itself is cleaned
................................................................................
    64     68       */
    65     69       int n;
    66     70       Table *pTab = pIdx->pTable;
    67     71       sqlite3 *db = sqlite3VdbeDb(v);
    68     72       pIdx->zColAff = (char *)sqlite3Malloc(pIdx->nColumn+2);
    69     73       if( !pIdx->zColAff ){
    70     74         db->mallocFailed = 1;
    71         -      return;
           75  +      return 0;
    72     76       }
    73     77       for(n=0; n<pIdx->nColumn; n++){
    74     78         pIdx->zColAff[n] = pTab->aCol[pIdx->aiColumn[n]].affinity;
    75     79       }
    76     80       pIdx->zColAff[n++] = SQLITE_AFF_NONE;
    77     81       pIdx->zColAff[n] = 0;
    78     82     }
    79     83    
    80         -  sqlite3VdbeChangeP4(v, -1, pIdx->zColAff, 0);
           84  +  return pIdx->zColAff;
    81     85   }
    82     86   
    83     87   /*
    84     88   ** Set P4 of the most recently inserted opcode to a column affinity
    85     89   ** string for table pTab. A column affinity string has one character
    86     90   ** for each column indexed by the index, according to the affinity of the
    87     91   ** column:
................................................................................
  1294   1298           sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
  1295   1299         }else{
  1296   1300           sqlite3VdbeAddOp2(v, OP_SCopy, regData+idx, regIdx+i);
  1297   1301         }
  1298   1302       }
  1299   1303       sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
  1300   1304       sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
  1301         -    sqlite3IndexAffinityStr(v, pIdx);
         1305  +    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
  1302   1306       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);
  1303   1307   
  1304   1308       /* Find out what action to take in case there is an indexing conflict */
  1305   1309       onError = pIdx->onError;
  1306   1310       if( onError==OE_None ){ 
  1307   1311         sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
  1308   1312         continue;  /* pIdx is not a UNIQUE index */

Changes to src/sqliteInt.h.

  2751   2751   */
  2752   2752   #define getVarint32(A,B)  (u8)((*(A)<(u8)0x80) ? ((B) = (u32)*(A)),1 : sqlite3GetVarint32((A), (u32 *)&(B)))
  2753   2753   #define putVarint32(A,B)  (u8)(((u32)(B)<(u32)0x80) ? (*(A) = (unsigned char)(B)),1 : sqlite3PutVarint32((A), (B)))
  2754   2754   #define getVarint    sqlite3GetVarint
  2755   2755   #define putVarint    sqlite3PutVarint
  2756   2756   
  2757   2757   
  2758         -void sqlite3IndexAffinityStr(Vdbe *, Index *);
         2758  +const char *sqlite3IndexAffinityStr(Vdbe *, Index *);
  2759   2759   void sqlite3TableAffinityStr(Vdbe *, Table *);
  2760   2760   char sqlite3CompareAffinity(Expr *pExpr, char aff2);
  2761   2761   int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity);
  2762   2762   char sqlite3ExprAffinity(Expr *pExpr);
  2763   2763   int sqlite3Atoi64(const char*, i64*);
  2764   2764   void sqlite3Error(sqlite3*, int, const char*,...);
  2765   2765   void *sqlite3HexToBlob(sqlite3*, const char *z, int n);

Changes to src/where.c.

  2271   2271           disableTerm(pLevel, pOther);
  2272   2272         }
  2273   2273       }
  2274   2274     }
  2275   2275   }
  2276   2276   
  2277   2277   /*
  2278         -** Apply the affinities associated with the first n columns of index
  2279         -** pIdx to the values in the n registers starting at base.
         2278  +** Code an OP_Affinity opcode to apply the column affinity string zAff
         2279  +** to the n registers starting at base. 
         2280  +**
         2281  +** Buffer zAff was allocated using sqlite3DbMalloc(). It is the 
         2282  +** responsibility of this function to arrange for it to be eventually
         2283  +** freed using sqlite3DbFree().
  2280   2284   */
  2281         -static void codeApplyAffinity(Parse *pParse, int base, int n, Index *pIdx){
  2282         -  if( n>0 ){
  2283         -    Vdbe *v = pParse->pVdbe;
  2284         -    assert( v!=0 );
  2285         -    sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
  2286         -    sqlite3IndexAffinityStr(v, pIdx);
  2287         -    sqlite3ExprCacheAffinityChange(pParse, base, n);
  2288         -  }
         2285  +static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
         2286  +  Vdbe *v = pParse->pVdbe;
         2287  +  assert( v!=0 );
         2288  +  sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
         2289  +  sqlite3VdbeChangeP4(v, -1, zAff, P4_DYNAMIC);
         2290  +  sqlite3ExprCacheAffinityChange(pParse, base, n);
  2289   2291   }
  2290   2292   
  2291   2293   
  2292   2294   /*
  2293   2295   ** Generate code for a single equality term of the WHERE clause.  An equality
  2294   2296   ** term can be either X=expr or X IN (...).   pTerm is the term to be 
  2295   2297   ** coded.
................................................................................
  2372   2374   **
  2373   2375   ** This routine always allocates at least one memory cell and returns
  2374   2376   ** the index of that memory cell. The code that
  2375   2377   ** calls this routine will use that memory cell to store the termination
  2376   2378   ** key value of the loop.  If one or more IN operators appear, then
  2377   2379   ** this routine allocates an additional nEq memory cells for internal
  2378   2380   ** use.
         2381  +**
         2382  +** Before returning, *pzAff is set to point to a buffer containing a
         2383  +** copy of the column affinity string of the index allocated using
         2384  +** sqlite3DbMalloc(). Except, entries in the copy of the string associated
         2385  +** with equality constraints that use NONE affinity are set to
         2386  +** SQLITE_AFF_NONE. This is to deal with SQL such as the following:
         2387  +**
         2388  +**   CREATE TABLE t1(a TEXT PRIMARY KEY, b);
         2389  +**   SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;
         2390  +**
         2391  +** In the example above, the index on t1(a) has TEXT affinity. But since
         2392  +** the right hand side of the equality constraint (t2.b) has NONE affinity,
         2393  +** no conversion should be attempted before using a t2.b value as part of
         2394  +** a key to search the index. Hence the first byte in the returned affinity
         2395  +** string in this example would be set to SQLITE_AFF_NONE.
  2379   2396   */
  2380   2397   static int codeAllEqualityTerms(
  2381   2398     Parse *pParse,        /* Parsing context */
  2382   2399     WhereLevel *pLevel,   /* Which nested loop of the FROM we are coding */
  2383   2400     WhereClause *pWC,     /* The WHERE clause */
  2384   2401     Bitmask notReady,     /* Which parts of FROM have not yet been coded */
  2385         -  int nExtraReg         /* Number of extra registers to allocate */
         2402  +  int nExtraReg,        /* Number of extra registers to allocate */
         2403  +  char **pzAff          /* OUT: Set to point to affinity string */
  2386   2404   ){
  2387   2405     int nEq = pLevel->plan.nEq;   /* The number of == or IN constraints to code */
  2388   2406     Vdbe *v = pParse->pVdbe;      /* The vm under construction */
  2389   2407     Index *pIdx;                  /* The index being used for this loop */
  2390   2408     int iCur = pLevel->iTabCur;   /* The cursor of the table */
  2391   2409     WhereTerm *pTerm;             /* A single constraint term */
  2392   2410     int j;                        /* Loop counter */
  2393   2411     int regBase;                  /* Base register */
  2394   2412     int nReg;                     /* Number of registers to allocate */
         2413  +  char *zAff;                   /* Affinity string to return */
  2395   2414   
  2396   2415     /* This module is only called on query plans that use an index. */
  2397   2416     assert( pLevel->plan.wsFlags & WHERE_INDEXED );
  2398   2417     pIdx = pLevel->plan.u.pIdx;
  2399   2418   
  2400   2419     /* Figure out how many memory cells we will need then allocate them.
  2401   2420     */
  2402   2421     regBase = pParse->nMem + 1;
  2403   2422     nReg = pLevel->plan.nEq + nExtraReg;
  2404   2423     pParse->nMem += nReg;
         2424  +
         2425  +  zAff = sqlite3DbStrDup(pParse->db, sqlite3IndexAffinityStr(v, pIdx));
         2426  +  if( !zAff ){
         2427  +    pParse->db->mallocFailed = 1;
         2428  +  }
  2405   2429   
  2406   2430     /* Evaluate the equality constraints
  2407   2431     */
  2408   2432     assert( pIdx->nColumn>=nEq );
  2409   2433     for(j=0; j<nEq; j++){
  2410   2434       int r1;
  2411   2435       int k = pIdx->aiColumn[j];
................................................................................
  2421   2445           sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
  2422   2446         }
  2423   2447       }
  2424   2448       testcase( pTerm->eOperator & WO_ISNULL );
  2425   2449       testcase( pTerm->eOperator & WO_IN );
  2426   2450       if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
  2427   2451         sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
         2452  +      if( zAff 
         2453  +       && sqlite3CompareAffinity(pTerm->pExpr->pRight, zAff[j])==SQLITE_AFF_NONE
         2454  +      ){
         2455  +        zAff[j] = SQLITE_AFF_NONE;
         2456  +      }
  2428   2457       }
  2429   2458     }
         2459  +  *pzAff = zAff;
  2430   2460     return regBase;
  2431   2461   }
  2432   2462   
  2433   2463   /*
  2434   2464   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  2435   2465   ** implementation described by pWInfo.
  2436   2466   */
................................................................................
  2678   2708       int endEq;                   /* True if range end uses ==, >= or <= */
  2679   2709       int start_constraints;       /* Start of range is constrained */
  2680   2710       int nConstraint;             /* Number of constraint terms */
  2681   2711       Index *pIdx;         /* The index we will be using */
  2682   2712       int iIdxCur;         /* The VDBE cursor for the index */
  2683   2713       int nExtraReg = 0;   /* Number of extra registers needed */
  2684   2714       int op;              /* Instruction opcode */
         2715  +    char *zAff;
  2685   2716   
  2686   2717       pIdx = pLevel->plan.u.pIdx;
  2687   2718       iIdxCur = pLevel->iIdxCur;
  2688   2719       k = pIdx->aiColumn[nEq];     /* Column for inequality constraints */
  2689   2720   
  2690   2721       /* If this loop satisfies a sort order (pOrderBy) request that 
  2691   2722       ** was passed to this function to implement a "SELECT min(x) ..." 
................................................................................
  2717   2748         nExtraReg = 1;
  2718   2749       }
  2719   2750   
  2720   2751       /* Generate code to evaluate all constraint terms using == or IN
  2721   2752       ** and store the values of those terms in an array of registers
  2722   2753       ** starting at regBase.
  2723   2754       */
  2724         -    regBase = codeAllEqualityTerms(pParse, pLevel, pWC, notReady, nExtraReg);
         2755  +    regBase = codeAllEqualityTerms(
         2756  +        pParse, pLevel, pWC, notReady, nExtraReg, &zAff
         2757  +    );
  2725   2758       addrNxt = pLevel->addrNxt;
  2726         -
  2727   2759   
  2728   2760       /* If we are doing a reverse order scan on an ascending index, or
  2729   2761       ** a forward order scan on a descending index, interchange the 
  2730   2762       ** start and end terms (pRangeStart and pRangeEnd).
  2731   2763       */
  2732   2764       if( bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC) ){
  2733   2765         SWAP(WhereTerm *, pRangeEnd, pRangeStart);
................................................................................
  2740   2772       startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);
  2741   2773       endEq =   !pRangeEnd || pRangeEnd->eOperator & (WO_LE|WO_GE);
  2742   2774       start_constraints = pRangeStart || nEq>0;
  2743   2775   
  2744   2776       /* Seek the index cursor to the start of the range. */
  2745   2777       nConstraint = nEq;
  2746   2778       if( pRangeStart ){
  2747         -      sqlite3ExprCode(pParse, pRangeStart->pExpr->pRight, regBase+nEq);
         2779  +      Expr *pRight = pRangeStart->pExpr->pRight;
         2780  +      sqlite3ExprCode(pParse, pRight, regBase+nEq);
  2748   2781         sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
         2782  +      if( zAff 
         2783  +       && sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE
         2784  +      ){
         2785  +        /* Since the comparison is to be performed with no conversions applied
         2786  +        ** to the operands, set the affinity to apply to pRight to 
         2787  +        ** SQLITE_AFF_NONE.  */
         2788  +        zAff[nConstraint] = SQLITE_AFF_NONE;
         2789  +      }
  2749   2790         nConstraint++;
  2750   2791       }else if( isMinQuery ){
  2751   2792         sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
  2752   2793         nConstraint++;
  2753   2794         startEq = 0;
  2754   2795         start_constraints = 1;
  2755   2796       }
  2756         -    codeApplyAffinity(pParse, regBase, nConstraint, pIdx);
         2797  +    codeApplyAffinity(pParse, regBase, nConstraint, zAff);
  2757   2798       op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
  2758   2799       assert( op!=0 );
  2759   2800       testcase( op==OP_Rewind );
  2760   2801       testcase( op==OP_Last );
  2761   2802       testcase( op==OP_SeekGt );
  2762   2803       testcase( op==OP_SeekGe );
  2763   2804       testcase( op==OP_SeekLe );
................................................................................
  2766   2807                         SQLITE_INT_TO_PTR(nConstraint), P4_INT32);
  2767   2808   
  2768   2809       /* Load the value for the inequality constraint at the end of the
  2769   2810       ** range (if any).
  2770   2811       */
  2771   2812       nConstraint = nEq;
  2772   2813       if( pRangeEnd ){
         2814  +      Expr *pRight = pRangeEnd->pExpr->pRight;
  2773   2815         sqlite3ExprCacheRemove(pParse, regBase+nEq);
  2774         -      sqlite3ExprCode(pParse, pRangeEnd->pExpr->pRight, regBase+nEq);
         2816  +      sqlite3ExprCode(pParse, pRight, regBase+nEq);
  2775   2817         sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  2776         -      codeApplyAffinity(pParse, regBase, nEq+1, pIdx);
         2818  +      zAff = sqlite3DbStrDup(pParse->db, zAff);
         2819  +      if( zAff 
         2820  +       && sqlite3CompareAffinity(pRight, zAff[nConstraint])==SQLITE_AFF_NONE
         2821  +      ){
         2822  +        /* Since the comparison is to be performed with no conversions applied
         2823  +        ** to the operands, set the affinity to apply to pRight to 
         2824  +        ** SQLITE_AFF_NONE.  */
         2825  +        zAff[nConstraint] = SQLITE_AFF_NONE;
         2826  +      }
         2827  +      codeApplyAffinity(pParse, regBase, nEq+1, zAff);
  2777   2828         nConstraint++;
  2778   2829       }
  2779   2830   
  2780   2831       /* Top of the loop body */
  2781   2832       pLevel->p2 = sqlite3VdbeCurrentAddr(v);
  2782   2833   
  2783   2834       /* Check if the index cursor is past the end of the range. */