From 7b113bab79fc36d1c077ad5ba9c2e129296a9d17 Mon Sep 17 00:00:00 2001 From: drh Date: Sat, 28 Jan 2012 15:22:22 +0000 Subject: [PATCH] Add support for multiple rows of VALUES in an INSERT statement. FossilOrigin-Name: eb3b6a0ceb8bfb9fd59ff5fec420f863a9b5c4e4 --- manifest | 20 ++++++++-------- manifest.uuid | 2 +- src/parse.y | 60 ++++++++++++++++++++++++++++++++++++---------- src/select.c | 9 +++++-- src/sqliteInt.h | 1 + test/insert.test | 15 ++++++++++++ test/trigger1.test | 14 ++++++++++- 7 files changed, 94 insertions(+), 27 deletions(-) diff --git a/manifest b/manifest index c257a3ab4b..2275b55721 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Only\sinvalidate\sthe\sschema\swhen\sthe\sOP_ParseSchema\sopcode\sfails,\snot\non\sany\sgeneral\sfailure\sof\sa\svdbe\sprogram. -D 2012-01-25T20:43:22.828 +C Add\ssupport\sfor\smultiple\srows\sof\sVALUES\sin\san\sINSERT\sstatement. +D 2012-01-28T15:22:22.673 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 3f79a373e57c3b92dabf76f40b065e719d31ac34 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -170,7 +170,7 @@ F src/os_unix.c 657672fab2580a84116c140b36ee3d6b6fc75b4e F src/os_win.c 5ac061ae1326a71500cee578ed0fd9113b4f6a37 F src/pager.c 2d892f7b901a8867a33bc21742086165a3a99af8 F src/pager.h a435da8421dc7844b7f9c7f37b636c160c50208a -F src/parse.y fabb2e7047417d840e6fdb3ef0988a86849a08ba +F src/parse.y 203ea3305abf7441ec55dde8714246fc87dff981 F src/pcache.c f8043b433a57aba85384a531e3937a804432a346 F src/pcache.h b1d8775a9bddf44e65edb0d20bfc57a4982f840f F src/pcache1.c 281822d22265245b19f908cb3f5df725f7e11b06 @@ -180,11 +180,11 @@ F src/printf.c 7ffb4ebb8b341f67e049695ba031da717b3d2699 F src/random.c cd4a67b3953b88019f8cd4ccd81394a8ddfaba50 F src/resolve.c 3d3e80a98f203ac6b9329e9621e29eda85ddfd40 F src/rowset.c 69afa95a97c524ba6faf3805e717b5b7ae85a697 -F src/select.c 2d45c1b7894fda3ba6f36996cc9f0dcfd2072ef1 +F src/select.c 1ad267692ab09afe05092eddcb5aba96b796afe1 F src/shell.c f492df9fc2de27e4700ecbaa948729fc47af88d7 F src/sqlite.h.in 53516617d2945a411d028674d7fa20dd394b9ec0 F src/sqlite3ext.h 6904f4aadf976f95241311fbffb00823075d9477 -F src/sqliteInt.h 60d36e72f08029bc637f8d62c1f88a5df5089c70 +F src/sqliteInt.h ee35c21bb13099df24b3675eabb0ff820213ed9d F src/sqliteLimit.h 164b0e6749d31e0daa1a4589a169d31c0dec7b3d F src/status.c 4568e72dfd36b6a5911f93457364deb072e0b03a F src/table.c 2cd62736f845d82200acfa1287e33feb3c15d62e @@ -526,7 +526,7 @@ F test/index4.test 2983216eb8c86ee62d9ed7cb206b5cc3331c0026 F test/indexedby.test be501e381b82b2f8ab406309ba7aac46e221f4ad F test/indexfault.test 31d4ab9a7d2f6e9616933eb079722362a883eb1d F test/init.test 15c823093fdabbf7b531fe22cf037134d09587a7 -F test/insert.test aef273dd1cee84cc92407469e6bd1b3cdcb76908 +F test/insert.test d540650825c98d8082d32f786c611d70e1c21a80 F test/insert2.test 4f3a04d168c728ed5ec2c88842e772606c7ce435 F test/insert3.test 1b7db95a03ad9c5013fdf7d6722b6cd66ee55e30 F test/insert4.test 87f6798f31d60c4e177622fcc3663367e6ecbd90 @@ -856,7 +856,7 @@ F test/trace2.test 962175290996d5f06dc4402ca218bbfc7df4cb20 F test/trans.test 6e1b4c6a42dba31bd65f8fa5e61a2708e08ddde6 F test/trans2.test d5337e61de45e66b1fcbf9db833fa8c82e624b22 F test/trans3.test d728abaa318ca364dc370e06576aa7e5fbed7e97 -F test/trigger1.test 38c657eaf9907344c9e0bcb16af94a452c6babde +F test/trigger1.test 38524d80ac26c232d23ecec4b037eb60fb67eedd F test/trigger2.test 834187beafd1db383af0c659cfa49b0576832816 F test/trigger3.test d2c60d8be271c355d61727411e753181e877230a F test/trigger4.test 74700b76ebf3947b2f7a92405141eb2cf2a5d359 @@ -988,7 +988,7 @@ F tool/tostr.awk e75472c2f98dd76e06b8c9c1367f4ab07e122d06 F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f F tool/warnings-clang.sh 9f406d66e750e8ac031c63a9ef3248aaa347ef2a F tool/warnings.sh fbc018d67fd7395f440c28f33ef0f94420226381 -P c05c3fd20d93f430140d762ead23bacd337ffb4d -R 5aa59fe283a6d87f44afad865139b28c +P 11f68d997da4aadf3f51c12c5139f3fdda8678bf +R c77c00103abe24fe57aac8dfef87e492 U drh -Z 2035157af4127fd9885fd84104456171 +Z 48c6e58778740de0aff3a3cb036591d9 diff --git a/manifest.uuid b/manifest.uuid index 1758df6a10..ffd0c69df1 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -11f68d997da4aadf3f51c12c5139f3fdda8678bf \ No newline at end of file +eb3b6a0ceb8bfb9fd59ff5fec420f863a9b5c4e4 \ No newline at end of file diff --git a/src/parse.y b/src/parse.y index 2b8e9170f3..d0f5f4152f 100644 --- a/src/parse.y +++ b/src/parse.y @@ -94,6 +94,14 @@ struct TrigEvent { int a; IdList * b; }; */ struct AttachKey { int type; Token key; }; +/* +** One or more VALUES claues +*/ +struct ValueList { + ExprList *pList; + Select *pSelect; +}; + } // end %include // Input is a single SQL command @@ -679,9 +687,8 @@ setlist(A) ::= nm(X) EQ expr(Y). { ////////////////////////// The INSERT command ///////////////////////////////// // -cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) - VALUES LP itemlist(Y) RP. - {sqlite3Insert(pParse, X, Y, 0, F, R);} +cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) valuelist(Y). + {sqlite3Insert(pParse, X, Y.pList, Y.pSelect, F, R);} cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {sqlite3Insert(pParse, X, 0, S, F, R);} cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES. @@ -691,14 +698,41 @@ cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES. insert_cmd(A) ::= INSERT orconf(R). {A = R;} insert_cmd(A) ::= REPLACE. {A = OE_Replace;} - -%type itemlist {ExprList*} -%destructor itemlist {sqlite3ExprListDelete(pParse->db, $$);} - -itemlist(A) ::= itemlist(X) COMMA expr(Y). - {A = sqlite3ExprListAppend(pParse,X,Y.pExpr);} -itemlist(A) ::= expr(X). - {A = sqlite3ExprListAppend(pParse,0,X.pExpr);} +// A ValueList is either a single VALUES clause or a comma-separated list +// of VALUES clauses. If it is a single VALUES clause then the +// ValueList.pList field points to the expression list of that clause. +// If it is a list of VALUES clauses, then those clauses are transformed +// into a set of SELECT statements without FROM clauses and connected by +// UNION ALL and the ValueList.pSelect points to the right-most SELECT in +// that compound. +%type valuelist {struct ValueList} +%destructor valuelist { + sqlite3ExprListDelete(pParse->db, $$.pList); + sqlite3SelectDelete(pParse->db, $$.pSelect); +} +valuelist(A) ::= VALUES LP nexprlist(X) RP. { + A.pList = X; + A.pSelect = 0; +} +valuelist(A) ::= valuelist(X) COMMA LP exprlist(Y) RP. { + Select *pRight = sqlite3SelectNew(pParse, Y, 0, 0, 0, 0, 0, 0, 0, 0); + if( X.pList ){ + X.pSelect = sqlite3SelectNew(pParse, X.pList, 0, 0, 0, 0, 0, 0, 0, 0); + X.pList = 0; + } + A.pList = 0; + if( X.pSelect==0 || pRight==0 ){ + sqlite3SelectDelete(pParse->db, pRight); + sqlite3SelectDelete(pParse->db, X.pSelect); + A.pSelect = 0; + }else{ + pRight->op = TK_ALL; + pRight->pPrior = X.pSelect; + pRight->selFlags |= SF_Values; + pRight->pPrior->selFlags |= SF_Values; + A.pSelect = pRight; + } +} %type inscollist_opt {IdList*} %destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);} @@ -1261,8 +1295,8 @@ trigger_cmd(A) ::= // INSERT trigger_cmd(A) ::= - insert_cmd(R) INTO trnm(X) inscollist_opt(F) VALUES LP itemlist(Y) RP. - {A = sqlite3TriggerInsertStep(pParse->db, &X, F, Y, 0, R);} + insert_cmd(R) INTO trnm(X) inscollist_opt(F) valuelist(Y). + {A = sqlite3TriggerInsertStep(pParse->db, &X, F, Y.pList, Y.pSelect, R);} trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) inscollist_opt(F) select(S). {A = sqlite3TriggerInsertStep(pParse->db, &X, F, 0, S, R);} diff --git a/src/select.c b/src/select.c index 2eef8df5e5..2eb4f07137 100644 --- a/src/select.c +++ b/src/select.c @@ -73,6 +73,7 @@ Select *sqlite3SelectNew( pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db,TK_ALL,0)); } pNew->pEList = pEList; + if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc)); pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; @@ -1611,8 +1612,12 @@ static int multiSelect( */ assert( p->pEList && pPrior->pEList ); if( p->pEList->nExpr!=pPrior->pEList->nExpr ){ - sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s" - " do not have the same number of result columns", selectOpName(p->op)); + if( p->selFlags & SF_Values ){ + sqlite3ErrorMsg(pParse, "all VALUES must have the same number of terms"); + }else{ + sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s" + " do not have the same number of result columns", selectOpName(p->op)); + } rc = 1; goto multi_select_end; } diff --git a/src/sqliteInt.h b/src/sqliteInt.h index ba6980d2f4..f084f55c24 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -2090,6 +2090,7 @@ struct Select { #define SF_Expanded 0x10 /* sqlite3SelectExpand() called on this */ #define SF_HasTypeInfo 0x20 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x40 /* Sort using a sorter */ +#define SF_Values 0x80 /* Synthesized from VALUES clause */ /* diff --git a/test/insert.test b/test/insert.test index 9ea9cd727d..05718d556f 100644 --- a/test/insert.test +++ b/test/insert.test @@ -386,6 +386,21 @@ do_test insert-9.2 { } } {1 1 2 2 3 3 12 101 13 102 16 103} +# Multiple VALUES clauses +# +do_test insert-10.1 { + execsql { + CREATE TABLE t10(a,b,c); + INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); + SELECT * FROM t10; + } +} {1 2 3 4 5 6 7 8 9} +do_test insert-10.2 { + catchsql { + INSERT INTO t10 VALUES(11,12,13), (14,15); + } +} {1 {all VALUES must have the same number of terms}} + integrity_check insert-99.0 finish_test diff --git a/test/trigger1.test b/test/trigger1.test index dc344d4d0f..1e77fe0fc8 100644 --- a/test/trigger1.test +++ b/test/trigger1.test @@ -290,9 +290,21 @@ ifcapable tempdb { SELECT * FROM t2; } } {1 {no such table: main.t2}} - do_test trigger-3.6 { + do_test trigger-3.6.1 { catchsql { DROP TRIGGER r1; + CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN + INSERT INTO t2 VALUES(NEW.a,NEW.b), (NEW.b*100, NEW.a*100); + END; + INSERT INTO t1 VALUES(1,2); + SELECT * FROM t2; + } + } {0 {1 2 200 100}} + do_test trigger-3.6.2 { + catchsql { + DROP TRIGGER r1; + DELETE FROM t1; + DELETE FROM t2; CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(NEW.a,NEW.b); END; -- 2.47.2