From 9d2985c7b4b1d8faa07f72c6576e19206023feec Mon Sep 17 00:00:00 2001 From: drh Date: Thu, 8 Sep 2005 01:58:42 +0000 Subject: [PATCH] Optimizations and refinements. Improvements to test coverage. (CVS 2667) FossilOrigin-Name: 7283f7c29db4f622380b6a5cb745a4dc0c8e6a25 --- manifest | 22 ++++++++--------- manifest.uuid | 2 +- src/delete.c | 8 +++---- src/select.c | 59 +++++++++++++++++++++++++++++----------------- src/sqliteInt.h | 11 ++++----- src/update.c | 8 +++---- test/collate3.test | 3 ++- test/misc4.test | 9 ++++++- 8 files changed, 72 insertions(+), 50 deletions(-) diff --git a/manifest b/manifest index 3ed447c3d2..1d4982624a 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C All\sregression\stests\snow\spass.\s\sBut\sI\sam\ssure\sthere\smust\sstill\sbe\sproblems.\nNew\stests\sneed\sto\sbe\sadded.\s(CVS\s2666) -D 2005-09-08T00:13:27 +C Optimizations\sand\srefinements.\s\sImprovements\sto\stest\scoverage.\s(CVS\s2667) +D 2005-09-08T01:58:43 F Makefile.in 12784cdce5ffc8dfb707300c34e4f1eb3b8a14f1 F Makefile.linux-gcc 06be33b2a9ad4f005a5f42b22c4a19dab3cbb5c7 F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -38,7 +38,7 @@ F src/build.c fcedb4baf328f7846c0c686d46d5c4d0d0842423 F src/callback.c 9a1162c8f9dae9fad6d548339669aacb5f6cf76b F src/complete.c 4de937dfdd4c79a501772ab2035b26082f337a79 F src/date.c 7444b0900a28da77e57e3337a636873cff0ae940 -F src/delete.c be1fc25c9e109cd8cbab42a43ee696263da7c04b +F src/delete.c 16a0e19460b14d219f39ff5c7a9eef808aa1969c F src/experimental.c 50c1e3b34f752f4ac10c36f287db095c2b61766d F src/expr.c 38f1e135aa80dfc62e253c1e22dd6e194efd2d61 F src/func.c 713cf33a0ab8685d44ed31a9c753983a7ff9fd6e @@ -63,10 +63,10 @@ F src/pragma.c 69413fbdc0c6aaa493a776ea52c1b3e6cf35dfb2 F src/prepare.c 86f0d8e744b8d956eff6bc40e29049efee017610 F src/printf.c c01e9ad473d79463fb1f483b1eca5c3cbed2a4e5 F src/random.c 90adff4e73a3b249eb4f1fc2a6ff9cf78c7233a4 -F src/select.c 5f4a9396fc3b4f1d24224a4842737e708b13fb61 +F src/select.c 7e52f53ccf116456f1f5eebb5493973eda43c89a F src/shell.c b21daba017b8feef2fdc65ecde57f70209494217 F src/sqlite.h.in d6561d51025d08de4f455607f3f9f9aa76e855d5 -F src/sqliteInt.h b7d841739ce478a299e46db5ebcb510e833a36c5 +F src/sqliteInt.h d2f3284eb67068fcbbd7df99b25f3bd874fa0a53 F src/table.c 25b3ff2b39b7d87e8d4a5da0713d68dfc06cbee9 F src/tclsqlite.c ac94682f9e601dd373912c46414a5a842db2089a F src/test1.c b569b60e35f0e3ea20e5ebfaf6e522a01c08d481 @@ -76,7 +76,7 @@ F src/test4.c a8fd681e139e1c61f22a77d07fc3a99cb28fff3f F src/test5.c 64f08b2a50ef371a1bd68ff206829e7b1b9997f5 F src/tokenize.c e1faf5637f3f4f90933785a0ecf64595f3ac3530 F src/trigger.c f51dec15921629591cb98bf2e350018e268b109a -F src/update.c a9d2c5f504212d62da1b094476f1389c0e02f83f +F src/update.c c2716c2115533ffae3d08bf8853aaba4f970f37e F src/utf.c bda5eb85039ef16f2d17004c1e18c96e1ab0a80c F src/util.c 5650f6fe5ee30e0678985ad7b94da91e3f85752b F src/vacuum.c 829d9e1a6d7c094b80e0899686670932eafd768c @@ -125,7 +125,7 @@ F test/capi3b.test 5b6a66f9f295f79f443b5d3f33187fa5ef6cf336 F test/cast.test 937af85faabdb189b6cd05879977f2469223be59 F test/collate1.test f79736d2ebf5492167ee4d1f4ab4c09dda776b03 F test/collate2.test 224a632ba04907c049804b08162efd234aa7871f -F test/collate3.test 51362bdfb43a72bd2b087d90b2623b0695538e7a +F test/collate3.test 947a77f5b8227e037a7094d0e338a5504f155cc4 F test/collate4.test daf498e294dcd596b961d425c3f2dda117e4717e F test/collate5.test 8fb4e7e0241839356bd8710f437c32efb47bfff8 F test/collate6.test 6c9470d1606ee3e564675b229653e320c49ec638 @@ -178,7 +178,7 @@ F test/minmax.test 9429a06f1f93acf76fcacafd17160a4392e88526 F test/misc1.test 4ca69ca2e2ef33c7a0b0fc8b324111e37a522d29 F test/misc2.test 5c699af2fede2694736a9f45aea7e2f052686e15 F test/misc3.test 7bd937e2c62bcc6be71939faf068d506467b1e03 -F test/misc4.test 879b1eedac550b70fd3f2b7cb993505927459ed3 +F test/misc4.test a4d1368bc598b03b3cf642d0bd22d51b28d6c156 F test/misc5.test 24bd03404039ec727028ac9cf7fd9066fd209ec9 F test/misuse.test 1c7fee3c4c0cb4008717ecccf5c72281fac0008e F test/notnull.test 7a08117a71e74b0321aaa937dbeb41a09d6eb1d0 @@ -306,7 +306,7 @@ F www/tclsqlite.tcl 3df553505b6efcad08f91e9b975deb2e6c9bb955 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl a99cf5f6d8bd4d5537584a2b342f0fb9fa601d8b F www/whentouse.tcl 97e2b5cd296f7d8057e11f44427dea8a4c2db513 -P a1b6d910cdbb53f12366402d9585dce2aad3ba3d -R 798c4d51d5a5c7cc59df64f56a2ed72b +P bcc7d722cea4487a3adf9d9b2af4b74bfbfc5f39 +R 91fa1142a737c9df354f4c8b73b255ba U drh -Z 7290bc3839b076b3e9d2d69315c82577 +Z 5018c1d6effebbbf13aed3b0f917e94d diff --git a/manifest.uuid b/manifest.uuid index 177ce02eaf..72b5aabd4d 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -bcc7d722cea4487a3adf9d9b2af4b74bfbfc5f39 \ No newline at end of file +7283f7c29db4f622380b6a5cb745a4dc0c8e6a25 \ No newline at end of file diff --git a/src/delete.c b/src/delete.c index 8db7a8ad10..b472dddce9 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.109 2005/07/21 18:23:20 drh Exp $ +** $Id: delete.c,v 1.110 2005/09/08 01:58:43 drh Exp $ */ #include "sqliteInt.h" @@ -178,12 +178,12 @@ void sqlite3DeleteFrom( if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, triggers_exist, pTab->iDb); - /* If we are trying to delete from a view, construct that view into - ** a temporary table. + /* If we are trying to delete from a view, realize that view into + ** a ephemeral table. */ if( isView ){ Select *pView = sqlite3SelectDup(pTab->pSelect); - sqlite3Select(pParse, pView, SRT_TempTable, iCur, 0, 0, 0, 0); + sqlite3Select(pParse, pView, SRT_VirtualTab, iCur, 0, 0, 0, 0); sqlite3SelectDelete(pView); } diff --git a/src/select.c b/src/select.c index 08d824198d..e6f329be8c 100644 --- a/src/select.c +++ b/src/select.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.264 2005/09/08 00:13:28 drh Exp $ +** $Id: select.c,v 1.265 2005/09/08 01:58:43 drh Exp $ */ #include "sqliteInt.h" @@ -330,10 +330,10 @@ void sqlite3SelectDelete(Select *p){ */ static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){ sqlite3ExprCodeExprList(pParse, pOrderBy); - sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iTab, 0); + sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iECursor, 0); sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0); sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0); - sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iTab, 0); + sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iECursor, 0); } /* @@ -465,7 +465,7 @@ static int selectInnerLoop( /* Store the result as data using a unique key. */ case SRT_Table: - case SRT_TempTable: { + case SRT_VirtualTab: { sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0); if( pOrderBy ){ pushOntoSorter(pParse, v, pOrderBy); @@ -528,15 +528,13 @@ static int selectInnerLoop( ** popping the data from the stack. */ case SRT_Subroutine: - case SRT_Callback: - case SRT_Sorter: { + case SRT_Callback: { if( pOrderBy ){ sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0); pushOntoSorter(pParse, v, pOrderBy); }else if( eDest==SRT_Subroutine ){ sqlite3VdbeAddOp(v, OP_Gosub, 0, iParm); }else{ - assert( eDest!=SRT_Sorter ); sqlite3VdbeAddOp(v, OP_Callback, nColumn, 0); } break; @@ -620,14 +618,13 @@ static void generateSortTail( int iTab; ExprList *pOrderBy = p->pOrderBy; - if( eDest==SRT_Sorter ) return; - iTab = pOrderBy->iTab; + iTab = pOrderBy->iECursor; addr = 1 + sqlite3VdbeAddOp(v, OP_Sort, iTab, brk); codeLimiter(v, p, cont, brk, 0); sqlite3VdbeAddOp(v, OP_Column, iTab, pOrderBy->nExpr + 1); switch( eDest ){ case SRT_Table: - case SRT_TempTable: { + case SRT_VirtualTab: { sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0); sqlite3VdbeAddOp(v, OP_Pull, 1, 0); sqlite3VdbeAddOp(v, OP_Insert, iParm, 0); @@ -1343,10 +1340,10 @@ static void computeLimitRegisters(Parse *pParse, Select *p){ static void createSortingIndex(Parse *pParse, Select *p, ExprList *pOrderBy){ if( pOrderBy ){ int addr; - assert( pOrderBy->iTab==0 ); - pOrderBy->iTab = pParse->nTab++; + assert( pOrderBy->iECursor==0 ); + pOrderBy->iECursor = pParse->nTab++; addr = sqlite3VdbeAddOp(pParse->pVdbe, OP_OpenVirtual, - pOrderBy->iTab, pOrderBy->nExpr+1); + pOrderBy->iECursor, pOrderBy->nExpr+1); assert( p->addrOpenVirt[2] == -1 ); p->addrOpenVirt[2] = addr; } @@ -1468,7 +1465,7 @@ static int multiSelect( /* Create the destination temporary table if necessary */ - if( eDest==SRT_TempTable ){ + if( eDest==SRT_VirtualTab ){ assert( p->pEList ); assert( nSetP2nErr || p==0 ) return 1; @@ -2646,7 +2644,7 @@ int sqlite3Select( }else{ needRestoreContext = 0; } - sqlite3Select(pParse, pItem->pSelect, SRT_TempTable, + sqlite3Select(pParse, pItem->pSelect, SRT_VirtualTab, pItem->iCursor, p, i, &isAgg, 0); if( needRestoreContext ){ pParse->zAuthContext = zSavedAuthContext; @@ -2682,12 +2680,17 @@ int sqlite3Select( #endif /* If there is an ORDER BY clause, resolve any collation sequences - ** names that have been explicitly specified. + ** names that have been explicitly specified and create a sorting index. + ** + ** This sorting index might end up being unused if the data can be + ** extracted in pre-sorted order. If that is the case, then the + ** OP_OpenVirtual instruction will be changed to an OP_Noop once + ** we figure out that the sorting index is not needed. The addrSortIndex + ** variable is used to facilitate that change. */ if( pOrderBy ){ struct ExprList_item *pTerm; KeyInfo *pKeyInfo; - int addr; for(i=0, pTerm=pOrderBy->a; inExpr; i++, pTerm++){ if( pTerm->zName ){ pTerm->pExpr->pColl = sqlite3LocateCollSeq(pParse, pTerm->zName, -1); @@ -2697,10 +2700,12 @@ int sqlite3Select( goto select_end; } pKeyInfo = keyInfoFromExprList(pParse, pOrderBy); - pOrderBy->iTab = pParse->nTab++; - addr = sqlite3VdbeOp3(v, OP_OpenVirtual, pOrderBy->iTab, pOrderBy->nExpr+2, + pOrderBy->iECursor = pParse->nTab++; + p->addrOpenVirt[2] = addrSortIndex = + sqlite3VdbeOp3(v, OP_OpenVirtual, pOrderBy->iECursor, pOrderBy->nExpr+2, (char*)pKeyInfo, P3_KEYINFO_HANDOFF); - p->addrOpenVirt[2] = addr; + }else{ + addrSortIndex = -1; } /* Set the limiter. @@ -2709,7 +2714,7 @@ int sqlite3Select( /* If the output is destined for a temporary table, open that table. */ - if( eDest==SRT_TempTable ){ + if( eDest==SRT_VirtualTab ){ sqlite3VdbeAddOp(v, OP_OpenVirtual, iParm, pEList->nExpr); } @@ -2741,6 +2746,15 @@ int sqlite3Select( pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy); if( pWInfo==0 ) goto select_end; + /* If sorting index that was created by a prior OP_OpenVirtual + ** instruction ended up not being needed, then change the OP_OpenVirtual + ** into an OP_Noop. + */ + if( addrSortIndex>=0 && pOrderBy==0 ){ + uncreateSortingIndex(pParse, addrSortIndex); + p->addrOpenVirt[2] = -1; + } + /* Use the standard inner loop */ if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest, @@ -2785,6 +2799,7 @@ int sqlite3Select( sNC.pSrcList = pTabList; sNC.pAggInfo = &sAggInfo; sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0; + sAggInfo.pGroupBy = pGroupBy; if( sqlite3ExprAnalyzeAggList(&sNC, pEList) ){ goto select_end; } diff --git a/src/sqliteInt.h b/src/sqliteInt.h index f3ee4752a3..35bd4a4b7b 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -11,7 +11,7 @@ ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.409 2005/09/07 22:48:16 drh Exp $ +** @(#) $Id: sqliteInt.h,v 1.410 2005/09/08 01:58:43 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ @@ -927,7 +927,7 @@ struct Expr { struct ExprList { int nExpr; /* Number of expressions on the list */ int nAlloc; /* Number of entries allocated below */ - int iTab; /* VDBE Cursor associated with this ExprList */ + int iECursor; /* VDBE Cursor associated with this ExprList */ struct ExprList_item { Expr *pExpr; /* The list of expressions */ char *zName; /* Token associated with this expression */ @@ -1132,10 +1132,9 @@ struct Select { #define SRT_Mem 5 /* Store result in a memory cell */ #define SRT_Set 6 /* Store non-null results as keys in an index */ #define SRT_Table 7 /* Store result as data and add automatic rowid */ -#define SRT_TempTable 8 /* Store result in a trasient table */ -#define SRT_Sorter 9 /* Store results in the sorter NOT USED */ -#define SRT_Subroutine 10 /* Call a subroutine to handle results */ -#define SRT_Exists 11 /* Put 0 or 1 in a memory cell */ +#define SRT_VirtualTab 8 /* Create virtual table and store results there */ +#define SRT_Subroutine 9 /* Call a subroutine to handle results */ +#define SRT_Exists 10 /* Put 0 or 1 in a memory cell */ /* ** An SQL parser context. A copy of this structure is passed through diff --git a/src/update.c b/src/update.c index 99900be8b3..704775d818 100644 --- a/src/update.c +++ b/src/update.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** to handle UPDATE statements. ** -** $Id: update.c,v 1.110 2005/07/21 18:23:20 drh Exp $ +** $Id: update.c,v 1.111 2005/09/08 01:58:43 drh Exp $ */ #include "sqliteInt.h" @@ -259,13 +259,13 @@ void sqlite3Update( if( pParse->nested==0 ) sqlite3VdbeCountChanges(v); sqlite3BeginWriteOperation(pParse, 1, pTab->iDb); - /* If we are trying to update a view, construct that view into - ** a temporary table. + /* If we are trying to update a view, realize that view into + ** a ephemeral table. */ if( isView ){ Select *pView; pView = sqlite3SelectDup(pTab->pSelect); - sqlite3Select(pParse, pView, SRT_TempTable, iCur, 0, 0, 0, 0); + sqlite3Select(pParse, pView, SRT_VirtualTab, iCur, 0, 0, 0, 0); sqlite3SelectDelete(pView); } diff --git a/test/collate3.test b/test/collate3.test index 7a471c0dea..ad52782706 100644 --- a/test/collate3.test +++ b/test/collate3.test @@ -11,7 +11,7 @@ # This file implements regression tests for SQLite library. The # focus of this script is page cache subsystem. # -# $Id: collate3.test,v 1.10 2005/01/29 08:32:46 danielk1977 Exp $ +# $Id: collate3.test,v 1.11 2005/09/08 01:58:43 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -152,6 +152,7 @@ ifcapable compound { } } {0 {}} do_test collate3-2.13 { +btree_breakpoint catchsql { SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare; } diff --git a/test/misc4.test b/test/misc4.test index 0a8dbd5399..4f1272b269 100644 --- a/test/misc4.test +++ b/test/misc4.test @@ -13,7 +13,7 @@ # This file implements tests for miscellanous features that were # left out of other test files. # -# $Id: misc4.test,v 1.18 2005/09/07 22:48:16 drh Exp $ +# $Id: misc4.test,v 1.19 2005/09/08 01:58:43 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -100,6 +100,13 @@ do_test misc4-3.1 { ORDER BY 1, 2; } } {1 x 1 z} +do_test misc4-3.2 { + catchsql { + SELECT ID, Value FROM Table1 + UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 + ORDER BY 1, 2; + } +} {1 {aggregate functions are not allowed in the GROUP BY clause}} } ;# ifcapable compound # Ticket #1047. Make sure column types are preserved in subqueries. -- 2.47.2