/ Check-in [d0d5af79]
Login

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

Overview
Comment:Only consider AS names from the result set as candidates for resolving identifiers in the WHERE clause if there are no other matches. In the ORDER BY clause, AS names take priority over any column names. Fix for ticket [2500cdb9be].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d0d5af79a005de30194863c57c5018ee75e03581
User & Date: drh 2013-04-14 23:51:27
Context
2013-04-15
13:10
Fix a formatting typo in a comment. No changes to code. check-in: 34124249 user: drh tags: trunk
2013-04-14
23:51
Only consider AS names from the result set as candidates for resolving identifiers in the WHERE clause if there are no other matches. In the ORDER BY clause, AS names take priority over any column names. Fix for ticket [2500cdb9be]. check-in: d0d5af79 user: drh tags: trunk
2013-04-13
19:59
Only consider AS names from the result set as candidates for resolving identifiers in the WHERE clause if there are no other matches. In the ORDER BY clause, AS names take priority over any column names. Candidate fix for ticket [2500cdb9be]. Closed-Leaf check-in: ad53924d user: drh tags: tkt-2500cdb9be
2013-04-12
16:53
Add a test case for detaching a database referenced by an fts4aux table created in the temp schema. check-in: 6d6f0592 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/resolve.c.

   384    384       **     SELECT a+b AS x FROM table WHERE x<10;
   385    385       **
   386    386       ** In cases like this, replace pExpr with a copy of the expression that
   387    387       ** forms the result set entry ("a+b" in the example) and return immediately.
   388    388       ** Note that the expression in the result set should have already been
   389    389       ** resolved by the time the WHERE clause is resolved.
   390    390       */
   391         -    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
          391  +    if( (pEList = pNC->pEList)!=0
          392  +     && zTab==0
          393  +     && ((pNC->ncFlags & NC_AsMaybe)==0 || cnt==0)
          394  +    ){
   392    395         for(j=0; j<pEList->nExpr; j++){
   393    396           char *zAs = pEList->a[j].zName;
   394    397           if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
   395    398             Expr *pOrig;
   396    399             assert( pExpr->pLeft==0 && pExpr->pRight==0 );
   397    400             assert( pExpr->x.pList==0 );
   398    401             assert( pExpr->x.pSelect==0 );
................................................................................
   475    478     pExpr->pLeft = 0;
   476    479     sqlite3ExprDelete(db, pExpr->pRight);
   477    480     pExpr->pRight = 0;
   478    481     pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN);
   479    482   lookupname_end:
   480    483     if( cnt==1 ){
   481    484       assert( pNC!=0 );
          485  +    if( pExpr->op!=TK_AS ){
   482    486       sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
          487  +    }
   483    488       /* Increment the nRef value on all name contexts from TopNC up to
   484    489       ** the point where the name matched. */
   485    490       for(;;){
   486    491         assert( pTopNC!=0 );
   487    492         pTopNC->nRef++;
   488    493         if( pTopNC==pNC ) break;
   489    494         pTopNC = pTopNC->pNext;
................................................................................
  1150   1155       ** expressions in the WHERE clause (etc.) can refer to expressions by
  1151   1156       ** aliases in the result set.
  1152   1157       **
  1153   1158       ** Minor point: If this is the case, then the expression will be
  1154   1159       ** re-evaluated for each reference to it.
  1155   1160       */
  1156   1161       sNC.pEList = p->pEList;
  1157         -    if( sqlite3ResolveExprNames(&sNC, p->pWhere) ||
  1158         -       sqlite3ResolveExprNames(&sNC, p->pHaving)
  1159         -    ){
  1160         -      return WRC_Abort;
  1161         -    }
         1162  +    if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort;
         1163  +    sNC.ncFlags |= NC_AsMaybe;
         1164  +    if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort;
         1165  +    sNC.ncFlags &= ~NC_AsMaybe;
  1162   1166   
  1163   1167       /* The ORDER BY and GROUP BY clauses may not refer to terms in
  1164   1168       ** outer queries 
  1165   1169       */
  1166   1170       sNC.pNext = 0;
  1167   1171       sNC.ncFlags |= NC_AllowAgg;
  1168   1172   

Changes to src/sqliteInt.h.

  2075   2075   /*
  2076   2076   ** Allowed values for the NameContext, ncFlags field.
  2077   2077   */
  2078   2078   #define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
  2079   2079   #define NC_HasAgg    0x02    /* One or more aggregate functions seen */
  2080   2080   #define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
  2081   2081   #define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */
         2082  +#define NC_AsMaybe   0x10    /* Resolve to AS terms of the result set only
         2083  +                             ** if no other resolution is available */
  2082   2084   
  2083   2085   /*
  2084   2086   ** An instance of the following structure contains all information
  2085   2087   ** needed to generate code for a single SELECT statement.
  2086   2088   **
  2087   2089   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
  2088   2090   ** If there is a LIMIT clause, the parser sets nLimit to the value of the

Added test/resolver01.test.

            1  +# 2013-04-13
            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  +#
           12  +# This file tests features of the name resolver (the component that
           13  +# figures out what identifiers in the SQL statement refer to) that
           14  +# were fixed by ticket [2500cdb9be]
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_test resolver01-1.1 {
           21  +  catchsql {
           22  +    CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22);
           23  +    CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44);
           24  +    SELECT 1 AS y FROM t1, t2 ORDER BY y;
           25  +  }
           26  +} {0 1}
           27  +do_test resolver01-1.2 {
           28  +  catchsql {
           29  +    SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase;
           30  +  }
           31  +} {0 2}
           32  +do_test resolver01-1.3 {
           33  +  catchsql {
           34  +    SELECT 3 AS y FROM t1, t2 ORDER BY +y;
           35  +  }
           36  +} {0 3}
           37  +
           38  +
           39  +finish_test

Changes to test/tkt2822.test.

   205    205       SELECT a AS "b" FROM t3 ORDER BY [B];
   206    206     }
   207    207   } {1 9}
   208    208   
   209    209   # In "ORDER BY +b" the term is now an expression rather than
   210    210   # a label.  It therefore matches by rule (3) instead of rule (2).
   211    211   # 
          212  +# 2013-04-13:  This is busted.  Changed to conform to PostgreSQL and
          213  +# MySQL and Oracle behavior.
          214  +# 
   212    215   do_test tkt2822-5.5 {
   213    216     execsql {
   214    217       SELECT a AS b FROM t3 ORDER BY +b;
   215    218     }
   216         -} {9 1}
          219  +} {1 9}
   217    220   
   218    221   # Tests for rule 2 in compound queries
   219    222   #
   220    223   do_test tkt2822-6.1 {
   221    224     execsql {
   222    225       CREATE TABLE t6a(p,q);
   223    226       INSERT INTO t6a VALUES(1,8);