/ Check-in [99127a66]
Login

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

Overview
Comment:Improvements to column name resolution in queries with parenthesized FROM clauses. Also includes a fix for ticket [beba9cae6345a3].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 99127a669c49f82918853091c9c5b7db5ad73cba
User & Date: drh 2013-01-03 18:07:37
References
2015-01-22
11:41 New ticket [32b63d54] Fuzzer induced crash. artifact: c108a800 user: drh
2013-02-26
12:31 New ticket [7a31705a] Name resolution fails when table name is a prefix of another table. artifact: 542dd814 user: drh
Context
2013-01-03
19:34
Bring back test_regexp changes that were made when porting to Fossil. Also fix some bugs that were introduced by the Fossil port. check-in: 45c158b1 user: drh tags: trunk
18:07
Improvements to column name resolution in queries with parenthesized FROM clauses. Also includes a fix for ticket [beba9cae6345a3]. check-in: 99127a66 user: drh tags: trunk
17:34
Further corner-case fixes to the name resolution logic. Closed-Leaf check-in: 20730bad user: drh tags: name-resolution-fix
2013-01-01
14:01
Reduce the size of the Index object (by 8 bytes on x64). check-in: 5a2ac944 user: drh tags: trunk
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   **
................................................................................
   201    230     assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
   202    231     assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );
   203    232   
   204    233     /* Initialize the node to no-match */
   205    234     pExpr->iTable = -1;
   206    235     pExpr->pTab = 0;
   207    236     ExprSetIrreducible(pExpr);
          237  +
          238  +  /* Translate the schema name in zDb into a pointer to the corresponding
          239  +  ** schema.  If not found, pSchema will remain NULL and nothing will match
          240  +  ** resulting in an appropriate error message toward the end of this routine
          241  +  */
          242  +  if( zDb ){
          243  +    for(i=0; i<db->nDb; i++){
          244  +      assert( db->aDb[i].zName );
          245  +      if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){
          246  +        pSchema = db->aDb[i].pSchema;
          247  +        break;
          248  +      }
          249  +    }
          250  +  }
   208    251   
   209    252     /* Start at the inner-most context and move outward until a match is found */
   210    253     while( pNC && cnt==0 ){
   211    254       ExprList *pEList;
   212    255       SrcList *pSrcList = pNC->pSrcList;
   213    256   
   214    257       if( pSrcList ){
   215    258         for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
   216    259           Table *pTab;
   217         -        int iDb;
   218    260           Column *pCol;
   219    261     
   220    262           pTab = pItem->pTab;
   221    263           assert( pTab!=0 && pTab->zName!=0 );
   222         -        iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
   223    264           assert( pTab->nCol>0 );
   224         -        if( zTab ){
   225         -          if( pItem->zAlias ){
   226         -            char *zTabName = pItem->zAlias;
   227         -            if( sqlite3StrICmp(zTabName, zTab)!=0 ) continue;
   228         -          }else{
   229         -            char *zTabName = pTab->zName;
   230         -            if( NEVER(zTabName==0) || sqlite3StrICmp(zTabName, zTab)!=0 ){
   231         -              continue;
          265  +        if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){
          266  +          ExprList *pEList = pItem->pSelect->pEList;
          267  +          int hit = 0;
          268  +          for(j=0; j<pEList->nExpr; j++){
          269  +            if( sqlite3MatchSpanName(pEList->a[j].zSpan, zCol, zTab, zDb) ){
          270  +              cnt++;
          271  +              cntTab = 2;
          272  +              pMatch = pItem;
          273  +              pExpr->iColumn = j;
          274  +              hit = 1;
   232    275               }
   233         -            if( zDb!=0 && sqlite3StrICmp(db->aDb[iDb].zName, zDb)!=0 ){
   234         -              continue;
   235         -            }
          276  +          }
          277  +          if( hit || zTab==0 ) continue;
          278  +        }
          279  +        if( zDb && pTab->pSchema!=pSchema ){
          280  +          continue;
          281  +        }
          282  +        if( zTab ){
          283  +          const char *zTabName = pItem->zAlias ? pItem->zAlias : pTab->zName;
          284  +          assert( zTabName!=0 );
          285  +          if( sqlite3StrICmp(zTabName, zTab)!=0 ){
          286  +            continue;
   236    287             }
   237    288           }
   238    289           if( 0==(cntTab++) ){
   239         -          pExpr->iTable = pItem->iCursor;
   240         -          pExpr->pTab = pTab;
   241         -          pSchema = pTab->pSchema;
   242    290             pMatch = pItem;
   243    291           }
   244    292           for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
   245    293             if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
   246    294               /* If there has been exactly one prior match and this match
   247    295               ** is for the right-hand table of a NATURAL JOIN or is in a 
   248    296               ** USING clause, then skip this match.
   249    297               */
   250    298               if( cnt==1 ){
   251    299                 if( pItem->jointype & JT_NATURAL ) continue;
   252    300                 if( nameInUsingClause(pItem->pUsing, zCol) ) continue;
   253    301               }
   254    302               cnt++;
   255         -            pExpr->iTable = pItem->iCursor;
   256         -            pExpr->pTab = pTab;
   257    303               pMatch = pItem;
   258         -            pSchema = pTab->pSchema;
   259    304               /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
   260    305               pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
   261    306               break;
   262    307             }
   263    308           }
   264    309         }
   265         -    }
          310  +      if( pMatch ){
          311  +        pExpr->iTable = pMatch->iCursor;
          312  +        pExpr->pTab = pMatch->pTab;
          313  +        pSchema = pExpr->pTab->pSchema;
          314  +      }
          315  +    } /* if( pSrcList ) */
   266    316   
   267    317   #ifndef SQLITE_OMIT_TRIGGER
   268    318       /* If we have not already resolved the name, then maybe 
   269    319       ** it is a new.* or old.* trigger argument reference
   270    320       */
   271    321       if( zDb==0 && zTab!=0 && cnt==0 && pParse->pTriggerTab!=0 ){
   272    322         int op = pParse->eTriggerOp;

Changes to src/select.c.

  1332   1332     *pnCol = nCol;
  1333   1333     *paCol = aCol;
  1334   1334   
  1335   1335     for(i=0, pCol=aCol; i<nCol; i++, pCol++){
  1336   1336       /* Get an appropriate name for the column
  1337   1337       */
  1338   1338       p = sqlite3ExprSkipCollate(pEList->a[i].pExpr);
  1339         -    assert( p->pRight==0 || ExprHasProperty(p->pRight, EP_IntValue)
  1340         -               || p->pRight->u.zToken==0 || p->pRight->u.zToken[0]!=0 );
  1341   1339       if( (zName = pEList->a[i].zName)!=0 ){
  1342   1340         /* If the column contains an "AS <name>" phrase, use <name> as the name */
  1343   1341         zName = sqlite3DbStrDup(db, zName);
  1344   1342       }else{
  1345   1343         Expr *pColExpr = p;  /* The expression that is the result column name */
  1346   1344         Table *pTab;         /* Table associated with this expression */
  1347   1345         while( pColExpr->op==TK_DOT ){
................................................................................
  1371   1369       /* Make sure the column name is unique.  If the name is not unique,
  1372   1370       ** append a integer to the name so that it becomes unique.
  1373   1371       */
  1374   1372       nName = sqlite3Strlen30(zName);
  1375   1373       for(j=cnt=0; j<i; j++){
  1376   1374         if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
  1377   1375           char *zNewName;
         1376  +        int k;
         1377  +        for(k=nName-1; k>1 && sqlite3Isdigit(zName[k]); k--){}
         1378  +        if( zName[k]==':' ) nName = k;
  1378   1379           zName[nName] = 0;
  1379   1380           zNewName = sqlite3MPrintf(db, "%s:%d", zName, ++cnt);
  1380   1381           sqlite3DbFree(db, zName);
  1381   1382           zName = zNewName;
  1382   1383           j = -1;
  1383   1384           if( zName==0 ) break;
  1384   1385         }
................................................................................
  3287   3288   static int selectExpander(Walker *pWalker, Select *p){
  3288   3289     Parse *pParse = pWalker->pParse;
  3289   3290     int i, j, k;
  3290   3291     SrcList *pTabList;
  3291   3292     ExprList *pEList;
  3292   3293     struct SrcList_item *pFrom;
  3293   3294     sqlite3 *db = pParse->db;
         3295  +  Expr *pE, *pRight, *pExpr;
  3294   3296   
  3295   3297     if( db->mallocFailed  ){
  3296   3298       return WRC_Abort;
  3297   3299     }
  3298   3300     if( NEVER(p->pSrc==0) || (p->selFlags & SF_Expanded)!=0 ){
  3299   3301       return WRC_Prune;
  3300   3302     }
................................................................................
  3372   3374     ** The following code just has to locate the TK_ALL expressions and expand
  3373   3375     ** each one to the list of all columns in all tables.
  3374   3376     **
  3375   3377     ** The first loop just checks to see if there are any "*" operators
  3376   3378     ** that need expanding.
  3377   3379     */
  3378   3380     for(k=0; k<pEList->nExpr; k++){
  3379         -    Expr *pE = pEList->a[k].pExpr;
         3381  +    pE = pEList->a[k].pExpr;
  3380   3382       if( pE->op==TK_ALL ) break;
  3381   3383       assert( pE->op!=TK_DOT || pE->pRight!=0 );
  3382   3384       assert( pE->op!=TK_DOT || (pE->pLeft!=0 && pE->pLeft->op==TK_ID) );
  3383   3385       if( pE->op==TK_DOT && pE->pRight->op==TK_ALL ) break;
  3384   3386     }
  3385   3387     if( k<pEList->nExpr ){
  3386   3388       /*
................................................................................
  3390   3392       */
  3391   3393       struct ExprList_item *a = pEList->a;
  3392   3394       ExprList *pNew = 0;
  3393   3395       int flags = pParse->db->flags;
  3394   3396       int longNames = (flags & SQLITE_FullColNames)!=0
  3395   3397                         && (flags & SQLITE_ShortColNames)==0;
  3396   3398   
         3399  +    /* When processing FROM-clause subqueries, it is always the case
         3400  +    ** that full_column_names=OFF and short_column_names=ON.  The
         3401  +    ** sqlite3ResultSetOfSelect() routine makes it so. */
         3402  +    assert( (p->selFlags & SF_NestedFrom)==0
         3403  +          || ((flags & SQLITE_FullColNames)==0 &&
         3404  +              (flags & SQLITE_ShortColNames)!=0) );
         3405  +
  3397   3406       for(k=0; k<pEList->nExpr; k++){
  3398         -      Expr *pE = a[k].pExpr;
  3399         -      assert( pE->op!=TK_DOT || pE->pRight!=0 );
  3400         -      if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pE->pRight->op!=TK_ALL) ){
         3407  +      pE = a[k].pExpr;
         3408  +      pRight = pE->pRight;
         3409  +      assert( pE->op!=TK_DOT || pRight!=0 );
         3410  +      if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pRight->op!=TK_ALL) ){
  3401   3411           /* This particular expression does not need to be expanded.
  3402   3412           */
  3403   3413           pNew = sqlite3ExprListAppend(pParse, pNew, a[k].pExpr);
  3404   3414           if( pNew ){
  3405   3415             pNew->a[pNew->nExpr-1].zName = a[k].zName;
  3406   3416             pNew->a[pNew->nExpr-1].zSpan = a[k].zSpan;
  3407   3417             a[k].zName = 0;
................................................................................
  3408   3418             a[k].zSpan = 0;
  3409   3419           }
  3410   3420           a[k].pExpr = 0;
  3411   3421         }else{
  3412   3422           /* This expression is a "*" or a "TABLE.*" and needs to be
  3413   3423           ** expanded. */
  3414   3424           int tableSeen = 0;      /* Set to 1 when TABLE matches */
  3415         -        char *zTName;            /* text of name of TABLE */
         3425  +        char *zTName = 0;       /* text of name of TABLE */
  3416   3426           if( pE->op==TK_DOT ){
  3417   3427             assert( pE->pLeft!=0 );
  3418   3428             assert( !ExprHasProperty(pE->pLeft, EP_IntValue) );
  3419   3429             zTName = pE->pLeft->u.zToken;
  3420         -        }else{
  3421         -          zTName = 0;
  3422   3430           }
  3423   3431           for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3424   3432             Table *pTab = pFrom->pTab;
         3433  +          Select *pSub = pFrom->pSelect;
  3425   3434             char *zTabName = pFrom->zAlias;
         3435  +          const char *zSchemaName = 0;
         3436  +          int iDb;
  3426   3437             if( zTabName==0 ){
  3427   3438               zTabName = pTab->zName;
  3428   3439             }
  3429   3440             if( db->mallocFailed ) break;
  3430         -          if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
  3431         -            continue;
         3441  +          if( pSub==0 || (pSub->selFlags & SF_NestedFrom)==0 ){
         3442  +            pSub = 0;
         3443  +            if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
         3444  +              continue;
         3445  +            }
         3446  +            iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
         3447  +            zSchemaName = iDb>=0 ? db->aDb[iDb].zName : "*";
  3432   3448             }
  3433         -          tableSeen = 1;
  3434   3449             for(j=0; j<pTab->nCol; j++){
  3435         -            Expr *pExpr, *pRight;
  3436   3450               char *zName = pTab->aCol[j].zName;
  3437   3451               char *zColname;  /* The computed column name */
  3438   3452               char *zToFree;   /* Malloced string that needs to be freed */
  3439   3453               Token sColname;  /* Computed column name as a token */
         3454  +
         3455  +            assert( zName );
         3456  +            if( zTName && pSub
         3457  +             && sqlite3MatchSpanName(pSub->pEList->a[j].zSpan, 0, zTName, 0)==0
         3458  +            ){
         3459  +              continue;
         3460  +            }
  3440   3461   
  3441   3462               /* If a column is marked as 'hidden' (currently only possible
  3442   3463               ** for virtual tables), do not include it in the expanded
  3443   3464               ** result-set list.
  3444   3465               */
  3445   3466               if( IsHiddenColumn(&pTab->aCol[j]) ){
  3446   3467                 assert(IsVirtual(pTab));
  3447   3468                 continue;
  3448   3469               }
         3470  +            tableSeen = 1;
  3449   3471   
  3450   3472               if( i>0 && zTName==0 ){
  3451   3473                 if( (pFrom->jointype & JT_NATURAL)!=0
  3452   3474                   && tableAndColumnIndex(pTabList, i, zName, 0, 0)
  3453   3475                 ){
  3454   3476                   /* In a NATURAL join, omit the join columns from the 
  3455   3477                   ** table to the right of the join */
................................................................................
  3464   3486               pRight = sqlite3Expr(db, TK_ID, zName);
  3465   3487               zColname = zName;
  3466   3488               zToFree = 0;
  3467   3489               if( longNames || pTabList->nSrc>1 ){
  3468   3490                 Expr *pLeft;
  3469   3491                 pLeft = sqlite3Expr(db, TK_ID, zTabName);
  3470   3492                 pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pRight, 0);
         3493  +              if( zSchemaName ){
         3494  +                pLeft = sqlite3Expr(db, TK_ID, zSchemaName);
         3495  +                pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pExpr, 0);
         3496  +              }
  3471   3497                 if( longNames ){
  3472   3498                   zColname = sqlite3MPrintf(db, "%s.%s", zTabName, zName);
  3473   3499                   zToFree = zColname;
  3474   3500                 }
  3475   3501               }else{
  3476   3502                 pExpr = pRight;
  3477   3503               }
  3478   3504               pNew = sqlite3ExprListAppend(pParse, pNew, pExpr);
  3479   3505               sColname.z = zColname;
  3480   3506               sColname.n = sqlite3Strlen30(zColname);
  3481   3507               sqlite3ExprListSetName(pParse, pNew, &sColname, 0);
         3508  +            if( pNew && (p->selFlags & SF_NestedFrom)!=0 ){
         3509  +              struct ExprList_item *pX = &pNew->a[pNew->nExpr-1];
         3510  +              if( pSub ){
         3511  +                pX->zSpan = sqlite3DbStrDup(db, pSub->pEList->a[j].zSpan);
         3512  +                testcase( pX->zSpan==0 );
         3513  +              }else{
         3514  +                pX->zSpan = sqlite3MPrintf(db, "%s.%s.%s",
         3515  +                                           zSchemaName, zTabName, zColname);
         3516  +                testcase( pX->zSpan==0 );
         3517  +              }
         3518  +              pX->bSpanIsTab = 1;
         3519  +            }
  3482   3520               sqlite3DbFree(db, zToFree);
  3483   3521             }
  3484   3522           }
  3485   3523           if( !tableSeen ){
  3486   3524             if( zTName ){
  3487   3525               sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
  3488   3526             }else{

Changes to src/sqliteInt.h.

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

Added test/selectD.test.

            1  +# 2012 December 19
            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  +# This file implements regression tests for name resolution in SELECT
           12  +# statements that have parenthesized FROM clauses.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +
           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
           25  +  }
           26  +  do_test selectD-$i.0 {
           27  +    db eval {
           28  +      ATTACH ':memory:' AS aux1;
           29  +      CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
           30  +      CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
           31  +      CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
           32  +      CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
           33  +      CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
           34  +    }
           35  +  } {}
           36  +  do_test selectD-$i.1 {
           37  +    db eval {
           38  +      SELECT *
           39  +        FROM (t1), (t2), (t3), (t4)
           40  +       WHERE t4.a=t3.a+111 
           41  +         AND t3.a=t2.a+111
           42  +         AND t2.a=t1.a+111;
           43  +    }
           44  +  } {111 x1 222 x2 333 x3 444 x4}
           45  +  do_test selectD-$i.2.1 {
           46  +    db eval {
           47  +      SELECT *
           48  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           49  +                              ON t3.a=t2.a+111)
           50  +                     ON t2.a=t1.a+111;
           51  +    }
           52  +  } {111 x1 222 x2 333 x3 444 x4}
           53  +  do_test selectD-$i.2.2 {
           54  +    db eval {
           55  +      SELECT t3.a
           56  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           57  +                              ON t3.a=t2.a+111)
           58  +                     ON t2.a=t1.a+111;
           59  +    }
           60  +  } {333}
           61  +  do_test selectD-$i.2.3 {
           62  +    db eval {
           63  +      SELECT t3.*
           64  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           65  +                              ON t3.a=t2.a+111)
           66  +                     ON t2.a=t1.a+111;
           67  +    }
           68  +  } {333 x3}
           69  +  do_test selectD-$i.2.3 {
           70  +    db eval {
           71  +      SELECT t3.*, t2.*
           72  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
           73  +                              ON t3.a=t2.a+111)
           74  +                     ON t2.a=t1.a+111;
           75  +    }
           76  +  } {333 x3 222 x2}
           77  +  do_test selectD-$i.2.4 {
           78  +    db eval {
           79  +      SELECT *
           80  +        FROM t1 JOIN (t2 JOIN (main.t4 JOIN aux1.t4 ON aux1.t4.a=main.t4.a+111)
           81  +                              ON main.t4.a=t2.a+222)
           82  +                     ON t2.a=t1.a+111;
           83  +    }
           84  +  } {111 x1 222 x2 444 x4 555 x5}
           85  +  do_test selectD-$i.2.5 {
           86  +    db eval {
           87  +      SELECT *
           88  +        FROM t1 JOIN (t2 JOIN (main.t4 AS x JOIN aux1.t4 ON aux1.t4.a=x.a+111)
           89  +                              ON x.a=t2.a+222)
           90  +                     ON t2.a=t1.a+111;
           91  +    }
           92  +  } {111 x1 222 x2 444 x4 555 x5}
           93  +  do_test selectD-$i.2.6 {
           94  +    catchsql {
           95  +      SELECT *
           96  +        FROM t1 JOIN (t2 JOIN (main.t4 JOIN aux.t4 ON aux.t4.a=main.t4.a+111)
           97  +                              ON main.t4.a=t2.a+222)
           98  +                     ON t2.a=t1.a+111;
           99  +    }
          100  +  } {1 {no such table: aux.t4}}
          101  +  do_test selectD-$i.2.7 {
          102  +    db eval {
          103  +      SELECT x.a, y.b
          104  +        FROM t1 JOIN (t2 JOIN (main.t4 x JOIN aux1.t4 y ON y.a=x.a+111)
          105  +                              ON x.a=t2.a+222)
          106  +                     ON t2.a=t1.a+111;
          107  +    }
          108  +  } {444 x5}
          109  +  do_test selectD-$i.3 {
          110  +    db eval {
          111  +      UPDATE t2 SET a=111;
          112  +      UPDATE t3 SET a=111;
          113  +      UPDATE t4 SET a=111;
          114  +      SELECT *
          115  +        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
          116  +    }
          117  +  } {111 x1 x2 x3 x4}
          118  +  do_test selectD-$i.4 {
          119  +    db eval {
          120  +      UPDATE t2 SET a=111;
          121  +      UPDATE t3 SET a=111;
          122  +      UPDATE t4 SET a=111;
          123  +      SELECT *
          124  +        FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
          125  +                                        USING (a))
          126  +                           USING (a);
          127  +    }
          128  +  } {111 x1 x2 x3 x4}
          129  +  do_test selectD-$i.5 {
          130  +    db eval {
          131  +      UPDATE t3 SET a=222;
          132  +      UPDATE t4 SET a=222;
          133  +      SELECT *
          134  +        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
          135  +             ON t1.a=t3.a-111;
          136  +    }
          137  +  } {111 x1 x2 222 x3 x4}
          138  +  do_test selectD-$i.6 {
          139  +    db eval {
          140  +      UPDATE t4 SET a=333;
          141  +      SELECT *
          142  +        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
          143  +             ON t1.a=t3.a-111;
          144  +    }
          145  +  } {111 x1 x2 222 x3 {}}
          146  +  do_test selectD-$i.7 {
          147  +    db eval {
          148  +      SELECT t1.*, t2.*, t3.*, t4.b
          149  +        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
          150  +             ON t1.a=t3.a-111;
          151  +    }
          152  +  } {111 x1 111 x2 222 x3 {}}
          153  +}
          154  +
          155  +finish_test