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 |
Timelines: | family | ancestors | descendants | both | name-resolution-fix |
Files: | files | file ages | folders |
SHA1: |
4cf5ed7ea198abc32f8118e79490e77f |
User & Date: | drh 2013-01-03 00:45:56.842 |
Context
2013-01-03
| ||
16:54 | Remove an incorrect assert() statement (ticket [beba9cae6345a]). Fix other minor problems in the name resolution logic. (check-in: afe96a118c user: drh tags: name-resolution-fix) | |
00:45 | Now supports result sets of the form "TABLE.*" with nested FROM clauses. (check-in: 4cf5ed7ea1 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: ef01e30456 user: drh tags: name-resolution-fix) | |
Changes
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 | 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: ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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: ** |
︙ | ︙ | |||
236 237 238 239 240 241 242 | if( zDb && pTab->pSchema!=pSchema ){ continue; } if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){ ExprList *pEList = pItem->pSelect->pEList; int hit = 0; for(j=0; j<pEList->nExpr; j++){ | | < | 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | if( zDb && pTab->pSchema!=pSchema ){ continue; } 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; } } if( hit || zTab==0 ) continue; |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 | 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; } | > | 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 | 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; } |
︙ | ︙ | |||
3375 3376 3377 3378 3379 3380 3381 | ** 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++){ | | | > | | | < < > > > > > | | | | > > < > > > > > > > | 3376 3377 3378 3379 3380 3381 3382 3383 3384 3385 3386 3387 3388 3389 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 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 3467 3468 3469 3470 3471 3472 | ** 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 ){ /* ** If we get here it means the result set contains one or more "*" ** operators that need to be expanded. Loop through each expression ** in the result set and expand them one by one. */ struct ExprList_item *a = pEList->a; ExprList *pNew = 0; int flags = pParse->db->flags; int longNames = (flags & SQLITE_FullColNames)!=0 && (flags & SQLITE_ShortColNames)==0 && (p->selFlags & SF_NestedFrom)==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; if( zTabName==0 ){ zTabName = pTab->zName; } if( db->mallocFailed ) break; if( pSub==0 || (pSub->selFlags & SF_NestedFrom)==0 ){ int iDb; pSub = 0; if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){ continue; } iDb = sqlite3SchemaToIndex(db, pTab->pSchema); zSchemaName = iDb>=0 ? db->aDb[i].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 */ 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 */ |
︙ | ︙ | |||
3480 3481 3482 3483 3484 3485 3486 | 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 ){ | > > | > > > > > | 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 | 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); }else{ pX->zSpan = sqlite3MPrintf(db, "%s.%s.%s", zSchemaName, zTabName, zColname); } pX->bSpanIsTab = 1; } sqlite3DbFree(db, zToFree); } } if( !tableSeen ){ if( zTName ){ sqlite3ErrorMsg(pParse, "no such table: %s", zTName); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1773 1774 1775 1776 1777 1778 1779 | ** 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 | | > | | 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 | ** 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 |
︙ | ︙ | |||
3076 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 | 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*); | > | 3077 3078 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 | 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*); |
︙ | ︙ |
Changes to test/selectD.test.
︙ | ︙ | |||
11 12 13 14 15 16 17 | # 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 | > > > > > > > > | | | | | | | > > > | | | | | | | | | | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > > > > > > > | 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 | # 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 { 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 t4(a,b); INSERT INTO t4 VALUES(444,'x4'); } } {} 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.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 |