Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -3279,10 +3279,16 @@ for(i=0; inExpr; 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( inExpr-1 ){ sqlite3ExplainNL(pOut); } } sqlite3ExplainPop(pOut); Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -148,10 +148,39 @@ } } 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 @@ -203,44 +232,63 @@ /* 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; inDb; 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; inSrc; 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; + assert( pTab->nCol>0 ); + if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){ + ExprList *pEList = pItem->pSelect->pEList; + int hit = 0; + for(j=0; jnExpr; 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; jnCol; j++, pCol++){ if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ /* If there has been exactly one prior match and this match @@ -250,21 +298,23 @@ 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; } } } - } + 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 */ Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -1334,12 +1334,10 @@ for(i=0, pCol=aCol; ia[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 " phrase, use as the name */ zName = sqlite3DbStrDup(db, zName); }else{ Expr *pColExpr = p; /* The expression that is the result column name */ @@ -1373,10 +1371,13 @@ */ nName = sqlite3Strlen30(zName); for(j=cnt=0; j1 && 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; @@ -3289,10 +3290,11 @@ 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 ){ @@ -3374,11 +3376,11 @@ ** ** The first loop just checks to see if there are any "*" operators ** that need expanding. */ for(k=0; knExpr; k++){ - Expr *pE = pEList->a[k].pExpr; + 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; } @@ -3392,14 +3394,22 @@ 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; knExpr; 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) ){ + 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; @@ -3410,44 +3420,56 @@ 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 */ + 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; - }else{ - zTName = 0; } for(i=0, pFrom=pTabList->a; inSrc; 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( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){ - continue; + 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 : "*"; } - tableSeen = 1; for(j=0; jnCol; 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 */ + + 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) ){ @@ -3466,10 +3488,14 @@ 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{ @@ -3477,10 +3503,22 @@ } 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 ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1769,22 +1769,31 @@ ** 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 */ - 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 */ + 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 @@ -3070,10 +3079,11 @@ 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 *); ADDED test/selectD.test Index: test/selectD.test ================================================================== --- /dev/null +++ test/selectD.test @@ -0,0 +1,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