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 @@ -238,12 +267,11 @@ } if( pItem->pSelect && (pItem->pSelect->selFlags & SF_NestedFrom)!=0 ){ ExprList *pEList = pItem->pSelect->pEList; int hit = 0; for(j=0; jnExpr; j++){ - if( zTab && sqlite3StrICmp(pEList->a[j].zSpan, zTab)!=0 ) continue; - if( sqlite3StrICmp(pEList->a[j].zName, zCol)==0 ){ + if( sqlite3MatchSpanName(pEList->a[j].zSpan, zCol, zTab, zDb) ){ cnt++; cntTab = 2; pMatch = pItem; pExpr->iColumn = j; } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3292,10 +3292,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 ){ @@ -3377,11 +3378,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; } @@ -3397,13 +3398,14 @@ int longNames = (flags & SQLITE_FullColNames)!=0 && (flags & SQLITE_ShortColNames)==0 && (p->selFlags & SF_NestedFrom)==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; @@ -3414,44 +3416,55 @@ 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; 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 ){ + int iDb; + pSub = 0; + if( zTName && sqlite3StrICmp(zTName, zTabName)!=0 ){ + continue; + } + iDb = sqlite3SchemaToIndex(db, pTab->pSchema); + zSchemaName = iDb>=0 ? db->aDb[i].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 */ + + 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) ){ @@ -3482,11 +3495,18 @@ pNew = sqlite3ExprListAppend(pParse, pNew, pExpr); sColname.z = zColname; sColname.n = sqlite3Strlen30(zColname); sqlite3ExprListSetName(pParse, pNew, &sColname, 0); if( pNew && (p->selFlags & SF_NestedFrom)!=0 ){ - pNew->a[pNew->nExpr-1].zSpan = sqlite3DbStrDup(db, zTabName); + 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 ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1775,11 +1775,12 @@ ** 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 table to which the column of a FROM-clause subquery refers. +** 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 */ @@ -1786,11 +1787,11 @@ 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 table name, not the span */ + 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 */ }; @@ -3078,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 *); Index: test/selectD.test ================================================================== --- test/selectD.test +++ test/selectD.test @@ -13,66 +13,109 @@ # set testdir [file dirname $argv0] source $testdir/tester.tcl -do_test selectD-1.1 { - 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 TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3'); - CREATE TABLE t4(a,b); INSERT INTO t4 VALUES(444,'x4'); - SELECT * - FROM (t1), (t2), (t3), (t4) - WHERE t4.a=t3.a+111 - AND t3.a=t2.a+111 - AND t2.a=t1.a+111; +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 } -} {111 x1 222 x2 333 x3 444 x4} -do_test selectD-1.2 { - 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-1.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-1.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-1.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-1.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.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