From a3a5bd9b62324bcfafaf2e6d4d98fbc57e1a1d94 Mon Sep 17 00:00:00 2001 From: drh Date: Sat, 13 Apr 2013 19:59:58 +0000 Subject: [PATCH] Only consider AS names from the result set as candidates for resolving identifiers in the WHERE clause if there are no other matches. In the ORDER BY clause, AS names take priority over any column names. Candidate fix for ticket [2500cdb9be]. FossilOrigin-Name: ad53924dcadffb95c6497c46c228c67e8f5370e4 --- manifest | 22 +++++++++++++--------- manifest.uuid | 2 +- src/resolve.c | 18 +++++++++++------- src/sqliteInt.h | 2 ++ test/resolver01.test | 39 +++++++++++++++++++++++++++++++++++++++ test/tkt2822.test | 5 ++++- 6 files changed, 70 insertions(+), 18 deletions(-) create mode 100644 test/resolver01.test diff --git a/manifest b/manifest index c4d5a473e1..3a4b72859e 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sa\stest\scase\sfor\sdetaching\sa\sdatabase\sreferenced\sby\san\sfts4aux\stable\screated\sin\sthe\stemp\sschema. -D 2013-04-12T16:53:19.544 +C Only\sconsider\sAS\snames\sfrom\sthe\sresult\sset\sas\scandidates\sfor\sresolving\nidentifiers\sin\sthe\sWHERE\sclause\sif\sthere\sare\sno\sother\smatches.\s\sIn\sthe\nORDER\sBY\sclause,\sAS\snames\stake\spriority\sover\sany\scolumn\snames.\nCandidate\sfix\sfor\sticket\s[2500cdb9be]. +D 2013-04-13T19:59:58.676 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 3dd3fcb87b70c78d99b2c8a03e44ec86d6ca9ce2 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -178,14 +178,14 @@ F src/pragma.c 216d6c24520f7b2e267a2356e5d3269d28690f49 F src/prepare.c 743e484233c51109666d402f470523553b41797c F src/printf.c 4a9f882f1c1787a8b494a2987765acf9d97ac21f F src/random.c cd4a67b3953b88019f8cd4ccd81394a8ddfaba50 -F src/resolve.c 9079da7d59aed2bb14ec8315bc7f720dd85b5b65 +F src/resolve.c 10a1b332e3eb36e5d561085e18c58a8578cd7d73 F src/rowset.c 64655f1a627c9c212d9ab497899e7424a34222e0 F src/select.c 01540bcd3df3c8f1187158e77986028b1c667258 F src/shell.c 319b7791cee6c763b60fde1b590bfaf62613cf37 F src/sqlite.h.in f606903594d314d1b63e677affb1a03e3c9a49c7 F src/sqlite3.rc fea433eb0a59f4c9393c8e6d76a6e2596b1fe0c0 F src/sqlite3ext.h 7183ab832e23db0f934494f16928da127a571d75 -F src/sqliteInt.h 1c517a89cfdba28084093607ec79af70359c7f9b +F src/sqliteInt.h 7580edf7e7e13af8dff6152666e4ea32535abf8c F src/sqliteLimit.h 3ea5c07b370b8033c30f66515ccf4e875c252429 F src/status.c bedc37ec1a6bb9399944024d63f4c769971955a9 F src/table.c 2cd62736f845d82200acfa1287e33feb3c15d62e @@ -685,6 +685,7 @@ F test/regexp1.test 5cbb6e7122ca51260d71079cf9245b63b8f64e1a F test/reindex.test 44edd3966b474468b823d481eafef0c305022254 F test/releasetest.mk 2eced2f9ae701fd0a29e714a241760503ccba25a F test/releasetest.tcl 06d289d8255794073a58d2850742f627924545ce +F test/resolver01.test d1b487fc567bdb70b5dd09ccaaa877ddc61a233e F test/rollback.test a1b4784b864331eae8b2a98c189efa2a8b11ff07 F test/rowhash.test 0bc1d31415e4575d10cacf31e1a66b5cc0f8be81 F test/rowid.test f777404492adb0e00868fd706a3721328fd3af48 @@ -854,7 +855,7 @@ F test/tkt2686.test 6ee01c9b9e9c48f6d3a1fdd553b1cc4258f903d6 F test/tkt2767.test 569000d842678f9cf2db7e0d1b27cbc9011381b0 F test/tkt2817.test f31839e01f4243cff7399ef654d3af3558cb8d8d F test/tkt2820.test 39940276b3436d125deb7d8ebeee053e4cf13213 -F test/tkt2822.test f391776423a7c0d0949edfce375708bfb0f3141e +F test/tkt2822.test c3589494fba643e039bcf0bfde7554ff6028406d F test/tkt2832.test a9b0b74a02dca166a04d9e37739c414b10929caa F test/tkt2854.test e432965db29e27e16f539b2ba7f502eb2ccc49af F test/tkt2920.test a8737380e4ae6424e00c0273dc12775704efbebf @@ -1050,7 +1051,10 @@ F tool/vdbe-compress.tcl f12c884766bd14277f4fcedcae07078011717381 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh fbc018d67fd7395f440c28f33ef0f94420226381 F tool/win/sqlite.vsix 97894c2790eda7b5bce3cc79cb2a8ec2fde9b3ac -P 546a187f1361dad340ed8f6c28dd89e6c19f9c33 -R 05383c39b4618f3bdf18eec38f2fc283 -U dan -Z db66ae2fc0950c0cd2f83a29d6b2c3f8 +P 6d6f0592abe237256dbdf30ab0ba0bb0c365dd95 +R b0858137a1ee08b4782056449cba9377 +T *branch * tkt-2500cdb9be +T *sym-tkt-2500cdb9be * +T -sym-trunk * +U drh +Z 489735ec39077e65a3d08cafc2217189 diff --git a/manifest.uuid b/manifest.uuid index d1d8020d4c..ac4feed793 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -6d6f0592abe237256dbdf30ab0ba0bb0c365dd95 \ No newline at end of file +ad53924dcadffb95c6497c46c228c67e8f5370e4 \ No newline at end of file diff --git a/src/resolve.c b/src/resolve.c index 0380138678..9b350caf80 100644 --- a/src/resolve.c +++ b/src/resolve.c @@ -388,7 +388,10 @@ static int lookupName( ** Note that the expression in the result set should have already been ** resolved by the time the WHERE clause is resolved. */ - if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){ + if( (pEList = pNC->pEList)!=0 + && zTab==0 + && ((pNC->ncFlags & NC_AsMaybe)==0 || cnt==0) + ){ for(j=0; jnExpr; j++){ char *zAs = pEList->a[j].zName; if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){ @@ -479,7 +482,9 @@ static int lookupName( lookupname_end: if( cnt==1 ){ assert( pNC!=0 ); - sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList); + if( pExpr->op!=TK_AS ){ + sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList); + } /* Increment the nRef value on all name contexts from TopNC up to ** the point where the name matched. */ for(;;){ @@ -1154,11 +1159,10 @@ static int resolveSelectStep(Walker *pWalker, Select *p){ ** re-evaluated for each reference to it. */ sNC.pEList = p->pEList; - if( sqlite3ResolveExprNames(&sNC, p->pWhere) || - sqlite3ResolveExprNames(&sNC, p->pHaving) - ){ - return WRC_Abort; - } + if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort; + sNC.ncFlags |= NC_AsMaybe; + if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort; + sNC.ncFlags &= ~NC_AsMaybe; /* The ORDER BY and GROUP BY clauses may not refer to terms in ** outer queries diff --git a/src/sqliteInt.h b/src/sqliteInt.h index a528e37e44..264db39d14 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -2079,6 +2079,8 @@ struct NameContext { #define NC_HasAgg 0x02 /* One or more aggregate functions seen */ #define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */ #define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */ +#define NC_AsMaybe 0x10 /* Resolve to AS terms of the result set only + ** if no other resolution is available */ /* ** An instance of the following structure contains all information diff --git a/test/resolver01.test b/test/resolver01.test new file mode 100644 index 0000000000..3ca6acec47 --- /dev/null +++ b/test/resolver01.test @@ -0,0 +1,39 @@ +# 2013-04-13 +# +# 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 tests features of the name resolver (the component that +# figures out what identifiers in the SQL statement refer to) that +# were fixed by ticket [2500cdb9be] +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_test resolver01-1.1 { + catchsql { + CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22); + CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44); + SELECT 1 AS y FROM t1, t2 ORDER BY y; + } +} {0 1} +do_test resolver01-1.2 { + catchsql { + SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase; + } +} {0 2} +do_test resolver01-1.3 { + catchsql { + SELECT 3 AS y FROM t1, t2 ORDER BY +y; + } +} {0 3} + + +finish_test diff --git a/test/tkt2822.test b/test/tkt2822.test index d0b16338c6..d3512d3038 100644 --- a/test/tkt2822.test +++ b/test/tkt2822.test @@ -208,12 +208,15 @@ do_test tkt2822-5.4 { # In "ORDER BY +b" the term is now an expression rather than # a label. It therefore matches by rule (3) instead of rule (2). +# +# 2013-04-13: This is busted. Changed to conform to PostgreSQL and +# MySQL and Oracle behavior. # do_test tkt2822-5.5 { execsql { SELECT a AS b FROM t3 ORDER BY +b; } -} {9 1} +} {1 9} # Tests for rule 2 in compound queries # -- 2.47.2