/ Check-in [eb3d0d8b]
Login

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 Unified Diffs Show Whitespace Changes Patch

Changes to test/e_insert.test.

21
22
23
24
25
26
27





28
29
30
31
32
33

34
35
36
37
38
39
40
...
197
198
199
200
201
202
203



204























205
206
207
208
209
210
211
...
234
235
236
237
238
239
240





















241
242
243
244
245
246
247
...
271
272
273
274
275
276
277



278


279































































280
281
282
#
#   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
#   
#   e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".
#
#   e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".
#






do_execsql_test e_insert-0.0 {
  CREATE TABLE a1(a, b);
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');

  CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);

} {}

proc delete_all_data {} {
  db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
    db eval "DELETE FROM '[string map {' ''} $t]'"
  }
}
................................................................................
    1a   "INSERT INTO a2(b, c) VALUES('b', 'c')"     {}
    1b   "SELECT * FROM a2"                          {{} b c}

    2a   "INSERT INTO a2(a, b) VALUES('a', 'b')"     {}
    2b   "SELECT * FROM a2"                          {{} b c  a b xyz}
}




delete_all_data

























# EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number
# of columns in the result of the SELECT must be the same as the number
# of items in the column-list.
#
do_insert_tests e_insert-2.2 -error {
................................................................................
    1    "INSERT INTO a1 SELECT a, b, c FROM a2"            {a1 2 3}
    2    "INSERT INTO a1 SELECT * FROM a2"                  {a1 2 3}
    3    "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1"    {a1 2 5}
    4    "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1"  {a1 2 3}
    5    "INSERT INTO a1 SELECT a2.a FROM a2,a1"            {a1 2 1}
}























# EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement
# inserts a single new row into the named table.
#
delete_all_data
do_insert_tests e_insert-3.1 {
    1    "SELECT count(*) FROM a3"           {0}
................................................................................
    5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    5.2    "SELECT * FROM a1"                  {{} {}}

    6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    6.2    "SELECT * FROM a1"                  {{} {} {} {}}
}







































































delete_all_data

finish_test







>
>
>
>
>




<

>







 







>
>
>

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







 







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







 







>
>
>
|
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

37
38
39
40
41
42
43
44
45
...
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
...
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
...
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
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
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
#
#   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
#   
#   e_insert-2.*: Test statements of the form "INSERT ... SELECT ...".
#
#   e_insert-3.*: Test statements of the form "INSERT ... DEFAULT VALUES".
#
#   e_insert-4.*: Test statements regarding the conflict clause.
#
#   e_insert-5.*: Test that the qualified table name and "DEFAULT VALUES"
#                 syntaxes do not work in trigger bodies.
#

do_execsql_test e_insert-0.0 {
  CREATE TABLE a1(a, b);
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');

  CREATE TABLE a3(x DEFAULT 1.0, y DEFAULT 'string', z);
  CREATE TABLE a4(c UNIQUE, d);
} {}

proc delete_all_data {} {
  db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
    db eval "DELETE FROM '[string map {' ''} $t]'"
  }
}
................................................................................
    1a   "INSERT INTO a2(b, c) VALUES('b', 'c')"     {}
    1b   "SELECT * FROM a2"                          {{} b c}

    2a   "INSERT INTO a2(a, b) VALUES('a', 'b')"     {}
    2b   "SELECT * FROM a2"                          {{} b c  a b xyz}
}

# EVIDENCE-OF: R-52173-30215 A new entry is inserted into the table for
# each row of data returned by executing the SELECT statement.
#
delete_all_data
do_insert_tests e_insert-2.1 {
    0    "SELECT count(*) FROM a1"            {0}

    1a   "SELECT count(*) FROM (SELECT 1, 2)" {1}
    1b   "INSERT INTO a1 SELECT 1, 2"         {}
    1c   "SELECT count(*) FROM a1"            {1}

    2a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {1}
    2b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
    2c   "SELECT count(*) FROM a1"                              {2}

    3a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {2}
    3b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
    3c   "SELECT count(*) FROM a1"                              {4}

    4a   "SELECT count(*) FROM (SELECT b, a FROM a1)"           {4}
    4b   "INSERT INTO a1 SELECT b, a FROM a1"                   {}
    4c   "SELECT count(*) FROM a1"                              {8}

    4a   "SELECT count(*) FROM (SELECT min(b), min(a) FROM a1)" {1}
    4b   "INSERT INTO a1 SELECT min(b), min(a) FROM a1"         {}
    4c   "SELECT count(*) FROM a1"                              {9}
}


# EVIDENCE-OF: R-63614-47421 If a column-list is specified, the number
# of columns in the result of the SELECT must be the same as the number
# of items in the column-list.
#
do_insert_tests e_insert-2.2 -error {
................................................................................
    1    "INSERT INTO a1 SELECT a, b, c FROM a2"            {a1 2 3}
    2    "INSERT INTO a1 SELECT * FROM a2"                  {a1 2 3}
    3    "INSERT INTO a1 SELECT * FROM a2 CROSS JOIN a1"    {a1 2 5}
    4    "INSERT INTO a1 SELECT * FROM a2 NATURAL JOIN a1"  {a1 2 3}
    5    "INSERT INTO a1 SELECT a2.a FROM a2,a1"            {a1 2 1}
}

