From 49ffdbf47e45d2192c4211ee3ec1886884f1b073 Mon Sep 17 00:00:00 2001 From: shane Date: Fri, 10 Oct 2008 18:25:45 +0000 Subject: [PATCH] Further simplifications of the code for the LIMIT clause on an UPDATE or DELETE. Added a few test cases to wherelimit.test. (CVS 5797) FossilOrigin-Name: 282c6a46b25f4e4278fd4c8b0b1cde1de28d8f51 --- manifest | 20 +++++++-------- manifest.uuid | 2 +- src/delete.c | 61 +++++++++++++++++++++++++++++--------------- src/parse.y | 20 ++++----------- src/sqliteInt.h | 4 +-- test/wherelimit.test | 11 +++++++- 6 files changed, 69 insertions(+), 49 deletions(-) diff --git a/manifest b/manifest index d0066e8ed9..c053be0c7c 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sthe\sgenfkey\sprogram\sto\sthe\stool/\sdirectory.\s(CVS\s5796) -D 2008-10-10T17:58:27 +C Further\ssimplifications\sof\sthe\scode\sfor\sthe\sLIMIT\sclause\son\san\sUPDATE\sor\sDELETE.\s\sAdded\sa\sfew\stest\scases\sto\swherelimit.test.\s(CVS\s5797) +D 2008-10-10T18:25:46 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in 7fc26e087207e7a4a7723583dbd7997477af3b13 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -106,7 +106,7 @@ F src/build.c 8714bd809583bbe07bf22d0e1808a3fc31abe330 F src/callback.c e970e5beddbdb23f89a6d05cb1a6419d9f755624 F src/complete.c cb14e06dbe79dee031031f0d9e686ff306afe07c F src/date.c 1b1fc801df40aae891bff8b03f65313df192d677 -F src/delete.c f72c98c5770c94f1a8b7bbdf7aeb49503a4927a4 +F src/delete.c c7aaf47f274494f9ae1f89cf095ca890b1d9d2bf F src/expr.c 0ceafeff3a4e0f460d6a7695a675ae12391e313d F src/fault.c dc88c821842157460750d2d61a8a8b4197d047ff F src/func.c 8431b40a7843d1024145684d303c55b4ee087bbe @@ -140,7 +140,7 @@ F src/os_unix.c f33b69d8a85372b270fe37ee664a4c2140a5217d F src/os_win.c 04033a86a39f49cb8e348f515eb0116aa9d36678 F src/pager.c d98f56128e849083f2f612196efebd982c491fea F src/pager.h 9c1917be28fff58118e1fe0ddbc7adfb8dd4f44d -F src/parse.y 8862ab3391c9546f25d0e929906f100d0a9e7d2b +F src/parse.y e8620c7efd46659fd7a9cbad13b51225af7f600c F src/pcache.c f8d7beceba164a34441ac37e88abb3a404f968a7 F src/pcache.h 28d9ce2d66909db1f01652586450b62b64793093 F src/pragma.c f0f48d0d50e9d8fa785178fc2410244c06f6a287 @@ -152,7 +152,7 @@ F src/select.c d910d7350df0d918e22286c5bfd39d4ea68ec813 F src/shell.c d83b578a8ccdd3e0e7fef4388a0887ce9f810967 F src/sqlite.h.in 28e6466da6b90e56bfbc6c50a1a281931a8c733c F src/sqlite3ext.h 1e3887c9bd3ae66cb599e922824b04cd0d0f2c3e -F src/sqliteInt.h 0da9c664d89995d28587548048837e3063fa0be3 +F src/sqliteInt.h f60fac64c8970ca366080daa3aa65b9a54a2334a F src/sqliteLimit.h f435e728c6b620ef7312814d660a81f9356eb5c8 F src/status.c 237b193efae0cf6ac3f0817a208de6c6c6ef6d76 F src/table.c 22744786199c9195720c15a7a42cb97b2e2728d8 @@ -618,7 +618,7 @@ F test/where3.test 97d3936e6a443b968f1a61cdcc0f673252000e94 F test/where4.test e9b9e2f2f98f00379e6031db6a6fca29bae782a2 F test/where5.test fdf66f96d29a064b63eb543e28da4dfdccd81ad2 F test/where6.test 42c4373595f4409d9c6a9987b4a60000ad664faf -F test/wherelimit.test 3464395714151cdce9f7f28a19d2cf344bd934c8 +F test/wherelimit.test 5e9fd41e79bb2b2d588ed999d641d9c965619b31 F test/zeroblob.test 792124852ec61458a2eb527b5091791215e0be95 F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b F tool/fragck.tcl 5265a95126abcf6ab357f7efa544787e5963f439 @@ -648,7 +648,7 @@ F tool/speedtest16.c c8a9c793df96db7e4933f0852abb7a03d48f2e81 F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e -P 3bb33cf59da49f13201c0226e964cda067a4e780 -R aef0f958c65788fc079e6ee823734d14 -U danielk1977 -Z 3eb8c7cc05cdf3fbc1c3afcf6ba604ca +P 84e73fe8a6f538c4b1bb4f641a661d6fafb60c76 +R eb4c22bf5cef8091ed9bce14d73bbe63 +U shane +Z 9ca81fedb812b2854e888eab6c64b7ec diff --git a/manifest.uuid b/manifest.uuid index e5584ad8d4..2d6ec05808 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -84e73fe8a6f538c4b1bb4f641a661d6fafb60c76 \ No newline at end of file +282c6a46b25f4e4278fd4c8b0b1cde1de28d8f51 \ No newline at end of file diff --git a/src/delete.c b/src/delete.c index 82cc1336c0..7f98812a6c 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.180 2008/10/10 13:35:58 shane Exp $ +** $Id: delete.c,v 1.181 2008/10/10 18:25:46 shane Exp $ */ #include "sqliteInt.h" @@ -126,24 +126,37 @@ void sqlite3MaterializeView( ** pLimitWhere (pInClause) */ Expr *sqlite3LimitWhere( - Parse *pParse, /* The parser context */ - SrcList *pSrc, /* the FROM clause -- which tables to scan */ - Expr *pWhere, /* The WHERE clause. May be null */ - ExprList *pOrderBy, /* The ORDER BY clause. May be null */ - Expr *pLimit, /* The LIMIT clause. May be null */ - Expr *pOffset /* The OFFSET clause. May be null */ + Parse *pParse, /* The parser context */ + SrcList *pSrc, /* the FROM clause -- which tables to scan */ + Expr *pWhere, /* The WHERE clause. May be null */ + ExprList *pOrderBy, /* The ORDER BY clause. May be null */ + Expr *pLimit, /* The LIMIT clause. May be null */ + Expr *pOffset, /* The OFFSET clause. May be null */ + char *zStmtType /* Either DELETE or UPDATE. For error messages. */ ){ - Expr *pWhereRowid = NULL; - Expr *pInClause = NULL; - Expr *pSelectRowid = NULL; - ExprList *pEList = NULL; - SrcList *pSelectSrc = NULL; - Select *pSelect = NULL; + Expr *pWhereRowid = NULL; /* WHERE rowid .. */ + Expr *pInClause = NULL; /* WHERE rowid IN ( select ) */ + Expr *pSelectRowid = NULL; /* SELECT rowid ... */ + ExprList *pEList = NULL; /* Expression list contaning only pSelectRowid */ + SrcList *pSelectSrc = NULL; /* SELECT rowid FROM x ... (dup of pSrc) */ + Select *pSelect = NULL; /* Complete SELECT tree */ + + /* Check that there isn't an ORDER BY without a LIMIT clause. + */ + if( pOrderBy && (pLimit == 0) ) { + sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType); + pParse->parseError = 1; + goto limit_where_cleanup_2; + } /* We only need to generate a select expression if there ** is a limit/offset term to enforce. */ - if (!pLimit && !pOffset) return pWhere; + if( pLimit == 0 ) { + /* if pLimit is null, pOffset will always be null as well. */ + assert( pOffset == 0 ); + return pWhere; + } /* Generate a select expression tree to enforce the limit/offset ** term for the DELETE or UPDATE statement. For example: @@ -155,35 +168,43 @@ Expr *sqlite3LimitWhere( */ pSelectRowid = sqlite3Expr(pParse->db, TK_ROW, 0, 0, 0); - if( pSelectRowid == 0 ) return 0; + if( pSelectRowid == 0 ) goto limit_where_cleanup_2; pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid, 0); - if( pEList == 0 ) return 0; + if( pEList == 0 ) goto limit_where_cleanup_2; /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc); if( pSelectSrc == 0 ) { sqlite3ExprListDelete(pParse->db, pEList); - return 0; + goto limit_where_cleanup_2; } /* generate the SELECT expression tree. */ pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0,pOrderBy,0,pLimit,pOffset); if( pSelect == 0 ) return 0; + /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */ pWhereRowid = sqlite3Expr(pParse->db, TK_ROW, 0, 0, 0); - if( pWhereRowid == 0 ) goto limit_where_cleanup; + if( pWhereRowid == 0 ) goto limit_where_cleanup_1; pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); - if( pInClause == 0 ) goto limit_where_cleanup; + if( pInClause == 0 ) goto limit_where_cleanup_1; pInClause->pSelect = pSelect; sqlite3ExprSetHeight(pParse, pInClause); return pInClause; /* something went wrong. clean up anything allocated. */ -limit_where_cleanup: +limit_where_cleanup_1: sqlite3SelectDelete(pParse->db, pSelect); return 0; + +limit_where_cleanup_2: + sqlite3ExprDelete(pParse->db, pWhere); + sqlite3ExprListDelete(pParse->db, pOrderBy); + sqlite3ExprDelete(pParse->db, pLimit); + sqlite3ExprDelete(pParse->db, pOffset); + return 0; } #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) */ diff --git a/src/parse.y b/src/parse.y index 61b322fa46..3959269ccf 100644 --- a/src/parse.y +++ b/src/parse.y @@ -14,7 +14,7 @@ ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** -** @(#) $Id: parse.y,v 1.258 2008/10/10 17:47:21 danielk1977 Exp $ +** @(#) $Id: parse.y,v 1.259 2008/10/10 18:25:46 shane Exp $ */ // All token codes are small integers with #defines that begin with "TK_" @@ -582,13 +582,8 @@ limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); - if( O && !L.pLimit ){ - sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on DELETE"); - pParse->parseError = 1; - }else{ - W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset); - sqlite3DeleteFrom(pParse,X,W); - } + W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE"); + sqlite3DeleteFrom(pParse,X,W); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT @@ -610,13 +605,8 @@ where_opt(A) ::= WHERE expr(X). {A = X;} cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); - if( O && !L.pLimit ){ - sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on UPDATE"); - pParse->parseError = 1; - }else{ - W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit,L.pOffset); - sqlite3Update(pParse,X,Y,W,R); - } + W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE"); + sqlite3Update(pParse,X,Y,W,R); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT diff --git a/src/sqliteInt.h b/src/sqliteInt.h index f13a739db7..caf62320b1 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -11,7 +11,7 @@ ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.780 2008/10/10 04:34:16 shane Exp $ +** @(#) $Id: sqliteInt.h,v 1.781 2008/10/10 18:25:46 shane Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ @@ -2162,7 +2162,7 @@ Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) -Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *); +Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); diff --git a/test/wherelimit.test b/test/wherelimit.test index 5352d6fcdc..f0cfbb61e8 100644 --- a/test/wherelimit.test +++ b/test/wherelimit.test @@ -12,7 +12,7 @@ # focus of this file is testing the LIMIT ... OFFSET ... clause # of UPDATE and DELETE statements. # -# $Id: wherelimit.test,v 1.1 2008/10/10 06:02:00 shane Exp $ +# $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -57,6 +57,15 @@ ifcapable {update_delete_limit} { catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} } {1 {near "AS": syntax error}} + # OFFSET w/o LIMIT + do_test wherelimit-0.6 { + catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2} + } {1 {near "OFFSET": syntax error}} + do_test wherelimit-0.7 { + catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2} + } {1 {near "OFFSET": syntax error}} + + # check deletes w/o where clauses but with limit/offsets create_test_data 5 do_test wherelimit-1.0 { -- 2.47.2