Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -795,28 +795,46 @@ ** this routine return an integer between 1 and N where N is the number of ** elements in pEList, corresponding to the matching entry. If there is ** no match, or if pE is not a simple identifier, then this routine ** return 0. ** +** The sameNameOnly flag is set if pE comes from a GROUP BY clause. The +** arguments of a GROUP BY clause are not supposed to be able to match +** against AS names in SQL. But early versions of SQLite allowed this +** behavior by mistake. To provide backwards compatibility, a GROUP BY +** term will match as AS alias only if the corresponding result set expression +** refers to a table column by the same name. In other words: +** +** SELECT t1.x AS x, t2.x AS y FROM t1,t2 GROUP BY x; -- match +** SELECT t1.y AS x, t2.y AS y FROM t1,t2 GROUP BY x; -- no match +** ** pEList has been resolved. pE has not. */ static int resolveAsName( Parse *pParse, /* Parsing context for error messages */ ExprList *pEList, /* List of expressions to scan */ - Expr *pE /* Expression we are trying to match */ + Expr *pE, /* Expression we are trying to match */ + int sameNameOnly /* Only resolve if the alias matches the column name */ ){ int i; /* Loop counter */ UNUSED_PARAMETER(pParse); if( pE->op==TK_ID ){ char *zCol = pE->u.zToken; for(i=0; inExpr; i++){ char *zAs = pEList->a[i].zName; - if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){ - return i+1; + if( zAs==0 ) continue; + if( sqlite3StrICmp(zAs, zCol)!=0 ) continue; + if( sameNameOnly ){ + Expr *p = pEList->a[i].pExpr; + Table *pTab; + if( p->op!=TK_COLUMN ) continue; + pTab = p->pTab; + if( sqlite3StrICmp(pTab->aCol[p->iColumn].zName, zAs)!=0 ) continue; } + return i+1; } } return 0; } @@ -952,11 +970,11 @@ if( iCol<=0 || iCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr); return 1; } }else{ - iCol = resolveAsName(pParse, pEList, pE); + iCol = resolveAsName(pParse, pEList, pE, 0); if( iCol==0 ){ pDup = sqlite3ExprDup(db, pE, 0); if( !db->mallocFailed ){ assert(pDup); iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup); @@ -1073,20 +1091,18 @@ nResult = pSelect->pEList->nExpr; pParse = pNC->pParse; for(i=0, pItem=pOrderBy->a; inExpr; i++, pItem++){ Expr *pE = pItem->pExpr; Expr *pE2 = sqlite3ExprSkipCollate(pE); - if( zType[0]!='G' ){ - iCol = resolveAsName(pParse, pSelect->pEList, pE2); - if( iCol>0 ){ - /* If an AS-name match is found, mark this ORDER BY column as being - ** a copy of the iCol-th result-set column. The subsequent call to - ** sqlite3ResolveOrderGroupBy() will convert the expression to a - ** copy of the iCol-th result-set expression. */ - pItem->u.x.iOrderByCol = (u16)iCol; - continue; - } + iCol = resolveAsName(pParse, pSelect->pEList, pE2, zType[0]=='G'); + if( iCol>0 ){ + /* If an AS-name match is found, mark this ORDER BY column as being + ** a copy of the iCol-th result-set column. The subsequent call to + ** sqlite3ResolveOrderGroupBy() will convert the expression to a + ** copy of the iCol-th result-set expression. */ + pItem->u.x.iOrderByCol = (u16)iCol; + continue; } if( sqlite3ExprIsInteger(pE2, &iCol) ){ /* The ORDER BY term is an integer constant. Again, set the column ** number so that sqlite3ResolveOrderGroupBy() will convert the ** order-by term to a copy of the result-set expression */ Index: test/resolver01.test ================================================================== --- test/resolver01.test +++ test/resolver01.test @@ -155,14 +155,14 @@ # # This first case case has been wrong in SQLite for time out of mind. # For SQLite version 3.7.17 the answer was two rows, which is wrong. # do_execsql_test resolver01-5.1 { - CREATE TABLE t5(m CHAR(2)); - INSERT INTO t5 VALUES('ax'); - INSERT INTO t5 VALUES('bx'); - INSERT INTO t5 VALUES('cy'); + CREATE TABLE t5(m CHAR(2), n); + INSERT INTO t5 VALUES('ax',1); + INSERT INTO t5 VALUES('bx',2); + INSERT INTO t5 VALUES('cy',2); SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2; } {1 x 1 x 1 y} # This case is unambiguous and has always been correct. # @@ -179,11 +179,11 @@ do_execsql_test resolver01-5.4 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY substr(m,2,1) ORDER BY 1, 2; } {1 y 2 x} -# These test case weere provided in the 2013-08-14 email from Rob Golsteijn +# These test cases were provided in the 2013-08-14 email from Rob Golsteijn # that originally reported the problem of ticket [1c69be2dafc28]. # do_execsql_test resolver01-6.1 { CREATE TABLE t61(name); SELECT min(name) FROM t61 GROUP BY lower(name); @@ -199,10 +199,28 @@ NULLIF(name,'abc') AS name FROM t63 GROUP BY lower(name); } {1 {} 1 {}} - - - +# (2014-06-10) The fix to GROUP BY name binding has resulted in errors +# in some legacy Android applications. To work around this, GROUP BY terms +# can be bound to AS aliases as long as the corresponding expression is +# a column by the same name as the alias. Verify that this exception +# case works. Continuation of ticket [1c69be2dafc28]. +# +do_execsql_test resolver01-7.1 { + CREATE TABLE t7(m, x); + INSERT INTO t7 VALUES('bx',1),('ax',2),('dx',3); + SELECT count(*), t5.m AS m FROM t5, t7 GROUP BY m ORDER BY 2; +} {3 ax 3 bx 3 cy} +do_execsql_test resolver01-7.2 { + SELECT count(*), t5.m AS n FROM t5, t7 GROUP BY n ORDER BY 2; +} {3 ax 6 cy} +do_test resolver01-7.3 { + catchsql { + CREATE TABLE t7b(m, n); + INSERT INTO t7b SELECT * FROM t7; + SELECT count(*), t5.m AS n FROM t5, t7b GROUP BY n ORDER BY 2; + } +} {1 {ambiguous column name: n}} finish_test