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