Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -1436,17 +1436,14 @@ if( pS ){ /* The "table" is actually a sub-select or a view in the FROM clause ** of the SELECT statement. Return the declaration type and origin ** data for the result-set column of the sub-select. */ - if( iCol>=0 && ALWAYS(iColpEList->nExpr) ){ + if( iCol>=0 && iColpEList->nExpr ){ /* If iCol is less than zero, then the expression requests the ** rowid of the sub-select or view. This expression is legal (see ** test case misc2.2.2) - it always evaluates to NULL. - ** - ** The ALWAYS() is because iCol>=pS->pEList->nExpr will have been - ** caught already by name resolution. */ NameContext sNC; Expr *p = pS->pEList->a[iCol].pExpr; sNC.pSrcList = pS->pSrc; sNC.pNext = pNC; @@ -1552,22 +1549,10 @@ sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT); } #endif /* !defined(SQLITE_OMIT_DECLTYPE) */ } -/* -** Return the Table objecct in the SrcList that has cursor iCursor. -** Or return NULL if no such Table object exists in the SrcList. -*/ -static Table *tableWithCursor(SrcList *pList, int iCursor){ - int j; - for(j=0; jnSrc; j++){ - if( pList->a[j].iCursor==iCursor ) return pList->a[j].pTab; - } - return 0; -} - /* ** Compute the column names for a SELECT statement. ** ** The only guarantee that SQLite makes about column names is that if the @@ -1597,28 +1582,33 @@ ** then the result column name with the table name ** prefix, ex: TABLE.COLUMN. Otherwise use zSpan. */ static void generateColumnNames( Parse *pParse, /* Parser context */ - SrcList *pTabList, /* The FROM clause of the SELECT */ - ExprList *pEList /* Expressions defining the result set */ + Select *pSelect /* Generate column names for this SELECT statement */ ){ Vdbe *v = pParse->pVdbe; int i; Table *pTab; + SrcList *pTabList; + ExprList *pEList; sqlite3 *db = pParse->db; - int fullName; /* TABLE.COLUMN if no AS clause and is a direct table ref */ - int srcName; /* COLUMN or TABLE.COLUMN if no AS clause and is direct */ + int fullName; /* TABLE.COLUMN if no AS clause and is a direct table ref */ + int srcName; /* COLUMN or TABLE.COLUMN if no AS clause and is direct */ #ifndef SQLITE_OMIT_EXPLAIN /* If this is an EXPLAIN, skip this step */ if( pParse->explain ){ return; } #endif if( pParse->colNamesSet || db->mallocFailed ) return; + /* Column names are determined by the left-most term of a compound select */ + while( pSelect->pPrior ) pSelect = pSelect->pPrior; + pTabList = pSelect->pSrc; + pEList = pSelect->pEList; assert( v!=0 ); assert( pTabList!=0 ); pParse->colNamesSet = 1; fullName = (db->flags & SQLITE_FullColNames)!=0; srcName = (db->flags & SQLITE_ShortColNames)!=0 || fullName; @@ -1629,16 +1619,15 @@ assert( p!=0 ); if( pEList->a[i].zName ){ /* An AS clause always takes first priority */ char *zName = pEList->a[i].zName; sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT); - }else if( srcName - && (p->op==TK_COLUMN || p->op==TK_AGG_COLUMN) - && (pTab = tableWithCursor(pTabList, p->iTable))!=0 - ){ + }else if( srcName && p->op==TK_COLUMN ){ char *zCol; int iCol = p->iColumn; + pTab = p->pTab; + assert( pTab!=0 ); if( iCol<0 ) iCol = pTab->iPKey; assert( iCol==-1 || (iCol>=0 && iColnCol) ); if( iCol<0 ){ zCol = "rowid"; }else{ @@ -2466,15 +2455,10 @@ */ assert( unionTab==dest.iSDParm || dest.eDest!=priorOp ); if( dest.eDest!=priorOp ){ int iCont, iBreak, iStart; assert( p->pEList ); - if( dest.eDest==SRT_Output ){ - Select *pFirst = p; - while( pFirst->pPrior ) pFirst = pFirst->pPrior; - generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList); - } iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); sqlite3VdbeAddOp2(v, OP_Rewind, unionTab, iBreak); VdbeCoverage(v); iStart = sqlite3VdbeCurrentAddr(v); @@ -2541,15 +2525,10 @@ /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); - if( dest.eDest==SRT_Output ){ - Select *pFirst = p; - while( pFirst->pPrior ) pFirst = pFirst->pPrior; - generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList); - } iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak); VdbeCoverage(v); r1 = sqlite3GetTempReg(pParse); @@ -3153,18 +3132,10 @@ /* Jump to the this point in order to terminate the query. */ sqlite3VdbeResolveLabel(v, labelEnd); - /* Set the number of output columns - */ - if( pDest->eDest==SRT_Output ){ - Select *pFirst = pPrior; - while( pFirst->pPrior ) pFirst = pFirst->pPrior; - generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList); - } - /* Reassembly the compound query so that it will be freed correctly ** by the calling function */ if( p->pPrior ){ sqlite3SelectDelete(db, p->pPrior); } @@ -3455,11 +3426,10 @@ Select *pParent; /* Current UNION ALL term of the other query */ Select *pSub; /* The inner query or "subquery" */ Select *pSub1; /* Pointer to the rightmost select in sub-query */ SrcList *pSrc; /* The FROM clause of the outer query */ SrcList *pSubSrc; /* The FROM clause of the subquery */ - ExprList *pList; /* The result set of the outer query */ int iParent; /* VDBE cursor number of the pSub result set temp table */ int iNewParent = -1;/* Replacement table for iParent */ int isLeftJoin = 0; /* True if pSub is the right side of a LEFT JOIN */ int i; /* Loop counter */ Expr *pWhere; /* The WHERE clause */ @@ -3780,18 +3750,10 @@ ** \_____________________ outer query ______________________________/ ** ** We look at every expression in the outer query and every place we see ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10". */ - pList = pParent->pEList; - for(i=0; inExpr; i++){ - if( pList->a[i].zName==0 ){ - char *zName = sqlite3DbStrDup(db, pList->a[i].zSpan); - sqlite3Dequote(zName); - pList->a[i].zName = zName; - } - } if( pSub->pOrderBy ){ /* At this point, any non-zero iOrderByCol values indicate that the ** ORDER BY column expression is identical to the iOrderByCol'th ** expression returned by SELECT statement pSub. Since these values ** do not necessarily correspond to columns in SELECT statement pParent, @@ -5216,10 +5178,18 @@ if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p, ("after name resolution:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif + + /* Get a pointer the VDBE under construction, allocating a new VDBE if one + ** does not already exist */ + v = sqlite3GetVdbe(pParse); + if( v==0 ) goto select_end; + if( pDest->eDest==SRT_Output ){ + generateColumnNames(pParse, p); + } /* Try to flatten subqueries in the FROM clause up into the main query */ #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) for(i=0; !p->pPrior && inSrc; i++){ @@ -5252,15 +5222,10 @@ sSort.pOrderBy = p->pOrderBy; } } #endif - /* Get a pointer the VDBE under construction, allocating a new VDBE if one - ** does not already exist */ - v = sqlite3GetVdbe(pParse); - if( v==0 ) goto select_end; - #ifndef SQLITE_OMIT_COMPOUND_SELECT /* Handle compound SELECT statements using the separate multiSelect() ** procedure. */ if( p->pPrior ){ @@ -6056,19 +6021,13 @@ ** successful coding of the SELECT. */ select_end: explainSetInteger(pParse->iSelectId, iRestoreSelectId); - /* Identify column names if results of the SELECT are to be output. - */ - if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){ - generateColumnNames(pParse, pTabList, pEList); - } - sqlite3DbFree(db, sAggInfo.aCol); sqlite3DbFree(db, sAggInfo.aFunc); #if SELECTTRACE_ENABLED SELECTTRACE(1,pParse,p,("end processing\n")); pParse->nSelectIndent--; #endif return rc; } Index: test/colname.test ================================================================== --- test/colname.test +++ test/colname.test @@ -11,11 +11,10 @@ # This file implements regression tests for SQLite library. # # The focus of this file is testing how SQLite generates the names # of columns in a result set. # -# $Id: colname.test,v 1.7 2009/06/02 15:47:38 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Rules (applied in order): @@ -323,7 +322,61 @@ CREATE TABLE "t3893"("x"); INSERT INTO t3893 VALUES(123); SELECT "y"."x" FROM (SELECT "x" FROM "t3893") AS "y"; } } {123} + +# 2017-07-29: Interaction between column naming and query flattening. +# For years now, the query flattener has inserted AS clauses on the +# outer query that were the original SQL text of the column. This caused +# column-name shifts when the query flattener was enhanced, breaking +# legacy applications. See https://sqlite.org/src/info/41c27bc0ff1d3135 +# for details. +# +# To fix this, the column naming logic was moved ahead of the query +# flattener so that column names are assigned before the query flattener +# runs. +# +db close +sqlite3 db :memory: +do_test colname-9.100 { + db eval { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(1,2); + CREATE VIEW v1(x,y) AS SELECT a,b FROM t1; + } + execsql2 {SELECT v1.x, (Y) FROM v1} + # Prior to the fix, this would return: "v1.x 1 (Y) 2" +} {x 1 y 2} +do_test colname-9.110 { + execsql2 {SELECT * FROM v1} +} {x 1 y 2} +do_test colname-9.120 { + db eval { + CREATE VIEW v2(x,y) AS SELECT a,b FROM t1 LIMIT 10; + } + execsql2 {SELECT * FROM v2 WHERE 1} +} {x 1 y 2} +do_test colname-9.130 { + execsql2 {SELECT v2.x, [v2].[y] FROM v2 WHERE 1} +} {x 1 y 2} +do_test colname-9.140 { + execsql2 {SELECT +x, +y FROM v2 WHERE 1} +} {+x 1 +y 2} + +do_test colname-9.200 { + db eval { + CREATE TABLE t2(c,d); + INSERT INTO t2 VALUES(3,4); + CREATE VIEW v3 AS SELECT c AS a, d AS b FROM t2; + } + execsql2 {SELECT t1.a, v3.a AS n FROM t1 LEFT JOIN v3} +} {a 1 n 3} +do_test colname-9.211 { + execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3} +} {n 1 a 3} +do_test colname-9.210 { + execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3} +} {a 1 n 3} + finish_test