# EVIDENCE-OF: R-31074-37730 Any SELECT statement, including compound
# SELECTs and SELECT statements with ORDER BY and/or LIMIT clauses, may
# be used in an INSERT statement of this form.
#
delete_all_data
do_execsql_test e_insert-2.3.0 {
  INSERT INTO a1 VALUES('x', 'y');
} {}
do_insert_tests e_insert-2.3 {
  1  "INSERT INTO a1 SELECT a,b FROM a1 UNION SELECT b,a FROM a1 ORDER BY 1" {}
  2  "INSERT INTO a1(b, a) SELECT * FROM a1 LIMIT 1"                         {}
  3  "INSERT INTO a1 SELECT 'a'||a, 'b'||b FROM a1 LIMIT 2 OFFSET 1"         {}
  4  "INSERT INTO a1 SELECT * FROM a1 ORDER BY b, a"                         {}
  S  "SELECT * FROM a1" {
      x y 
      x y y x
      y x
      ax by ay bx 
      ay bx ax by y x y x x y x y
  }
}

# EVIDENCE-OF: R-25149-22012 The INSERT ... DEFAULT VALUES statement
# inserts a single new row into the named table.
#
delete_all_data
do_insert_tests e_insert-3.1 {
    1    "SELECT count(*) FROM a3"           {0}
................................................................................
    5.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    5.2    "SELECT * FROM a1"                  {{} {}}

    6.1    "INSERT INTO a1 DEFAULT VALUES"     {}
    6.2    "SELECT * FROM a1"                  {{} {} {} {}}
}

# EVIDENCE-OF: R-46928-50290 The optional conflict-clause allows the
# specification of an alternative constraint conflict resolution
# algorithm to use during this one INSERT command.
#
# EVIDENCE-OF: R-23110-47146 the parser allows the use of the single
# keyword REPLACE as an alias for "INSERT OR REPLACE".
#
#    The two requirements above are tested by e_select-4.1.* and
#    e_select-4.2.*, respectively.
#
do_execsql_test e_insert-4.1.0 {
  INSERT INTO a4 VALUES(1, 'a');
  INSERT INTO a4 VALUES(2, 'a');
  INSERT INTO a4 VALUES(3, 'a');
} {}
foreach {tn sql error ac data } {
  1.1  "INSERT INTO a4 VALUES(2,'b')"  {column c is not unique}  1 {1 a 2 a 3 a}
  1.2  "INSERT OR REPLACE INTO a4 VALUES(2, 'b')"            {}  1 {1 a 3 a 2 b}
  1.3  "INSERT OR IGNORE INTO a4 VALUES(3, 'c')"             {}  1 {1 a 3 a 2 b}
  1.4  "BEGIN" {} 0 {1 a 3 a 2 b}
  1.5  "INSERT INTO a4 VALUES(1, 'd')" {column c is not unique}  0 {1 a 3 a 2 b}
  1.6  "INSERT OR ABORT INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  0 {1 a 3 a 2 b}
  1.7  "INSERT OR ROLLBACK INTO a4 VALUES(1, 'd')" 
        {column c is not unique}  1 {1 a 3 a 2 b}
  1.8  "INSERT INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  1 {1 a 3 a 2 b}
  1.9  "INSERT OR FAIL INTO a4 SELECT 4, 'e' UNION ALL SELECT 3, 'e'"
        {column c is not unique}  1 {1 a 3 a 2 b 4 e}

  2.1  "INSERT INTO a4 VALUES(2,'f')"  
        {column c is not unique}  1 {1 a 3 a 2 b 4 e}
  2.2  "REPLACE INTO a4 VALUES(2, 'f')" {}  1 {1 a 3 a 4 e 2 f}
} {
  do_catchsql_test e_insert-4.1.$tn.1 $sql [list [expr {$error!=""}] $error]
  do_execsql_test  e_insert-4.1.$tn.2 {SELECT * FROM a4} [list {*}$data]
  do_test          e_insert-4.1.$tn.3 {sqlite3_get_autocommit db} $ac
}

# EVIDENCE-OF: R-64196-02418 The optional "database-name." prefix on the
# table-name is support for top-level INSERT statements only.
#
# EVIDENCE-OF: R-05731-00924 The table name must be unqualified for
# INSERT statements that occur within CREATE TRIGGER statements.
#
set err {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}

do_catchsql_test e_insert-5.1.1 {
  CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
    INSERT INTO main.a4 VALUES(new.a, new.b);
  END;
} $err
do_catchsql_test e_insert-5.1.2 {
  CREATE TEMP TABLE IF NOT EXISTS tmptable(a, b);
  CREATE TRIGGER AFTER DELETE ON a3 BEGIN
    INSERT INTO temp.tmptable VALUES(1, 2);
  END;
} $err

# EVIDENCE-OF: R-15888-36326 Similarly, the "DEFAULT VALUES" form of the
# INSERT statement is supported for top-level INSERT statements only and
# not for INSERT statements within triggers.
#
do_catchsql_test e_insert-5.2.1 {
  CREATE TRIGGER AFTER UPDATE ON a1 BEGIN
    INSERT INTO a4 DEFAULT VALUES;
  END;
} {1 {near "DEFAULT": syntax error}}


delete_all_data

finish_test