diff options
Diffstat (limited to 'third_party/sqlite/src/test/insert4.test')
| -rw-r--r-- | third_party/sqlite/src/test/insert4.test | 306 |
1 files changed, 306 insertions, 0 deletions
diff --git a/third_party/sqlite/src/test/insert4.test b/third_party/sqlite/src/test/insert4.test new file mode 100644 index 0000000..7e87612 --- /dev/null +++ b/third_party/sqlite/src/test/insert4.test @@ -0,0 +1,306 @@ +# 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; + } +} {1 9 9 1} +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} + + + + +finish_test |