SQLite4
Check-in [4ea88c4882]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add support for multiple VALUES in a single INSERT statement.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4ea88c4882f9894c28e79c997e70caccddb3635c
User & Date: drh 2013-01-18 23:15:05
Context
2013-01-19
02:16
Fix for queries that use and inequality on ROWID in the WHERE clause. check-in: 32de931aa1 user: drh tags: trunk
2013-01-18
23:15
Add support for multiple VALUES in a single INSERT statement. check-in: 4ea88c4882 user: drh tags: trunk
20:43
Remove obsolete shell test scripts from the tool/ subdirectory. check-in: 51b9711376 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

89
90
91
92
93
94
95








96
97
98
99
100
101
102
...
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693











694
695












696








697
698
699
700
701

702
703
704
705
706
707
708
....
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
....
1399
1400
1401
1402
1403
1404
1405
1406
*/
struct TrigEvent { int a; IdList * b; };

/*
** An instance of this structure holds the ATTACH key and the key type.
*/
struct AttachKey { int type;  Token key; };









} // end %include

// Input is a single SQL command
input ::= cmdlist.
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
................................................................................
setlist(A) ::= nm(X) EQ expr(Y). {
  A = sqlite4ExprListAppend(pParse, 0, Y.pExpr);
  sqlite4ExprListSetName(pParse, A, &X, 1);
}

