Avoid reevaluating WHERE and ORDER BY expressions that alias terms in the
result set. Ticket #3343. Note that aliased GROUP BY expressions are still
evaluated twice. (CVS 5637)
FossilOrigin-Name: ab0292caa5887cc1bdc0e8c9d3f3502b83975440
diff --git a/manifest b/manifest
index d65fd38..cda16b2 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\sioerr5.test\sso\sthat\sit\sworks\swith\sthe\snew\spcache\smodule.\s(CVS\s5636)
-D 2008-08-28T18:35:34
+C Avoid\sreevaluating\sWHERE\sand\sORDER\sBY\sexpressions\sthat\salias\sterms\sin\sthe\nresult\sset.\s\sTicket\s#3343.\s\sNote\sthat\saliased\sGROUP\sBY\sexpressions\sare\sstill\nevaluated\stwice.\s(CVS\s5637)
+D 2008-08-29T02:14:03
F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0
F Makefile.in 689e14735f862a5553bceef206d8c13e29504e44
F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654
@@ -103,8 +103,8 @@
F src/callback.c 27e55dfafc66e97dd8e7343a1d7bb295f3afd736
F src/complete.c cb14e06dbe79dee031031f0d9e686ff306afe07c
F src/date.c 2373f20a91bec70e20e0e715520c8010d26ebb52
-F src/delete.c d979a250a47b9f52effa2998070810239e38db8b
-F src/expr.c 622c8d8e3cc8246a33aba30af5c166e4a3898c4b
+F src/delete.c 434dd6f9d19475b35f2f57041d4bbba9f64da5ac
+F src/expr.c 6413795aa13ceb05994e6b2b453a77df3b892e7b
F src/fault.c 3638519d1e0b82bccfafcb9f5ff491918b28f8e1
F src/func.c f2e0b55f13e678a5c5ff94f0f225ed0a995acba7
F src/global.c 239d03b6dbae6c985deb2ffb54ab919debb9f8d7
@@ -143,12 +143,12 @@
F src/prepare.c c197041e0c4770672cda75e6bfe10242f885e510
F src/printf.c 785f87120589c1db672e37c6eb1087c456e6f84d
F src/random.c 5c754319d38abdd6acd74601ee0105504adc508a
-F src/resolve.c 74725e61c9eefb597a203631d921efd9005b7a88
+F src/resolve.c a6abf83125bce0c80ba04acc27c3565155ad305c
F src/select.c 8187927315ee592a8ee94d753b8a1a3625c33523
F src/shell.c d83b578a8ccdd3e0e7fef4388a0887ce9f810967
F src/sqlite.h.in c0e84a2d6e9f3263599174ff7261ba6daf730b4f
F src/sqlite3ext.h 1e3887c9bd3ae66cb599e922824b04cd0d0f2c3e
-F src/sqliteInt.h c66e9c22a9c7fcf32db52a14fcddaa4d87bf9559
+F src/sqliteInt.h 90839e736d81d6c8b9d5e5d67764664b3605ceaf
F src/sqliteLimit.h f435e728c6b620ef7312814d660a81f9356eb5c8
F src/status.c 8caa772cd9310bc297280f7cf0ede4d69ed5b801
F src/table.c 22744786199c9195720c15a7a42cb97b2e2728d8
@@ -179,7 +179,7 @@
F src/test_server.c f0a403b5f699c09bd2b1236b6f69830fd6221f6b
F src/test_tclvar.c 9e42fa59d3d2f064b7ab8628e7ab2dc8a9fe93d4
F src/test_thread.c d74fc445e0dba0e00806117eb449b307c0b146bf
-F src/tokenize.c d16ca0e9944161c76d2e4c11dc379ec88189b93b
+F src/tokenize.c 76fe4cb8a606c24c76843ee2170cf84085f40817
F src/trigger.c 649940b5bf5838a33721fb72372e7c9d1faf56a9
F src/update.c f2cf6f00d542956bd49ba4b9815c2900d9225bf2
F src/utf.c c63e6f69082f85c19ab88d62dedaf91d71ac1a50
@@ -198,6 +198,7 @@
F src/where.c 72a4ac6890e9571375458021688dba6c45689082
F tclinstaller.tcl 4356d9d94d2b5ed5e68f9f0c80c4df3048dd7617
F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2
+F test/alias.test c321c114a8a31a33e3cbda910fa39949f5d9dcb2
F test/all.test 89e09ed0074083ac6f208dc3243429e8f89efb69
F test/alter.test 6353aae6839e486c9b7d8f73b1f4a1e98e57332c
F test/alter2.test dd55146e812622c8fc51fd2216bcd8dca8880752
@@ -625,7 +626,7 @@
F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff
F tool/speedtest8.c 1dbced29de5f59ba2ebf877edcadf171540374d1
F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e
-P 44193b92770062271711570d8532fa5af5f5da54
-R b653a7b6746fff19fe5c3b4b1ca78010
-U danielk1977
-Z e1c85e2c49e0ca001d86550ca68a5398
+P 83e6a75e7d70b4b01f0892924d7a8a49d5ef6bf2
+R f7b963f31369c623d2a04efc9a423904
+U drh
+Z 9a20437031aab1a22d3ddda0e55ea04f
diff --git a/manifest.uuid b/manifest.uuid
index ab3f51f..e6f959c 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-83e6a75e7d70b4b01f0892924d7a8a49d5ef6bf2
\ No newline at end of file
+ab0292caa5887cc1bdc0e8c9d3f3502b83975440
\ No newline at end of file
diff --git a/src/delete.c b/src/delete.c
index 72c2ec1..4219b88 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -12,7 +12,7 @@
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
-** $Id: delete.c,v 1.173 2008/08/22 12:30:52 drh Exp $
+** $Id: delete.c,v 1.174 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
@@ -104,8 +104,8 @@
Token viewName;
pWhere = sqlite3ExprDup(db, pWhere);
- viewName.z = pView->zName;
- viewName.n = strlen(viewName.z);
+ viewName.z = (u8*)pView->zName;
+ viewName.n = (unsigned int)strlen((const char*)viewName.z);
pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName, pDup, 0,0);
pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
}
diff --git a/src/expr.c b/src/expr.c
index becc82a..8f2d13f 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
-** $Id: expr.c,v 1.391 2008/08/22 17:34:45 drh Exp $
+** $Id: expr.c,v 1.392 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -701,6 +701,7 @@
pItem->sortOrder = pOldItem->sortOrder;
pItem->done = 0;
pItem->iCol = pOldItem->iCol;
+ pItem->iAlias = pOldItem->iAlias;
}
return pNew;
}
@@ -829,6 +830,7 @@
memset(pItem, 0, sizeof(*pItem));
pItem->zName = sqlite3NameFromToken(db, pName);
pItem->pExpr = pExpr;
+ pItem->iAlias = 0;
}
return pList;
@@ -1665,11 +1667,46 @@
}
/*
+** Generate code to store the value of the iAlias-th alias in register
+** target. The first time this is called, pExpr is evaluated to compute
+** the value of the alias. The value is stored in an auxiliary register
+** and the number of that register is returned. On subsequent calls,
+** the register number is returned without generating any code.
+**
+** Note that in order for this to work, code must be generated in the
+** same order that it is executed.
+**
+** Aliases are numbered starting with 1. So iAlias is in the range
+** of 1 to pParse->nAlias inclusive.
+**
+** pParse->aAlias[iAlias-1] records the register number where the value
+** of the iAlias-th alias is stored. If zero, that means that the
+** alias has not yet been computed.
+*/
+static int codeAlias(Parse *pParse, int iAlias, Expr *pExpr){
+ sqlite3 *db = pParse->db;
+ int iReg;
+ if( pParse->aAlias==0 ){
+ pParse->aAlias = sqlite3DbMallocZero(db,
+ sizeof(pParse->aAlias[0])*pParse->nAlias );
+ if( db->mallocFailed ) return 0;
+ }
+ assert( iAlias>0 && iAlias<=pParse->nAlias );
+ iReg = pParse->aAlias[iAlias-1];
+ if( iReg==0 ){
+ iReg = ++pParse->nMem;
+ sqlite3ExprCode(pParse, pExpr, iReg);
+ pParse->aAlias[iAlias-1] = iReg;
+ }
+ return iReg;
+}
+
+/*
** Generate code into the current Vdbe to evaluate the given
** expression. Attempt to store the results in register "target".
** Return the register where results are stored.
**
-** With this routine, there is no guaranteed that results will
+** With this routine, there is no guarantee that results will
** be stored in target. The result might be stored in some other
** register if it is convenient to do so. The calling function
** must check the return code and move the results to the desired
@@ -1682,8 +1719,10 @@
int regFree1 = 0; /* If non-zero free this temporary register */
int regFree2 = 0; /* If non-zero free this temporary register */
int r1, r2, r3, r4; /* Various register numbers */
+ sqlite3 *db;
- assert( v!=0 || pParse->db->mallocFailed );
+ db = pParse->db;
+ assert( v!=0 || db->mallocFailed );
assert( target>0 && target<=pParse->nMem );
if( v==0 ) return 0;
@@ -1729,7 +1768,7 @@
break;
}
case TK_STRING: {
- sqlite3DequoteExpr(pParse->db, pExpr);
+ sqlite3DequoteExpr(db, pExpr);
sqlite3VdbeAddOp4(v,OP_String8, 0, target, 0,
(char*)pExpr->token.z, pExpr->token.n);
break;
@@ -1765,6 +1804,10 @@
inReg = pExpr->iTable;
break;
}
+ case TK_AS: {
+ inReg = codeAlias(pParse, pExpr->iTable, pExpr->pLeft);
+ break;
+ }
#ifndef SQLITE_OMIT_CAST
case TK_CAST: {
/* Expressions of the form: CAST(pLeft AS token) */
@@ -1921,7 +1964,6 @@
const char *zId;
int constMask = 0;
int i;
- sqlite3 *db = pParse->db;
u8 enc = ENC(db);
CollSeq *pColl = 0;
@@ -1929,7 +1971,7 @@
testcase( op==TK_FUNCTION );
zId = (char*)pExpr->token.z;
nId = pExpr->token.n;
- pDef = sqlite3FindFunction(pParse->db, zId, nId, nExpr, enc, 0);
+ pDef = sqlite3FindFunction(db, zId, nId, nExpr, enc, 0);
assert( pDef!=0 );
if( pList ){
nExpr = pList->nExpr;
@@ -1966,7 +2008,7 @@
}
}
if( pDef->needCollSeq ){
- if( !pColl ) pColl = pParse->db->pDfltColl;
+ if( !pColl ) pColl = db->pDfltColl;
sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
}
sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
@@ -2206,7 +2248,7 @@
assert( pExpr->iColumn==OE_Rollback ||
pExpr->iColumn == OE_Abort ||
pExpr->iColumn == OE_Fail );
- sqlite3DequoteExpr(pParse->db, pExpr);
+ sqlite3DequoteExpr(db, pExpr);
sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, pExpr->iColumn, 0,
(char*)pExpr->token.z, pExpr->token.n);
} else {
@@ -2422,7 +2464,13 @@
assert( target>0 );
n = pList->nExpr;
for(pItem=pList->a, i=0; i<n; i++, pItem++){
- sqlite3ExprCode(pParse, pItem->pExpr, target+i);
+ if( pItem->iAlias ){
+ int iReg = codeAlias(pParse, pItem->iAlias, pItem->pExpr);
+ Vdbe *v = sqlite3GetVdbe(pParse);
+ sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target+i);
+ }else{
+ sqlite3ExprCode(pParse, pItem->pExpr, target+i);
+ }
if( doHardCopy ) sqlite3ExprHardCopy(pParse, target, n);
}
return n;
@@ -2811,6 +2859,8 @@
switch( pExpr->op ){
case TK_AGG_COLUMN:
case TK_COLUMN: {
+ testcase( pExpr->op==TK_AGG_COLUMN );
+ testcase( pExpr->op==TK_COLUMN );
/* Check to see if the column is in one of the tables in the FROM
** clause of the aggregate query */
if( pSrcList ){
diff --git a/src/resolve.c b/src/resolve.c
index 439fc74..dfbc3b1 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -14,13 +14,76 @@
** resolve all identifiers by associating them with a particular
** table and column.
**
-** $Id: resolve.c,v 1.4 2008/08/25 17:23:29 drh Exp $
+** $Id: resolve.c,v 1.5 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>
#include <string.h>
/*
+** Turn the pExpr expression into an alias for the iCol-th column of the
+** result set in pEList.
+**
+** If the result set column is a simple column reference, then this routine
+** makes an exact copy. But for any other kind of expression, this
+** routine make a copy of the result set column as the argument to the
+** TK_AS operator. The TK_AS operator causes the expression to be
+** evaluated just once and then reused for each alias.
+**
+** The reason for suppressing the TK_AS term when the expression is a simple
+** column reference is so that the column reference will be recognized as
+** usable by indices within the WHERE clause processing logic.
+**
+** Hack: The TK_AS operator is inhibited if zType[0]=='G'. This means
+** that in a GROUP BY clause, the expression is evaluated twice. Hence:
+**
+** SELECT random()%5 AS x, count(*) FROM tab GROUP BY x
+**
+** Is equivalent to:
+**
+** SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5
+**
+** The result of random()%5 in the GROUP BY clause is probably different
+** from the result in the result-set. We might fix this someday. Or
+** then again, we might not...
+*/
+static void resolveAlias(
+ Parse *pParse, /* Parsing context */
+ ExprList *pEList, /* A result set */
+ int iCol, /* A column in the result set. 0..pEList->nExpr-1 */
+ Expr *pExpr, /* Transform this into an alias to the result set */
+ const char *zType /* "GROUP" or "ORDER" or "" */
+){
+ Expr *pOrig; /* The iCol-th column of the result set */
+ Expr *pDup; /* Copy of pOrig */
+ sqlite3 *db; /* The database connection */
+
+ assert( iCol>=0 && iCol<pEList->nExpr );
+ pOrig = pEList->a[iCol].pExpr;
+ assert( pOrig!=0 );
+ assert( pOrig->flags & EP_Resolved );
+ db = pParse->db;
+ pDup = sqlite3ExprDup(db, pOrig);
+ if( pDup==0 ) return;
+ if( pDup->op!=TK_COLUMN && zType[0]!='G' ){
+ pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0);
+ if( pDup==0 ) return;
+ if( pEList->a[iCol].iAlias==0 ){
+ pEList->a[iCol].iAlias = ++pParse->nAlias;
+ }
+ pDup->iTable = pEList->a[iCol].iAlias;
+ }
+ if( pExpr->flags & EP_ExpCollate ){
+ pDup->pColl = pExpr->pColl;
+ pDup->flags |= EP_ExpCollate;
+ }
+ if( pExpr->span.dyn ) sqlite3DbFree(db, (char*)pExpr->span.z);
+ if( pExpr->token.dyn ) sqlite3DbFree(db, (char*)pExpr->token.z);
+ memcpy(pExpr, pDup, sizeof(*pExpr));
+ sqlite3DbFree(db, pDup);
+}
+
+/*
** 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
@@ -218,7 +281,7 @@
for(j=0; j<pEList->nExpr; j++){
char *zAs = pEList->a[j].zName;
if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
- Expr *pDup, *pOrig;
+ Expr *pOrig;
assert( pExpr->pLeft==0 && pExpr->pRight==0 );
assert( pExpr->pList==0 );
assert( pExpr->pSelect==0 );
@@ -228,15 +291,7 @@
sqlite3DbFree(db, zCol);
return 2;
}
- pDup = sqlite3ExprDup(db, pOrig);
- if( pExpr->flags & EP_ExpCollate ){
- pDup->pColl = pExpr->pColl;
- pDup->flags |= EP_ExpCollate;
- }
- if( pExpr->span.dyn ) sqlite3DbFree(db, (char*)pExpr->span.z);
- if( pExpr->token.dyn ) sqlite3DbFree(db, (char*)pExpr->token.z);
- memcpy(pExpr, pDup, sizeof(*pExpr));
- sqlite3DbFree(db, pDup);
+ resolveAlias(pParse, pEList, j, pExpr, "");
cnt = 1;
pMatch = 0;
assert( zTab==0 && zDb==0 );
@@ -731,24 +786,11 @@
assert( pEList!=0 ); /* sqlite3SelectNew() guarantees this */
for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
if( pItem->iCol ){
- Expr *pE;
- CollSeq *pColl;
- int flags;
-
if( pItem->iCol>pEList->nExpr ){
resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr);
return 1;
}
- pE = pItem->pExpr;
- pColl = pE->pColl;
- flags = pE->flags & EP_ExpCollate;
- sqlite3ExprDelete(db, pE);
- pE = sqlite3ExprDup(db, pEList->a[pItem->iCol-1].pExpr);
- pItem->pExpr = pE;
- if( pE && flags ){
- pE->pColl = pColl;
- pE->flags |= flags;
- }
+ resolveAlias(pParse, pEList, pItem->iCol-1, pItem->pExpr, zType);
}
}
return 0;
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index d66036b..b41104a 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.761 2008/08/22 17:34:45 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.762 2008/08/29 02:14:03 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_
@@ -1345,6 +1345,7 @@
u8 sortOrder; /* 1 for DESC or 0 for ASC */
u8 done; /* A flag to indicate when processing is finished */
u16 iCol; /* For ORDER BY, column number in result set */
+ u16 iAlias; /* Index into Parse.aAlias[] for zName */
} *a; /* One entry for each expression */
};
@@ -1681,6 +1682,8 @@
int nVarExpr; /* Number of used slots in apVarExpr[] */
int nVarExprAlloc; /* Number of allocated slots in apVarExpr[] */
Expr **apVarExpr; /* Pointers to :aaa and $aaaa wildcard expressions */
+ int nAlias; /* Number of aliased result set columns */
+ int *aAlias; /* Register used to hold aliased result */
u8 explain; /* True if the EXPLAIN flag is found on the query */
Token sErrToken; /* The token at which the error occurred */
Token sNameToken; /* Token with unqualified schema object name */
diff --git a/src/tokenize.c b/src/tokenize.c
index 3e5bd9d..802c163 100644
--- a/src/tokenize.c
+++ b/src/tokenize.c
@@ -15,7 +15,7 @@
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
-** $Id: tokenize.c,v 1.150 2008/08/08 14:19:41 drh Exp $
+** $Id: tokenize.c,v 1.151 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -503,6 +503,7 @@
sqlite3DeleteTrigger(db, pParse->pNewTrigger);
sqlite3DbFree(db, pParse->apVarExpr);
+ sqlite3DbFree(db, pParse->aAlias);
if( nErr>0 && (pParse->rc==SQLITE_OK || pParse->rc==SQLITE_DONE) ){
pParse->rc = SQLITE_ERROR;
}
diff --git a/test/alias.test b/test/alias.test
new file mode 100644
index 0000000..a5844d3
--- /dev/null
+++ b/test/alias.test
@@ -0,0 +1,137 @@
+# 2008 August 28
+#
+# 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 implements regression tests for SQLite library. The
+# focus of this script is correct code generation of aliased result-set
+# values. See ticket #3343.
+#
+# $Id: alias.test,v 1.1 2008/08/29 02:14:03 drh Exp $
+#
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# A procedure to return a sequence of increasing integers.
+#
+namespace eval ::seq {
+ variable counter 0
+ proc value {args} {
+ variable counter
+ incr counter
+ return $counter
+ }
+ proc reset {} {
+ variable counter
+ set counter 0
+ }
+}
+
+
+do_test alias-1.1 {
+ db function sequence ::seq::value
+ db eval {
+ CREATE TABLE t1(x);
+ INSERT INTO t1 VALUES(9);
+ INSERT INTO t1 VALUES(8);
+ INSERT INTO t1 VALUES(7);
+ SELECT x, sequence() FROM t1;
+ }
+} {9 1 8 2 7 3}
+do_test alias-1.2 {
+ ::seq::reset
+ db eval {
+--pragma vdbe_listing=on; pragma vdbe_trace=on;
+ SELECT x, sequence() AS y FROM t1 WHERE y>0
+ }
+} {9 1 8 2 7 3}
+do_test alias-1.3 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
+ }
+} {9 1 8 2 7 3}
+do_test alias-1.4 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
+ }
+} {9 1 8 2 7 3}
+do_test alias-1.5 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1
+ WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)
+ AND y NOT LIKE 'abc%' AND y%10==2
+ }
+} {8 2}
+do_test alias-1.6 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
+ }
+} {9 1 8 2 7 3}
+do_test alias-1.7 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
+ }
+} {7 3}
+do_test alias-1.8 {
+ ::seq::reset
+ db eval {
+ SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
+ }
+} {7 -2 8 -1 9 0}
+do_test alias-1.9 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1 ORDER BY -y
+ }
+} {7 3 8 2 9 1}
+do_test alias-1.10 {
+ ::seq::reset
+ db eval {
+ SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
+ }
+} {8 2 9 1 7 3}
+
+unset -nocomplain random_int_list
+set random_int_list [db eval {
+ SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
+}]
+do_test alias-1.11 {
+ lsort -integer $::random_int_list
+} $random_int_list
+
+
+do_test alias-2.1 {
+ db eval {
+ SELECT 4 UNION SELECT 1 ORDER BY 1
+ }
+} {1 4}
+do_test alias-2.2 {
+ db eval {
+ SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
+ }
+} {1 4 9}
+
+if 0 {
+ # Aliases in the GROUP BY clause cause the expression to be evaluated
+ # twice in the current implementation. This might change in the future.
+ #
+ do_test alias-3.1 {
+ ::seq::reset
+ db eval {
+ SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
+ }
+ } {1 1 2 1 3 1}
+}
+
+finish_test