/ Check-in [eb3d0d8b]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add further tests to e_insert.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eb3d0d8bb78aa7132f842480bc88621b1573f19c
User & Date: dan 2010-09-20 14:05:52
Context
2010-09-20
14:55
Changes to test scripts to work with SQLITE_TEMP_STORE=2. check-in: ba8ca9c9 user: dan tags: trunk
14:05
Add further tests to e_insert.test. check-in: eb3d0d8b user: dan tags: trunk
08:47
Add a test case to verify that bug [313723c356] has been fixed. check-in: 4ea134a8 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_insert.test.

    21     21   #
    22     22   #   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
    23     23   #   
    24     24   #   e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".
    25     25   #
    26     26   #   e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".
    27     27   #
           28  +#   e_insert-4.*: Test statements regarding the conflict clause.
           29  +#
           30  +#   e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES"
           31  +#                 syntaxes do not work in trigger bodies.
           32  +#
    28     33   
    29     34   do_execsql_test e_insert-0.0 {
    30     35     CREATE TABLE a1(a, b);
    31     36     CREATE TABLE a2(a, b, c DEFAULT 'xyz');
    32         -
    33     37     CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
           38  +  CREATE TABLE a4(c UNIQUE, d);
    34     39   } {}
    35     40   
    36     41   proc delete_all_data {} {
    37     42     db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
    38     43       db eval "DELETE FROM '[string map {' ''} $t]'"
    39     44     }
    40     45   }
