/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

3277
3278
3279
3280
3281
3282
3283






3284
3285
3286
3287
3288
3289
3290
  }else{
    sqlite3ExplainPush(pOut);
    for(i=0; i<pList->nExpr; i++){
      sqlite3ExplainPrintf(pOut, "item[%d] = ", i);
      sqlite3ExplainPush(pOut);
      sqlite3ExplainExpr(pOut, pList->a[i].pExpr);
      sqlite3ExplainPop(pOut);






      if( i<pList->nExpr-1 ){
        sqlite3ExplainNL(pOut);
      }
    }
    sqlite3ExplainPop(pOut);
  }
}







>
>
>
>
>
>







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

Changes to src/resolve.c.

146
147
148
149
150
151
152





























153
154
155
156
157
158
159
...
201
202
203
204
205
206
207














208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

224
225
226
227
228

229
230




231
232
233

234
235





236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264




265

266
267
268
269
270
271
272
    for(k=0; k<pUsing->nId; k++){
      if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1;
    }
  }
  return 0;
}































/*
** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
** that name in the set of source tables in pSrcList and make the pExpr 
** expression node refer back to that source column.  The following changes
** are made to pExpr:
**
................................................................................
  assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
  assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );

  /* Initialize the node to no-match */
  pExpr->iTable = -1;
  pExpr->pTab = 0;
  ExprSetIrreducible(pExpr);















  /* Start at the inner-most context and move outward until a match is found */
  while( pNC && cnt==0 ){
    ExprList *pEList;
    SrcList *pSrcList = pNC->pSrcList;

    if( pSrcList ){
      for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
        Table *pTab;
        int iDb;
        Column *pCol;
  
        pTab = pItem->pTab;
        assert( pTab!=0 && pTab->zName!=0 );
        iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
        assert( pTab->nCol>0 );

        if( zTab ){
          if( pItem->zAlias ){
            char *zTabName = pItem->zAlias;
            if( sqlite3StrICmp(zTabName, zTab)!=0 ) continue;
          }else{

            char *zTabName = pTab->zName;
            if( NEVER(zTabName==0) || sqlite3StrICmp(zTabName, zTab)!=0 ){




              continue;
            }
            if( zDb!=0 && sqlite3StrICmp(db->aDb[iDb].zName, zDb)!=0 ){

              continue;
            }





          }
        }
        if( 0==(cntTab++) ){
          pExpr->iTable = pItem->iCursor;
          pExpr->pTab = pTab;
          pSchema = pTab->pSchema;
          pMatch = pItem;
        }
        for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
          if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
            /* If there has been exactly one prior match and this match
            ** is for the right-hand table of a NATURAL JOIN or is in a 
            ** USING clause, then skip this match.
            */
            if( cnt==1 ){
              if( pItem->jointype & JT_NATURAL ) continue;
              if( nameInUsingClause(pItem->pUsing, zCol) ) continue;
            }
            cnt++;
            pExpr->iTable = pItem->iCursor;
            pExpr->pTab = pTab;
            pMatch = pItem;
            pSchema = pTab->pSchema;
            /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
            pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
            break;
          }
        }
      }




    }


#ifndef SQLITE_OMIT_TRIGGER
    /* If we have not already resolved the name, then maybe 
    ** it is a new.* or old.* trigger argument reference
    */
    if( zDb==0 && zTab!=0 && cnt==0 && pParse->pTriggerTab!=0 ){
      int op = pParse->eTriggerOp;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>









<




<

>
|
|
|
|
<
>
|
|
>
>
>
>
|
|
<
>
|
|
>
>
>
>
>



<
<
<













<
<

<






>
>
>
>
|
>







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
...
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259

260
261
262
263

264
265
266
267
268
269

270
271
272
273
274
275
276
277
278

279
280
281
282
283
284
285
286
287
288
289



290
291
292
293
294
295
296
297
298
299
300
301
302


303

304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
    for(k=0; k<pUsing->nId; k++){
      if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1;
    }
  }
  return 0;
}

