1# 2007 January 24 2# 3# The author disclaims copyright to this source code. In place of 4# 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. The 12# focus of this file is testing the INSERT transfer optimization. 13# 14# $Id: insert4.test,v 1.6 2007/09/03 17:30:07 danielk1977 Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# The sqlite3_xferopt_count variable is incremented whenever the 20# insert transfer optimization applies. 21# 22# This procedure runs a test to see if the sqlite3_xferopt_count is 23# set to N. 24# 25proc xferopt_test {testname N} { 26 do_test $testname {set ::sqlite3_xferopt_count} $N 27} 28 29# Create tables used for testing. 30# 31execsql { 32 PRAGMA legacy_file_format = 0; 33 CREATE TABLE t1(a int, b int, check(b>a)); 34 CREATE TABLE t2(x int, y int); 35 CREATE VIEW v2 AS SELECT y, x FROM t2; 36 CREATE TABLE t3(a int, b int); 37} 38 39# Ticket #2252. Make sure the an INSERT from identical tables 40# does not violate constraints. 41# 42do_test insert4-1.1 { 43 set sqlite3_xferopt_count 0 44 execsql { 45 DELETE FROM t1; 46 DELETE FROM t2; 47 INSERT INTO t2 VALUES(9,1); 48 } 49 catchsql { 50 INSERT INTO t1 SELECT * FROM t2; 51 } 52} {1 {constraint failed}} 53xferopt_test insert4-1.2 0 54do_test insert4-1.3 { 55 execsql { 56 SELECT * FROM t1; 57 } 58} {} 59 60# Tests to make sure that the transfer optimization is not occurring 61# when it is not a valid optimization. 62# 63# The SELECT must be against a real table. 64do_test insert4-2.1.1 { 65 execsql { 66 DELETE FROM t1; 67 INSERT INTO t1 SELECT 4, 8; 68 SELECT * FROM t1; 69 } 70} {4 8} 71xferopt_test insert4-2.1.2 0 72do_test insert4-2.2.1 { 73 catchsql { 74 DELETE FROM t1; 75 INSERT INTO t1 SELECT * FROM v2; 76 SELECT * FROM t1; 77 } 78} {0 {1 9}} 79xferopt_test insert4-2.2.2 0 80 81# Do not run the transfer optimization if there is a LIMIT clause 82# 83do_test insert4-2.3.1 { 84 execsql { 85 DELETE FROM t2; 86 INSERT INTO t2 VALUES(9,1); 87 INSERT INTO t2 SELECT y, x FROM t2; 88 INSERT INTO t3 SELECT * FROM t2 LIMIT 1; 89 SELECT * FROM t3; 90 } 91} {9 1} 92xferopt_test insert4-2.3.2 0 93do_test insert4-2.3.3 { 94 catchsql { 95 DELETE FROM t1; 96 INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 97 SELECT * FROM t1; 98 } 99} {1 {constraint failed}} 100xferopt_test insert4-2.3.4 0 101 102# Do not run the transfer optimization if there is a DISTINCT 103# 104do_test insert4-2.4.1 { 105 execsql { 106 DELETE FROM t3; 107 INSERT INTO t3 SELECT DISTINCT * FROM t2; 108 SELECT * FROM t3; 109 } 110} {9 1 1 9} 111xferopt_test insert4-2.4.2 0 112do_test insert4-2.4.3 { 113 catchsql { 114 DELETE FROM t1; 115 INSERT INTO t1 SELECT DISTINCT * FROM t2; 116 } 117} {1 {constraint failed}} 118xferopt_test insert4-2.4.4 0 119 120# The following procedure constructs two tables then tries to transfer 121# data from one table to the other. Checks are made to make sure the 122# transfer is successful and that the transfer optimization was used or 123# not, as appropriate. 124# 125# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA 126# 127# The TESTID argument is the symbolic name for this test. The XFER-USED 128# argument is true if the transfer optimization should be employed and 129# false if not. INIT-DATA is a single row of data that is to be 130# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for 131# the destination and source tables. 132# 133proc xfer_check {testid xferused initdata destschema srcschema} { 134 execsql "CREATE TABLE dest($destschema)" 135 execsql "CREATE TABLE src($srcschema)" 136 execsql "INSERT INTO src VALUES([join $initdata ,])" 137 set ::sqlite3_xferopt_count 0 138 do_test $testid.1 { 139 execsql { 140 INSERT INTO dest SELECT * FROM src; 141 SELECT * FROM dest; 142 } 143 } $initdata 144 do_test $testid.2 { 145 set ::sqlite3_xferopt_count 146 } $xferused 147 execsql { 148 DROP TABLE dest; 149 DROP TABLE src; 150 } 151} 152 153 154# Do run the transfer optimization if tables have identical 155# CHECK constraints. 156# 157xfer_check insert4-3.1 1 {1 9} \ 158 {a int, b int CHECK(b>a)} \ 159 {x int, y int CHECK(y>x)} 160xfer_check insert4-3.2 1 {1 9} \ 161 {a int, b int CHECK(b>a)} \ 162 {x int CHECK(y>x), y int} 163 164# Do run the transfer optimization if the destination table lacks 165# any CHECK constraints regardless of whether or not there are CHECK 166# constraints on the source table. 167# 168xfer_check insert4-3.3 1 {1 9} \ 169 {a int, b int} \ 170 {x int, y int CHECK(y>x)} 171 172# Do run the transfer optimization if the destination table omits 173# NOT NULL constraints that the source table has. 174# 175xfer_check insert4-3.4 0 {1 9} \ 176 {a int, b int CHECK(b>a)} \ 177 {x int, y int} 178 179# Do not run the optimization if the destination has NOT NULL 180# constraints that the source table lacks. 181# 182xfer_check insert4-3.5 0 {1 9} \ 183 {a int, b int NOT NULL} \ 184 {x int, y int} 185xfer_check insert4-3.6 0 {1 9} \ 186 {a int, b int NOT NULL} \ 187 {x int NOT NULL, y int} 188xfer_check insert4-3.7 0 {1 9} \ 189 {a int NOT NULL, b int NOT NULL} \ 190 {x int NOT NULL, y int} 191xfer_check insert4-3.8 0 {1 9} \ 192 {a int NOT NULL, b int} \ 193 {x int, y int} 194 195 196# Do run the transfer optimization if the destination table and 197# source table have the same NOT NULL constraints or if the 198# source table has extra NOT NULL constraints. 199# 200xfer_check insert4-3.9 1 {1 9} \ 201 {a int, b int} \ 202 {x int NOT NULL, y int} 203xfer_check insert4-3.10 1 {1 9} \ 204 {a int, b int} \ 205 {x int NOT NULL, y int NOT NULL} 206xfer_check insert4-3.11 1 {1 9} \ 207 {a int NOT NULL, b int} \ 208 {x int NOT NULL, y int NOT NULL} 209xfer_check insert4-3.12 1 {1 9} \ 210 {a int, b int NOT NULL} \ 211 {x int NOT NULL, y int NOT NULL} 212 213# Do not run the optimization if any corresponding table 214# columns have different affinities. 215# 216xfer_check insert4-3.20 0 {1 9} \ 217 {a text, b int} \ 218 {x int, b int} 219xfer_check insert4-3.21 0 {1 9} \ 220 {a int, b int} \ 221 {x text, b int} 222 223# "int" and "integer" are equivalent so the optimization should 224# run here. 225# 226xfer_check insert4-3.22 1 {1 9} \ 227 {a int, b int} \ 228 {x integer, b int} 229 230# Ticket #2291. 231# 232do_test insert4-4.1 { 233 execsql { 234 CREATE TABLE t4(a, b, UNIQUE(a,b)); 235 INSERT INTO t4 VALUES(NULL,0); 236 INSERT INTO t4 VALUES(NULL,1); 237 INSERT INTO t4 VALUES(NULL,1); 238 VACUUM; 239 } 240} {} 241 242# Check some error conditions: 243# 244do_test insert4-5.1 { 245 # Table does not exist. 246 catchsql { INSERT INTO t2 SELECT * FROM nosuchtable } 247} {1 {no such table: nosuchtable}} 248do_test insert4-5.2 { 249 # Number of columns does not match. 250 catchsql { 251 CREATE TABLE t5(a, b, c); 252 INSERT INTO t4 SELECT * FROM t5; 253 } 254} {1 {table t4 has 2 columns but 3 values were supplied}} 255 256do_test insert4-6.1 { 257 execsql { 258 CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase); 259 CREATE INDEX t2_i1 ON t2(x ASC, y DESC); 260 CREATE INDEX t3_i1 ON t3(a, b); 261 INSERT INTO t2 SELECT * FROM t3; 262 } 263} {} 264 265 266 267finish_test 268