000001  # 2010 September 18
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # The majority of this file implements tests to verify that the "testable
000013  # statements" in the lang_insert.html document are correct.
000014  #
000015  # Also, it contains tests to verify the statements in (the very short)
000016  # lang_replace.html.
000017  #
000018  set testdir [file dirname $argv0]
000019  source $testdir/tester.tcl
000020  
000021  ifcapable !compound {
000022    finish_test
000023    return
000024  }
000025  
000026  # Organization of tests:
000027  #
000028  #   e_insert-0.*: Test the syntax diagram.
000029  #
000030  #   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
000031  #   
000032  #   e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".
000033  #
000034  #   e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".
000035  #
000036  #   e_insert-4.*: Test statements regarding the conflict clause.
000037  #
000038  #   e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES"
000039  #                 syntaxes do not work in trigger bodies.
000040  #
000041  
000042  do_execsql_test e_insert-0.0 {
000043    CREATE TABLE a1(a, b);
000044    CREATE TABLE a2(a, b, c DEFAULT 'xyz');
000045    CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
000046    CREATE TABLE a4(c UNIQUE, d);
000047  } {}
000048  
000049  proc do_insert_tests {args} {
000050    uplevel do_select_tests $args
000051  }
000052  
000053  # -- syntax diagram insert-stmt
000054  #
000055  do_insert_tests e_insert-0 {
000056       1  "INSERT             INTO a1 DEFAULT VALUES"                   {}
000057       2  "INSERT             INTO main.a1 DEFAULT VALUES"              {}
000058       3  "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES"              {}
000059       4  "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES"                   {}
000060       5  "INSERT OR ABORT    INTO main.a1 DEFAULT VALUES"              {}
000061       6  "INSERT OR ABORT    INTO a1 DEFAULT VALUES"                   {}
000062       7  "INSERT OR REPLACE  INTO main.a1 DEFAULT VALUES"              {}
000063       8  "INSERT OR REPLACE  INTO a1 DEFAULT VALUES"                   {}
000064       9  "INSERT OR FAIL     INTO main.a1 DEFAULT VALUES"              {}
000065      10  "INSERT OR FAIL     INTO a1 DEFAULT VALUES"                   {}
000066      11  "INSERT OR FAIL     INTO main.a1 DEFAULT VALUES"              {}
000067      12  "INSERT OR IGNORE   INTO a1 DEFAULT VALUES"                   {}
000068      13  "REPLACE            INTO a1 DEFAULT VALUES"                   {}
000069      14  "REPLACE            INTO main.a1 DEFAULT VALUES"              {}
000070      15  "INSERT             INTO a1      VALUES(1, 2)"                {}
000071      16  "INSERT             INTO main.a1 VALUES(1, 2)"                {}
000072      17  "INSERT OR ROLLBACK INTO main.a1 VALUES(1, 2)"                {}
000073      18  "INSERT OR ROLLBACK INTO a1      VALUES(1, 2)"                {}
000074      19  "INSERT OR ABORT    INTO main.a1 VALUES(1, 2)"                {}
000075      20  "INSERT OR ABORT    INTO a1      VALUES(1, 2)"                {}
000076      21  "INSERT OR REPLACE  INTO main.a1 VALUES(1, 2)"                {}
000077      22  "INSERT OR REPLACE  INTO a1      VALUES(1, 2)"                {}
000078      23  "INSERT OR FAIL     INTO main.a1 VALUES(1, 2)"                {}
000079      24  "INSERT OR FAIL     INTO a1      VALUES(1, 2)"                {}
000080      25  "INSERT OR FAIL     INTO main.a1 VALUES(1, 2)"                {}
000081      26  "INSERT OR IGNORE   INTO a1      VALUES(1, 2)"                {}
000082      27  "REPLACE            INTO a1      VALUES(1, 2)"                {}
000083      28  "REPLACE            INTO main.a1 VALUES(1, 2)"                {}
000084      29  "INSERT             INTO a1      (b, a) VALUES(1, 2)"         {}
000085      30  "INSERT             INTO main.a1 (b, a) VALUES(1, 2)"         {}
000086      31  "INSERT OR ROLLBACK INTO main.a1 (b, a) VALUES(1, 2)"         {}
000087      32  "INSERT OR ROLLBACK INTO a1      (b, a) VALUES(1, 2)"         {}
000088      33  "INSERT OR ABORT    INTO main.a1 (b, a) VALUES(1, 2)"         {}
000089      34  "INSERT OR ABORT    INTO a1      (b, a) VALUES(1, 2)"         {}
000090      35  "INSERT OR REPLACE  INTO main.a1 (b, a) VALUES(1, 2)"         {}
000091      36  "INSERT OR REPLACE  INTO a1      (b, a) VALUES(1, 2)"         {}
000092      37  "INSERT OR FAIL     INTO main.a1 (b, a) VALUES(1, 2)"         {}
000093      38  "INSERT OR FAIL     INTO a1      (b, a) VALUES(1, 2)"         {}
000094      39  "INSERT OR FAIL     INTO main.a1 (b, a) VALUES(1, 2)"         {}
000095      40  "INSERT OR IGNORE   INTO a1      (b, a) VALUES(1, 2)"         {}
000096      41  "REPLACE            INTO a1      (b, a) VALUES(1, 2)"         {}
000097      42  "REPLACE            INTO main.a1 (b, a) VALUES(1, 2)"         {}
000098      43  "INSERT             INTO a1      SELECT c, b FROM a2"         {}
000099      44  "INSERT             INTO main.a1 SELECT c, b FROM a2"         {}
000100      45  "INSERT OR ROLLBACK INTO main.a1 SELECT c, b FROM a2"         {}
000101      46  "INSERT OR ROLLBACK INTO a1      SELECT c, b FROM a2"         {}
000102      47  "INSERT OR ABORT    INTO main.a1 SELECT c, b FROM a2"         {}
000103      48  "INSERT OR ABORT    INTO a1      SELECT c, b FROM a2"         {}
000104      49  "INSERT OR REPLACE  INTO main.a1 SELECT c, b FROM a2"         {}
000105      50  "INSERT OR REPLACE  INTO a1      SELECT c, b FROM a2"         {}
000106      51  "INSERT OR FAIL     INTO main.a1 SELECT c, b FROM a2"         {}
000107      52  "INSERT OR FAIL     INTO a1      SELECT c, b FROM a2"         {}
000108      53  "INSERT OR FAIL     INTO main.a1 SELECT c, b FROM a2"         {}
000109      54  "INSERT OR IGNORE   INTO a1      SELECT c, b FROM a2"         {}
000110      55  "REPLACE            INTO a1      SELECT c, b FROM a2"         {}
000111      56  "REPLACE            INTO main.a1 SELECT c, b FROM a2"         {}
000112      57  "INSERT             INTO a1      (b, a) SELECT c, b FROM a2"  {}
000113      58  "INSERT             INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000114      59  "INSERT OR ROLLBACK INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000115      60  "INSERT OR ROLLBACK INTO a1      (b, a) SELECT c, b FROM a2"  {}
000116      61  "INSERT OR ABORT    INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000117      62  "INSERT OR ABORT    INTO a1      (b, a) SELECT c, b FROM a2"  {}
000118      63  "INSERT OR REPLACE  INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000119      64  "INSERT OR REPLACE  INTO a1      (b, a) SELECT c, b FROM a2"  {}
000120      65  "INSERT OR FAIL     INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000121      66  "INSERT OR FAIL     INTO a1      (b, a) SELECT c, b FROM a2"  {}
000122      67  "INSERT OR FAIL     INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000123      68  "INSERT OR IGNORE   INTO a1      (b, a) SELECT c, b FROM a2"  {}
000124      69  "REPLACE            INTO a1      (b, a) SELECT c, b FROM a2"  {}
000125      70  "REPLACE            INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
000126      71  "INSERT             INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000127      72  "INSERT             INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000128      73  "INSERT OR ROLLBACK INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000129      74  "INSERT OR ROLLBACK INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000130      75  "INSERT OR ABORT    INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000131      76  "INSERT OR ABORT    INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000132      77  "INSERT OR REPLACE  INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000133      78  "INSERT OR REPLACE  INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000134      79  "INSERT OR FAIL     INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000135      80  "INSERT OR FAIL     INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000136      81  "INSERT OR FAIL     INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000137      82  "INSERT OR IGNORE   INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000138      83  "REPLACE            INTO a1      (b, a) VALUES(1, 2),(3,4)"   {}
000139      84  "REPLACE            INTO main.a1 (b, a) VALUES(1, 2),(3,4)"   {}
000140  }
000141  
000142  delete_all_data
000143  
000144  # EVIDENCE-OF: R-21490-41092 The first form (with the "VALUES" keyword)
000145  # creates one or more new rows in an existing table.
000146  #
000147  do_insert_tests e_insert-1.1 {
000148      0    "SELECT count(*) FROM a2"           {0}
000149  
000150      1a   "INSERT INTO a2 VALUES(1, 2, 3)"    {}
000151      1b   "SELECT count(*) FROM a2"           {1}
000152  
000153      2a   "INSERT INTO a2(a, b) VALUES(1, 2)" {}
000154      2b   "SELECT count(*) FROM a2"           {2}
000155  
000156      3a   "INSERT INTO a2(a) VALUES(3),(4)"   {}
000157      3b   "SELECT count(*) FROM a2"           {4}
000158  }
000159  
000160  # EVIDENCE-OF: R-19218-01018 If the column-name list after table-name is
000161  # omitted then the number of values inserted into each row must be the
000162  # same as the number of columns in the table.
000163  #
000164  #   A test in the block above verifies that if the VALUES list has the
000165  #   correct number of columns (for table a2, 3 columns) works. So these
000166  #   tests just show that other values cause an error.
000167  #
000168  do_insert_tests e_insert-1.2 -error { 
000169    table %s has %d columns but %d values were supplied
000170  } {
000171      1    "INSERT INTO a2 VALUES(1)"         {a2 3 1}
000172      2    "INSERT INTO a2 VALUES(1,2)"       {a2 3 2}
000173      3    "INSERT INTO a2 VALUES(1,2,3,4)"   {a2 3 4}
000174      4    "INSERT INTO a2 VALUES(1,2,3,4,5)" {a2 3 5}
000175  }
000176  
000177  # EVIDENCE-OF: R-29730-42609 In this case the result of evaluating the
000178  # left-most expression from each term of the VALUES list is inserted
000179  # into the left-most column of each new row, and so forth for each
000180  # subsequent expression.
000181  #
000182  delete_all_data
000183  do_insert_tests e_insert-1.3 {
000184      1a   "INSERT INTO a2 VALUES(1, 2, 3)"    {}
000185      1b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {1 2 3}
000186  
000187      2a   "INSERT INTO a2 VALUES('abc', NULL, 3*3+1)"      {}
000188      2b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {abc {} 10}
000189  
000190      3a   "INSERT INTO a2 VALUES((SELECT count(*) FROM a2), 'x', 'y')" {}
000191      3b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {2 x y}
000192  }
000193  
000194  # EVIDENCE-OF: R-21115-58321 If a column-name list is specified, then
000195  # the number of values in each term of the VALUE list must match the
000196  # number of specified columns.
000197  #
000198  do_insert_tests e_insert-1.4 -error { 
000199    %d values for %d columns
000200  } {
000201      1    "INSERT INTO a2(a, b, c) VALUES(1)"         {1 3}
000202      2    "INSERT INTO a2(a, b, c) VALUES(1,2)"       {2 3}
000203      3    "INSERT INTO a2(a, b, c) VALUES(1,2,3,4)"   {4 3}
000204      4    "INSERT INTO a2(a, b, c) VALUES(1,2,3,4,5)" {5 3}
000205  
000206      5    "INSERT INTO a2(c, a) VALUES(1)"            {1 2}
000207      6    "INSERT INTO a2(c, a) VALUES(1,2,3)"        {3 2}
000208      7    "INSERT INTO a2(c, a) VALUES(1,2,3,4)"      {4 2}
000209      8    "INSERT INTO a2(c, a) VALUES(1,2,3,4,5)"    {5 2}
000210  }
000211  
000212  # EVIDENCE-OF: R-07016-26442 Each of the named columns of the new row is
000213  # populated with the results of evaluating the corresponding VALUES
000214  # expression.
000215  #
000216  # EVIDENCE-OF: R-12183-43719 Table columns that do not appear in the
000217  # column list are populated with the default column value (specified as
000218  # part of the CREATE TABLE statement), or with NULL if no default value
000219  # is specified.
000220  #
000221  delete_all_data
000222  do_insert_tests e_insert-1.5 {
000223      1a   "INSERT INTO a2(b, c) VALUES('b', 'c')"     {}
000224      1b   "SELECT * FROM a2"                          {{} b c}
000225  
000226      2a   "INSERT INTO a2(a, b) VALUES('a', 'b')"     {}
000227      2b   "SELECT * FROM a2"                          {{} b c  a b xyz}
000228  }
000229  
000230  # EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for
000231  # each row of data returned by executing the SELECT statement.
000232  #
000233  delete_all_data
000234  do_insert_tests e_insert-2.1 {
000235      0    "SELECT count(*) FROM a1"            {0}
000236  
000237      1a   "SELECT count(*) FROM (SELECT 1, 2)" {1}
000238      1b   "INSERT INTO a1 SELECT 1, 2"         {}
000239      1c   "SELECT count(*) FROM a1"            {1}
000240  
000241      2a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {1}
000242      2b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
000243      2c   "SELECT count(*) FROM a1"                              {2}
000244  
000245      3a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {2}
000246      3b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
000247      3c   "SELECT count(*) FROM a1"                              {4}
000248  
000249      4a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {4}
000250      4b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
000251      4c   "SELECT count(*) FROM a1"                              {8}
000252  
000253      4a   "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1}
000254      4b   "INSERT INTO a1 SELECT min(b), min(a) FROM a1"         {}
000255      4c   "SELECT count(*) FROM a1"                              {9}
000256  }
000257  
000258  
000259  # EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number
000260  # of columns in the result of the SELECT must be the same as the number
000261  # of items in the column-list.
000262  #
000263  do_insert_tests e_insert-2.2 -error {
000264    %d values for %d columns
000265  } {
000266      1    "INSERT INTO a3(x, y) SELECT a, b, c FROM a2"            {3 2}
000267      2    "INSERT INTO a3(x, y) SELECT * FROM a2"                  {3 2}
000268      3    "INSERT INTO a3(x, y) SELECT * FROM a2 CROSS JOIN a1"    {5 2}
000269      4    "INSERT INTO a3(x, y) SELECT * FROM a2 NATURAL JOIN a1"  {3 2}
000270      5    "INSERT INTO a3(x, y) SELECT a2.a FROM a2,a1"            {1 2}
000271  
000272      6    "INSERT INTO a3(z) SELECT a, b, c FROM a2"               {3 1}
000273      7    "INSERT INTO a3(z) SELECT * FROM a2"                     {3 1}
000274      8    "INSERT INTO a3(z) SELECT * FROM a2 CROSS JOIN a1"       {5 1}
000275      9    "INSERT INTO a3(z) SELECT * FROM a2 NATURAL JOIN a1"     {3 1}
000276      10   "INSERT INTO a3(z) SELECT a1.* FROM a2,a1"               {2 1}
000277  }
000278  
000279  # EVIDENCE-OF: R-58951-07798 Otherwise, if no column-list is specified,
000280  # the number of columns in the result of the SELECT must be the same as
000281  # the number of columns in the table.
000282  #
000283  do_insert_tests e_insert-2.3 -error {
000284    table %s has %d columns but %d values were supplied
000285  } {
000286      1    "INSERT INTO a1 SELECT a, b, c FROM a2"            {a1 2 3}
000287      2    "INSERT INTO a1 SELECT * FROM a2"                  {a1 2 3}
000288      3    "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1"    {a1 2 5}
000289      4    "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1"  {a1 2 3}
000290      5    "INSERT INTO a1 SELECT a2.a FROM a2,a1"            {a1 2 1}
000291  }
000292  
000293  # EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound
000294  # SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may
000295  # be used in an INSERT statement of this form.
000296  #
000297  delete_all_data
000298  do_execsql_test e_insert-2.3.0 {
000299    INSERT INTO a1 VALUES('x', 'y');
000300  } {}
000301  do_insert_tests e_insert-2.3 {
000302    1  "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {}
000303    2  "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1"                         {}
000304    3  "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1"         {}
000305    4  "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a"                         {}
000306    S  "SELECT * FROM a1" {
000307        x y 
000308        x y y x
000309        y x
000310        ax by ay bx 
000311        ay bx ax by y x y x x y x y
000312    }
000313  }
000314  
000315  # EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement
000316  # inserts a single new row into the named table.
000317  #
000318  delete_all_data
000319  do_insert_tests e_insert-3.1 {
000320      1    "SELECT count(*) FROM a3"           {0}
000321      2a   "INSERT INTO a3 DEFAULT VALUES"     {}
000322      2b   "SELECT count(*) FROM a3"           {1}
000323  }
000324  
000325  # EVIDENCE-OF: R-18927-01951 Each column of the new row is populated
000326  # with its default value, or with a NULL if no default value is
000327  # specified as part of the column definition in the CREATE TABLE
000328  # statement.
000329  #
000330  delete_all_data
000331  do_insert_tests e_insert-3.2 {
000332      1.1    "INSERT INTO a3 DEFAULT VALUES"     {}
000333      1.2    "SELECT * FROM a3"                  {1.0 string {}}
000334  
000335      2.1    "INSERT INTO a3 DEFAULT VALUES"     {}
000336      2.2    "SELECT * FROM a3"                  {1.0 string {} 1.0 string {}}
000337  
000338      3.1    "INSERT INTO a2 DEFAULT VALUES"     {}
000339      3.2    "SELECT * FROM a2"                  {{} {} xyz}
000340  
000341      4.1    "INSERT INTO a2 DEFAULT VALUES"     {}
000342      4.2    "SELECT * FROM a2"                  {{} {} xyz {} {} xyz}
000343  
000344      5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
000345      5.2    "SELECT * FROM a1"                  {{} {}}
000346  
000347      6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
000348      6.2    "SELECT * FROM a1"                  {{} {} {} {}}
000349  }
000350  
000351  # EVIDENCE-OF: R-00267-47727 The initial "INSERT" keyword can be
000352  # replaced by "REPLACE" or "INSERT OR action" to specify an alternative
000353  # constraint conflict resolution algorithm to use during that one INSERT
000354  # command.
000355  #
000356  # EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
000357  # keyword REPLACE as an alias for "INSERT OR REPLACE".
000358  #
000359  #    The two requirements above are tested by e_select-4.1.* and
000360  #    e_select-4.2.*, respectively.
000361  #
000362  # EVIDENCE-OF: R-03421-22330 The REPLACE command is an alias for the
000363  # "INSERT OR REPLACE" variant of the INSERT command.
000364  #
000365  #    This is a dup of R-23110-47146. Therefore it is also verified 
000366  #    by e_select-4.2.*. This requirement is the only one from
000367  #    lang_replace.html.
000368  #
000369  do_execsql_test e_insert-4.1.0 {
000370    INSERT INTO a4 VALUES(1, 'a');
000371    INSERT INTO a4 VALUES(2, 'a');
000372    INSERT INTO a4 VALUES(3, 'a');
000373  } {}
000374  foreach {tn sql error ac data } {
000375    1.1  "INSERT INTO a4 VALUES(2,'b')"  {UNIQUE constraint failed: a4.c}  1 {1 a 2 a 3 a}
000376    1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
000377    1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
000378    1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
000379    1.5  "INSERT INTO a4 VALUES(1, 'd')" {UNIQUE constraint failed: a4.c}  0 {1 a 3 a 2 b}
000380    1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
000381          {UNIQUE constraint failed: a4.c}  0 {1 a 3 a 2 b}
000382    1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
000383          {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b}
000384    1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
000385          {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b}
000386    1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
000387          {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b 4 e}
000388  
000389    2.1  "INSERT INTO a4 VALUES(2,'f')"  
000390          {UNIQUE constraint failed: a4.c}  1 {1 a 3 a 2 b 4 e}
000391    2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {1 a 3 a 4 e 2 f}
000392  } {
000393    do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
000394    do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
000395    do_test          e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac
000396  }
000397  
000398  # EVIDENCE-OF: R-59829-49719 The optional "schema-name." prefix on the
000399  # table-name is supported for top-level INSERT statements only.
000400  #
000401  # EVIDENCE-OF: R-05731-00924 The table name must be unqualified for
000402  # INSERT statements that occur within CREATE TRIGGER statements.
000403  #
000404  set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
000405  
000406  do_catchsql_test e_insert-5.1.1 {
000407    CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
000408      INSERT INTO main.a4 VALUES(new.a, new.b);
000409    END;
000410  } $err
000411  do_catchsql_test e_insert-5.1.2 {
000412    CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b);
000413    CREATE TRIGGER AFTER DELETE ON a3 BEGIN
000414      INSERT INTO temp.tmptable VALUES(1, 2);
000415    END;
000416  } $err
000417  
000418  # EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the
000419  # INSERT statement is supported for top-level INSERT statements only and
000420  # not for INSERT statements within triggers.
000421  #
000422  do_catchsql_test e_insert-5.2.1 {
000423    CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
000424      INSERT INTO a4 DEFAULT VALUES;
000425    END;
000426  } {1 {near "DEFAULT": syntax error}}
000427  
000428  
000429  delete_all_data
000430  
000431  finish_test