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.3 2007/02/24 15:18:50 drh 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 CREATE TABLE t1(a int, b int, check(b>a)); 33 CREATE TABLE t2(x int, y int); 34 CREATE VIEW v2 AS SELECT y, x FROM t2; 35 CREATE TABLE t3(a int, b int); 36} 37 38# Ticket #2252. Make sure the an INSERT from identical tables 39# does not violate constraints. 40# 41do_test insert4-1.1 { 42 set sqlite3_xferopt_count 0 43 execsql { 44 DELETE FROM t1; 45 DELETE FROM t2; 46 INSERT INTO t2 VALUES(9,1); 47 } 48 catchsql { 49 INSERT INTO t1 SELECT * FROM t2; 50 } 51} {1 {constraint failed}} 52xferopt_test insert4-1.2 0 53do_test insert4-1.3 { 54 execsql { 55 SELECT * FROM t1; 56 } 57} {} 58 59# Tests to make sure that the transfer optimization is not occurring 60# when it is not a valid optimization. 61# 62# The SELECT must be against a real table. 63do_test insert4-2.1.1 { 64 execsql { 65 DELETE FROM t1; 66 INSERT INTO t1 SELECT 4, 8; 67 SELECT * FROM t1; 68 } 69} {4 8} 70xferopt_test insert4-2.1.2 0 71do_test insert4-2.2.1 { 72 catchsql { 73 DELETE FROM t1; 74 INSERT INTO t1 SELECT * FROM v2; 75 SELECT * FROM t1; 76 } 77} {0 {1 9}} 78xferopt_test insert4-2.2.2 0 79 80# Do not run the transfer optimization if there is a LIMIT clause 81# 82do_test insert4-2.3.1 { 83 execsql { 84 DELETE FROM t2; 85 INSERT INTO t2 VALUES(9,1); 86 INSERT INTO t2 SELECT y, x FROM t2; 87 INSERT INTO t3 SELECT * FROM t2 LIMIT 1; 88 SELECT * FROM t3; 89 } 90} {9 1} 91xferopt_test insert4-2.3.2 0 92do_test insert4-2.3.3 { 93 catchsql { 94 DELETE FROM t1; 95 INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 96 SELECT * FROM t1; 97 } 98} {1 {constraint failed}} 99xferopt_test insert4-2.3.4 0 100 101# Do not run the transfer optimization if there is a DISTINCT 102# 103do_test insert4-2.4.1 { 104 execsql { 105 DELETE FROM t3; 106 INSERT INTO t3 SELECT DISTINCT * FROM t2; 107 SELECT * FROM t3; 108 } 109} {9 1 1 9} 110xferopt_test insert4-2.4.2 0 111do_test insert4-2.4.3 { 112 catchsql { 113 DELETE FROM t1; 114 INSERT INTO t1 SELECT DISTINCT * FROM t2; 115 } 116} {1 {constraint failed}} 117xferopt_test insert4-2.4.4 0 118 119# The following procedure constructs two tables then tries to transfer 120# data from one table to the other. Checks are made to make sure the 121# transfer is successful and that the transfer optimization was used or 122# not, as appropriate. 123# 124# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA 125# 126# The TESTID argument is the symbolic name for this test. The XFER-USED 127# argument is true if the transfer optimization should be employed and 128# false if not. INIT-DATA is a single row of data that is to be 129# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for 130# the destination and source tables. 131# 132proc xfer_check {testid xferused initdata destschema srcschema} { 133 execsql "CREATE TABLE dest($destschema)" 134 execsql "CREATE TABLE src($srcschema)" 135 execsql "INSERT INTO src VALUES([join $initdata ,])" 136 set ::sqlite3_xferopt_count 0 137 do_test $testid.1 { 138 execsql { 139 INSERT INTO dest SELECT * FROM src; 140 SELECT * FROM dest; 141 } 142 } $initdata 143 do_test $testid.2 { 144 set ::sqlite3_xferopt_count 145 } $xferused 146 execsql { 147 DROP TABLE dest; 148 DROP TABLE src; 149 } 150} 151 152 153# Do run the transfer optimization if tables have identical 154# CHECK constraints. 155# 156xfer_check insert4-3.1 1 {1 9} \ 157 {a int, b int CHECK(b>a)} \ 158 {x int, y int CHECK(y>x)} 159xfer_check insert4-3.2 1 {1 9} \ 160 {a int, b int CHECK(b>a)} \ 161 {x int CHECK(y>x), y int} 162 163# Do run the transfer optimization if the destination table lacks 164# any CHECK constraints regardless of whether or not there are CHECK 165# constraints on the source table. 166# 167xfer_check insert4-3.3 1 {1 9} \ 168 {a int, b int} \ 169 {x int, y int CHECK(y>x)} 170 171# Do run the transfer optimization if the destination table omits 172# NOT NULL constraints that the source table has. 173# 174xfer_check insert4-3.4 0 {1 9} \ 175 {a int, b int CHECK(b>a)} \ 176 {x int, y int} 177 178# Do not run the optimization if the destination has NOT NULL 179# constraints that the source table lacks. 180# 181xfer_check insert4-3.5 0 {1 9} \ 182 {a int, b int NOT NULL} \ 183 {x int, y int} 184xfer_check insert4-3.6 0 {1 9} \ 185 {a int, b int NOT NULL} \ 186 {x int NOT NULL, y int} 187xfer_check insert4-3.7 0 {1 9} \ 188 {a int NOT NULL, b int NOT NULL} \ 189 {x int NOT NULL, y int} 190xfer_check insert4-3.8 0 {1 9} \ 191 {a int NOT NULL, b int} \ 192 {x int, y int} 193 194 195# Do run the transfer optimization if the destination table and 196# source table have the same NOT NULL constraints or if the 197# source table has extra NOT NULL constraints. 198# 199xfer_check insert4-3.9 1 {1 9} \ 200 {a int, b int} \ 201 {x int NOT NULL, y int} 202xfer_check insert4-3.10 1 {1 9} \ 203 {a int, b int} \ 204 {x int NOT NULL, y int NOT NULL} 205xfer_check insert4-3.11 1 {1 9} \ 206 {a int NOT NULL, b int} \ 207 {x int NOT NULL, y int NOT NULL} 208xfer_check insert4-3.12 1 {1 9} \ 209 {a int, b int NOT NULL} \ 210 {x int NOT NULL, y int NOT NULL} 211 212# Do not run the optimization if any corresponding table 213# columns have different affinities. 214# 215xfer_check insert4-3.20 0 {1 9} \ 216 {a text, b int} \ 217 {x int, b int} 218xfer_check insert4-3.21 0 {1 9} \ 219 {a int, b int} \ 220 {x text, b int} 221 222# "int" and "integer" are equivalent so the optimization should 223# run here. 224# 225xfer_check insert4-3.22 1 {1 9} \ 226 {a int, b int} \ 227 {x integer, b int} 228 229 230finish_test 231