/ Check-in [4cf5ed7e]
Login

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

Overview
Comment:Now supports result sets of the form "TABLE.*" with nested FROM clauses.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | name-resolution-fix
Files: files | file ages | folders
SHA1: 4cf5ed7ea198abc32f8118e79490e77f847f08c1
User & Date: drh 2013-01-03 00:45:56
Context
2013-01-03
16:54
Remove an incorrect assert() statement (ticket [beba9cae6345a]). Fix other minor problems in the name resolution logic. check-in: afe96a11 user: drh tags: name-resolution-fix
00:45
Now supports result sets of the form "TABLE.*" with nested FROM clauses. check-in: 4cf5ed7e user: drh tags: name-resolution-fix
2013-01-02
14:57
When resolving result-set name collisions, make them x:1, x:2, x:3, etc. instead of x:1, x:1:1, x:1;1;1. check-in: ef01e304 user: drh tags: name-resolution-fix
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  3277   3277     }else{
  3278   3278       sqlite3ExplainPush(pOut);
  3279   3279       for(i=0; i<pList->nExpr; i++){
  3280   3280         sqlite3ExplainPrintf(pOut, "item[%d] = ", i);
  3281   3281         sqlite3ExplainPush(pOut);
  3282   3282         sqlite3ExplainExpr(pOut, pList->a[i].pExpr);
  3283   3283         sqlite3ExplainPop(pOut);
         3284  +      if( pList->a[i].zName ){
         3285  +        sqlite3ExplainPrintf(pOut, " AS %s", pList->a[i].zName);
         3286  +      }
         3287  +      if( pList->a[i].bSpanIsTab ){
         3288  +        sqlite3ExplainPrintf(pOut, " (%s)", pList->a[i].zSpan);
         3289  +      }
  3284   3290         if( i<pList->nExpr-1 ){
  3285   3291           sqlite3ExplainNL(pOut);
  3286   3292         }
  3287   3293       }
  3288   3294       sqlite3ExplainPop(pOut);
  3289   3295     }
  3290   3296   }

