/ Check-in [7ab0b258]
Login

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

Overview
Comment:Omit all use of Expr nodes for TK_AS, as those nodes no longer served a useful purpose and in fact interferred with the query planner.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7ab0b258eabfcfb7f1b0bd1b12e166d2f267823d
User & Date: drh 2015-09-05 22:36:07
References
2015-11-24
02:08 New ticket [e5c6268d] Assertion fault on DISTINCT query containing a correlated aggregate.. artifact: 0d68ffc0 user: drh
Context
2015-09-06
02:51
Add a memory barrier to the mutex initialization logic, try to work around an issue reported by WebKit. check-in: 11a9a786 user: drh tags: trunk
2015-09-05
22:36
Omit all use of Expr nodes for TK_AS, as those nodes no longer served a useful purpose and in fact interferred with the query planner. check-in: 7ab0b258 user: drh tags: trunk
19:21
Fix an unreachable conditional in the WHERE clause analysis logic. check-in: 24924a58 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

    87     87     assert( zC!=0 );
    88     88     s.z = zC;
    89     89     s.n = sqlite3Strlen30(s.z);
    90     90     return sqlite3ExprAddCollateToken(pParse, pExpr, &s, 0);
    91     91   }
    92     92   
    93     93   /*
    94         -** Skip over any TK_COLLATE or TK_AS operators and any unlikely()
           94  +** Skip over any TK_COLLATE operators and any unlikely()
    95     95   ** or likelihood() function at the root of an expression.
    96     96   */
    97     97   Expr *sqlite3ExprSkipCollate(Expr *pExpr){
    98     98     while( pExpr && ExprHasProperty(pExpr, EP_Skip) ){
    99     99       if( ExprHasProperty(pExpr, EP_Unlikely) ){
   100    100         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
   101    101         assert( pExpr->x.pList->nExpr>0 );
   102    102         assert( pExpr->op==TK_FUNCTION );
   103    103         pExpr = pExpr->x.pList->a[0].pExpr;
   104    104       }else{
   105         -      assert( pExpr->op==TK_COLLATE || pExpr->op==TK_AS );
          105  +      assert( pExpr->op==TK_COLLATE );
   106    106         pExpr = pExpr->pLeft;
   107    107       }
   108    108     }   
   109    109     return pExpr;
   110    110   }
   111    111   
   112    112   /*
................................................................................
  2697   2697         }
  2698   2698         break;
  2699   2699       }
  2700   2700       case TK_REGISTER: {
  2701   2701         inReg = pExpr->iTable;
  2702   2702         break;
  2703   2703       }
  2704         -    case TK_AS: {
  2705         -      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
  2706         -      break;
  2707         -    }
  2708   2704   #ifndef SQLITE_OMIT_CAST
  2709   2705       case TK_CAST: {
  2710   2706         /* Expressions of the form:   CAST(pLeft AS token) */
  2711   2707         inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
  2712   2708         if( inReg!=target ){
  2713   2709           sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
  2714   2710           inReg = target;

Changes to src/resolve.c.

    41     41     }
    42     42   }
    43     43   
    44     44   /*
    45     45   ** Turn the pExpr expression into an alias for the iCol-th column of the
    46     46   ** result set in pEList.
    47     47   **
    48         -** If the result set column is a simple column reference, then this routine
    49         -** makes an exact copy.  But for any other kind of expression, this
    50         -** routine make a copy of the result set column as the argument to the
    51         -** TK_AS operator.  The TK_AS operator causes the expression to be
    52         -** evaluated just once and then reused for each alias.
    53         -**
    54         -** The reason for suppressing the TK_AS term when the expression is a simple
    55         -** column reference is so that the column reference will be recognized as
    56         -** usable by indices within the WHERE clause processing logic. 
    57         -**
    58         -** The TK_AS operator is inhibited if zType[0]=='G'.  This means
    59         -** that in a GROUP BY clause, the expression is evaluated twice.  Hence:
    60         -**
    61         -**     SELECT random()%5 AS x, count(*) FROM tab GROUP BY x
    62         -**
    63         -** Is equivalent to:
    64         -**
    65         -**     SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5
    66         -**
    67         -** The result of random()%5 in the GROUP BY clause is probably different
    68         -** from the result in the result-set.  On the other hand Standard SQL does
    69         -** not allow the GROUP BY clause to contain references to result-set columns.
    70         -** So this should never come up in well-formed queries.
    71         -**
    72     48   ** If the reference is followed by a COLLATE operator, then make sure
    73     49   ** the COLLATE operator is preserved.  For example:
    74     50   **
    75     51   **     SELECT a+b, c+d FROM t1 ORDER BY 1 COLLATE nocase;
    76     52   **
    77     53   ** Should be transformed into:
    78     54   **
................................................................................
    98     74   
    99     75     assert( iCol>=0 && iCol<pEList->nExpr );
   100     76     pOrig = pEList->a[iCol].pExpr;
   101     77     assert( pOrig!=0 );
   102     78     db = pParse->db;
   103     79     pDup = sqlite3ExprDup(db, pOrig, 0);
   104     80     if( pDup==0 ) return;
   105         -  if( pOrig->op!=TK_COLUMN && zType[0]!='G' ){
   106         -    incrAggFunctionDepth(pDup, nSubquery);
   107         -    pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0);
   108         -    if( pDup==0 ) return;
   109         -    ExprSetProperty(pDup, EP_Skip);
   110         -    if( pEList->a[iCol].u.x.iAlias==0 ){
   111         -      pEList->a[iCol].u.x.iAlias = (u16)(++pParse->nAlias);
   112         -    }
   113         -    pDup->iTable = pEList->a[iCol].u.x.iAlias;
   114         -  }
           81  +  if( zType[0]!='G' ) incrAggFunctionDepth(pDup, nSubquery);
   115     82     if( pExpr->op==TK_COLLATE ){
   116     83       pDup = sqlite3ExprAddCollateString(pParse, pDup, pExpr->u.zToken);
   117     84     }
           85  +  ExprSetProperty(pDup, EP_Alias);
   118     86   
   119     87     /* Before calling sqlite3ExprDelete(), set the EP_Static flag. This 
   120     88     ** prevents ExprDelete() from deleting the Expr structure itself,
   121     89     ** allowing it to be repopulated by the memcpy() on the following line.
   122     90     ** The pExpr->u.zToken might point into memory that will be freed by the
   123     91     ** sqlite3DbFree(db, pDup) on the last line of this block, so be sure to
   124     92     ** make a copy of the token before doing the sqlite3DbFree().
................................................................................
   502    470     pExpr->pLeft = 0;
   503    471     sqlite3ExprDelete(db, pExpr->pRight);
   504    472     pExpr->pRight = 0;
   505    473     pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN);
   506    474   lookupname_end:
   507    475     if( cnt==1 ){
   508    476       assert( pNC!=0 );
   509         -    if( pExpr->op!=TK_AS ){
          477  +    if( !ExprHasProperty(pExpr, EP_Alias) ){
   510    478         sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
   511    479       }
   512    480       /* Increment the nRef value on all name contexts from TopNC up to
   513    481       ** the point where the name matched. */
   514    482       for(;;){
   515    483         assert( pTopNC!=0 );
   516    484         pTopNC->nRef++;

Changes to src/sqliteInt.h.

  2131   2131   #define EP_Static    0x008000 /* Held in memory not obtained from malloc() */
  2132   2132   #define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
  2133   2133   #define EP_NoReduce  0x020000 /* Cannot EXPRDUP_REDUCE this Expr */
  2134   2134   #define EP_Unlikely  0x040000 /* unlikely() or likelihood() function */
  2135   2135   #define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
  2136   2136   #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
  2137   2137   #define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
         2138  +#define EP_Alias     0x400000 /* Is an alias for a result set column */
  2138   2139   
  2139   2140   /*
  2140   2141   ** Combinations of two or more EP_* flags
  2141   2142   */
  2142   2143   #define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */
  2143   2144   
  2144   2145   /*

Changes to src/treeview.c.

   249    249                             pExpr->u.zToken, pExpr->iColumn);
   250    250         break;
   251    251       }
   252    252       case TK_REGISTER: {
   253    253         sqlite3TreeViewLine(pView,"REGISTER(%d)", pExpr->iTable);
   254    254         break;
   255    255       }
   256         -    case TK_AS: {
   257         -      sqlite3TreeViewLine(pView,"AS %Q", pExpr->u.zToken);
   258         -      sqlite3TreeViewExpr(pView, pExpr->pLeft, 0);
   259         -      break;
   260         -    }
   261    256       case TK_ID: {
   262    257         sqlite3TreeViewLine(pView,"ID \"%w\"", pExpr->u.zToken);
   263    258         break;
   264    259       }
   265    260   #ifndef SQLITE_OMIT_CAST
   266    261       case TK_CAST: {
   267    262         /* Expressions of the form:   CAST(pLeft AS token) */

Changes to test/indexexpr1.test.

   193    193       SELECT x, printf('ab%04xyz',x), random() FROM c;
   194    194     CREATE UNIQUE INDEX t3abc ON t3(CAST(a AS text), b, substr(c,1,3));
   195    195     SELECT a FROM t3 WHERE CAST(a AS text)<='10' ORDER BY +a;
   196    196   } {1 10}
   197    197   do_catchsql_test indexexpr1-410 {
   198    198     INSERT INTO t3 SELECT * FROM t3 WHERE rowid=10;
   199    199   } {1 {UNIQUE constraint failed: index 't3abc'}}
          200  +
          201  +do_execsql_test indexexpr1-500 {
          202  +  CREATE TABLE t5(a);
          203  +  CREATE TABLE cnt(x);
          204  +  WITH RECURSIVE
          205  +    c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
          206  +  INSERT INTO cnt(x) SELECT x FROM c;
          207  +  INSERT INTO t5(a) SELECT printf('abc%03dxyz',x) FROM cnt;
          208  +  CREATE INDEX t5ax ON t5( substr(a,4,3) );
          209  +} {}
          210  +do_execsql_test indexexpr1-510 {
          211  +  -- The use of the "k" alias in the WHERE clause is technically
          212  +  -- illegal, but SQLite allows it for historical reasons.  In this
          213  +  -- test and the next, verify that "k" can be used by the t5ax index
          214  +  SELECT substr(a,4,3) AS k FROM cnt, t5 WHERE k=printf('%03d',x);
          215  +} {001 002 003 004 005}
          216  +do_execsql_test indexexpr1-510eqp {
          217  +  EXPLAIN QUERY PLAN
          218  +  SELECT substr(a,4,3) AS k FROM cnt, t5 WHERE k=printf('%03d',x);
          219  +} {/USING INDEX t5ax/}
          220  +
   200    221   
   201    222   finish_test