src/third_party/sqlite/src/test/e_insert.test

   1 # 2010 September 18
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # The majority of this file implements tests to verify that the "testable
  13 # statements" in the lang_insert.html document are correct.
  14 #
  15 # Also, it contains tests to verify the statements in (the very short)
  16 # lang_replace.html.
  17 #
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20
  21 # Organization of tests:
  22 #
  23 #   e_insert-0.*: Test the syntax diagram.
  24 #
  25 #   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
  26 #
  27 #   e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".
  28 #
  29 #   e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".
  30 #
  31 #   e_insert-4.*: Test statements regarding the conflict clause.
  32 #
  33 #   e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES"
  34 #                 syntaxes do not work in trigger bodies.
  35 #
  36
  37 do_execsql_test e_insert-0.0 {
  38   CREATE TABLE a1(a, b);
  39   CREATE TABLE a2(a, b, c DEFAULT 'xyz');
  40   CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
  41   CREATE TABLE a4(c UNIQUE, d);
  42 } {}
  43
  44 proc do_insert_tests {args} {
  45   uplevel do_select_tests $args
  46 }
  47
  48 # EVIDENCE-OF: R-41448-54465 -- syntax diagram insert-stmt
  49 #
  50 do_insert_tests e_insert-0 {
  51      1  "INSERT             INTO a1 DEFAULT VALUES"                   {}
  52      2  "INSERT             INTO main.a1 DEFAULT VALUES"              {}
  53      3  "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES"              {}
  54      4  "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES"                   {}
  55      5  "INSERT OR ABORT    INTO main.a1 DEFAULT VALUES"              {}
  56      6  "INSERT OR ABORT    INTO a1 DEFAULT VALUES"                   {}
  57      7  "INSERT OR REPLACE  INTO main.a1 DEFAULT VALUES"              {}
  58      8  "INSERT OR REPLACE  INTO a1 DEFAULT VALUES"                   {}
  59      9  "INSERT OR FAIL     INTO main.a1 DEFAULT VALUES"              {}
  60     10  "INSERT OR FAIL     INTO a1 DEFAULT VALUES"                   {}
  61     11  "INSERT OR FAIL     INTO main.a1 DEFAULT VALUES"              {}
  62     12  "INSERT OR IGNORE   INTO a1 DEFAULT VALUES"                   {}
  63     13  "REPLACE            INTO a1 DEFAULT VALUES"                   {}
  64     14  "REPLACE            INTO main.a1 DEFAULT VALUES"              {}
  65     15  "INSERT             INTO a1      VALUES(1, 2)"                {}
  66     16  "INSERT             INTO main.a1 VALUES(1, 2)"                {}
  67     17  "INSERT OR ROLLBACK INTO main.a1 VALUES(1, 2)"                {}
  68     18  "INSERT OR ROLLBACK INTO a1      VALUES(1, 2)"                {}
  69     19  "INSERT OR ABORT    INTO main.a1 VALUES(1, 2)"                {}
  70     20  "INSERT OR ABORT    INTO a1      VALUES(1, 2)"                {}
  71     21  "INSERT OR REPLACE  INTO main.a1 VALUES(1, 2)"                {}
  72     22  "INSERT OR REPLACE  INTO a1      VALUES(1, 2)"                {}
  73     23  "INSERT OR FAIL     INTO main.a1 VALUES(1, 2)"                {}
  74     24  "INSERT OR FAIL     INTO a1      VALUES(1, 2)"                {}
  75     25  "INSERT OR FAIL     INTO main.a1 VALUES(1, 2)"                {}
  76     26  "INSERT OR IGNORE   INTO a1      VALUES(1, 2)"                {}
  77     27  "REPLACE            INTO a1      VALUES(1, 2)"                {}
  78     28  "REPLACE            INTO main.a1 VALUES(1, 2)"                {}
  79     29  "INSERT             INTO a1      (b, a) VALUES(1, 2)"         {}
  80     30  "INSERT             INTO main.a1 (b, a) VALUES(1, 2)"         {}
  81     31  "INSERT OR ROLLBACK INTO main.a1 (b, a) VALUES(1, 2)"         {}
  82     32  "INSERT OR ROLLBACK INTO a1      (b, a) VALUES(1, 2)"         {}
  83     33  "INSERT OR ABORT    INTO main.a1 (b, a) VALUES(1, 2)"         {}
  84     34  "INSERT OR ABORT    INTO a1      (b, a) VALUES(1, 2)"         {}
  85     35  "INSERT OR REPLACE  INTO main.a1 (b, a) VALUES(1, 2)"         {}
  86     36  "INSERT OR REPLACE  INTO a1      (b, a) VALUES(1, 2)"         {}
  87     37  "INSERT OR FAIL     INTO main.a1 (b, a) VALUES(1, 2)"         {}
  88     38  "INSERT OR FAIL     INTO a1      (b, a) VALUES(1, 2)"         {}
  89     39  "INSERT OR FAIL     INTO main.a1 (b, a) VALUES(1, 2)"         {}
  90     40  "INSERT OR IGNORE   INTO a1      (b, a) VALUES(1, 2)"         {}
  91     41  "REPLACE            INTO a1      (b, a) VALUES(1, 2)"         {}
  92     42  "REPLACE            INTO main.a1 (b, a) VALUES(1, 2)"         {}
  93     43  "INSERT             INTO a1      SELECT c, b FROM a2"         {}
  94     44  "INSERT             INTO main.a1 SELECT c, b FROM a2"         {}
  95     45  "INSERT OR ROLLBACK INTO main.a1 SELECT c, b FROM a2"         {}
  96     46  "INSERT OR ROLLBACK INTO a1      SELECT c, b FROM a2"         {}
  97     47  "INSERT OR ABORT    INTO main.a1 SELECT c, b FROM a2"         {}
  98     48  "INSERT OR ABORT    INTO a1      SELECT c, b FROM a2"         {}
  99     49  "INSERT OR REPLACE  INTO main.a1 SELECT c, b FROM a2"         {}
 100     50  "INSERT OR REPLACE  INTO a1      SELECT c, b FROM a2"         {}
 101     51  "INSERT OR FAIL     INTO main.a1 SELECT c, b FROM a2"         {}
 102     52  "INSERT OR FAIL     INTO a1      SELECT c, b FROM a2"         {}
 103     53  "INSERT OR FAIL     INTO main.a1 SELECT c, b FROM a2"         {}
 104     54  "INSERT OR IGNORE   INTO a1      SELECT c, b FROM a2"         {}
 105     55  "REPLACE            INTO a1      SELECT c, b FROM a2"         {}
 106     56  "REPLACE            INTO main.a1 SELECT c, b FROM a2"         {}
 107     57  "INSERT             INTO a1      (b, a) SELECT c, b FROM a2"  {}
 108     58  "INSERT             INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 109     59  "INSERT OR ROLLBACK INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 110     60  "INSERT OR ROLLBACK INTO a1      (b, a) SELECT c, b FROM a2"  {}
 111     61  "INSERT OR ABORT    INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 112     62  "INSERT OR ABORT    INTO a1      (b, a) SELECT c, b FROM a2"  {}
 113     63  "INSERT OR REPLACE  INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 114     64  "INSERT OR REPLACE  INTO a1      (b, a) SELECT c, b FROM a2"  {}
 115     65  "INSERT OR FAIL     INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 116     66  "INSERT OR FAIL     INTO a1      (b, a) SELECT c, b FROM a2"  {}
 117     67  "INSERT OR FAIL     INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 118     68  "INSERT OR IGNORE   INTO a1      (b, a) SELECT c, b FROM a2"  {}
 119     69  "REPLACE            INTO a1      (b, a) SELECT c, b FROM a2"  {}
 120     70  "REPLACE            INTO main.a1 (b, a) SELECT c, b FROM a2"  {}
 121 }
 122
 123 delete_all_data
 124
 125 # EVIDENCE-OF: R-20288-20462 The first form (with the "VALUES" keyword)
 126 # creates a single new row in an existing table.
 127 #
 128 do_insert_tests e_insert-1.1 {
 129     0    "SELECT count(*) FROM a2"           {0}
 130
 131     1a   "INSERT INTO a2 VALUES(1, 2, 3)"    {}
 132     1b   "SELECT count(*) FROM a2"           {1}
 133
 134     2a   "INSERT INTO a2(a, b) VALUES(1, 2)" {}
 135     2b   "SELECT count(*) FROM a2"           {2}
 136 }
 137
 138 # EVIDENCE-OF: R-36040-20870 If no column-list is specified then the
 139 # number of values must be the same as the number of columns in the
 140 # table.
 141 #
 142 #   A test in the block above verifies that if the VALUES list has the
 143 #   correct number of columns (for table a2, 3 columns) works. So these
 144 #   tests just show that other values cause an error.
 145 #
 146 do_insert_tests e_insert-1.2 -error {
 147   table %s has %d columns but %d values were supplied
 148 } {
 149     1    "INSERT INTO a2 VALUES(1)"         {a2 3 1}
 150     2    "INSERT INTO a2 VALUES(1,2)"       {a2 3 2}
 151     3    "INSERT INTO a2 VALUES(1,2,3,4)"   {a2 3 4}
 152     4    "INSERT INTO a2 VALUES(1,2,3,4,5)" {a2 3 5}
 153 }
 154
 155 # EVIDENCE-OF: R-52422-65517 In this case the result of evaluting the
 156 # left-most expression in the VALUES list is inserted into the left-most
 157 # column of the new row, and so on.
 158 #
 159 delete_all_data
 160 do_insert_tests e_insert-1.3 {
 161     1a   "INSERT INTO a2 VALUES(1, 2, 3)"    {}
 162     1b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {1 2 3}
 163
 164     2a   "INSERT INTO a2 VALUES('abc', NULL, 3*3+1)"      {}
 165     2b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {abc {} 10}
 166
 167     3a   "INSERT INTO a2 VALUES((SELECT count(*) FROM a2), 'x', 'y')" {}
 168     3b   "SELECT * FROM a2 WHERE oid=last_insert_rowid()" {2 x y}
 169 }
 170
 171 # EVIDENCE-OF: R-62524-00361 If a column-list is specified, then the
 172 # number of values must match the number of specified columns.
 173 #
 174 do_insert_tests e_insert-1.4 -error {
 175   %d values for %d columns
 176 } {
 177     1    "INSERT INTO a2(a, b, c) VALUES(1)"         {1 3}
 178     2    "INSERT INTO a2(a, b, c) VALUES(1,2)"       {2 3}
 179     3    "INSERT INTO a2(a, b, c) VALUES(1,2,3,4)"   {4 3}
 180     4    "INSERT INTO a2(a, b, c) VALUES(1,2,3,4,5)" {5 3}
 181
 182     5    "INSERT INTO a2(c, a) VALUES(1)"            {1 2}
 183     6    "INSERT INTO a2(c, a) VALUES(1,2,3)"        {3 2}
 184     7    "INSERT INTO a2(c, a) VALUES(1,2,3,4)"      {4 2}
 185     8    "INSERT INTO a2(c, a) VALUES(1,2,3,4,5)"    {5 2}
 186 }
 187
 188 # EVIDENCE-OF: R-07016-26442 Each of the named columns of the new row is
 189 # populated with the results of evaluating the corresponding VALUES
 190 # expression.
 191 #
 192 # EVIDENCE-OF: R-12183-43719 Table columns that do not appear in the
 193 # column list are populated with the default column value (specified as
 194 # part of the CREATE TABLE statement), or with NULL if no default value
 195 # is specified.
 196 #
 197 delete_all_data
 198 do_insert_tests e_insert-1.5 {
 199     1a   "INSERT INTO a2(b, c) VALUES('b', 'c')"     {}
 200     1b   "SELECT * FROM a2"                          {{} b c}
 201
 202     2a   "INSERT INTO a2(a, b) VALUES('a', 'b')"     {}
 203     2b   "SELECT * FROM a2"                          {{} b c  a b xyz}
 204 }
 205
 206 # EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for
 207 # each row of data returned by executing the SELECT statement.
 208 #
 209 delete_all_data
 210 do_insert_tests e_insert-2.1 {
 211     0    "SELECT count(*) FROM a1"            {0}
 212
 213     1a   "SELECT count(*) FROM (SELECT 1, 2)" {1}
 214     1b   "INSERT INTO a1 SELECT 1, 2"         {}
 215     1c   "SELECT count(*) FROM a1"            {1}
 216
 217     2a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {1}
 218     2b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
 219     2c   "SELECT count(*) FROM a1"                              {2}
 220
 221     3a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {2}
 222     3b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
 223     3c   "SELECT count(*) FROM a1"                              {4}
 224
 225     4a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {4}
 226     4b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
 227     4c   "SELECT count(*) FROM a1"                              {8}
 228
 229     4a   "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1}
 230     4b   "INSERT INTO a1 SELECT min(b), min(a) FROM a1"         {}
 231     4c   "SELECT count(*) FROM a1"                              {9}
 232 }
 233
 234
 235 # EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number
 236 # of columns in the result of the SELECT must be the same as the number
 237 # of items in the column-list.
 238 #
 239 do_insert_tests e_insert-2.2 -error {
 240   %d values for %d columns
 241 } {
 242     1    "INSERT INTO a3(x, y) SELECT a, b, c FROM a2"            {3 2}
 243     2    "INSERT INTO a3(x, y) SELECT * FROM a2"                  {3 2}
 244     3    "INSERT INTO a3(x, y) SELECT * FROM a2 CROSS JOIN a1"    {5 2}
 245     4    "INSERT INTO a3(x, y) SELECT * FROM a2 NATURAL JOIN a1"  {3 2}
 246     5    "INSERT INTO a3(x, y) SELECT a2.a FROM a2,a1"            {1 2}
 247
 248     6    "INSERT INTO a3(z) SELECT a, b, c FROM a2"               {3 1}
 249     7    "INSERT INTO a3(z) SELECT * FROM a2"                     {3 1}
 250     8    "INSERT INTO a3(z) SELECT * FROM a2 CROSS JOIN a1"       {5 1}
 251     9    "INSERT INTO a3(z) SELECT * FROM a2 NATURAL JOIN a1"     {3 1}
 252     10   "INSERT INTO a3(z) SELECT a1.* FROM a2,a1"               {2 1}
 253 }
 254
 255 # EVIDENCE-OF: R-58951-07798 Otherwise, if no column-list is specified,
 256 # the number of columns in the result of the SELECT must be the same as
 257 # the number of columns in the table.
 258 #
 259 do_insert_tests e_insert-2.3 -error {
 260   table %s has %d columns but %d values were supplied
 261 } {
 262     1    "INSERT INTO a1 SELECT a, b, c FROM a2"            {a1 2 3}
 263     2    "INSERT INTO a1 SELECT * FROM a2"                  {a1 2 3}
 264     3    "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1"    {a1 2 5}
 265     4    "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1"  {a1 2 3}
 266     5    "INSERT INTO a1 SELECT a2.a FROM a2,a1"            {a1 2 1}
 267 }
 268
 269 # EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound
 270 # SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may
 271 # be used in an INSERT statement of this form.
 272 #
 273 delete_all_data
 274 do_execsql_test e_insert-2.3.0 {
 275   INSERT INTO a1 VALUES('x', 'y');
 276 } {}
 277 do_insert_tests e_insert-2.3 {
 278   1  "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {}
 279   2  "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1"                         {}
 280   3  "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1"         {}
 281   4  "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a"                         {}
 282   S  "SELECT * FROM a1" {
 283       x y
 284       x y y x
 285       y x
 286       ax by ay bx
 287       ay bx ax by y x y x x y x y
 288   }
 289 }
 290
 291 # EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement
 292 # inserts a single new row into the named table.
 293 #
 294 delete_all_data
 295 do_insert_tests e_insert-3.1 {
 296     1    "SELECT count(*) FROM a3"           {0}
 297     2a   "INSERT INTO a3 DEFAULT VALUES"     {}
 298     2b   "SELECT count(*) FROM a3"           {1}
 299 }
 300
 301 # EVIDENCE-OF: R-18927-01951 Each column of the new row is populated
 302 # with its default value, or with a NULL if no default value is
 303 # specified as part of the column definition in the CREATE TABLE
 304 # statement.
 305 #
 306 delete_all_data
 307 do_insert_tests e_insert-3.2 {
 308     1.1    "INSERT INTO a3 DEFAULT VALUES"     {}
 309     1.2    "SELECT * FROM a3"                  {1.0 string {}}
 310
 311     2.1    "INSERT INTO a3 DEFAULT VALUES"     {}
 312     2.2    "SELECT * FROM a3"                  {1.0 string {} 1.0 string {}}
 313
 314     3.1    "INSERT INTO a2 DEFAULT VALUES"     {}
 315     3.2    "SELECT * FROM a2"                  {{} {} xyz}
 316
 317     4.1    "INSERT INTO a2 DEFAULT VALUES"     {}
 318     4.2    "SELECT * FROM a2"                  {{} {} xyz {} {} xyz}
 319
 320     5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
 321     5.2    "SELECT * FROM a1"                  {{} {}}
 322
 323     6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
 324     6.2    "SELECT * FROM a1"                  {{} {} {} {}}
 325 }
 326
 327 # EVIDENCE-OF: R-46928-50290 The optional conflict-clause allows the
 328 # specification of an alternative constraint conflict resolution
 329 # algorithm to use during this one INSERT command.
 330 #
 331 # EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
 332 # keyword REPLACE as an alias for "INSERT OR REPLACE".
 333 #
 334 #    The two requirements above are tested by e_select-4.1.* and
 335 #    e_select-4.2.*, respectively.
 336 #
 337 # EVIDENCE-OF: R-03421-22330 The REPLACE command is an alias for the
 338 # "INSERT OR REPLACE" variant of the INSERT command.
 339 #
 340 #    This is a dup of R-23110-47146. Therefore it is also verified
 341 #    by e_select-4.2.*. This requirement is the only one from
 342 #    lang_replace.html.
 343 #
 344 do_execsql_test e_insert-4.1.0 {
 345   INSERT INTO a4 VALUES(1, 'a');
 346   INSERT INTO a4 VALUES(2, 'a');
 347   INSERT INTO a4 VALUES(3, 'a');
 348 } {}
 349 foreach {tn sql error ac data } {
 350   1.1  "INSERT INTO a4 VALUES(2,'b')"  {column c is not unique}  1 {1 a 2 a 3 a}
 351   1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
 352   1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
 353   1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
 354   1.5  "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique}  0 {1 a 3 a 2 b}
 355   1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')"
 356         {column c is not unique}  0 {1 a 3 a 2 b}
 357   1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')"
 358         {column c is not unique}  1 {1 a 3 a 2 b}
 359   1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
 360         {column c is not unique}  1 {1 a 3 a 2 b}
 361   1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
 362         {column c is not unique}  1 {1 a 3 a 2 b 4 e}
 363
 364   2.1  "INSERT INTO a4 VALUES(2,'f')"
 365         {column c is not unique}  1 {1 a 3 a 2 b 4 e}
 366   2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {1 a 3 a 4 e 2 f}
 367 } {
 368   do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
 369   do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
 370   do_test          e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac
 371 }
 372
 373 # EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the
 374 # table-name is support for top-level INSERT statements only.
 375 #
 376 # EVIDENCE-OF: R-05731-00924 The table name must be unqualified for
 377 # INSERT statements that occur within CREATE TRIGGER statements.
 378 #
 379 set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
 380
 381 do_catchsql_test e_insert-5.1.1 {
 382   CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
 383     INSERT INTO main.a4 VALUES(new.a, new.b);
 384   END;
 385 } $err
 386 do_catchsql_test e_insert-5.1.2 {
 387   CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b);
 388   CREATE TRIGGER AFTER DELETE ON a3 BEGIN
 389     INSERT INTO temp.tmptable VALUES(1, 2);
 390   END;
 391 } $err
 392
 393 # EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the
 394 # INSERT statement is supported for top-level INSERT statements only and
 395 # not for INSERT statements within triggers.
 396 #
 397 do_catchsql_test e_insert-5.2.1 {
 398   CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
 399     INSERT INTO a4 DEFAULT VALUES;
 400   END;
 401 } {1 {near "DEFAULT": syntax error}}
 402
 403
 404 delete_all_data
 405
 406 finish_test