/*
** Subqueries stores the original database, table and column names for their
** result sets in ExprList.a[].zSpan, in the form "DATABASE.TABLE.COLUMN".
** Check to see if the zSpan given to this routine matches the zDb, zTab,
** and zCol.  If any of zDb, zTab, and zCol are NULL then those fields will
** match anything.
*/
int sqlite3MatchSpanName(
  const char *zSpan,
  const char *zCol,
  const char *zTab,
  const char *zDb
){
  int n;
  for(n=0; ALWAYS(zSpan[n]) && zSpan[n]!='.'; n++){}
  if( zDb && sqlite3StrNICmp(zSpan, zDb, n)!=0 ){
    return 0;
  }
  zSpan += n+1;
  for(n=0; ALWAYS(zSpan[n]) && zSpan[n]!='.'; n++){}
  if( zTab && sqlite3StrNICmp(zSpan, zTab, n)!=0 ){
    return 0;
  }
  zSpan += n+1;
  if( zCol && sqlite3StrICmp(zSpan, zCol)!=0 ){
    return 0;
  }
  return 1;
}

/*
** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
** that name in the set of source tables in pSrcList and make the pExpr 
** expression node refer back to that source column.  The following changes
** are made to pExpr:
**
................................................................................
  assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
  assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );

  /* Initialize the node to no-match */
  pExpr->iTable = -1;
  pExpr->pTab = 0;
  ExprSetIrreducible(pExpr);

  /* Translate the schema name in zDb into a pointer to the corresponding
  ** schema.  If not found, pSchema will remain NULL and nothing will match
  ** resulting in an appropriate error message toward the end of this routine
  */
  if( zDb ){
    for(i=0; i<db->nDb; i++){
      assert( db->aDb[i].zName );
      if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){
        pSchema = db->aDb[i].pSchema;
        break;
      }
    }
  }

  /* Start at the inner-most context and move outward until a match is found */
  while( pNC && cnt==0 ){
    ExprList *pEList;
    SrcList *pSrcList = pNC->pSrcList;

    if( pSrcList ){
      for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
        Table *pTab;

        Column *pCol;
  
        pTab = pItem->pTab;
        assert( pTab!=0 && pTab->zName!=0 );

        assert( pTab->nCol>0 );
        if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){
          ExprList *pEList = pItem->pSelect->pEList;
          int hit = 0;
          for(j=0; j<pEList->nExpr; j++){
            if( sqlite3MatchSpanName(pEList->a[j].zSpan, zCol, zTab, zDb) ){

              cnt++;
              cntTab = 2;
              pMatch = pItem;
              pExpr->iColumn = j;
              hit = 1;
            }
          }
          if( hit || zTab==0 ) continue;
        }

        if( zDb && pTab->pSchema!=pSchema ){
          continue;
        }
        if( zTab ){
          const char *zTabName = pItem->zAlias ? pItem->zAlias : pTab->zName;
          assert( zTabName!=0 );
          if( sqlite3StrICmp(zTabName, zTab)!=0 ){
            continue;
          }
        }
        if( 0==(cntTab++) ){



          pMatch = pItem;
        }
        for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
          if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
            /* If there has been exactly one prior match and this match
            ** is for the right-hand table of a NATURAL JOIN or is in a 
            ** USING clause, then skip this match.
            */
            if( cnt==1 ){
              if( pItem->jointype & JT_NATURAL ) continue;
              if( nameInUsingClause(pItem->pUsing, zCol) ) continue;
            }
            cnt++;


            pMatch = pItem;

            /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
            pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
            break;
          }
        }
      }
      if( pMatch ){
        pExpr->iTable = pMatch->iCursor;
        pExpr->pTab = pMatch->pTab;
        pSchema = pExpr->pTab->pSchema;
      }
    } /* if( pSrcList ) */

