diff options
Diffstat (limited to 'test/insert4.test')
| -rw-r--r-- | test/insert4.test | 389 |
1 files changed, 389 insertions, 0 deletions
diff --git a/test/insert4.test b/test/insert4.test new file mode 100644 index 0000000..cb02b9d --- /dev/null +++ b/test/insert4.test @@ -0,0 +1,389 @@ +# 2007 January 24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the INSERT transfer optimization. +# +# $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable !view||!subquery { + finish_test + return +} + +# The sqlite3_xferopt_count variable is incremented whenever the +# insert transfer optimization applies. +# +# This procedure runs a test to see if the sqlite3_xferopt_count is +# set to N. +# +proc xferopt_test {testname N} { + do_test $testname {set ::sqlite3_xferopt_count} $N +} + +# Create tables used for testing. +# +execsql { + PRAGMA legacy_file_format = 0; + CREATE TABLE t1(a int, b int, check(b>a)); + CREATE TABLE t2(x int, y int); + CREATE VIEW v2 AS SELECT y, x FROM t2; + CREATE TABLE t3(a int, b int); +} + +# Ticket #2252. Make sure the an INSERT from identical tables +# does not violate constraints. +# +do_test insert4-1.1 { + set sqlite3_xferopt_count 0 + execsql { + DELETE FROM t1; + DELETE FROM t2; + INSERT INTO t2 VALUES(9,1); + } + catchsql { + INSERT INTO t1 SELECT * FROM t2; + } +} {1 {constraint failed}} +xferopt_test insert4-1.2 0 +do_test insert4-1.3 { + execsql { + SELECT * FROM t1; + } +} {} + +# Tests to make sure that the transfer optimization is not occurring +# when it is not a valid optimization. +# +# The SELECT must be against a real table. +do_test insert4-2.1.1 { + execsql { + DELETE FROM t1; + INSERT INTO t1 SELECT 4, 8; + SELECT * FROM t1; + } +} {4 8} +xferopt_test insert4-2.1.2 0 +do_test insert4-2.2.1 { + catchsql { + DELETE FROM t1; + INSERT INTO t1 SELECT * FROM v2; + SELECT * FROM t1; + } +} {0 {1 9}} +xferopt_test insert4-2.2.2 0 + +# Do not run the transfer optimization if there is a LIMIT clause +# +do_test insert4-2.3.1 { + execsql { + DELETE FROM t2; + INSERT INTO t2 VALUES(9,1); + INSERT INTO t2 SELECT y, x FROM t2; + INSERT INTO t3 SELECT * FROM t2 LIMIT 1; + SELECT * FROM t3; + } +} {9 1} +xferopt_test insert4-2.3.2 0 +do_test insert4-2.3.3 { + catchsql { + DELETE FROM t1; + INSERT INTO t1 SELECT * FROM t2 LIMIT 1; + SELECT * FROM t1; + } +} {1 {constraint failed}} +xferopt_test insert4-2.3.4 0 + +# Do not run the transfer optimization if there is a DISTINCT +# +do_test insert4-2.4.1 { + execsql { + DELETE FROM t3; + INSERT INTO t3 SELECT DISTINCT * FROM t2; + SELECT * FROM t3; + } +} {9 1 1 9} +xferopt_test insert4-2.4.2 0 +do_test insert4-2.4.3 { + catchsql { + DELETE FROM t1; + INSERT INTO t1 SELECT DISTINCT * FROM t2; + } +} {1 {constraint failed}} +xferopt_test insert4-2.4.4 0 + +# The following procedure constructs two tables then tries to transfer +# data from one table to the other. Checks are made to make sure the +# transfer is successful and that the transfer optimization was used or +# not, as appropriate. +# +# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA +# +# The TESTID argument is the symbolic name for this test. The XFER-USED +# argument is true if the transfer optimization should be employed and +# false if not. INIT-DATA is a single row of data that is to be +# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for +# the destination and source tables. +# +proc xfer_check {testid xferused initdata destschema srcschema} { + execsql "CREATE TABLE dest($destschema)" + execsql "CREATE TABLE src($srcschema)" + execsql "INSERT INTO src VALUES([join $initdata ,])" + set ::sqlite3_xferopt_count 0 + do_test $testid.1 { + execsql { + INSERT INTO dest SELECT * FROM src; + SELECT * FROM dest; + } + } $initdata + do_test $testid.2 { + set ::sqlite3_xferopt_count + } $xferused + execsql { + DROP TABLE dest; + DROP TABLE src; + } +} + + +# Do run the transfer optimization if tables have identical +# CHECK constraints. +# +xfer_check insert4-3.1 1 {1 9} \ + {a int, b int CHECK(b>a)} \ + {x int, y int CHECK(y>x)} +xfer_check insert4-3.2 1 {1 9} \ + {a int, b int CHECK(b>a)} \ + {x int CHECK(y>x), y int} + +# Do run the transfer optimization if the destination table lacks +# any CHECK constraints regardless of whether or not there are CHECK +# constraints on the source table. +# +xfer_check insert4-3.3 1 {1 9} \ + {a int, b int} \ + {x int, y int CHECK(y>x)} + +# Do run the transfer optimization if the destination table omits +# NOT NULL constraints that the source table has. +# +xfer_check insert4-3.4 0 {1 9} \ + {a int, b int CHECK(b>a)} \ + {x int, y int} + +# Do not run the optimization if the destination has NOT NULL +# constraints that the source table lacks. +# +xfer_check insert4-3.5 0 {1 9} \ + {a int, b int NOT NULL} \ + {x int, y int} +xfer_check insert4-3.6 0 {1 9} \ + {a int, b int NOT NULL} \ + {x int NOT NULL, y int} +xfer_check insert4-3.7 0 {1 9} \ + {a int NOT NULL, b int NOT NULL} \ + {x int NOT NULL, y int} +xfer_check insert4-3.8 0 {1 9} \ + {a int NOT NULL, b int} \ + {x int, y int} + + +# Do run the transfer optimization if the destination table and +# source table have the same NOT NULL constraints or if the +# source table has extra NOT NULL constraints. +# +xfer_check insert4-3.9 1 {1 9} \ + {a int, b int} \ + {x int NOT NULL, y int} +xfer_check insert4-3.10 1 {1 9} \ + {a int, b int} \ + {x int NOT NULL, y int NOT NULL} +xfer_check insert4-3.11 1 {1 9} \ + {a int NOT NULL, b int} \ + {x int NOT NULL, y int NOT NULL} +xfer_check insert4-3.12 1 {1 9} \ + {a int, b int NOT NULL} \ + {x int NOT NULL, y int NOT NULL} + +# Do not run the optimization if any corresponding table +# columns have different affinities. +# +xfer_check insert4-3.20 0 {1 9} \ + {a text, b int} \ + {x int, b int} +xfer_check insert4-3.21 0 {1 9} \ + {a int, b int} \ + {x text, b int} + +# "int" and "integer" are equivalent so the optimization should +# run here. +# +xfer_check insert4-3.22 1 {1 9} \ + {a int, b int} \ + {x integer, b int} + +# Ticket #2291. +# + +do_test insert4-4.1a { + execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))} +} {} +ifcapable vacuum { + do_test insert4-4.1b { + execsql { + INSERT INTO t4 VALUES(NULL,0); + INSERT INTO t4 VALUES(NULL,1); + INSERT INTO t4 VALUES(NULL,1); + VACUUM; + } + } {} +} + +# Check some error conditions: +# +do_test insert4-5.1 { + # Table does not exist. + catchsql { INSERT INTO t2 SELECT * FROM nosuchtable } +} {1 {no such table: nosuchtable}} +do_test insert4-5.2 { + # Number of columns does not match. + catchsql { + CREATE TABLE t5(a, b, c); + INSERT INTO t4 SELECT * FROM t5; + } +} {1 {table t4 has 2 columns but 3 values were supplied}} + +do_test insert4-6.1 { + set ::sqlite3_xferopt_count 0 + execsql { + CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase); + CREATE INDEX t2_i1 ON t2(x ASC, y DESC); + CREATE INDEX t3_i1 ON t3(a, b); + INSERT INTO t2 SELECT * FROM t3; + } + set ::sqlite3_xferopt_count +} {0} +do_test insert4-6.2 { + set ::sqlite3_xferopt_count 0 + execsql { + DROP INDEX t2_i2; + INSERT INTO t2 SELECT * FROM t3; + } + set ::sqlite3_xferopt_count +} {0} +do_test insert4-6.3 { + set ::sqlite3_xferopt_count 0 + execsql { + DROP INDEX t2_i1; + CREATE INDEX t2_i1 ON t2(x ASC, y ASC); + INSERT INTO t2 SELECT * FROM t3; + } + set ::sqlite3_xferopt_count +} {1} +do_test insert4-6.4 { + set ::sqlite3_xferopt_count 0 + execsql { + DROP INDEX t2_i1; + CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM); + INSERT INTO t2 SELECT * FROM t3; + } + set ::sqlite3_xferopt_count +} {0} + + +do_test insert4-6.5 { + execsql { + CREATE TABLE t6a(x CHECK( x<>'abc' )); + INSERT INTO t6a VALUES('ABC'); + SELECT * FROM t6a; + } +} {ABC} +do_test insert4-6.6 { + execsql { + CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase )); + } + catchsql { + INSERT INTO t6b SELECT * FROM t6a; + } +} {1 {constraint failed}} +do_test insert4-6.7 { + execsql { + DROP TABLE t6b; + CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' )); + } + catchsql { + INSERT INTO t6b SELECT * FROM t6a; + } +} {1 {constraint failed}} + +# Ticket [6284df89debdfa61db8073e062908af0c9b6118e] +# Disable the xfer optimization if the destination table contains +# a foreign key constraint +# +ifcapable foreignkey { + do_test insert4-7.1 { + set ::sqlite3_xferopt_count 0 + execsql { + CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123); + CREATE TABLE t7b(y INTEGER REFERENCES t7a); + CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234); + INSERT INTO t7b SELECT * FROM t7c; + SELECT * FROM t7b; + } + } {234} + do_test insert4-7.2 { + set ::sqlite3_xferopt_count + } {1} + do_test insert4-7.3 { + set ::sqlite3_xferopt_count 0 + execsql { + DELETE FROM t7b; + PRAGMA foreign_keys=ON; + } + catchsql { + INSERT INTO t7b SELECT * FROM t7c; + } + } {1 {foreign key constraint failed}} + do_test insert4-7.4 { + execsql {SELECT * FROM t7b} + } {} + do_test insert4-7.5 { + set ::sqlite3_xferopt_count + } {0} + do_test insert4-7.6 { + set ::sqlite3_xferopt_count 0 + execsql { + DELETE FROM t7b; DELETE FROM t7c; + INSERT INTO t7c VALUES(123); + INSERT INTO t7b SELECT * FROM t7c; + SELECT * FROM t7b; + } + } {123} + do_test insert4-7.7 { + set ::sqlite3_xferopt_count + } {0} + do_test insert4-7.7 { + set ::sqlite3_xferopt_count 0 + execsql { + PRAGMA foreign_keys=OFF; + DELETE FROM t7b; + INSERT INTO t7b SELECT * FROM t7c; + SELECT * FROM t7b; + } + } {123} + do_test insert4-7.8 { + set ::sqlite3_xferopt_count + } {1} +} + +finish_test |