////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) 
        VALUES LP itemlist(Y) RP.
            {sqlite4Insert(pParse, X, Y, 0, F, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
            {sqlite4Insert(pParse, X, 0, S, F, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
            {sqlite4Insert(pParse, X, 0, 0, F, R);}

%type insert_cmd {u8}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}













%type itemlist {ExprList*}












%destructor itemlist {sqlite4ExprListDelete(pParse->db, $$);}









itemlist(A) ::= itemlist(X) COMMA expr(Y).
    {A = sqlite4ExprListAppend(pParse,X,Y.pExpr);}
itemlist(A) ::= expr(X).
    {A = sqlite4ExprListAppend(pParse,0,X.pExpr);}


%type inscollist_opt {IdList*}
%destructor inscollist_opt {sqlite4IdListDelete(pParse->db, $$);}
%type inscollist {IdList*}
%destructor inscollist {sqlite4IdListDelete(pParse->db, $$);}

inscollist_opt(A) ::= .                       {A = 0;}
................................................................................
// UPDATE 
trigger_cmd(A) ::=
   UPDATE orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z).  
   { A = sqlite4TriggerUpdateStep(pParse->db, &X, Y, Z, R); }

// INSERT
trigger_cmd(A) ::=
   insert_cmd(R) INTO trnm(X) inscollist_opt(F) VALUES LP itemlist(Y) RP.  
   {A = sqlite4TriggerInsertStep(pParse->db, &X, F, Y, 0, R);}

trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) inscollist_opt(F) select(S).
               {A = sqlite4TriggerInsertStep(pParse->db, &X, F, 0, S, R);}

// DELETE
trigger_cmd(A) ::= DELETE FROM trnm(X) tridxby where_opt(Y).
               {A = sqlite4TriggerDeleteStep(pParse->db, &X, Y);}
................................................................................
vtabargtoken ::= ANY(X).            {sqlite4VtabArgExtend(pParse,&X);}
vtabargtoken ::= lp anylist RP(X).  {sqlite4VtabArgExtend(pParse,&X);}
lp ::= LP(X).                       {sqlite4VtabArgExtend(pParse,&X);}
anylist ::= .
anylist ::= anylist LP anylist RP.
anylist ::= anylist ANY.
%endif  SQLITE4_OMIT_VIRTUALTABLE








>
>
>
>
>
>
>
>







 







|
<
|









>
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
|
<
<
<
<
>







 







|
|







 







<
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
...
683
684
685
686
687
688
689
690

691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712

713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734




735
736
737
738
739
740
741
742
....
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
....
1433
1434
1435
1436
1437
1438
1439

*/
struct TrigEvent { int a; IdList * b; };

/*
** An instance of this structure holds the ATTACH key and the key type.
*/
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
input ::= cmdlist.
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
................................................................................
setlist(A) ::= nm(X) EQ expr(Y). {
  A = sqlite4ExprListAppend(pParse, 0, Y.pExpr);
  sqlite4ExprListSetName(pParse, A, &X, 1);
}

////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) valuelist(Y).

            {sqlite4Insert(pParse, X, Y.pList, Y.pSelect, F, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
            {sqlite4Insert(pParse, X, 0, S, F, R);}
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
            {sqlite4Insert(pParse, X, 0, 0, F, R);}

%type insert_cmd {u8}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

// 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 {
  sqlite4ExprListDelete(pParse->db, $$.pList);
  sqlite4SelectDelete(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 = sqlite4SelectNew(pParse, Y, 0, 0, 0, 0, 0, 0, 0, 0);
  if( X.pList ){
    X.pSelect = sqlite4SelectNew(pParse, X.pList, 0, 0, 0, 0, 0, 0, 0, 0);
    X.pList = 0;
  }
  A.pList = 0;
  if( X.pSelect==0 || pRight==0 ){
    sqlite4SelectDelete(pParse->db, pRight);
    sqlite4SelectDelete(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 {sqlite4IdListDelete(pParse->db, $$);}
%type inscollist {IdList*}
%destructor inscollist {sqlite4IdListDelete(pParse->db, $$);}

inscollist_opt(A) ::= .                       {A = 0;}
................................................................................
// UPDATE 
trigger_cmd(A) ::=
   UPDATE orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z).  
   { A = sqlite4TriggerUpdateStep(pParse->db, &X, Y, Z, R); }

// INSERT
trigger_cmd(A) ::=
   insert_cmd(R) INTO trnm(X) inscollist_opt(F) valuelist(Y).
   {A = sqlite4TriggerInsertStep(pParse->db, &X, F, Y.pList, Y.pSelect, R);}

trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) inscollist_opt(F) select(S).
               {A = sqlite4TriggerInsertStep(pParse->db, &X, F, 0, S, R);}

// DELETE
trigger_cmd(A) ::= DELETE FROM trnm(X) tridxby where_opt(Y).
               {A = sqlite4TriggerDeleteStep(pParse->db, &X, Y);}
................................................................................
vtabargtoken ::= ANY(X).            {sqlite4VtabArgExtend(pParse,&X);}
vtabargtoken ::= lp anylist RP(X).  {sqlite4VtabArgExtend(pParse,&X);}
lp ::= LP(X).                       {sqlite4VtabArgExtend(pParse,&X);}
anylist ::= .
anylist ::= anylist LP anylist RP.
anylist ::= anylist ANY.
%endif  SQLITE4_OMIT_VIRTUALTABLE

Changes to src/select.c.

69
70
71
72
73
74
75

76
77
78
79
80
81
82
....
1623
1624
1625
1626
1627
1628
1629



1630
1631

1632
1633
1634
1635
1636
1637
1638
    pNew = &standin;
    memset(pNew, 0, sizeof(*pNew));
  }
  if( pEList==0 ){
    pEList = sqlite4ExprListAppend(pParse, 0, sqlite4Expr(db,TK_ALL,0));
  }
  pNew->pEList = pEList;

  pNew->pSrc = pSrc;
  pNew->pWhere = pWhere;
  pNew->pGroupBy = pGroupBy;
  pNew->pHaving = pHaving;
  pNew->pOrderBy = pOrderBy;
  pNew->selFlags = isDistinct ? SF_Distinct : 0;
  pNew->op = TK_SELECT;
................................................................................
  }

  /* Make sure all SELECTs in the statement have the same number of elements
  ** in their result sets.
  */
  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){



    sqlite4ErrorMsg(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;
  }

  /* Compound SELECTs that have an ORDER BY clause are handled separately.
  */
  if( p->pOrderBy ){







>







 







>
>
>
|
|
>







69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
....
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
    pNew = &standin;
    memset(pNew, 0, sizeof(*pNew));
  }
  if( pEList==0 ){
    pEList = sqlite4ExprListAppend(pParse, 0, sqlite4Expr(db,TK_ALL,0));
  }
  pNew->pEList = pEList;
  if( pSrc==0 ) pSrc = sqlite4DbMallocZero(db, sizeof(*pSrc));
  pNew->pSrc = pSrc;
  pNew->pWhere = pWhere;
  pNew->pGroupBy = pGroupBy;
  pNew->pHaving = pHaving;
  pNew->pOrderBy = pOrderBy;
  pNew->selFlags = isDistinct ? SF_Distinct : 0;
  pNew->op = TK_SELECT;
................................................................................
  }

  /* Make sure all SELECTs in the statement have the same number of elements
  ** in their result sets.
  */
  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
    if( p->selFlags & SF_Values ){
      sqlite4ErrorMsg(pParse, "all VALUES must have the same number of terms");
    }else{
      sqlite4ErrorMsg(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;
  }

  /* Compound SELECTs that have an ORDER BY clause are handled separately.
  */
  if( p->pOrderBy ){

Changes to src/sqliteInt.h.

2065
2066
2067
2068
2069
2070
2071

2072
2073
2074
2075
2076
2077
2078
#define SF_Distinct        0x01  /* Output should be DISTINCT */
#define SF_Resolved        0x02  /* Identifiers have been resolved */
#define SF_Aggregate       0x04  /* Contains aggregate functions */
#define SF_UsesEphemeral   0x08  /* Uses the OpenEphemeral opcode */
#define SF_Expanded        0x10  /* sqlite4SelectExpand() called on this */
#define SF_HasTypeInfo     0x20  /* FROM subqueries have Table metadata */
#define SF_UseSorter       0x40  /* Sort using a sorter */



/*
** The results of a select can be distributed in several ways.  The
** "SRT" prefix means "SELECT Result Type".
*/
#define SRT_Union        1  /* Store result as keys in an index */







>







2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
#define SF_Distinct        0x01  /* Output should be DISTINCT */
#define SF_Resolved        0x02  /* Identifiers have been resolved */
#define SF_Aggregate       0x04  /* Contains aggregate functions */
#define SF_UsesEphemeral   0x08  /* Uses the OpenEphemeral opcode */
#define SF_Expanded        0x10  /* sqlite4SelectExpand() 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 */


/*
** The results of a select can be distributed in several ways.  The
** "SRT" prefix means "SELECT Result Type".
*/
#define SRT_Union        1  /* Store result as keys in an index */

Changes to test/insert.test.

358
359
360
361
362
363
364















365
366
367
  do_test insert-8.1 {
    execsql {
      INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)
    }
  } {}
}
















integrity_check insert-99.0

finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
  do_test insert-8.1 {
    execsql {
      INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)
    }
  } {}
}

# 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

Changes to test/trigger1.test.

286
287
288
289
290
291
292
293
294
295












296
297
298
299
300
301
302
  do_test trigger-3.5 {
    catchsql {
      CREATE TEMP TABLE t2(x,y);
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {1 {no such table: main.t2}}
  do_test trigger-3.6 {
    catchsql {
      DROP TRIGGER r1;












      CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
        INSERT INTO t2 VALUES(NEW.a,NEW.b);
      END;
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {0 {1 2}}







|


>
>
>
>
>
>
>
>
>
>
>
>







286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
  do_test trigger-3.5 {
    catchsql {
      CREATE TEMP TABLE t2(x,y);
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {1 {no such table: main.t2}}
  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;
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {0 {1 2}}