/ Check-in [07e69f43]
Login

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

Overview
Comment:Fix the vector BETWEEN operator so that it only evaluates the left-most vector expression once. Add support for vector comparisons in the CASE operator.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 07e69f43a294d35b5145a2b0242ee42d50adab14
User & Date: drh 2016-08-22 14:30:05
Context
2016-08-23
17:30
Fix an uninitialized variable in CASE expression code generation. check-in: c8ffae05 user: drh tags: rowvalue
2016-08-22
14:30
Fix the vector BETWEEN operator so that it only evaluates the left-most vector expression once. Add support for vector comparisons in the CASE operator. check-in: 07e69f43 user: drh tags: rowvalue
00:48
Reinstate the mechanism in BETWEEN that avoids evaluating the first expression more than once, but fix the affinity extractor so that it works with this mechanism. The de-duplication of the first expression still does not work for vector expressions, though. check-in: 2f39987f user: drh tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   */
    15     15   #include "sqliteInt.h"
           16  +
           17  +/* Forward declarations */
           18  +static void exprCodeBetween(Parse*,Expr*,int,void(*)(Parse*,Expr*,int,int),int);
           19  +static int exprCodeVector(Parse *pParse, Expr *p, int *piToFree);
           20  +
    16     21   
    17     22   /*
    18     23   ** Return the 'affinity' of the expression pExpr if any.
    19     24   **
    20     25   ** If pExpr is a column, a reference to a column via an 'AS' alias,
    21     26   ** or a sub-select with a column as the return value, then the 
    22     27   ** affinity of that column is returned. Otherwise, 0x00 is returned,
................................................................................
   322    327   /*
   323    328   ** If the expression passed as the only argument is of type TK_VECTOR 
   324    329   ** return the number of expressions in the vector. Or, if the expression
   325    330   ** is a sub-select, return the number of columns in the sub-select. For
   326    331   ** any other type of expression, return 1.
   327    332   */
   328    333   int sqlite3ExprVectorSize(Expr *pExpr){
   329         -  if( pExpr->op==TK_VECTOR ){
          334  +  u8 op = pExpr->op;
          335  +  if( op==TK_REGISTER ) op = pExpr->op2;
          336  +  if( op==TK_VECTOR ){
   330    337       return pExpr->x.pList->nExpr;
   331         -  }else if( pExpr->op==TK_SELECT ){
          338  +  }else if( op==TK_SELECT ){
   332    339       return pExpr->x.pSelect->pEList->nExpr;
   333    340     }else{
   334    341       return 1;
   335    342     }
   336    343   }
   337    344   
   338    345   #ifndef SQLITE_OMIT_SUBQUERY
................................................................................
   350    357   ** just the expression for the i-th term of the result set, and may
   351    358   ** not be ready for evaluation because the table cursor has not yet
   352    359   ** been positioned.
   353    360   */
   354    361   Expr *sqlite3VectorFieldSubexpr(Expr *pVector, int i){
   355    362     assert( i<sqlite3ExprVectorSize(pVector) );
   356    363     if( sqlite3ExprIsVector(pVector) ){
   357         -    if( pVector->op==TK_SELECT ){
          364  +    if( pVector->op==TK_SELECT
          365  +     || (pVector->op==TK_REGISTER && pVector->op2==TK_SELECT)
          366  +    ){
   358    367         return pVector->x.pSelect->pEList->a[i].pExpr;
   359    368       }else{
   360    369         return pVector->x.pList->a[i].pExpr;
   361    370       }
   362    371     }
   363    372     return pVector;
   364    373   }
................................................................................
   463    472     Parse *pParse,                  /* Parse context */
   464    473     Expr *pVector,                  /* Vector to extract element from */
   465    474     int iField,                     /* Field to extract from pVector */
   466    475     int regSelect,                  /* First in array of registers */
   467    476     Expr **ppExpr,                  /* OUT: Expression element */
   468    477     int *pRegFree                   /* OUT: Temp register to free */
   469    478   ){
   470         -  assert( pVector->op==TK_VECTOR || pVector->op==TK_SELECT );
   471         -  assert( pParse->nErr || pParse->db->mallocFailed
   472         -          || (pVector->op==TK_VECTOR)==(regSelect==0) );
   473         -  if( pVector->op==TK_SELECT ){
          479  +  u8 op = pVector->op;
          480  +  assert( op==TK_VECTOR || op==TK_SELECT || op==TK_REGISTER );
          481  +  if( op==TK_REGISTER ){
          482  +    *ppExpr = sqlite3VectorFieldSubexpr(pVector, iField);
          483  +    return pVector->iTable+iField;
          484  +  }
          485  +  if( op==TK_SELECT ){
   474    486       *ppExpr = pVector->x.pSelect->pEList->a[iField].pExpr;
   475    487        return regSelect+iField;
   476    488     }
   477    489     *ppExpr = pVector->x.pList->a[iField].pExpr;
   478    490     return sqlite3ExprCodeTemp(pParse, *ppExpr, pRegFree);
   479    491   }
   480    492   
................................................................................
  2626   2638     Parse *pParse,        /* Parsing and code generating context */
  2627   2639     Expr *pExpr,          /* The IN expression */
  2628   2640     int destIfFalse,      /* Jump here if LHS is not contained in the RHS */
  2629   2641     int destIfNull        /* Jump here if the results are unknown due to NULLs */
  2630   2642   ){
  2631   2643     int rRhsHasNull = 0;  /* Register that is true if RHS contains NULL values */
  2632   2644     int eType;            /* Type of the RHS */
  2633         -  int r1;               /* Temporary use register */
         2645  +  int r1, r2;           /* Temporary use registers */
  2634   2646     Vdbe *v;              /* Statement under construction */
  2635   2647     int *aiMap = 0;       /* Map from vector field to index column */
  2636   2648     char *zAff = 0;       /* Affinity string for comparisons */
  2637   2649     int nVector;          /* Size of vectors for this IN(...) op */
         2650  +  int iDummy;           /* Dummy parameter to exprCodeVector() */
  2638   2651     Expr *pLeft = pExpr->pLeft;
  2639   2652     int i;
  2640   2653   
  2641   2654     if( sqlite3ExprCheckIN(pParse, pExpr) ) return;
  2642   2655     zAff = exprINAffinity(pParse, pExpr);
  2643   2656     if( zAff==0 ) return;
  2644   2657     nVector = sqlite3ExprVectorSize(pExpr->pLeft);
................................................................................
  2667   2680   
  2668   2681     /* Code the LHS, the <expr> from "<expr> IN (...)". If the LHS is a 
  2669   2682     ** vector, then it is stored in an array of nVector registers starting 
  2670   2683     ** at r1.
  2671   2684     */
  2672   2685     r1 = sqlite3GetTempRange(pParse, nVector);
  2673   2686     sqlite3ExprCachePush(pParse);
  2674         -  if( nVector>1 && (pLeft->flags & EP_xIsSelect) ){
  2675         -    int regSelect = sqlite3CodeSubselect(pParse, pLeft, 0, 0);
  2676         -    for(i=0; i<nVector; i++){
  2677         -      sqlite3VdbeAddOp3(v, OP_Copy, regSelect+i, r1+aiMap[i], 0);
  2678         -    }
  2679         -  }else{
  2680         -    for(i=0; i<nVector; i++){
  2681         -      Expr *pLhs = sqlite3VectorFieldSubexpr(pLeft, i);
  2682         -      sqlite3ExprCode(pParse, pLhs, r1+aiMap[i]);
  2683         -    }
         2687  +  r2 = exprCodeVector(pParse, pLeft, &iDummy);
         2688  +  for(i=0; i<nVector; i++){
         2689  +    sqlite3VdbeAddOp3(v, OP_Copy, r2+i, r1+aiMap[i], 0);
  2684   2690     }
  2685   2691   
  2686   2692     /* If sqlite3FindInIndex() did not find or create an index that is
  2687   2693     ** suitable for evaluating the IN operator, then evaluate using a
  2688   2694     ** sequence of comparisons.
  2689   2695     */
  2690   2696     if( eType==IN_INDEX_NOOP ){
................................................................................
  3224   3230     }
  3225   3231     return 0;
  3226   3232   }
  3227   3233   #endif /* SQLITE_DEBUG || SQLITE_COVERAGE_TEST */
  3228   3234   
  3229   3235   
  3230   3236   /*
  3231         -** Convert an expression node to a TK_REGISTER
         3237  +** Convert a scalar expression node to a TK_REGISTER referencing
         3238  +** register iReg.  The caller must ensure that iReg already contains
         3239  +** the correct value for the expression.
  3232   3240   */
  3233   3241   static void exprToRegister(Expr *p, int iReg){
  3234   3242     p->op2 = p->op;
  3235   3243     p->op = TK_REGISTER;
  3236   3244     p->iTable = iReg;
  3237   3245     ExprClearProperty(p, EP_Skip);
  3238   3246   }
  3239   3247   
  3240         -static void exprCodeBetween(Parse*,Expr*,int,void(*)(Parse*,Expr*,int,int),int);
         3248  +/*
         3249  +** Evaluate an expression (either a vector or a scalar expression) and store
         3250  +** the result in continguous temporary registers.  Return the index of
         3251  +** the first register used to store the result.
         3252  +**
         3253  +** If the returned result register is a temporary scalar, then also write
         3254  +** that register number into *piFreeable.  If the returned result register
         3255  +** is not a temporary or if the expression is a vector set *piFreeable
         3256  +** to 0.
         3257  +*/
         3258  +static int exprCodeVector(Parse *pParse, Expr *p, int *piFreeable){
         3259  +  int iResult;
         3260  +  int nResult = sqlite3ExprVectorSize(p);
         3261  +  if( nResult==1 ){
         3262  +    iResult = sqlite3ExprCodeTemp(pParse, p, piFreeable);
         3263  +  }else{
         3264  +    *piFreeable = 0;
         3265  +    if( p->op==TK_SELECT ){
         3266  +      iResult = sqlite3CodeSubselect(pParse, p, 0, 0);
         3267  +    }else{
         3268  +      int i;
         3269  +      iResult = pParse->nMem+1;
         3270  +      pParse->nMem += nResult;
         3271  +      for(i=0; i<nResult; i++){
         3272  +        sqlite3ExprCode(pParse, p->x.pList->a[i].pExpr, i+iResult);
         3273  +      }
         3274  +    }
         3275  +  }
         3276  +  return iResult;
         3277  +}
         3278  +
  3241   3279   
  3242   3280   /*
  3243   3281   ** Generate code into the current Vdbe to evaluate the given
  3244   3282   ** expression.  Attempt to store the results in register "target".
  3245   3283   ** Return the register where results are stored.
  3246   3284   **
  3247   3285   ** With this routine, there is no guarantee that results will
................................................................................
  3777   3815         pEList = pExpr->x.pList;
  3778   3816         aListelem = pEList->a;
  3779   3817         nExpr = pEList->nExpr;
  3780   3818         endLabel = sqlite3VdbeMakeLabel(v);
  3781   3819         if( (pX = pExpr->pLeft)!=0 ){
  3782   3820           tempX = *pX;
  3783   3821           testcase( pX->op==TK_COLUMN );
  3784         -        exprToRegister(&tempX, sqlite3ExprCodeTemp(pParse, pX, &regFree1));
         3822  +        exprToRegister(&tempX, exprCodeVector(pParse, &tempX, &regFree1));
  3785   3823           testcase( regFree1==0 );
  3786   3824           opCompare.op = TK_EQ;
  3787   3825           opCompare.pLeft = &tempX;
  3788   3826           pTest = &opCompare;
  3789   3827           /* Ticket b351d95f9cd5ef17e9d9dbae18f5ca8611190001:
  3790   3828           ** The value in regFree1 might get SCopy-ed into the file result.
  3791   3829           ** So make sure that the regFree1 register is not reused for other
................................................................................
  4095   4133     exprAnd.pRight = &compRight;
  4096   4134     compLeft.op = TK_GE;
  4097   4135     compLeft.pLeft = &exprX;
  4098   4136     compLeft.pRight = pExpr->x.pList->a[0].pExpr;
  4099   4137     compRight.op = TK_LE;
  4100   4138     compRight.pLeft = &exprX;
  4101   4139     compRight.pRight = pExpr->x.pList->a[1].pExpr;
  4102         -  if( sqlite3ExprIsVector(&exprX)==0 ){
  4103         -    exprToRegister(&exprX, sqlite3ExprCodeTemp(pParse, &exprX, &regFree1));
  4104         -  }
         4140  +  exprToRegister(&exprX, exprCodeVector(pParse, &exprX, &regFree1));
  4105   4141     if( xJump ){
  4106   4142       xJump(pParse, &exprAnd, dest, jumpIfNull);
  4107   4143     }else{
  4108   4144       exprX.flags |= EP_FromJoin;
  4109   4145       sqlite3ExprCodeTarget(pParse, &exprAnd, dest);
  4110   4146     }
  4111   4147     sqlite3ReleaseTempReg(pParse, regFree1);

Changes to test/e_expr.test.

   843    843   proc x {} { incr ::xcount ; return [expr $::x] }
   844    844   foreach {tn x expr res nEval} {
   845    845     1  10  "x() >= 5 AND x() <= 15"  1  2
   846    846     2  10  "x() BETWEEN 5 AND 15"    1  1
   847    847   
   848    848     3   5  "x() >= 5 AND x() <= 5"   1  2
   849    849     4   5  "x() BETWEEN 5 AND 5"     1  1
          850  +
          851  +  5   9  "(x(),8) >= (9,7) AND (x(),8)<=(9,10)"  1 2
          852  +  6   9  "(x(),8) BETWEEN (9,7) AND (9,10)"      1 1
   850    853   } {
   851    854     do_test e_expr-13.1.$tn {
   852    855       set ::xcount 0
   853    856       set a [execsql "SELECT $expr"]
   854    857       list $::xcount $a
   855    858     } [list $nEval $res]
   856    859   }

Added test/rowvalue8.test.

            1  +# 2016-08-22
            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  +# Use of row values in CASE statements.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set ::testprefix rowvalue8
           17  +
           18  +do_execsql_test 1.1 {
           19  +  CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
           20  +  INSERT INTO t1(a,b,c,d) VALUES
           21  +     (1,1,2,3),
           22  +     (2,2,3,4),
           23  +     (3,1,2,4),
           24  +     (4,2,3,5),
           25  +     (5,3,4,6),
           26  +     (6,4,5,9);
           27  +  SELECT a, CASE (b,c) WHEN (1,2) THEN 'aleph'
           28  +                       WHEN (2,3) THEN 'bet'
           29  +                       WHEN (3,4) THEN 'gimel'
           30  +                       ELSE '-' END,
           31  +         '|'
           32  +    FROM t1
           33  +   ORDER BY a;
           34  +} {1 aleph | 2 bet | 3 aleph | 4 bet | 5 gimel | 6 - |}
           35  +do_execsql_test 1.2 {
           36  +  SELECT a, CASE (b,c,d) WHEN (1,2,3) THEN 'aleph'
           37  +                         WHEN (2,3,4) THEN 'bet'
           38  +                         WHEN (3,4,6) THEN 'gimel'
           39  +                         ELSE '-' END,
           40  +         '|'
           41  +    FROM t1
           42  +   ORDER BY a;
           43  +} {1 aleph | 2 bet | 3 - | 4 - | 5 gimel | 6 - |}
           44  +
           45  +do_execsql_test 2.1 {
           46  +  CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
           47  +  INSERT INTO t2(x,y) VALUES(1,6),(2,5),(3,4),(4,3),(5,2),(6,1);
           48  +  SELECT x, CASE (SELECT b,c FROM t1 WHERE a=y)
           49  +            WHEN (1,2) THEN 'aleph'
           50  +            WHEN (2,3) THEN 'bet'
           51  +            WHEN (3,4) THEN 'gimel'
           52  +            ELSE '-' END,
           53  +         '|'
           54  +    FROM t2
           55  +   ORDER BY +x;
           56  +} {1 - | 2 gimel | 3 bet | 4 aleph | 5 bet | 6 aleph |}
           57  +
           58  +
           59  +finish_test