1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2001 September 15 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. The 137c478bd9Sstevel@tonic-gate# focus of this file is testing the INSERT statement. 147c478bd9Sstevel@tonic-gate# 157c478bd9Sstevel@tonic-gate# $Id: insert.test,v 1.15 2003/06/15 23:42:25 drh Exp $ 167c478bd9Sstevel@tonic-gate 177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 197c478bd9Sstevel@tonic-gate 207c478bd9Sstevel@tonic-gate# Try to insert into a non-existant table. 217c478bd9Sstevel@tonic-gate# 227c478bd9Sstevel@tonic-gatedo_test insert-1.1 { 237c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg] 247c478bd9Sstevel@tonic-gate lappend v $msg 257c478bd9Sstevel@tonic-gate} {1 {no such table: test1}} 267c478bd9Sstevel@tonic-gate 277c478bd9Sstevel@tonic-gate# Try to insert into sqlite_master 287c478bd9Sstevel@tonic-gate# 297c478bd9Sstevel@tonic-gatedo_test insert-1.2 { 307c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg] 317c478bd9Sstevel@tonic-gate lappend v $msg 327c478bd9Sstevel@tonic-gate} {1 {table sqlite_master may not be modified}} 337c478bd9Sstevel@tonic-gate 347c478bd9Sstevel@tonic-gate# Try to insert the wrong number of entries. 357c478bd9Sstevel@tonic-gate# 367c478bd9Sstevel@tonic-gatedo_test insert-1.3 { 377c478bd9Sstevel@tonic-gate execsql {CREATE TABLE test1(one int, two int, three int)} 387c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg] 397c478bd9Sstevel@tonic-gate lappend v $msg 407c478bd9Sstevel@tonic-gate} {1 {table test1 has 3 columns but 2 values were supplied}} 417c478bd9Sstevel@tonic-gatedo_test insert-1.3b { 427c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg] 437c478bd9Sstevel@tonic-gate lappend v $msg 447c478bd9Sstevel@tonic-gate} {1 {table test1 has 3 columns but 4 values were supplied}} 457c478bd9Sstevel@tonic-gatedo_test insert-1.3c { 467c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg] 477c478bd9Sstevel@tonic-gate lappend v $msg 487c478bd9Sstevel@tonic-gate} {1 {4 values for 2 columns}} 497c478bd9Sstevel@tonic-gatedo_test insert-1.3d { 507c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg] 517c478bd9Sstevel@tonic-gate lappend v $msg 527c478bd9Sstevel@tonic-gate} {1 {1 values for 2 columns}} 537c478bd9Sstevel@tonic-gate 547c478bd9Sstevel@tonic-gate# Try to insert into a non-existant column of a table. 557c478bd9Sstevel@tonic-gate# 567c478bd9Sstevel@tonic-gatedo_test insert-1.4 { 577c478bd9Sstevel@tonic-gate set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg] 587c478bd9Sstevel@tonic-gate lappend v $msg 597c478bd9Sstevel@tonic-gate} {1 {table test1 has no column named four}} 607c478bd9Sstevel@tonic-gate 617c478bd9Sstevel@tonic-gate# Make sure the inserts actually happen 627c478bd9Sstevel@tonic-gate# 637c478bd9Sstevel@tonic-gatedo_test insert-1.5 { 647c478bd9Sstevel@tonic-gate execsql {INSERT INTO test1 VALUES(1,2,3)} 657c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1} 667c478bd9Sstevel@tonic-gate} {1 2 3} 677c478bd9Sstevel@tonic-gatedo_test insert-1.5b { 687c478bd9Sstevel@tonic-gate execsql {INSERT INTO test1 VALUES(4,5,6)} 697c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1 ORDER BY one} 707c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6} 717c478bd9Sstevel@tonic-gatedo_test insert-1.5c { 727c478bd9Sstevel@tonic-gate execsql {INSERT INTO test1 VALUES(7,8,9)} 737c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1 ORDER BY one} 747c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 7 8 9} 757c478bd9Sstevel@tonic-gate 767c478bd9Sstevel@tonic-gatedo_test insert-1.6 { 777c478bd9Sstevel@tonic-gate execsql {DELETE FROM test1} 787c478bd9Sstevel@tonic-gate execsql {INSERT INTO test1(one,two) VALUES(1,2)} 797c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1 ORDER BY one} 807c478bd9Sstevel@tonic-gate} {1 2 {}} 817c478bd9Sstevel@tonic-gatedo_test insert-1.6b { 827c478bd9Sstevel@tonic-gate execsql {INSERT INTO test1(two,three) VALUES(5,6)} 837c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1 ORDER BY one} 847c478bd9Sstevel@tonic-gate} {{} 5 6 1 2 {}} 857c478bd9Sstevel@tonic-gatedo_test insert-1.6c { 867c478bd9Sstevel@tonic-gate execsql {INSERT INTO test1(three,one) VALUES(7,8)} 877c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test1 ORDER BY one} 887c478bd9Sstevel@tonic-gate} {{} 5 6 1 2 {} 8 {} 7} 897c478bd9Sstevel@tonic-gate 907c478bd9Sstevel@tonic-gate# A table to use for testing default values 917c478bd9Sstevel@tonic-gate# 927c478bd9Sstevel@tonic-gatedo_test insert-2.1 { 937c478bd9Sstevel@tonic-gate execsql { 947c478bd9Sstevel@tonic-gate CREATE TABLE test2( 95*1da57d55SToomas Soome f1 int default -111, 967c478bd9Sstevel@tonic-gate f2 real default +4.32, 977c478bd9Sstevel@tonic-gate f3 int default +222, 987c478bd9Sstevel@tonic-gate f4 int default 7.89 997c478bd9Sstevel@tonic-gate ) 1007c478bd9Sstevel@tonic-gate } 1017c478bd9Sstevel@tonic-gate execsql {SELECT * from test2} 1027c478bd9Sstevel@tonic-gate} {} 1037c478bd9Sstevel@tonic-gatedo_test insert-2.2 { 1047c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)} 1057c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2} 1067c478bd9Sstevel@tonic-gate} {10 4.32 -10 7.89} 1077c478bd9Sstevel@tonic-gatedo_test insert-2.3 { 1087c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)} 1097c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2 WHERE f1==-111} 1107c478bd9Sstevel@tonic-gate} {-111 1.23 222 -3.45} 1117c478bd9Sstevel@tonic-gatedo_test insert-2.4 { 1127c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)} 1137c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2 WHERE f1==77} 1147c478bd9Sstevel@tonic-gate} {77 1.23 222 3.45} 1157c478bd9Sstevel@tonic-gatedo_test insert-2.10 { 1167c478bd9Sstevel@tonic-gate execsql { 1177c478bd9Sstevel@tonic-gate DROP TABLE test2; 1187c478bd9Sstevel@tonic-gate CREATE TABLE test2( 119*1da57d55SToomas Soome f1 int default 111, 1207c478bd9Sstevel@tonic-gate f2 real default -4.32, 1217c478bd9Sstevel@tonic-gate f3 text default hi, 1227c478bd9Sstevel@tonic-gate f4 text default 'abc-123', 1237c478bd9Sstevel@tonic-gate f5 varchar(10) 1247c478bd9Sstevel@tonic-gate ) 1257c478bd9Sstevel@tonic-gate } 1267c478bd9Sstevel@tonic-gate execsql {SELECT * from test2} 1277c478bd9Sstevel@tonic-gate} {} 1287c478bd9Sstevel@tonic-gatedo_test insert-2.11 { 1297c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')} 1307c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2} 1317c478bd9Sstevel@tonic-gate} {111 -2.22 hi hi! {}} 1327c478bd9Sstevel@tonic-gatedo_test insert-2.12 { 1337c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')} 1347c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2 ORDER BY f1} 1357c478bd9Sstevel@tonic-gate} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}} 1367c478bd9Sstevel@tonic-gate 1377c478bd9Sstevel@tonic-gate# Do additional inserts with default values, but this time 1387c478bd9Sstevel@tonic-gate# on a table that has indices. In particular we want to verify 1397c478bd9Sstevel@tonic-gate# that the correct default values are inserted into the indices. 1407c478bd9Sstevel@tonic-gate# 1417c478bd9Sstevel@tonic-gatedo_test insert-3.1 { 1427c478bd9Sstevel@tonic-gate execsql { 1437c478bd9Sstevel@tonic-gate DELETE FROM test2; 1447c478bd9Sstevel@tonic-gate CREATE INDEX index9 ON test2(f1,f2); 1457c478bd9Sstevel@tonic-gate CREATE INDEX indext ON test2(f4,f5); 1467c478bd9Sstevel@tonic-gate SELECT * from test2; 1477c478bd9Sstevel@tonic-gate } 1487c478bd9Sstevel@tonic-gate} {} 1497c478bd9Sstevel@tonic-gatedo_test insert-3.2 { 1507c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} 1517c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33} 1527c478bd9Sstevel@tonic-gate} {111 -3.33 hi hum {}} 1537c478bd9Sstevel@tonic-gatedo_test insert-3.3 { 1547c478bd9Sstevel@tonic-gate execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')} 1557c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33} 1567c478bd9Sstevel@tonic-gate} {111 -3.33 hi hum {}} 1577c478bd9Sstevel@tonic-gatedo_test insert-3.4 { 1587c478bd9Sstevel@tonic-gate execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44} 1597c478bd9Sstevel@tonic-gate} {22 -4.44 hi abc-123 wham} 1607c478bd9Sstevel@tonic-gateintegrity_check insert-3.5 1617c478bd9Sstevel@tonic-gate 1627c478bd9Sstevel@tonic-gate# Test of expressions in the VALUES clause 1637c478bd9Sstevel@tonic-gate# 1647c478bd9Sstevel@tonic-gatedo_test insert-4.1 { 1657c478bd9Sstevel@tonic-gate execsql { 1667c478bd9Sstevel@tonic-gate CREATE TABLE t3(a,b,c); 1677c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(1+2+3,4,5); 1687c478bd9Sstevel@tonic-gate SELECT * FROM t3; 1697c478bd9Sstevel@tonic-gate } 1707c478bd9Sstevel@tonic-gate} {6 4 5} 1717c478bd9Sstevel@tonic-gatedo_test insert-4.2 { 1727c478bd9Sstevel@tonic-gate execsql { 1737c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6); 1747c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a; 1757c478bd9Sstevel@tonic-gate } 1767c478bd9Sstevel@tonic-gate} {6 4 5 7 5 6} 1777c478bd9Sstevel@tonic-gatedo_test insert-4.3 { 1787c478bd9Sstevel@tonic-gate catchsql { 1797c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6); 1807c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a; 1817c478bd9Sstevel@tonic-gate } 1827c478bd9Sstevel@tonic-gate} {1 {no such column: t3.a}} 1837c478bd9Sstevel@tonic-gatedo_test insert-4.4 { 1847c478bd9Sstevel@tonic-gate execsql { 1857c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7); 1867c478bd9Sstevel@tonic-gate SELECT * FROM t3 ORDER BY a; 1877c478bd9Sstevel@tonic-gate } 1887c478bd9Sstevel@tonic-gate} {{} 6 7 6 4 5 7 5 6} 1897c478bd9Sstevel@tonic-gatedo_test insert-4.5 { 1907c478bd9Sstevel@tonic-gate execsql { 1917c478bd9Sstevel@tonic-gate SELECT b,c FROM t3 WHERE a IS NULL; 1927c478bd9Sstevel@tonic-gate } 1937c478bd9Sstevel@tonic-gate} {6 7} 1947c478bd9Sstevel@tonic-gatedo_test insert-4.6 { 1957c478bd9Sstevel@tonic-gate catchsql { 1967c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(notafunc(2,3),2,3); 1977c478bd9Sstevel@tonic-gate } 1987c478bd9Sstevel@tonic-gate} {1 {no such function: notafunc}} 1997c478bd9Sstevel@tonic-gatedo_test insert-4.7 { 2007c478bd9Sstevel@tonic-gate execsql { 2017c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99); 2027c478bd9Sstevel@tonic-gate SELECT * FROM t3 WHERE c=99; 2037c478bd9Sstevel@tonic-gate } 2047c478bd9Sstevel@tonic-gate} {1 3 99} 2057c478bd9Sstevel@tonic-gate 2067c478bd9Sstevel@tonic-gate# Test the ability to insert from a temporary table into itself. 2077c478bd9Sstevel@tonic-gate# Ticket #275. 2087c478bd9Sstevel@tonic-gate# 2097c478bd9Sstevel@tonic-gatedo_test insert-5.1 { 2107c478bd9Sstevel@tonic-gate execsql { 2117c478bd9Sstevel@tonic-gate CREATE TEMP TABLE t4(x); 2127c478bd9Sstevel@tonic-gate INSERT INTO t4 VALUES(1); 2137c478bd9Sstevel@tonic-gate SELECT * FROM t4; 2147c478bd9Sstevel@tonic-gate } 2157c478bd9Sstevel@tonic-gate} {1} 2167c478bd9Sstevel@tonic-gatedo_test insert-5.2 { 2177c478bd9Sstevel@tonic-gate execsql { 2187c478bd9Sstevel@tonic-gate INSERT INTO t4 SELECT x+1 FROM t4; 2197c478bd9Sstevel@tonic-gate SELECT * FROM t4; 2207c478bd9Sstevel@tonic-gate } 2217c478bd9Sstevel@tonic-gate} {1 2} 2227c478bd9Sstevel@tonic-gatedo_test insert-5.3 { 2237c478bd9Sstevel@tonic-gate # verify that a temporary table is used to copy t4 to t4 2247c478bd9Sstevel@tonic-gate set x [execsql { 2257c478bd9Sstevel@tonic-gate EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4; 2267c478bd9Sstevel@tonic-gate }] 2277c478bd9Sstevel@tonic-gate expr {[lsearch $x OpenTemp]>0} 2287c478bd9Sstevel@tonic-gate} {1} 2297c478bd9Sstevel@tonic-gatedo_test insert-5.4 { 2307c478bd9Sstevel@tonic-gate # Verify that table "test1" begins on page 3. This should be the same 2317c478bd9Sstevel@tonic-gate # page number used by "t4" above. 2327c478bd9Sstevel@tonic-gate execsql { 2337c478bd9Sstevel@tonic-gate SELECT rootpage FROM sqlite_master WHERE name='test1'; 2347c478bd9Sstevel@tonic-gate } 2357c478bd9Sstevel@tonic-gate} {3} 2367c478bd9Sstevel@tonic-gatedo_test insert-5.5 { 2377c478bd9Sstevel@tonic-gate # Verify that "t4" begins on page 3. 2387c478bd9Sstevel@tonic-gate execsql { 2397c478bd9Sstevel@tonic-gate SELECT rootpage FROM sqlite_temp_master WHERE name='t4'; 2407c478bd9Sstevel@tonic-gate } 2417c478bd9Sstevel@tonic-gate} {3} 2427c478bd9Sstevel@tonic-gatedo_test insert-5.6 { 2437c478bd9Sstevel@tonic-gate # This should not use an intermediate temporary table. 2447c478bd9Sstevel@tonic-gate execsql { 2457c478bd9Sstevel@tonic-gate INSERT INTO t4 SELECT one FROM test1 WHERE three=7; 2467c478bd9Sstevel@tonic-gate SELECT * FROM t4 2477c478bd9Sstevel@tonic-gate } 2487c478bd9Sstevel@tonic-gate} {1 2 8} 2497c478bd9Sstevel@tonic-gatedo_test insert-5.7 { 2507c478bd9Sstevel@tonic-gate # verify that no temporary table is used to copy test1 to t4 2517c478bd9Sstevel@tonic-gate set x [execsql { 2527c478bd9Sstevel@tonic-gate EXPLAIN INSERT INTO t4 SELECT one FROM test1; 2537c478bd9Sstevel@tonic-gate }] 2547c478bd9Sstevel@tonic-gate expr {[lsearch $x OpenTemp]>0} 2557c478bd9Sstevel@tonic-gate} {0} 2567c478bd9Sstevel@tonic-gate 2577c478bd9Sstevel@tonic-gate# Ticket #334: REPLACE statement corrupting indices. 2587c478bd9Sstevel@tonic-gate# 2597c478bd9Sstevel@tonic-gatedo_test insert-6.1 { 2607c478bd9Sstevel@tonic-gate execsql { 2617c478bd9Sstevel@tonic-gate CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); 2627c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,2); 2637c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(2,3); 2647c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE b=2; 2657c478bd9Sstevel@tonic-gate } 2667c478bd9Sstevel@tonic-gate} {2} 2677c478bd9Sstevel@tonic-gatedo_test insert-6.2 { 2687c478bd9Sstevel@tonic-gate execsql { 2697c478bd9Sstevel@tonic-gate REPLACE INTO t1 VALUES(1,4); 2707c478bd9Sstevel@tonic-gate SELECT b FROM t1 WHERE b=2; 2717c478bd9Sstevel@tonic-gate } 2727c478bd9Sstevel@tonic-gate} {} 2737c478bd9Sstevel@tonic-gatedo_test insert-6.3 { 2747c478bd9Sstevel@tonic-gate execsql { 2757c478bd9Sstevel@tonic-gate UPDATE OR REPLACE t1 SET a=2 WHERE b=4; 2767c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE b=4; 2777c478bd9Sstevel@tonic-gate } 2787c478bd9Sstevel@tonic-gate} {2 4} 2797c478bd9Sstevel@tonic-gatedo_test insert-6.4 { 2807c478bd9Sstevel@tonic-gate execsql { 2817c478bd9Sstevel@tonic-gate SELECT * FROM t1 WHERE b=3; 2827c478bd9Sstevel@tonic-gate } 2837c478bd9Sstevel@tonic-gate} {} 2847c478bd9Sstevel@tonic-gate 2857c478bd9Sstevel@tonic-gateintegrity_check insert-99.0 2867c478bd9Sstevel@tonic-gate 2877c478bd9Sstevel@tonic-gatefinish_test 288