#ifndef SQLITE_OMIT_TRIGGER
    /* If we have not already resolved the name, then maybe 
    ** it is a new.* or old.* trigger argument reference
    */
    if( zDb==0 && zTab!=0 && cnt==0 && pParse->pTriggerTab!=0 ){
      int op = pParse->eTriggerOp;

Changes to src/select.c.

1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
....
1371
1372
1373
1374
1375
1376
1377



1378
1379
1380
1381
1382
1383
1384
....
3287
3288
3289
3290
3291
3292
3293

3294
3295
3296
3297
3298
3299
3300
....
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
....
3390
3391
3392
3393
3394
3395
3396







3397
3398

3399
3400
3401
3402
3403
3404
3405
3406
3407
....
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424

3425


3426
3427
3428
3429


3430
3431
3432
3433



3434
3435
3436
3437
3438
3439







3440
3441
3442
3443
3444
3445
3446
3447
3448

3449
3450
3451
3452
3453
3454
3455
....
3464
3465
3466
3467
3468
3469
3470




3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
3481












3482
3483
3484
3485
3486
3487
3488
  *pnCol = nCol;
  *paCol = aCol;

  for(i=0, pCol=aCol; i<nCol; i++, pCol++){
    /* Get an appropriate name for the column
    */
    p = sqlite3ExprSkipCollate(pEList->a[i].pExpr);
    assert( p->pRight==0 || ExprHasProperty(p->pRight, EP_IntValue)
               || p->pRight->u.zToken==0 || p->pRight->u.zToken[0]!=0 );
    if( (zName = pEList->a[i].zName)!=0 ){
      /* If the column contains an "AS <name>" phrase, use <name> as the name */
      zName = sqlite3DbStrDup(db, zName);
    }else{
      Expr *pColExpr = p;  /* The expression that is the result column name */
      Table *pTab;         /* Table associated with this expression */
      while( pColExpr->op==TK_DOT ){
................................................................................
    /* Make sure the column name is unique.  If the name is not unique,
    ** append a integer to the name so that it becomes unique.
    */
    nName = sqlite3Strlen30(zName);
    for(j=cnt=0; j<i; j++){
      if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
        char *zNewName;



        zName[nName] = 0;
        zNewName = sqlite3MPrintf(db, "%s:%d", zName, ++cnt);
        sqlite3DbFree(db, zName);
        zName = zNewName;
        j = -1;
        if( zName==0 ) break;
      }
................................................................................
static int selectExpander(Walker *pWalker, Select *p){
  Parse *pParse = pWalker->pParse;
  int i, j, k;
  SrcList *pTabList;
  ExprList *pEList;
  struct SrcList_item *pFrom;
  sqlite3 *db = pParse->db;


  if( db->mallocFailed  ){
    return WRC_Abort;
  }
  if( NEVER(p->pSrc==0) || (p->selFlags & SF_Expanded)!=0 ){
    return WRC_Prune;
  }
................................................................................
  ** The following code just has to locate the TK_ALL expressions and expand
  ** each one to the list of all columns in all tables.
  **
  ** The first loop just checks to see if there are any "*" operators
  ** that need expanding.
  */
  for(k=0; k<pEList->nExpr; k++){
    Expr *pE = pEList->a[k].pExpr;
    if( pE->op==TK_ALL ) break;
    assert( pE->op!=TK_DOT || pE->pRight!=0 );
    assert( pE->op!=TK_DOT || (pE->pLeft!=0 && pE->pLeft->op==TK_ID) );
    if( pE->op==TK_DOT && pE->pRight->op==TK_ALL ) break;
  }
  if( k<pEList->nExpr ){
    /*
................................................................................
    */
    struct ExprList_item *a = pEList->a;
    ExprList *pNew = 0;
    int flags = pParse->db->flags;
    int longNames = (flags & SQLITE_FullColNames)!=0
                      && (flags & SQLITE_ShortColNames)==0;








    for(k=0; k<pEList->nExpr; k++){
      Expr *pE = a[k].pExpr;

      assert( pE->op!=TK_DOT || pE->pRight!=0 );
      if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pE->pRight->op!=TK_ALL) ){
        /* This particular expression does not need to be expanded.
        */
        pNew = sqlite3ExprListAppend(pParse, pNew, a[k].pExpr);
        if( pNew ){
          pNew->a[pNew->nExpr-1].zName = a[k].zName;
          pNew->a[pNew->nExpr-1].zSpan = a[k].zSpan;
          a[k].zName = 0;
................................................................................
          a[k].zSpan = 0;
        }
        a[k].pExpr = 0;
      }else{
        /* This expression is a "*" or a "TABLE.*" and needs to be
        ** expanded. */
        int tableSeen = 0;      /* Set to 1 when TABLE matches */
        char *zTName;            /* text of name of TABLE */
        if( pE->op==TK_DOT ){
          assert( pE->pLeft!=0 );
          assert( !ExprHasProperty(pE->pLeft, EP_IntValue) );
          zTName = pE->pLeft->u.zToken;
        }else{
          zTName = 0;
        }
        for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
          Table *pTab = pFrom->pTab;

          char *zTabName = pFrom->zAlias;


          if( zTabName==0 ){
            zTabName = pTab->zName;
          }
          if( db->mallocFailed ) break;


          if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
            continue;
          }
          tableSeen = 1;



          for(j=0; j<pTab->nCol; j++){
            Expr *pExpr, *pRight;
            char *zName = pTab->aCol[j].zName;
            char *zColname;  /* The computed column name */
            char *zToFree;   /* Malloced string that needs to be freed */
            Token sColname;  /* Computed column name as a token */








            /* If a column is marked as 'hidden' (currently only possible
            ** for virtual tables), do not include it in the expanded
            ** result-set list.
            */
            if( IsHiddenColumn(&pTab->aCol[j]) ){
              assert(IsVirtual(pTab));
              continue;
            }


            if( i>0 && zTName==0 ){
              if( (pFrom->jointype & JT_NATURAL)!=0
                && tableAndColumnIndex(pTabList, i, zName, 0, 0)
              ){
                /* In a NATURAL join, omit the join columns from the 
                ** table to the right of the join */
................................................................................
            pRight = sqlite3Expr(db, TK_ID, zName);
            zColname = zName;
            zToFree = 0;
            if( longNames || pTabList->nSrc>1 ){
              Expr *pLeft;
              pLeft = sqlite3Expr(db, TK_ID, zTabName);
              pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pRight, 0);




              if( longNames ){
                zColname = sqlite3MPrintf(db, "%s.%s", zTabName, zName);
                zToFree = zColname;
              }
            }else{
              pExpr = pRight;
            }
            pNew = sqlite3ExprListAppend(pParse, pNew, pExpr);
            sColname.z = zColname;
            sColname.n = sqlite3Strlen30(zColname);
            sqlite3ExprListSetName(pParse, pNew, &sColname, 0);












            sqlite3DbFree(db, zToFree);
          }
        }
        if( !tableSeen ){
          if( zTName ){
            sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
          }else{







<
<







 







>
>
>







 







>







 







|







 







>
>
>
>
>
>
>

|
>
|
|







 







|




<
<



>

>
>




>
>
|
|
|
<
>
>
>

<




>
>
>
>
>
>
>









>







 







>
>
>
>











>
>
>
>
>
>
>
>
>
>
>
>







1332
1333
1334
1335
1336
1337
1338


1339
1340
1341
1342
1343
1344
1345
....
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
....
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
3298
3299
3300
3301
3302
....
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
....
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
3415
3416
3417
....
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429


3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445

3446
3447
3448
3449

3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
....
3486
3487
3488
3489
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
  *pnCol = nCol;
  *paCol = aCol;

  for(i=0, pCol=aCol; i<nCol; i++, pCol++){
    /* Get an appropriate name for the column
    */
    p = sqlite3ExprSkipCollate(pEList->a[i].pExpr);


    if( (zName = pEList->a[i].zName)!=0 ){
      /* If the column contains an "AS <name>" phrase, use <name> as the name */
      zName = sqlite3DbStrDup(db, zName);
    }else{
      Expr *pColExpr = p;  /* The expression that is the result column name */
      Table *pTab;         /* Table associated with this expression */
      while( pColExpr->op==TK_DOT ){
................................................................................
    /* Make sure the column name is unique.  If the name is not unique,
    ** append a integer to the name so that it becomes unique.
    */
    nName = sqlite3Strlen30(zName);
    for(j=cnt=0; j<i; j++){
      if( sqlite3StrICmp(aCol[j].zName, zName)==0 ){
        char *zNewName;
        int k;
        for(k=nName-1; k>1 && sqlite3Isdigit(zName[k]); k--){}
        if( zName[k]==':' ) nName = k;
        zName[nName] = 0;
        zNewName = sqlite3MPrintf(db, "%s:%d", zName, ++cnt);
        sqlite3DbFree(db, zName);
        zName = zNewName;
        j = -1;
        if( zName==0 ) break;
      }
................................................................................
static int selectExpander(Walker *pWalker, Select *p){
  Parse *pParse = pWalker->pParse;
  int i, j, k;
  SrcList *pTabList;
  ExprList *pEList;
  struct SrcList_item *pFrom;
  sqlite3 *db = pParse->db;
  Expr *pE, *pRight, *pExpr;

  if( db->mallocFailed  ){
    return WRC_Abort;
  }
  if( NEVER(p->pSrc==0) || (p->selFlags & SF_Expanded)!=0 ){
    return WRC_Prune;
  }
................................................................................
  ** The following code just has to locate the TK_ALL expressions and expand
  ** each one to the list of all columns in all tables.
  **
  ** The first loop just checks to see if there are any "*" operators
  ** that need expanding.
  */
  for(k=0; k<pEList->nExpr; k++){
    pE = pEList->a[k].pExpr;
    if( pE->op==TK_ALL ) break;
    assert( pE->op!=TK_DOT || pE->pRight!=0 );
    assert( pE->op!=TK_DOT || (pE->pLeft!=0 && pE->pLeft->op==TK_ID) );
    if( pE->op==TK_DOT && pE->pRight->op==TK_ALL ) break;
  }
  if( k<pEList->nExpr ){
    /*
................................................................................
    */
    struct ExprList_item *a = pEList->a;
    ExprList *pNew = 0;
    int flags = pParse->db->flags;
    int longNames = (flags & SQLITE_FullColNames)!=0
                      && (flags & SQLITE_ShortColNames)==0;

    /* When processing FROM-clause subqueries, it is always the case
    ** that full_column_names=OFF and short_column_names=ON.  The
    ** sqlite3ResultSetOfSelect() routine makes it so. */
    assert( (p->selFlags & SF_NestedFrom)==0
          || ((flags & SQLITE_FullColNames)==0 &&
              (flags & SQLITE_ShortColNames)!=0) );

    for(k=0; k<pEList->nExpr; k++){
      pE = a[k].pExpr;
      pRight = pE->pRight;
      assert( pE->op!=TK_DOT || pRight!=0 );
      if( pE->op!=TK_ALL && (pE->op!=TK_DOT || pRight->op!=TK_ALL) ){
        /* This particular expression does not need to be expanded.
        */
        pNew = sqlite3ExprListAppend(pParse, pNew, a[k].pExpr);
        if( pNew ){
          pNew->a[pNew->nExpr-1].zName = a[k].zName;
          pNew->a[pNew->nExpr-1].zSpan = a[k].zSpan;
          a[k].zName = 0;
................................................................................
          a[k].zSpan = 0;
        }
        a[k].pExpr = 0;
      }else{
        /* This expression is a "*" or a "TABLE.*" and needs to be
        ** expanded. */
        int tableSeen = 0;      /* Set to 1 when TABLE matches */
        char *zTName = 0;       /* text of name of TABLE */
        if( pE->op==TK_DOT ){
          assert( pE->pLeft!=0 );
          assert( !ExprHasProperty(pE->pLeft, EP_IntValue) );
          zTName = pE->pLeft->u.zToken;


        }
        for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
          Table *pTab = pFrom->pTab;
          Select *pSub = pFrom->pSelect;
          char *zTabName = pFrom->zAlias;
          const char *zSchemaName = 0;
          int iDb;
          if( zTabName==0 ){
            zTabName = pTab->zName;
          }
          if( db->mallocFailed ) break;
          if( pSub==0 || (pSub->selFlags & SF_NestedFrom)==0 ){
            pSub = 0;
            if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){
              continue;
            }

            iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
            zSchemaName = iDb>=0 ? db->aDb[iDb].zName : "*";
          }
          for(j=0; j<pTab->nCol; j++){

            char *zName = pTab->aCol[j].zName;
            char *zColname;  /* The computed column name */
            char *zToFree;   /* Malloced string that needs to be freed */
            Token sColname;  /* Computed column name as a token */

            assert( zName );
            if( zTName && pSub
             && sqlite3MatchSpanName(pSub->pEList->a[j].zSpan, 0, zTName, 0)==0
            ){
              continue;
            }

            /* If a column is marked as 'hidden' (currently only possible
            ** for virtual tables), do not include it in the expanded
            ** result-set list.
            */
            if( IsHiddenColumn(&pTab->aCol[j]) ){
              assert(IsVirtual(pTab));
              continue;
            }
            tableSeen = 1;

            if( i>0 && zTName==0 ){
              if( (pFrom->jointype & JT_NATURAL)!=0
                && tableAndColumnIndex(pTabList, i, zName, 0, 0)
              ){
                /* In a NATURAL join, omit the join columns from the 
                ** table to the right of the join */
................................................................................
            pRight = sqlite3Expr(db, TK_ID, zName);
            zColname = zName;
            zToFree = 0;
            if( longNames || pTabList->nSrc>1 ){
              Expr *pLeft;
              pLeft = sqlite3Expr(db, TK_ID, zTabName);
              pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pRight, 0);
              if( zSchemaName ){
                pLeft = sqlite3Expr(db, TK_ID, zSchemaName);
                pExpr = sqlite3PExpr(pParse, TK_DOT, pLeft, pExpr, 0);
              }
              if( longNames ){
                zColname = sqlite3MPrintf(db, "%s.%s", zTabName, zName);
                zToFree = zColname;
              }
            }else{
              pExpr = pRight;
            }
            pNew = sqlite3ExprListAppend(pParse, pNew, pExpr);
            sColname.z = zColname;
            sColname.n = sqlite3Strlen30(zColname);
            sqlite3ExprListSetName(pParse, pNew, &sColname, 0);
            if( pNew && (p->selFlags & SF_NestedFrom)!=0 ){
              struct ExprList_item *pX = &pNew->a[pNew->nExpr-1];
              if( pSub ){
                pX->zSpan = sqlite3DbStrDup(db, pSub->pEList->a[j].zSpan);
                testcase( pX->zSpan==0 );
              }else{
                pX->zSpan = sqlite3MPrintf(db, "%s.%s.%s",
                                           zSchemaName, zTabName, zColname);
                testcase( pX->zSpan==0 );
              }
              pX->bSpanIsTab = 1;
            }
            sqlite3DbFree(db, zToFree);
          }
        }
        if( !tableSeen ){
          if( zTName ){
            sqlite3ErrorMsg(pParse, "no such table: %s", zTName);
          }else{

Changes to src/sqliteInt.h.

1767
1768
1769
1770
1771
1772
1773








1774
1775
1776
1777
1778
1779
1780
1781
1782
1783

1784
1785
1786
1787
1788
1789
1790
1791
1792
....
3068
3069
3070
3071
3072
3073
3074

3075
3076
3077
3078
3079
3080
3081
/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
** list of "ID = expr" items in an UPDATE.  A list of expressions can
** also be used as the argument to a function, in which case the a.zName
** field is not used.








*/
struct ExprList {
  int nExpr;             /* Number of expressions on the list */
  int iECursor;          /* VDBE Cursor associated with this ExprList */
  struct ExprList_item { /* For each expression in the list */
    Expr *pExpr;           /* The list of expressions */
    char *zName;           /* Token associated with this expression */
    char *zSpan;           /* Original text of the expression */
    u8 sortOrder;          /* 1 for DESC or 0 for ASC */
    u8 done;               /* A flag to indicate when processing is finished */

    u16 iOrderByCol;       /* For ORDER BY, column number in result set */
    u16 iAlias;            /* Index into Parse.aAlias[] for zName */
  } *a;                  /* Alloc a power of two greater or equal to nExpr */
};

/*
** An instance of this structure is used by the parser to record both
** the parse tree for an expression and the span of input text for an
** expression.
................................................................................
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);

int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);







>
>
>
>
>
>
>
>





|
|
|
|
|
>
|
|







 







>







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

/*
** An instance of this structure is used by the parser to record both
** the parse tree for an expression and the span of input text for an
** expression.
................................................................................
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);

Added test/selectD.test.























































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
# 2012 December 19
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for name resolution in SELECT
# statements that have parenthesized FROM clauses.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


for {set i 1} {$i<=2} {incr i} {
  db close
  forcedelete test$i.db
  sqlite3 db test$i.db
  if {$i==2} {
    optimization_control db query-flattener off
  }
  do_test selectD-$i.0 {
    db eval {
      ATTACH ':memory:' AS aux1;
      CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
      CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
      CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
      CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
      CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
    }
  } {}
  do_test selectD-$i.1 {
    db eval {
      SELECT *
        FROM (t1), (t2), (t3), (t4)
       WHERE t4.a=t3.a+111 
         AND t3.a=t2.a+111
         AND t2.a=t1.a+111;
    }
  } {111 x1 222 x2 333 x3 444 x4}
  do_test selectD-$i.2.1 {
    db eval {
      SELECT *
        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
                              ON t3.a=t2.a+111)
                     ON t2.a=t1.a+111;
    }
  } {111 x1 222 x2 333 x3 444 x4}
  do_test selectD-$i.2.2 {
    db eval {
      SELECT t3.a
        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
                              ON t3.a=t2.a+111)
                     ON t2.a=t1.a+111;
    }
  } {333}
  do_test selectD-$i.2.3 {
    db eval {
      SELECT t3.*
        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
                              ON t3.a=t2.a+111)
                     ON t2.a=t1.a+111;
    }
  } {333 x3}
  do_test selectD-$i.2.3 {
    db eval {
      SELECT t3.*, t2.*
        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
                              ON t3.a=t2.a+111)
                     ON t2.a=t1.a+111;
    }
  } {333 x3 222 x2}
  do_test selectD-$i.2.4 {
    db eval {
      SELECT *
        FROM t1 JOIN (t2 JOIN (main.t4 JOIN aux1.t4 ON aux1.t4.a=main.t4.a+111)
                              ON main.t4.a=t2.a+222)
                     ON t2.a=t1.a+111;
    }
  } {111 x1 222 x2 444 x4 555 x5}
  do_test selectD-$i.2.5 {
    db eval {
      SELECT *
        FROM t1 JOIN (t2 JOIN (main.t4 AS x JOIN aux1.t4 ON aux1.t4.a=x.a+111)
                              ON x.a=t2.a+222)
                     ON t2.a=t1.a+111;
    }
  } {111 x1 222 x2 444 x4 555 x5}
  do_test selectD-$i.2.6 {
    catchsql {
      SELECT *
        FROM t1 JOIN (t2 JOIN (main.t4 JOIN aux.t4 ON aux.t4.a=main.t4.a+111)
                              ON main.t4.a=t2.a+222)
                     ON t2.a=t1.a+111;
    }
  } {1 {no such table: aux.t4}}
  do_test selectD-$i.2.7 {
    db eval {
      SELECT x.a, y.b
        FROM t1 JOIN (t2 JOIN (main.t4 x JOIN aux1.t4 y ON y.a=x.a+111)
                              ON x.a=t2.a+222)
                     ON t2.a=t1.a+111;
    }
  } {444 x5}
  do_test selectD-$i.3 {
    db eval {
      UPDATE t2 SET a=111;
      UPDATE t3 SET a=111;
      UPDATE t4 SET a=111;
      SELECT *
        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
    }
  } {111 x1 x2 x3 x4}
  do_test selectD-$i.4 {
    db eval {
      UPDATE t2 SET a=111;
      UPDATE t3 SET a=111;
      UPDATE t4 SET a=111;
      SELECT *
        FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
                                        USING (a))
                           USING (a);
    }
  } {111 x1 x2 x3 x4}
  do_test selectD-$i.5 {
    db eval {
      UPDATE t3 SET a=222;
      UPDATE t4 SET a=222;
      SELECT *
        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
             ON t1.a=t3.a-111;
    }
  } {111 x1 x2 222 x3 x4}
  do_test selectD-$i.6 {
    db eval {
      UPDATE t4 SET a=333;
      SELECT *
        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
             ON t1.a=t3.a-111;
    }
  } {111 x1 x2 222 x3 {}}
  do_test selectD-$i.7 {
    db eval {
      SELECT t1.*, t2.*, t3.*, t4.b
        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
             ON t1.a=t3.a-111;
    }
  } {111 x1 111 x2 222 x3 {}}
}

finish_test