The attached sqlite 3.5.3 patch addresses several different compound
query column naming and resolving issues in ORDER BY and the SELECT
expression list mentioned in this ticket:
http://www.sqlite.org/cvstrac/tktview?tn=2822
(The exception being it does not support expressions in the ORDER BY
clause of compound SELECT statements. That functionality remains the
same as in version 3.5.3.)
I believe it makes compound query behavior more compatible with other
popular databases. It is mostly backwards compatible with the previous
syntax and only 2 tests performed by "make test" had to be altered.
It seems to work, although it's quite possible that I missed something.
At least this patch serves as a basis of syntax discussion.
If you want to test it, just put sqlite-3.5.3.tar.gz and the patch
file in the same directory and run these commands:
tar xzvf sqlite-3.5.3.tar.gz
cd sqlite-3.5.3
patch -p0 < ../union-alias-20071207.patch.txt
./configure
make
./sqlite3
Please report any issues to the mailing list.
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Index: src/select.c
===================================================================
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.363
diff -u -3 -p -r1.363 select.c
--- src/select.c 23 Nov 2007 13:42:52 -0000 1.363
+++ src/select.c 8 Dec 2007 03:49:30 -0000
@@ -1118,8 +1118,19 @@ Table *sqlite3ResultSetOfSelect(Parse *p
/* For columns of the from A.B use B as the name */
zName = sqlite3MPrintf(db, "%T", &pR->token);
}else if( p->span.z && p->span.z[0] ){
- /* Use the original text of the column expression as its name */
- zName = sqlite3MPrintf(db, "%T", &p->span);
+ Token t = p->span;
+ if( p->op==TK_COLUMN ){
+ /* Get rid of all dotted prefixes, if any */
+ int n;
+ for(n = t.n-1; n>=0; n--){
+ if( t.z[n]=='.' ){
+ t.n -= n+1;
+ t.z += n+1;
+ break;
+ }
+ }
+ }
+ zName = sqlite3MPrintf(db, "%T", &t);
}else{
/* If all else fails, make up a name */
zName = sqlite3MPrintf(db, "column%d", i+1);
@@ -1469,12 +1480,23 @@ static int matchOrderbyToColumn(
if( !mustComplete ) continue;
iCol--;
}
- if( iCol<0 && (zLabel = sqlite3NameFromToken(db, &pE->token))!=0 ){
+ if( iCol<0 && (
+ (zLabel = sqlite3NameFromToken(db, &pE->token))!=0
+ || ((pE->op==TK_STRING || pE->op==TK_DOT || pE->op==TK_ID)
+ && (zLabel = sqlite3NameFromToken(db, &pE->span))!=0) )){
for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){
char *zName;
int isMatch;
if( pItem->zName ){
zName = sqlite3DbStrDup(db, pItem->zName);
+ }else if( pItem->pExpr->op==TK_DOT && pE->op==TK_ID ){
+ Expr *pRight = pItem->pExpr->pRight;
+ if( pRight->op==TK_DOT ){
+ pRight = pRight->pRight;
+ }
+ zName = sqlite3NameFromToken(db, &pRight->token);
+ }else if( pItem->pExpr->op==TK_DOT && pE->op==TK_DOT ){
+ zName = sqlite3NameFromToken(db, &pItem->pExpr->span);
}else{
zName = sqlite3NameFromToken(db, &pItem->pExpr->token);
}
@@ -1829,7 +1851,7 @@ static int multiSelect(
if( eDest==SRT_Callback ){
Select *pFirst = p;
while( pFirst->pPrior ) pFirst = pFirst->pPrior;
- generateColumnNames(pParse, 0, pFirst->pEList);
+ generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList);
}
iBreak = sqlite3VdbeMakeLabel(v);
iCont = sqlite3VdbeMakeLabel(v);
@@ -1907,7 +1929,7 @@ static int multiSelect(
if( eDest==SRT_Callback ){
Select *pFirst = p;
while( pFirst->pPrior ) pFirst = pFirst->pPrior;
- generateColumnNames(pParse, 0, pFirst->pEList);
+ generateColumnNames(pParse, pFirst->pSrc, pFirst->pEList);
}
iBreak = sqlite3VdbeMakeLabel(v);
iCont = sqlite3VdbeMakeLabel(v);
Index: test/select1.test
===================================================================
RCS file: /sqlite/sqlite/test/select1.test,v
retrieving revision 1.54
diff -u -3 -p -r1.54 select1.test
--- test/select1.test 23 Jul 2007 22:51:15 -0000 1.54
+++ test/select1.test 8 Dec 2007 03:49:31 -0000
@@ -559,6 +559,66 @@ do_test select1-6.23 {
}
} {b d}
+# Ticket #2822
+do_test select1-6.30 {
+ execsql {
+ CREATE TABLE x1(a, b, c);
+ INSERT INTO x1 VALUES(6, 4, 2);
+ CREATE TABLE x2(a, b, c);
+ INSERT INTO x2 VALUES(7, 1, 3);
+ CREATE VIEW v1 AS
+ SELECT x1.b, x1.a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b, a;
+ CREATE VIEW v2 AS
+ SELECT b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b, a;
+ SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b, a;
+ }
+} {4 6 7 1}
+do_test select1-6.31 {
+ execsql {
+ SELECT b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
+ }
+} {4 6 7 1}
+do_test select1-6.32 {
+ execsql {
+ SELECT b, a FROM v1 ORDER BY b;
+ }
+} {4 6 7 1}
+do_test select1-6.33 {
+ execsql {
+ SELECT b, a FROM v2 ORDER BY b;
+ }
+} {4 6 7 1}
+do_test select1-6.34 {
+ execsql {
+ SELECT v2.b, a FROM v2 ORDER BY b;
+ }
+} {4 6 7 1}
+do_test select1-6.35 {
+ execsql {
+ SELECT v1.b, a FROM v1 ORDER BY b;
+ }
+} {4 6 7 1}
+do_test select1-6.36 {
+ execsql {
+ SELECT x1.b, x1.a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b;
+ }
+} {4 6 7 1}
+do_test select1-6.37 {
+ execsql {
+ SELECT v2.b, a FROM v2 ORDER BY v2.b;
+ }
+} {4 6 7 1}
+do_test select1-6.38 {
+ execsql {
+ SELECT v1.b, a FROM v1 ORDER BY v1.b;
+ }
+} {4 6 7 1}
+do_test select1-6.39 {
+ execsql {
+ SELECT x1.b, x1.a FROM x1 UNION SELECT a, b FROM x2 ORDER BY x1.b;
+ }
+} {4 6 7 1}
+
} ;#ifcapable compound
do_test select1-7.1 {
Index: test/subquery.test
===================================================================
RCS file: /sqlite/sqlite/test/subquery.test,v
retrieving revision 1.15
diff -u -3 -p -r1.15 subquery.test
--- test/subquery.test 18 Sep 2007 16:53:53 -0000 1.15
+++ test/subquery.test 8 Dec 2007 03:49:31 -0000
@@ -129,7 +129,7 @@ do_test subquery-1.10.4 {
SELECT "a.period", vsum
FROM (SELECT
- a.period,
+ a.period as "a.period",
(select sum(val) from t5 where period between a.period and '2002-4') vsum
FROM t5 a where a.period between '2002-1' and '2002-4')
WHERE vsum < 45 ;
@@ -137,7 +137,7 @@ do_test subquery-1.10.4 {
} {2002-2 30 2002-3 25 2002-4 15}
do_test subquery-1.10.5 {
execsql {
- SELECT "a.period", vsum from
+ SELECT period, vsum from
(select a.period,
(select sum(val) from t5 where period between a.period and '2002-4') vsum
FROM t5 a where a.period between '2002-1' and '2002-4')
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
