AryaWu/sqlite
0
1# 2001-09-152#3# The author disclaims copyright to this source code. In place of4# 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# This file implements regression tests for SQLite library. The12# focus of this file is testing the INSERT statement.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18# Try to insert into a non-existant table.19#20do_test insert-1.1 {21 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg]22 lappend v $msg23} {1 {no such table: test1}}24 25# Try to insert into sqlite_master26#27do_test insert-1.2 {28 set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg]29 lappend v $msg30} {1 {table sqlite_master may not be modified}}31 32# Try to insert the wrong number of entries.33#34do_test insert-1.3 {35 execsql {CREATE TABLE test1(one int, two int, three int)}36 set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg]37 lappend v $msg38} {1 {table test1 has 3 columns but 2 values were supplied}}39do_test insert-1.3b {40 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg]41 lappend v $msg42} {1 {table test1 has 3 columns but 4 values were supplied}}43do_test insert-1.3c {44 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg]45 lappend v $msg46} {1 {4 values for 2 columns}}47do_test insert-1.3d {48 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg]49 lappend v $msg50} {1 {1 values for 2 columns}}51 52# Try to insert into a non-existant column of a table.53#54do_test insert-1.4 {55 set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg]56 lappend v $msg57} {1 {table test1 has no column named four}}58 59# Make sure the inserts actually happen60#61do_test insert-1.5 {62 execsql {INSERT INTO test1 VALUES(1,2,3)}63 execsql {SELECT * FROM test1}64} {1 2 3}65do_test insert-1.5b {66 execsql {INSERT INTO test1 VALUES(4,5,6)}67 execsql {SELECT * FROM test1 ORDER BY one}68} {1 2 3 4 5 6}69do_test insert-1.5c {70 execsql {INSERT INTO test1 VALUES(7,8,9)}71 execsql {SELECT * FROM test1 ORDER BY one}72} {1 2 3 4 5 6 7 8 9}73 74do_test insert-1.6 {75 execsql {DELETE FROM test1}76 execsql {INSERT INTO test1(one,two) VALUES(1,2)}77 execsql {SELECT * FROM test1 ORDER BY one}78} {1 2 {}}79do_test insert-1.6b {80 execsql {INSERT INTO test1(two,three) VALUES(5,6)}81 execsql {SELECT * FROM test1 ORDER BY one}82} {{} 5 6 1 2 {}}83do_test insert-1.6c {84 execsql {INSERT INTO test1(three,one) VALUES(7,8)}85 execsql {SELECT * FROM test1 ORDER BY one}86} {{} 5 6 1 2 {} 8 {} 7}87 88# A table to use for testing default values89#90do_test insert-2.1 {91 execsql {92 CREATE TABLE test2(93 f1 int default -111, 94 f2 real default +4.32,95 f3 int default +222,96 f4 int default 7.8997 )98 }99 execsql {SELECT * from test2}100} {}101do_test insert-2.2 {102 execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)}103 execsql {SELECT * FROM test2}104} {10 4.32 -10 7.89}105do_test insert-2.3 {106 execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)}107 execsql {SELECT * FROM test2 WHERE f1==-111}108} {-111 1.23 222 -3.45}109do_test insert-2.4 {110 execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)}111 execsql {SELECT * FROM test2 WHERE f1==77}112} {77 1.23 222 3.45}113do_test insert-2.10 {114 execsql {115 DROP TABLE test2;116 CREATE TABLE test2(117 f1 int default 111, 118 f2 real default -4.32,119 f3 text default hi,120 f4 text default 'abc-123',121 f5 varchar(10)122 )123 }124 execsql {SELECT * from test2}125} {}126do_test insert-2.11 {127 execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')}128 execsql {SELECT * FROM test2}129} {111 -2.22 hi hi! {}}130do_test insert-2.12 {131 execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')}132 execsql {SELECT * FROM test2 ORDER BY f1}133} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}134 135# Do additional inserts with default values, but this time136# on a table that has indices. In particular we want to verify137# that the correct default values are inserted into the indices.138#139do_test insert-3.1 {140 execsql {141 DELETE FROM test2;142 CREATE INDEX index9 ON test2(f1,f2);143 CREATE INDEX indext ON test2(f4,f5);144 SELECT * from test2;145 }146} {}147 148# Update for sqlite3 v3:149# Change the 111 to '111' in the following two test cases, because150# the default value is being inserted as a string. TODO: It shouldn't be.151do_test insert-3.2 {152 execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}153 execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}154} {111 -3.33 hi hum {}}155do_test insert-3.3 {156 execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}157 execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}158} {111 -3.33 hi hum {}}159do_test insert-3.4 {160 execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}161} {22 -4.44 hi abc-123 wham}162ifcapable {reindex} {163 do_test insert-3.5 {164 execsql REINDEX165 } {}166}167integrity_check insert-3.5168 169# Test of expressions in the VALUES clause170#171do_test insert-4.1 {172 execsql {173 CREATE TABLE t3(a,b,c);174 INSERT INTO t3 VALUES(1+2+3,4,5);175 SELECT * FROM t3;176 }177} {6 4 5}178do_test insert-4.2 {179 ifcapable subquery {180 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);}181 } else {182 set maxa [execsql {SELECT max(a) FROM t3}]183 execsql "INSERT INTO t3 VALUES($maxa+1,5,6);"184 }185 execsql {186 SELECT * FROM t3 ORDER BY a;187 }188} {6 4 5 7 5 6}189ifcapable subquery {190 do_test insert-4.3 {191 catchsql {192 INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6);193 SELECT * FROM t3 ORDER BY a;194 }195 } {1 {no such column: t3.a}}196}197do_test insert-4.4 {198 ifcapable subquery {199 execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);}200 } else {201 set b [execsql {SELECT b FROM t3 WHERE a = 0}]202 if {$b==""} {set b NULL}203 execsql "INSERT INTO t3 VALUES($b,6,7);"204 }205 execsql {206 SELECT * FROM t3 ORDER BY a;207 }208} {{} 6 7 6 4 5 7 5 6}209do_test insert-4.5 {210 execsql {211 SELECT b,c FROM t3 WHERE a IS NULL;212 }213} {6 7}214do_test insert-4.6 {215 catchsql {216 INSERT INTO t3 VALUES(notafunc(2,3),2,3);217 }218} {1 {no such function: notafunc}}219do_test insert-4.7 {220 execsql {221 INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99);222 SELECT * FROM t3 WHERE c=99;223 }224} {1 3 99}225 226# Test the ability to insert from a temporary table into itself.227# Ticket #275.228#229ifcapable tempdb {230 do_test insert-5.1 {231 execsql {232 CREATE TEMP TABLE t4(x);233 INSERT INTO t4 VALUES(1);234 SELECT * FROM t4;235 }236 } {1}237 do_test insert-5.2 {238 execsql {239 INSERT INTO t4 SELECT x+1 FROM t4;240 SELECT * FROM t4;241 }242 } {1 2}243 ifcapable {explain} {244 do_test insert-5.3 {245 # verify that a temporary table is used to copy t4 to t4246 set x [execsql {247 EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4;248 }]249 expr {[lsearch $x OpenEphemeral]>0}250 } {1}251 }252 253 do_test insert-5.4 {254 # Verify that table "test1" begins on page 3. This should be the same255 # page number used by "t4" above.256 #257 # Update for v3 - the first table now begins on page 2 of each file, not 3.258 execsql {259 SELECT rootpage FROM sqlite_master WHERE name='test1';260 }261 } [expr $AUTOVACUUM?3:2]262 do_test insert-5.5 {263 # Verify that "t4" begins on page 3.264 #265 # Update for v3 - the first table now begins on page 2 of each file, not 3.266 execsql {267 SELECT rootpage FROM sqlite_temp_master WHERE name='t4';268 }269 } {2}270 do_test insert-5.6 {271 # This should not use an intermediate temporary table.272 execsql {273 INSERT INTO t4 SELECT one FROM test1 WHERE three=7;274 SELECT * FROM t4275 }276 } {1 2 8}277 ifcapable {explain} {278 do_test insert-5.7 {279 # verify that no temporary table is used to copy test1 to t4280 set x [execsql {281 EXPLAIN INSERT INTO t4 SELECT one FROM test1;282 }]283 expr {[lsearch $x OpenTemp]>0}284 } {0}285 }286}287 288# Ticket #334: REPLACE statement corrupting indices.289#290ifcapable conflict {291 # The REPLACE command is not available if SQLITE_OMIT_CONFLICT is 292 # defined at compilation time.293 do_test insert-6.1 {294 execsql {295 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);296 INSERT INTO t1 VALUES(1,2);297 INSERT INTO t1 VALUES(2,3);298 SELECT b FROM t1 WHERE b=2;299 }300 } {2}301 do_test insert-6.2 {302 execsql {303 REPLACE INTO t1 VALUES(1,4);304 SELECT b FROM t1 WHERE b=2;305 }306 } {}307 do_test insert-6.3 {308 execsql {309 UPDATE OR REPLACE t1 SET a=2 WHERE b=4;310 SELECT * FROM t1 WHERE b=4;311 }312 } {2 4}313 do_test insert-6.4 {314 execsql {315 SELECT * FROM t1 WHERE b=3;316 }317 } {}318 ifcapable {reindex} {319 do_test insert-6.5 {320 execsql REINDEX321 } {}322 }323 do_test insert-6.6 {324 execsql {325 DROP TABLE t1;326 }327 } {}328}329 330# Test that the special optimization for queries of the form 331# "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with 332# INSERT statments.333do_test insert-7.1 {334 execsql {335 CREATE TABLE t1(a);336 INSERT INTO t1 VALUES(1);337 INSERT INTO t1 VALUES(2);338 CREATE INDEX i1 ON t1(a);339 }340} {}341do_test insert-7.2 {342 execsql {343 INSERT INTO t1 SELECT max(a) FROM t1;344 }345} {}346do_test insert-7.3 {347 execsql {348 SELECT a FROM t1;349 }350} {1 2 2}351 352# Ticket #1140: Check for an infinite loop in the algorithm that tests353# to see if the right-hand side of an INSERT...SELECT references the left-hand354# side.355#356ifcapable subquery&&compound {357 do_test insert-8.1 {358 execsql {359 INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)360 }361 } {}362}363 364# Make sure the rowid cache in the VDBE is reset correctly when365# an explicit rowid is given.366#367do_test insert-9.1 {368 execsql {369 CREATE TABLE t5(x);370 INSERT INTO t5 VALUES(1);371 INSERT INTO t5 VALUES(2);372 INSERT INTO t5 VALUES(3);373 INSERT INTO t5(rowid, x) SELECT nullif(x*2+10,14), x+100 FROM t5;374 SELECT rowid, x FROM t5;375 }376} {1 1 2 2 3 3 12 101 13 102 16 103}377do_test insert-9.2 {378 execsql {379 CREATE TABLE t6(x INTEGER PRIMARY KEY, y);380 INSERT INTO t6 VALUES(1,1);381 INSERT INTO t6 VALUES(2,2);382 INSERT INTO t6 VALUES(3,3);383 INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6;384 SELECT x, y FROM t6;385 }386} {1 1 2 2 3 3 12 101 13 102 16 103}387 388# Multiple VALUES clauses389#390ifcapable compound {391 do_test insert-10.1 {392 execsql {393 CREATE TABLE t10(a,b,c);394 INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9);395 SELECT * FROM t10;396 }397 } {1 2 3 4 5 6 7 8 9}398 do_test insert-10.2 {399 catchsql {400 INSERT INTO t10 VALUES(11,12,13), (14,15), (16,17,28);401 }402 } {1 {all VALUES must have the same number of terms}}403}404 405# Need for the OP_SoftNull opcode406#407do_execsql_test insert-11.1 {408 CREATE TABLE t11a AS SELECT '123456789' AS x;409 CREATE TABLE t11b (a INTEGER PRIMARY KEY, b, c);410 INSERT INTO t11b SELECT x, x, x FROM t11a;411 SELECT quote(a), quote(b), quote(c) FROM t11b;412} {123456789 '123456789' '123456789'}413 414 415# More columns of input than there are columns in the table.416# Ticket http://sqlite.org/src/info/e9654505cfda9361417#418do_execsql_test insert-12.1 {419 CREATE TABLE t12a(a,b,c,d,e,f,g);420 INSERT INTO t12a VALUES(101,102,103,104,105,106,107);421 CREATE TABLE t12b(x);422 INSERT INTO t12b(x,rowid,x,x,x,x,x) SELECT * FROM t12a;423 SELECT rowid, x FROM t12b;424} {102 101}425do_execsql_test insert-12.2 {426 CREATE TABLE tab1( value INTEGER);427 INSERT INTO tab1 (value, _rowid_) values( 11, 1);428 INSERT INTO tab1 (value, _rowid_) SELECT 22,999;429 SELECT * FROM tab1;430} {11 22}431do_execsql_test insert-12.3 {432 CREATE TABLE t12c(a, b DEFAULT 'xyzzy', c);433 INSERT INTO t12c(a, rowid, c) SELECT 'one', 999, 'two';434 SELECT * FROM t12c;435} {one xyzzy two}436 437# 2018-06-11. From OSSFuzz. A column cache malfunction in438# the constraint checking on an index of expressions causes439# an assertion fault in a REPLACE. Ticket440# https://sqlite.org/src/info/c2432ef9089ee73b441#442do_execsql_test insert-13.1 {443 DROP TABLE IF EXISTS t13;444 CREATE TABLE t13(a INTEGER PRIMARY KEY,b UNIQUE);445 CREATE INDEX t13x1 ON t13(-b=b);446 INSERT INTO t13 VALUES(1,5),(6,2);447 REPLACE INTO t13 SELECT b,0 FROM t13;448 SELECT * FROM t13 ORDER BY +b;449} {2 0 6 2 1 5}450 451# 2019-01-17. From the chromium fuzzer.452#453do_execsql_test insert-14.1 {454 DROP TABLE IF EXISTS t14;455 CREATE TABLE t14(x INTEGER PRIMARY KEY);456 INSERT INTO t14 VALUES(CASE WHEN 1 THEN null END);457 SELECT x FROM t14;458} {1}459 460integrity_check insert-14.2461 462# 2019-08-12.463#464do_execsql_test insert-15.1 {465 DROP TABLE IF EXISTS t1;466 DROP TABLE IF EXISTS t2;467 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);468 CREATE INDEX i1 ON t1(b);469 CREATE TABLE t2(a, b);470 INSERT INTO t2 VALUES(4, randomblob(31000));471 INSERT INTO t2 VALUES(4, randomblob(32000));472 INSERT INTO t2 VALUES(4, randomblob(33000));473 REPLACE INTO t1 SELECT a, b FROM t2;474 SELECT a, length(b) FROM t1;475} {4 33000}476 477# 2019-10-16478# ticket https://sqlite.org/src/info/a8a4847a2d96f5de479# On a REPLACE INTO, if an AFTER trigger adds back the conflicting480# row, you can end up with the wrong number of rows in an index.481#482db close483sqlite3 db :memory:484do_catchsql_test insert-16.1 {485 PRAGMA recursive_triggers = true;486 CREATE TABLE t0(c0,c1);487 CREATE UNIQUE INDEX i0 ON t0(c0);488 INSERT INTO t0(c0,c1) VALUES(123,1);489 CREATE TRIGGER tr0 AFTER DELETE ON t0490 BEGIN491 INSERT INTO t0 VALUES(123,2);492 END;493 REPLACE INTO t0(c0,c1) VALUES(123,3);494} {1 {UNIQUE constraint failed: t0.c0}}495do_execsql_test insert-16.2 {496 SELECT * FROM t0;497} {123 1}498integrity_check insert-16.3499do_catchsql_test insert-16.4 {500 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);501 CREATE INDEX t1b ON t1(b);502 INSERT INTO t1 VALUES(1, 'one');503 CREATE TRIGGER tr3 AFTER DELETE ON t1 BEGIN504 INSERT INTO t1 VALUES(1, 'three');505 END;506 REPLACE INTO t1 VALUES(1, 'two');507} {1 {UNIQUE constraint failed: t1.a}}508integrity_check insert-16.5509do_catchsql_test insert-16.6 {510 PRAGMA foreign_keys = 1;511 CREATE TABLE p1(a, b UNIQUE);512 CREATE TABLE c1(c, d REFERENCES p1(b) ON DELETE CASCADE);513 CREATE TRIGGER tr6 AFTER DELETE ON c1 BEGIN514 INSERT INTO p1 VALUES(4, 1);515 END;516 INSERT INTO p1 VALUES(1, 1);517 INSERT INTO c1 VALUES(2, 1);518 REPLACE INTO p1 VALUES(3, 1);2519} {1 {UNIQUE constraint failed: p1.b}}520integrity_check insert-16.7521 522# 2019-10-25 ticket c1e19e12046d23fe523do_catchsql_test insert-17.1 {524 PRAGMA temp.recursive_triggers = true;525 DROP TABLE IF EXISTS t0;526 CREATE TABLE t0(aa, bb);527 CREATE UNIQUE INDEX t0bb ON t0(bb);528 CREATE TRIGGER "r17.1" BEFORE DELETE ON t0529 BEGIN INSERT INTO t0(aa,bb) VALUES(99,1);530 END;531 INSERT INTO t0(aa,bb) VALUES(10,20);532 REPLACE INTO t0(aa,bb) VALUES(30,20);533} {1 {UNIQUE constraint failed: t0.rowid}}534integrity_check insert-17.2535do_catchsql_test insert-17.3 {536 DROP TABLE IF EXISTS t1;537 CREATE TABLE t1(a, b UNIQUE, c UNIQUE);538 INSERT INTO t1(a,b,c) VALUES(1,1,1),(2,2,2),(3,3,3),(4,4,4);539 CREATE TRIGGER "r17.3" AFTER DELETE ON t1 WHEN OLD.c<>3 BEGIN540 INSERT INTO t1(rowid,a,b,c) VALUES(100,100,100,3);541 END;542 REPLACE INTO t1(rowid,a,b,c) VALUES(200,1,2,3);543} {1 {UNIQUE constraint failed: t1.c}}544integrity_check insert-17.4545do_execsql_test insert-17.5 {546 CREATE TABLE t2(a INTEGER PRIMARY KEY, b);547 CREATE UNIQUE INDEX t2b ON t2(b);548 INSERT INTO t2(a,b) VALUES(1,1),(2,2),(3,3),(4,4);549 CREATE TABLE fire(x);550 CREATE TRIGGER t2r1 AFTER DELETE ON t2 BEGIN551 INSERT INTO fire VALUES(old.a);552 END;553 UPDATE OR REPLACE t2 SET a=4, b=3 WHERE a=1;554 SELECT *, 'x' FROM t2 ORDER BY a;555} {2 2 x 4 3 x}556do_execsql_test insert-17.6 {557 SELECT x FROM fire ORDER BY x;558} {3 4}559do_execsql_test insert-17.7 {560 DELETE FROM t2;561 DELETE FROM fire;562 INSERT INTO t2(a,b) VALUES(1,1),(2,2),(3,3),(4,4);563 UPDATE OR REPLACE t2 SET a=1, b=3 WHERE a=1;564 SELECT *, 'x' FROM t2 ORDER BY a;565} {1 3 x 2 2 x 4 4 x}566do_execsql_test insert-17.8 {567 SELECT x FROM fire ORDER BY x;568} {3}569do_execsql_test insert-17.10 {570 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INT, c INT, d INT);571 CREATE UNIQUE INDEX t3bpi ON t3(b) WHERE c<=d;572 CREATE UNIQUE INDEX t3d ON t3(d);573 INSERT INTO t3(a,b,c,d) VALUES(1,1,1,1),(2,1,3,2),(3,4,5,6);574 CREATE TRIGGER t3r1 AFTER DELETE ON t3 BEGIN575 SELECT 'hi';576 END;577 REPLACE INTO t3(a,b,c,d) VALUES(4,4,8,9);578} {}579do_execsql_test insert-17.11 {580 SELECT *, 'x' FROM t3 ORDER BY a;581} {1 1 1 1 x 2 1 3 2 x 4 4 8 9 x}582do_execsql_test insert-17.12 {583 REPLACE INTO t3(a,b,c,d) VALUES(5,1,11,2);584 SELECT *, 'x' FROM t3 ORDER BY a;585} {1 1 1 1 x 4 4 8 9 x 5 1 11 2 x}586 587do_execsql_test insert-17.13 {588 DELETE FROM t3;589 INSERT INTO t3(a,b,c,d) VALUES(1,1,1,1),(2,1,3,2),(3,4,5,6);590 DROP TRIGGER t3r1;591 CREATE TRIGGER t3r1 AFTER DELETE ON t3 BEGIN592 INSERT INTO t3(b,c,d) VALUES(old.b,old.c,old.d);593 END;594} {}595do_catchsql_test insert-17.14 {596 REPLACE INTO t3(a,b,c,d) VALUES(4,4,8,9);597} {1 {UNIQUE constraint failed: t3.b}}598do_catchsql_test insert-17.15 {599 REPLACE INTO t3(a,b,c,d) VALUES(5,1,11,2);600} {1 {UNIQUE constraint failed: t3.d}}601 602 603finish_test604