Overview
| SHA1 Hash: | eb3b6a0ceb8bfb9fd59ff5fec420f863a9b5c4e4 |
|---|---|
| Date: | 2012-01-28 15:22:22 |
| User: | drh |
| Comment: | Add support for multiple rows of VALUES in an INSERT statement. |
Tags And Properties
- branch=trunk inherited from [704b122e53]
- sym-trunk inherited from [704b122e53]
Changes
Changes to src/parse.y
89 */ 89 */ 90 struct TrigEvent { int a; IdList * b; }; 90 struct TrigEvent { int a; IdList * b; }; 91 91 92 /* 92 /* 93 ** An instance of this structure holds the ATTACH key and the key type. 93 ** An instance of this structure holds the ATTACH key and the key type. 94 */ 94 */ 95 struct AttachKey { int type; Token key; }; 95 struct AttachKey { int type; Token key; }; > 96 > 97 /* > 98 ** One or more VALUES claues > 99 */ > 100 struct ValueList { > 101 ExprList *pList; > 102 Select *pSelect; > 103 }; 96 104 97 } // end %include 105 } // end %include 98 106 99 // Input is a single SQL command 107 // Input is a single SQL command 100 input ::= cmdlist. 108 input ::= cmdlist. 101 cmdlist ::= cmdlist ecmd. 109 cmdlist ::= cmdlist ecmd. 102 cmdlist ::= ecmd. 110 cmdlist ::= ecmd. ................................................................................................................................................................................ 675 setlist(A) ::= nm(X) EQ expr(Y). { 683 setlist(A) ::= nm(X) EQ expr(Y). { 676 A = sqlite3ExprListAppend(pParse, 0, Y.pExpr); 684 A = sqlite3ExprListAppend(pParse, 0, Y.pExpr); 677 sqlite3ExprListSetName(pParse, A, &X, 1); 685 sqlite3ExprListSetName(pParse, A, &X, 1); 678 } 686 } 679 687 680 ////////////////////////// The INSERT command ///////////////////////////////// 688 ////////////////////////// The INSERT command ///////////////////////////////// 681 // 689 // 682 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) | 690 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) valuelist(Y). 683 VALUES LP itemlist(Y) RP. < 684 {sqlite3Insert(pParse, X, Y, 0, F, R);} | 691 {sqlite3Insert(pParse, X, Y.pList, Y.pSelect, F, R);} 685 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). 692 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). 686 {sqlite3Insert(pParse, X, 0, S, F, R);} 693 {sqlite3Insert(pParse, X, 0, S, F, R);} 687 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES. 694 cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES. 688 {sqlite3Insert(pParse, X, 0, 0, F, R);} 695 {sqlite3Insert(pParse, X, 0, 0, F, R);} 689 696 690 %type insert_cmd {u8} 697 %type insert_cmd {u8} 691 insert_cmd(A) ::= INSERT orconf(R). {A = R;} 698 insert_cmd(A) ::= INSERT orconf(R). {A = R;} 692 insert_cmd(A) ::= REPLACE. {A = OE_Replace;} 699 insert_cmd(A) ::= REPLACE. {A = OE_Replace;} 693 700 > 701 // A ValueList is either a single VALUES clause or a comma-separated list > 702 // of VALUES clauses. If it is a single VALUES clause then the > 703 // ValueList.pList field points to the expression list of that clause. > 704 // If it is a list of VALUES clauses, then those clauses are transformed > 705 // into a set of SELECT statements without FROM clauses and connected by > 706 // UNION ALL and the ValueList.pSelect points to the right-most SELECT in > 707 // that compound. > 708 %type valuelist {struct ValueList} > 709 %destructor valuelist { > 710 sqlite3ExprListDelete(pParse->db, $$.pList); > 711 sqlite3SelectDelete(pParse->db, $$.pSelect); 694 | 712 } 695 %type itemlist {ExprList*} < > 713 valuelist(A) ::= VALUES LP nexprlist(X) RP. { > 714 A.pList = X; > 715 A.pSelect = 0; > 716 } > 717 valuelist(A) ::= valuelist(X) COMMA LP exprlist(Y) RP. { > 718 Select *pRight = sqlite3SelectNew(pParse, Y, 0, 0, 0, 0, 0, 0, 0, 0); > 719 if( X.pList ){ > 720 X.pSelect = sqlite3SelectNew(pParse, X.pList, 0, 0, 0, 0, 0, 0, 0, 0); > 721 X.pList = 0; > 722 } > 723 A.pList = 0; > 724 if( X.pSelect==0 || pRight==0 ){ 696 %destructor itemlist {sqlite3ExprListDelete(pParse->db, $$);} | 725 sqlite3SelectDelete(pParse->db, pRight); > 726 sqlite3SelectDelete(pParse->db, X.pSelect); > 727 A.pSelect = 0; > 728 }else{ > 729 pRight->op = TK_ALL; > 730 pRight->pPrior = X.pSelect; > 731 pRight->selFlags |= SF_Values; > 732 pRight->pPrior->selFlags |= SF_Values; > 733 A.pSelect = pRight; 697 | 734 } 698 itemlist(A) ::= itemlist(X) COMMA expr(Y). < 699 {A = sqlite3ExprListAppend(pParse,X,Y.pExpr);} < 700 itemlist(A) ::= expr(X). < 701 {A = sqlite3ExprListAppend(pParse,0,X.pExpr);} < > 735 } 702 736 703 %type inscollist_opt {IdList*} 737 %type inscollist_opt {IdList*} 704 %destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);} 738 %destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);} 705 %type inscollist {IdList*} 739 %type inscollist {IdList*} 706 %destructor inscollist {sqlite3IdListDelete(pParse->db, $$);} 740 %destructor inscollist {sqlite3IdListDelete(pParse->db, $$);} 707 741 708 inscollist_opt(A) ::= . {A = 0;} 742 inscollist_opt(A) ::= . {A = 0;} ................................................................................................................................................................................ 1257 // UPDATE 1291 // UPDATE 1258 trigger_cmd(A) ::= 1292 trigger_cmd(A) ::= 1259 UPDATE orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z). 1293 UPDATE orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z). 1260 { A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R); } 1294 { A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R); } 1261 1295 1262 // INSERT 1296 // INSERT 1263 trigger_cmd(A) ::= 1297 trigger_cmd(A) ::= 1264 insert_cmd(R) INTO trnm(X) inscollist_opt(F) VALUES LP itemlist(Y) RP. | 1298 insert_cmd(R) INTO trnm(X) inscollist_opt(F) valuelist(Y). 1265 {A = sqlite3TriggerInsertStep(pParse->db, &X, F, Y, 0, R);} | 1299 {A = sqlite3TriggerInsertStep(pParse->db, &X, F, Y.pList, Y.pSelect, R);} 1266 1300 1267 trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) inscollist_opt(F) select(S). 1301 trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) inscollist_opt(F) select(S). 1268 {A = sqlite3TriggerInsertStep(pParse->db, &X, F, 0, S, R);} 1302 {A = sqlite3TriggerInsertStep(pParse->db, &X, F, 0, S, R);} 1269 1303 1270 // DELETE 1304 // DELETE 1271 trigger_cmd(A) ::= DELETE FROM trnm(X) tridxby where_opt(Y). 1305 trigger_cmd(A) ::= DELETE FROM trnm(X) tridxby where_opt(Y). 1272 {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y);} 1306 {A = sqlite3TriggerDeleteStep(pParse->db, &X, Y);}
Changes to src/select.c
69 pNew = &standin; 69 pNew = &standin; 70 memset(pNew, 0, sizeof(*pNew)); 70 memset(pNew, 0, sizeof(*pNew)); 71 } 71 } 72 if( pEList==0 ){ 72 if( pEList==0 ){ 73 pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db,TK_ALL,0)); 73 pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db,TK_ALL,0)); 74 } 74 } 75 pNew->pEList = pEList; 75 pNew->pEList = pEList; > 76 if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc)); 76 pNew->pSrc = pSrc; 77 pNew->pSrc = pSrc; 77 pNew->pWhere = pWhere; 78 pNew->pWhere = pWhere; 78 pNew->pGroupBy = pGroupBy; 79 pNew->pGroupBy = pGroupBy; 79 pNew->pHaving = pHaving; 80 pNew->pHaving = pHaving; 80 pNew->pOrderBy = pOrderBy; 81 pNew->pOrderBy = pOrderBy; 81 pNew->selFlags = isDistinct ? SF_Distinct : 0; 82 pNew->selFlags = isDistinct ? SF_Distinct : 0; 82 pNew->op = TK_SELECT; 83 pNew->op = TK_SELECT; ................................................................................................................................................................................ 1607 } 1608 } 1608 1609 1609 /* Make sure all SELECTs in the statement have the same number of elements 1610 /* Make sure all SELECTs in the statement have the same number of elements 1610 ** in their result sets. 1611 ** in their result sets. 1611 */ 1612 */ 1612 assert( p->pEList && pPrior->pEList ); 1613 assert( p->pEList && pPrior->pEList ); 1613 if( p->pEList->nExpr!=pPrior->pEList->nExpr ){ 1614 if( p->pEList->nExpr!=pPrior->pEList->nExpr ){ > 1615 if( p->selFlags & SF_Values ){ > 1616 sqlite3ErrorMsg(pParse, "all VALUES must have the same number of terms"); > 1617 }else{ 1614 sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s" | 1618 sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s" 1615 " do not have the same number of result columns", selectOpName(p->op)); | 1619 " do not have the same number of result columns", selectOpName(p->op)); > 1620 } 1616 rc = 1; 1621 rc = 1; 1617 goto multi_select_end; 1622 goto multi_select_end; 1618 } 1623 } 1619 1624 1620 /* Compound SELECTs that have an ORDER BY clause are handled separately. 1625 /* Compound SELECTs that have an ORDER BY clause are handled separately. 1621 */ 1626 */ 1622 if( p->pOrderBy ){ 1627 if( p->pOrderBy ){
Changes to src/sqliteInt.h
2086 #define SF_Distinct 0x01 /* Output should be DISTINCT */ 2086 #define SF_Distinct 0x01 /* Output should be DISTINCT */ 2087 #define SF_Resolved 0x02 /* Identifiers have been resolved */ 2087 #define SF_Resolved 0x02 /* Identifiers have been resolved */ 2088 #define SF_Aggregate 0x04 /* Contains aggregate functions */ 2088 #define SF_Aggregate 0x04 /* Contains aggregate functions */ 2089 #define SF_UsesEphemeral 0x08 /* Uses the OpenEphemeral opcode */ 2089 #define SF_UsesEphemeral 0x08 /* Uses the OpenEphemeral opcode */ 2090 #define SF_Expanded 0x10 /* sqlite3SelectExpand() called on this */ 2090 #define SF_Expanded 0x10 /* sqlite3SelectExpand() called on this */ 2091 #define SF_HasTypeInfo 0x20 /* FROM subqueries have Table metadata */ 2091 #define SF_HasTypeInfo 0x20 /* FROM subqueries have Table metadata */ 2092 #define SF_UseSorter 0x40 /* Sort using a sorter */ 2092 #define SF_UseSorter 0x40 /* Sort using a sorter */ > 2093 #define SF_Values 0x80 /* Synthesized from VALUES clause */ 2093 2094 2094 2095 2095 /* 2096 /* 2096 ** The results of a select can be distributed in several ways. The 2097 ** The results of a select can be distributed in several ways. The 2097 ** "SRT" prefix means "SELECT Result Type". 2098 ** "SRT" prefix means "SELECT Result Type". 2098 */ 2099 */ 2099 #define SRT_Union 1 /* Store result as keys in an index */ 2100 #define SRT_Union 1 /* Store result as keys in an index */
Changes to test/insert.test
382 INSERT INTO t6 VALUES(2,2); 382 INSERT INTO t6 VALUES(2,2); 383 INSERT INTO t6 VALUES(3,3); 383 INSERT INTO t6 VALUES(3,3); 384 INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6; 384 INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6; 385 SELECT x, y FROM t6; 385 SELECT x, y FROM t6; 386 } 386 } 387 } {1 1 2 2 3 3 12 101 13 102 16 103} 387 } {1 1 2 2 3 3 12 101 13 102 16 103} 388 388 > 389 # Multiple VALUES clauses > 390 # > 391 do_test insert-10.1 { > 392 execsql { > 393 CREATE TABLE t10(a,b,c); > 394 INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); > 395 SELECT * FROM t10; > 396 } > 397 } {1 2 3 4 5 6 7 8 9} > 398 do_test insert-10.2 { > 399 catchsql { > 400 INSERT INTO t10 VALUES(11,12,13), (14,15); > 401 } > 402 } {1 {all VALUES must have the same number of terms}} > 403 389 integrity_check insert-99.0 404 integrity_check insert-99.0 390 405 391 finish_test 406 finish_test
Changes to test/trigger1.test
286 do_test trigger-3.5 { 286 do_test trigger-3.5 { 287 catchsql { 287 catchsql { 288 CREATE TEMP TABLE t2(x,y); 288 CREATE TEMP TABLE t2(x,y); 289 INSERT INTO t1 VALUES(1,2); 289 INSERT INTO t1 VALUES(1,2); 290 SELECT * FROM t2; 290 SELECT * FROM t2; 291 } 291 } 292 } {1 {no such table: main.t2}} 292 } {1 {no such table: main.t2}} 293 do_test trigger-3.6 { | 293 do_test trigger-3.6.1 { 294 catchsql { 294 catchsql { 295 DROP TRIGGER r1; 295 DROP TRIGGER r1; > 296 CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN > 297 INSERT INTO t2 VALUES(NEW.a,NEW.b), (NEW.b*100, NEW.a*100); > 298 END; > 299 INSERT INTO t1 VALUES(1,2); > 300 SELECT * FROM t2; > 301 } > 302 } {0 {1 2 200 100}} > 303 do_test trigger-3.6.2 { > 304 catchsql { > 305 DROP TRIGGER r1; > 306 DELETE FROM t1; > 307 DELETE FROM t2; 296 CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN 308 CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN 297 INSERT INTO t2 VALUES(NEW.a,NEW.b); 309 INSERT INTO t2 VALUES(NEW.a,NEW.b); 298 END; 310 END; 299 INSERT INTO t1 VALUES(1,2); 311 INSERT INTO t1 VALUES(1,2); 300 SELECT * FROM t2; 312 SELECT * FROM t2; 301 } 313 } 302 } {0 {1 2}} 314 } {0 {1 2}}