................................................................................
   197    202       1a   "INSERT INTO a2(b, c) VALUES('b', 'c')"     {}
   198    203       1b   "SELECT * FROM a2"                          {{} b c}
   199    204   
   200    205       2a   "INSERT INTO a2(a, b) VALUES('a', 'b')"     {}
   201    206       2b   "SELECT * FROM a2"                          {{} b c  a b xyz}
   202    207   }
   203    208   
          209  +# EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for
          210  +# each row of data returned by executing the SELECT statement.
          211  +#
   204    212   delete_all_data
          213  +do_insert_tests e_insert-2.1 {
          214  +    0    "SELECT count(*) FROM a1"            {0}
          215  +
          216  +    1a   "SELECT count(*) FROM (SELECT 1, 2)" {1}
          217  +    1b   "INSERT INTO a1 SELECT 1, 2"         {}
          218  +    1c   "SELECT count(*) FROM a1"            {1}
          219  +
          220  +    2a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {1}
          221  +    2b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
          222  +    2c   "SELECT count(*) FROM a1"                              {2}
          223  +
          224  +    3a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {2}
          225  +    3b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
          226  +    3c   "SELECT count(*) FROM a1"                              {4}
          227  +
          228  +    4a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {4}
          229  +    4b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
          230  +    4c   "SELECT count(*) FROM a1"                              {8}
          231  +
          232  +    4a   "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1}
          233  +    4b   "INSERT INTO a1 SELECT min(b), min(a) FROM a1"         {}
          234  +    4c   "SELECT count(*) FROM a1"                              {9}
          235  +}
   205    236   
   206    237   
   207    238   # EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number
   208    239   # of columns in the result of the SELECT must be the same as the number
   209    240   # of items in the column-list.
   210    241   #
   211    242   do_insert_tests e_insert-2.2 -error {
................................................................................
   234    265       1    "INSERT INTO a1 SELECT a, b, c FROM a2"            {a1 2 3}
   235    266       2    "INSERT INTO a1 SELECT * FROM a2"                  {a1 2 3}
   236    267       3    "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1"    {a1 2 5}
   237    268       4    "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1"  {a1 2 3}
   238    269       5    "INSERT INTO a1 SELECT a2.a FROM a2,a1"            {a1 2 1}
   239    270   }
   240    271   
          272  +# EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound
          273  +# SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may
          274  +# be used in an INSERT statement of this form.
          275  +#
          276  +delete_all_data
          277  +do_execsql_test e_insert-2.3.0 {
          278  +  INSERT INTO a1 VALUES('x', 'y');
          279  +} {}
          280  +do_insert_tests e_insert-2.3 {
          281  +  1  "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {}
          282  +  2  "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1"                         {}
          283  +  3  "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1"         {}
          284  +  4  "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a"                         {}
          285  +  S  "SELECT * FROM a1" {
          286  +      x y 
          287  +      x y y x
          288  +      y x
          289  +      ax by ay bx 
          290  +      ay bx ax by y x y x x y x y
          291  +  }
          292  +}
   241    293   
   242    294   # EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement
   243    295   # inserts a single new row into the named table.
   244    296   #
   245    297   delete_all_data
   246    298   do_insert_tests e_insert-3.1 {
   247    299       1    "SELECT count(*) FROM a3"           {0}
................................................................................
   271    323       5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
   272    324       5.2    "SELECT * FROM a1"                  {{} {}}
   273    325   
   274    326       6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
   275    327       6.2    "SELECT * FROM a1"                  {{} {} {} {}}
   276    328   }
   277    329   
          330  +# EVIDENCE-OF: R-46928-50290 The optional conflict-clause allows the
          331  +# specification of an alternative constraint conflict resolution
          332  +# algorithm to use during this one INSERT command.
          333  +#
          334  +# EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
          335  +# keyword REPLACE as an alias for "INSERT OR REPLACE".
          336  +#
          337  +#    The two requirements above are tested by e_select-4.1.* and
          338  +#    e_select-4.2.*, respectively.
          339  +#
          340  +do_execsql_test e_insert-4.1.0 {
          341  +  INSERT INTO a4 VALUES(1, 'a');
          342  +  INSERT INTO a4 VALUES(2, 'a');
          343  +  INSERT INTO a4 VALUES(3, 'a');
          344  +} {}
          345  +foreach {tn sql error ac data } {
          346  +  1.1  "INSERT INTO a4 VALUES(2,'b')"  {column c is not unique}  1 {1 a 2 a 3 a}
          347  +  1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
          348  +  1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
          349  +  1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
          350  +  1.5  "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique}  0 {1 a 3 a 2 b}
          351  +  1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
          352  +        {column c is not unique}  0 {1 a 3 a 2 b}
          353  +  1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
          354  +        {column c is not unique}  1 {1 a 3 a 2 b}
          355  +  1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
          356  +        {column c is not unique}  1 {1 a 3 a 2 b}
          357  +  1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
          358  +        {column c is not unique}  1 {1 a 3 a 2 b 4 e}
          359  +
          360  +  2.1  "INSERT INTO a4 VALUES(2,'f')"  
          361  +        {column c is not unique}  1 {1 a 3 a 2 b 4 e}
          362  +  2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {1 a 3 a 4 e 2 f}
          363  +} {
          364  +  do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
          365  +  do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
          366  +  do_test          e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac
          367  +}
          368  +
          369  +# EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the
          370  +# table-name is support for top-level INSERT statements only.
          371  +#
          372  +# EVIDENCE-OF: R-05731-00924 The table name must be unqualified for
          373  +# INSERT statements that occur within CREATE TRIGGER statements.
          374  +#
          375  +set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
          376  +
          377  +do_catchsql_test e_insert-5.1.1 {
          378  +  CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
          379  +    INSERT INTO main.a4 VALUES(new.a, new.b);
          380  +  END;
          381  +} $err
          382  +do_catchsql_test e_insert-5.1.2 {
          383  +  CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b);
          384  +  CREATE TRIGGER AFTER DELETE ON a3 BEGIN
          385  +    INSERT INTO temp.tmptable VALUES(1, 2);
          386  +  END;
          387  +} $err
          388  +
          389  +# EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the
          390  +# INSERT statement is supported for top-level INSERT statements only and
          391  +# not for INSERT statements within triggers.
          392  +#
          393  +do_catchsql_test e_insert-5.2.1 {
          394  +  CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
          395  +    INSERT INTO a4 DEFAULT VALUES;
          396  +  END;
          397  +} {1 {near "DEFAULT": syntax error}}
   278    398   
   279    399   
   280    400   delete_all_data
   281    401   
   282    402   finish_test