Changes to src/resolve.c.

   146    146       for(k=0; k<pUsing->nId; k++){
   147    147         if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1;
   148    148       }
   149    149     }
   150    150     return 0;
   151    151   }
   152    152   
          153  +/*
          154  +** Subqueries stores the original database, table and column names for their
          155  +** result sets in ExprList.a[].zSpan, in the form "DATABASE.TABLE.COLUMN".
          156  +** Check to see if the zSpan given to this routine matches the zDb, zTab,
          157  +** and zCol.  If any of zDb, zTab, and zCol are NULL then those fields will
          158  +** match anything.
          159  +*/
          160  +int sqlite3MatchSpanName(
          161  +  const char *zSpan,
          162  +  const char *zCol,
          163  +  const char *zTab,
          164  +  const char *zDb
          165  +){
          166  +  int n;
          167  +  for(n=0; ALWAYS(zSpan[n]) && zSpan[n]!='.'; n++){}
          168  +  if( zDb && sqlite3StrNICmp(zSpan, zDb, n)!=0 ){
          169  +    return 0;
          170  +  }
          171  +  zSpan += n+1;
          172  +  for(n=0; ALWAYS(zSpan[n]) && zSpan[n]!='.'; n++){}
          173  +  if( zTab && sqlite3StrNICmp(zSpan, zTab, n)!=0 ){
          174  +    return 0;
          175  +  }
          176  +  zSpan += n+1;
          177  +  if( zCol && sqlite3StrICmp(zSpan, zCol)!=0 ){
          178  +    return 0;
          179  +  }
          180  +  return 1;
          181  +}
   153    182   
   154    183   /*
   155    184   ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
   156    185   ** that name in the set of source tables in pSrcList and make the pExpr 
   157    186   ** expression node refer back to that source column.  The following changes
   158    187   ** are made to pExpr:
   159    188   **
................................................................................
   236    265           if( zDb && pTab->pSchema!=pSchema ){
   237    266             continue;
   238    267           }
   239    268           if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){
   240    269             ExprList *pEList = pItem->pSelect->pEList;
   241    270             int hit = 0;
   242    271             for(j=0; j<pEList->nExpr; j++){
   243         -            if( zTab && sqlite3StrICmp(pEList->a[j].zSpan, zTab)!=0 ) continue;
   244         -            if( sqlite3StrICmp(pEList->a[j].zName, zCol)==0 ){
          272  +            if( sqlite3MatchSpanName(pEList->a[j].zSpan, zCol, zTab, zDb) ){
   245    273                 cnt++;
   246    274                 cntTab = 2;
   247    275                 pMatch = pItem;
   248    276                 pExpr->iColumn = j;
   249    277               }
   250    278             }
   251    279             if( hit || zTab==0 ) continue;

Changes to src/select.c.

  3290   3290   static int selectExpander(Walker *pWalker, Select *p){
  3291   3291     Parse *pParse = pWalker->pParse;
  3292   3292     int i, j, k;
  3293   3293     SrcList *pTabList;
  3294   3294     ExprList *pEList;
  3295   3295     struct SrcList_item *pFrom;
  3296   3296     sqlite3 *db = pParse->db;
         3297  +  Expr *pE, *pRight, *pExpr;
  3297   3298   
  3298   3299     if( db->mallocFailed  ){
  3299   3300       return WRC_Abort;
  3300   3301     }
  3301   3302     if( NEVER(p->pSrc==0) || (p->selFlags & SF_Expanded)!=0 ){
  3302   3303       return WRC_Prune;
  3303   3304     }
................................................................................
  3375   3376     ** The following code just has to locate the TK_ALL expressions and expand
  3376   3377     ** each one to the list of all columns in all tables.
  3377   3378     **
  3378   3379     ** The first loop just checks to see if there are any "*" operators
  3379   3380     ** that need expanding.
  3380   3381     */
  3381   3382     for(k=0; k<pEList->nExpr; k++){
  3382         -    Expr *pE = pEList->a[k].pExpr;
         3383  +    pE = pEList->a[k].pExpr;
  3383   3384       if( pE->op==TK_ALL ) break;
  3384   3385       assert( pE->op!=TK_DOT || pE->pRight!=0 );
  3385   3386       assert( pE->op!=TK_DOT || (pE->pLeft!=0 && pE->pLeft->op==TK_ID) );
  3386   3387       if( pE->op==TK_DOT && pE->pRight->op==TK_ALL ) break;
  3387   3388     }
  3388   3389     if( k<pEList->nExpr ){
  3389   3390       /*
................................................................................
  3395   3396       ExprList *pNew = 0;
  3396   3397       int flags = pParse->db->flags;
  3397   3398       int longNames = (flags & SQLITE_FullColNames)!=0
  3398   3399                         && (flags & SQLITE_ShortColNames)==0
  3399   3400                         && (p->selFlags & SF_NestedFrom)==0;
  3400   3401   
  3401   3402       for(k=0; k<pEList->nExpr; k++){
  3402         -      Expr *pE = a[k].pExpr;
  3403         -      assert( pE->op!=TK_DOT || pE->pRight!=0 );
  3404         -      if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pE->pRight->op!=TK_ALL) ){
         3403  +      pE = a[k].pExpr;
         3404  +      pRight = pE->pRight;
         3405  +      assert( pE->op!=TK_DOT || pRight!=0 );
         3406  +      if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pRight->op!=TK_ALL) ){
  3405   3407           /* This particular expression does not need to be expanded.
  3406   3408           */
  3407   3409           pNew = sqlite3ExprListAppend(pParse, pNew, a[k].pExpr);
  3408   3410           if( pNew ){
  3409   3411             pNew->a[pNew->nExpr-1].zName = a[k].zName;
  3410   3412             pNew->a[pNew->nExpr-1].zSpan = a[k].zSpan;
  3411   3413             a[k].zName = 0;
................................................................................
  3412   3414             a[k].zSpan = 0;
  3413   3415           }
  3414   3416           a[k].pExpr = 0;
  3415   3417         }else{
  3416   3418           /* This expression is a "*" or a "TABLE.*" and needs to be
  3417   3419           ** expanded. */
  3418   3420           int tableSeen = 0;      /* Set to 1 when TABLE matches */
  3419         -        char *zTName;            /* text of name of TABLE */
         3421  +        char *zTName = 0;       /* text of name of TABLE */
  3420   3422           if( pE->op==TK_DOT ){
  3421   3423             assert( pE->pLeft!=0 );
  3422   3424             assert( !ExprHasProperty(pE->pLeft, EP_IntValue) );
  3423   3425             zTName = pE->pLeft->u.zToken;
  3424         -        }else{
  3425         -          zTName = 0;
  3426   3426           }
  3427   3427           for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3428   3428             Table *pTab = pFrom->pTab;
         3429  +          Select *pSub = pFrom->pSelect;
  3429   3430             char *zTabName = pFrom->zAlias;
         3431  +          const char *zSchemaName = 0;
  3430   3432             if( zTabName==0 ){
  3431   3433               zTabName = pTab->zName;
  3432   3434             }
  3433   3435             if( db->mallocFailed ) break;
  3434         -          if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
  3435         -            continue;
         3436  +          if( pSub==0 || (pSub->selFlags & SF_NestedFrom)==0 ){
         3437  +            int iDb;
         3438  +            pSub = 0;
         3439  +            if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
         3440  +              continue;
         3441  +            }
         3442  +            iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
         3443  +            zSchemaName = iDb>=0 ? db->aDb[i].zName : "*";
  3436   3444             }
  3437         -          tableSeen = 1;
  3438   3445             for(j=0; j<pTab->nCol; j++){
  3439         -            Expr *pExpr, *pRight;
  3440   3446               char *zName = pTab->aCol[j].zName;
  3441   3447               char *zColname;  /* The computed column name */
  3442   3448               char *zToFree;   /* Malloced string that needs to be freed */
  3443   3449               Token sColname;  /* Computed column name as a token */
         3450  +
         3451  +            if( zTName && pSub
         3452  +             && sqlite3MatchSpanName(pSub->pEList->a[j].zSpan, 0, zTName, 0)==0
         3453  +            ){
         3454  +              continue;
         3455  +            }
  3444   3456   
  3445   3457               /* If a column is marked as 'hidden' (currently only possible
  3446   3458               ** for virtual tables), do not include it in the expanded
  3447   3459               ** result-set list.
  3448   3460               */
  3449   3461               if( IsHiddenColumn(&pTab->aCol[j]) ){
  3450   3462                 assert(IsVirtual(pTab));
  3451   3463                 continue;
  3452   3464               }
         3465  +            tableSeen = 1;
  3453   3466   
  3454   3467               if( i>0 && zTName==0 ){
  3455   3468                 if( (pFrom->jointype & JT_NATURAL)!=0
  3456   3469                   && tableAndColumnIndex(pTabList, i, zName, 0, 0)
  3457   3470                 ){
  3458   3471                   /* In a NATURAL join, omit the join columns from the 
  3459   3472                   ** table to the right of the join */
................................................................................
  3480   3493                 pExpr = pRight;
  3481   3494               }
  3482   3495               pNew = sqlite3ExprListAppend(pParse, pNew, pExpr);
  3483   3496               sColname.z = zColname;
  3484   3497               sColname.n = sqlite3Strlen30(zColname);
  3485   3498               sqlite3ExprListSetName(pParse, pNew, &sColname, 0);
  3486   3499               if( pNew && (p->selFlags & SF_NestedFrom)!=0 ){
  3487         -              pNew->a[pNew->nExpr-1].zSpan = sqlite3DbStrDup(db, zTabName);
         3500  +              struct ExprList_item *pX = &pNew->a[pNew->nExpr-1];
         3501  +              if( pSub ){
         3502  +                pX->zSpan = sqlite3DbStrDup(db, pSub->pEList->a[j].zSpan);
         3503  +              }else{
         3504  +                pX->zSpan = sqlite3MPrintf(db, "%s.%s.%s",
         3505  +                                           zSchemaName, zTabName, zColname);
         3506  +              }
         3507  +              pX->bSpanIsTab = 1;
  3488   3508               }
  3489   3509               sqlite3DbFree(db, zToFree);
  3490   3510             }
  3491   3511           }
  3492   3512           if( !tableSeen ){
  3493   3513             if( zTName ){
  3494   3514               sqlite3ErrorMsg(pParse, "no such table: %s", zTName);

Changes to src/sqliteInt.h.

  1773   1773   ** field is not used.
  1774   1774   **
  1775   1775   ** By default the Expr.zSpan field holds a human-readable description of
  1776   1776   ** the expression that is used in the generation of error messages and
  1777   1777   ** column labels.  In this case, Expr.zSpan is typically the text of a
  1778   1778   ** column expression as it exists in a SELECT statement.  However, if
  1779   1779   ** the bSpanIsTab flag is set, then zSpan is overloaded to mean the name
  1780         -** of the table to which the column of a FROM-clause subquery refers.
         1780  +** of the result column in the form: DATABASE.TABLE.COLUMN.  This later
         1781  +** form is used for name resolution with nested FROM clauses.
  1781   1782   */
  1782   1783   struct ExprList {
  1783   1784     int nExpr;             /* Number of expressions on the list */
  1784   1785     int iECursor;          /* VDBE Cursor associated with this ExprList */
  1785   1786     struct ExprList_item { /* For each expression in the list */
  1786   1787       Expr *pExpr;            /* The list of expressions */
  1787   1788       char *zName;            /* Token associated with this expression */
  1788   1789       char *zSpan;            /* Original text of the expression */
  1789   1790       u8 sortOrder;           /* 1 for DESC or 0 for ASC */
  1790   1791       unsigned done :1;       /* A flag to indicate when processing is finished */
  1791         -    unsigned bSpanIsTab :1; /* zSpan holds table name, not the span */
         1792  +    unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
  1792   1793       u16 iOrderByCol;        /* For ORDER BY, column number in result set */
  1793   1794       u16 iAlias;             /* Index into Parse.aAlias[] for zName */
  1794   1795     } *a;                  /* Alloc a power of two greater or equal to nExpr */
  1795   1796   };
  1796   1797   
  1797   1798   /*
  1798   1799   ** An instance of this structure is used by the parser to record both
................................................................................
  3076   3077   void sqlite3AlterFunctions(void);
  3077   3078   void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
  3078   3079   int sqlite3GetToken(const unsigned char *, int *);
  3079   3080   void sqlite3NestedParse(Parse*, const char*, ...);
  3080   3081   void sqlite3ExpirePreparedStatements(sqlite3*);
  3081   3082   int sqlite3CodeSubselect(Parse *, Expr *, int, int);
  3082   3083   void sqlite3SelectPrep(Parse*, Select*, NameContext*);
         3084  +int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
  3083   3085   int sqlite3ResolveExprNames(NameContext*, Expr*);
  3084   3086   void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
  3085   3087   int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
  3086   3088   void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
  3087   3089   void sqlite3AlterFinishAddColumn(Parse *, Token *);
  3088   3090   void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
  3089   3091   CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);

Changes to test/selectD.test.

    11     11   # This file implements regression tests for name resolution in SELECT
    12     12   # statements that have parenthesized FROM clauses.
    13     13   #
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   
    18         -do_test selectD-1.1 {
    19         -  db eval {
    20         -    CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
    21         -    CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
    22         -    CREATE TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
    23         -    CREATE TABLE t4(a,b); INSERT INTO t4 VALUES(444,'x4');
    24     18   
    25         -    SELECT *
    26         -      FROM (t1), (t2), (t3), (t4)
    27         -     WHERE t4.a=t3.a+111 
    28         -       AND t3.a=t2.a+111
    29         -       AND t2.a=t1.a+111;
           19  +for {set i 1} {$i<=2} {incr i} {
           20  +  db close
           21  +  forcedelete test$i.db
           22  +  sqlite3 db test$i.db
           23  +  if {$i==2} {
           24  +    optimization_control db query-flattener off
    30     25     }
    31         -} {111 x1 222 x2 333 x3 444 x4}
    32         -do_test selectD-1.2 {
    33         -  db eval {
    34         -    SELECT *
    35         -      FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
    36         -                            ON t3.a=t2.a+111)
    37         -                   ON t2.a=t1.a+111;
    38         -  }
    39         -} {111 x1 222 x2 333 x3 444 x4}
    40         -do_test selectD-1.3 {
    41         -  db eval {
    42         -    UPDATE t2 SET a=111;
    43         -    UPDATE t3 SET a=111;
    44         -    UPDATE t4 SET a=111;
    45         -    SELECT *
    46         -      FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
    47         -  }
    48         -} {111 x1 x2 x3 x4}
    49         -do_test selectD-1.4 {
    50         -  db eval {
    51         -    UPDATE t2 SET a=111;
    52         -    UPDATE t3 SET a=111;
    53         -    UPDATE t4 SET a=111;
    54         -    SELECT *
    55         -      FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
    56         -                                      USING (a))
    57         -                         USING (a);
    58         -  }
    59         -} {111 x1 x2 x3 x4}
    60         -do_test selectD-1.5 {
    61         -  db eval {
    62         -    UPDATE t3 SET a=222;
    63         -    UPDATE t4 SET a=222;
    64         -    SELECT *
    65         -      FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
    66         -           ON t1.a=t3.a-111;
    67         -  }
    68         -} {111 x1 x2 222 x3 x4}
    69         -do_test selectD-1.6 {
    70         -  db eval {
    71         -    UPDATE t4 SET a=333;
    72         -    SELECT *
    73         -      FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
    74         -           ON t1.a=t3.a-111;
    75         -  }
    76         -} {111 x1 x2 222 x3 {}}
           26  +  do_test selectD-$i.0 {
           27  +    db eval {
           28  +      CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
           29  +      CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
           30  +      CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
           31  +      CREATE TABLE t4(a,b); INSERT INTO t4 VALUES(444,'x4');
           32  +    }
           33  +  } {}
           34  +  do_test selectD-$i.1 {
           35  +    db eval {
           36  +      SELECT *
           37  +        FROM (t1), (t2), (t3), (t4)
           38  +       WHERE t4.a=t3.a+111 
           39  +         AND t3.a=t2.a+111
           40  +         AND t2.a=t1.a+111;
           41  +    }
           42  +  } {111 x1 222 x2 333 x3 444 x4}
           43  +  do_test selectD-$i.2.1 {
           44  +    db eval {
           45  +      SELECT *
           46  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           47  +                              ON t3.a=t2.a+111)
           48  +                     ON t2.a=t1.a+111;
           49  +    }
           50  +  } {111 x1 222 x2 333 x3 444 x4}
           51  +  do_test selectD-$i.2.2 {
           52  +    db eval {
           53  +      SELECT t3.a
           54  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           55  +                              ON t3.a=t2.a+111)
           56  +                     ON t2.a=t1.a+111;
           57  +    }
           58  +  } {333}
           59  +  do_test selectD-$i.2.3 {
           60  +    db eval {
           61  +      SELECT t3.*
           62  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           63  +                              ON t3.a=t2.a+111)
           64  +                     ON t2.a=t1.a+111;
           65  +    }
           66  +  } {333 x3}
           67  +  do_test selectD-$i.2.3 {
           68  +    db eval {
           69  +      SELECT t3.*, t2.*
           70  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           71  +                              ON t3.a=t2.a+111)
           72  +                     ON t2.a=t1.a+111;
           73  +    }
           74  +  } {333 x3 222 x2}
           75  +  do_test selectD-$i.3 {
           76  +    db eval {
           77  +      UPDATE t2 SET a=111;
           78  +      UPDATE t3 SET a=111;
           79  +      UPDATE t4 SET a=111;
           80  +      SELECT *
           81  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
           82  +    }
           83  +  } {111 x1 x2 x3 x4}
           84  +  do_test selectD-$i.4 {
           85  +    db eval {
           86  +      UPDATE t2 SET a=111;
           87  +      UPDATE t3 SET a=111;
           88  +      UPDATE t4 SET a=111;
           89  +      SELECT *
           90  +        FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
           91  +                                        USING (a))
           92  +                           USING (a);
           93  +    }
           94  +  } {111 x1 x2 x3 x4}
           95  +  do_test selectD-$i.5 {
           96  +    db eval {
           97  +      UPDATE t3 SET a=222;
           98  +      UPDATE t4 SET a=222;
           99  +      SELECT *
          100  +        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
          101  +             ON t1.a=t3.a-111;
          102  +    }
          103  +  } {111 x1 x2 222 x3 x4}
          104  +  do_test selectD-$i.6 {
          105  +    db eval {
          106  +      UPDATE t4 SET a=333;
          107  +      SELECT *
          108  +        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
          109  +             ON t1.a=t3.a-111;
          110  +    }
          111  +  } {111 x1 x2 222 x3 {}}
          112  +  do_test selectD-$i.7 {
          113  +    db eval {
          114  +      SELECT t1.*, t2.*, t3.*, t4.b
          115  +        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
          116  +             ON t1.a=t3.a-111;
          117  +    }
          118  +  } {111 x1 111 x2 222 x3 {}}
          119  +}
    77    120   
    78    121   finish_test