| OLD | NEW |
|---|
| (Empty) |
| 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 {CHECK constraint failed: t1}} | |
| 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 {CHECK constraint failed: t1}} | |
| 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 } {9 1 1 9} | |
| 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 {CHECK constraint failed: t1}} | |
| 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 a, b 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 {CHECK constraint failed: t6b}} | |
| 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 {CHECK constraint failed: t6b}} | |
| 328 | |
| 329 # Ticket [6284df89debdfa61db8073e062908af0c9b6118e] | |
| 330 # Disable the xfer optimization if the destination table contains | |
| 331 # a foreign key constraint | |
| 332 # | |
| 333 ifcapable foreignkey { | |
| 334 do_test insert4-7.1 { | |
| 335 set ::sqlite3_xferopt_count 0 | |
| 336 execsql { | |
| 337 CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123); | |
| 338 CREATE TABLE t7b(y INTEGER REFERENCES t7a); | |
| 339 CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234); | |
| 340 INSERT INTO t7b SELECT * FROM t7c; | |
| 341 SELECT * FROM t7b; | |
| 342 } | |
| 343 } {234} | |
| 344 do_test insert4-7.2 { | |
| 345 set ::sqlite3_xferopt_count | |
| 346 } {1} | |
| 347 do_test insert4-7.3 { | |
| 348 set ::sqlite3_xferopt_count 0 | |
| 349 execsql { | |
| 350 DELETE FROM t7b; | |
| 351 PRAGMA foreign_keys=ON; | |
| 352 } | |
| 353 catchsql { | |
| 354 INSERT INTO t7b SELECT * FROM t7c; | |
| 355 } | |
| 356 } {1 {FOREIGN KEY constraint failed}} | |
| 357 do_test insert4-7.4 { | |
| 358 execsql {SELECT * FROM t7b} | |
| 359 } {} | |
| 360 do_test insert4-7.5 { | |
| 361 set ::sqlite3_xferopt_count | |
| 362 } {0} | |
| 363 do_test insert4-7.6 { | |
| 364 set ::sqlite3_xferopt_count 0 | |
| 365 execsql { | |
| 366 DELETE FROM t7b; DELETE FROM t7c; | |
| 367 INSERT INTO t7c VALUES(123); | |
| 368 INSERT INTO t7b SELECT * FROM t7c; | |
| 369 SELECT * FROM t7b; | |
| 370 } | |
| 371 } {123} | |
| 372 do_test insert4-7.7 { | |
| 373 set ::sqlite3_xferopt_count | |
| 374 } {0} | |
| 375 do_test insert4-7.7 { | |
| 376 set ::sqlite3_xferopt_count 0 | |
| 377 execsql { | |
| 378 PRAGMA foreign_keys=OFF; | |
| 379 DELETE FROM t7b; | |
| 380 INSERT INTO t7b SELECT * FROM t7c; | |
| 381 SELECT * FROM t7b; | |
| 382 } | |
| 383 } {123} | |
| 384 do_test insert4-7.8 { | |
| 385 set ::sqlite3_xferopt_count | |
| 386 } {1} | |
| 387 } | |
| 388 | |
| 389 # Ticket [676bc02b87176125635cb174d110b431581912bb] | |
| 390 # Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer | |
| 391 # optimization. | |
| 392 # | |
| 393 do_test insert4-8.1 { | |
| 394 execsql { | |
| 395 DROP TABLE IF EXISTS t1; | |
| 396 DROP TABLE IF EXISTS t2; | |
| 397 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); | |
| 398 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); | |
| 399 INSERT INTO t1 VALUES(1,2); | |
| 400 INSERT INTO t2 VALUES(1,3); | |
| 401 INSERT INTO t1 SELECT * FROM t2; | |
| 402 SELECT * FROM t1; | |
| 403 } | |
| 404 } {1 3} | |
| 405 do_test insert4-8.2 { | |
| 406 execsql { | |
| 407 DROP TABLE IF EXISTS t1; | |
| 408 DROP TABLE IF EXISTS t2; | |
| 409 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); | |
| 410 CREATE TABLE t2(x, y); | |
| 411 INSERT INTO t1 VALUES(1,2); | |
| 412 INSERT INTO t2 VALUES(1,3); | |
| 413 INSERT INTO t1 SELECT * FROM t2; | |
| 414 SELECT * FROM t1; | |
| 415 } | |
| 416 } {1 3} | |
| 417 do_test insert4-8.3 { | |
| 418 execsql { | |
| 419 DROP TABLE IF EXISTS t1; | |
| 420 DROP TABLE IF EXISTS t2; | |
| 421 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); | |
| 422 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); | |
| 423 INSERT INTO t1 VALUES(1,2); | |
| 424 INSERT INTO t2 VALUES(1,3); | |
| 425 INSERT INTO t1 SELECT * FROM t2; | |
| 426 SELECT * FROM t1; | |
| 427 } | |
| 428 } {1 2} | |
| 429 do_test insert4-8.4 { | |
| 430 execsql { | |
| 431 DROP TABLE IF EXISTS t1; | |
| 432 DROP TABLE IF EXISTS t2; | |
| 433 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); | |
| 434 CREATE TABLE t2(x, y); | |
| 435 INSERT INTO t1 VALUES(1,2); | |
| 436 INSERT INTO t2 VALUES(1,3); | |
| 437 INSERT INTO t1 SELECT * FROM t2; | |
| 438 SELECT * FROM t1; | |
| 439 } | |
| 440 } {1 2} | |
| 441 do_test insert4-8.5 { | |
| 442 execsql { | |
| 443 DROP TABLE IF EXISTS t1; | |
| 444 DROP TABLE IF EXISTS t2; | |
| 445 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); | |
| 446 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); | |
| 447 INSERT INTO t1 VALUES(1,2); | |
| 448 INSERT INTO t2 VALUES(-99,100); | |
| 449 INSERT INTO t2 VALUES(1,3); | |
| 450 SELECT * FROM t1; | |
| 451 } | |
| 452 catchsql { | |
| 453 INSERT INTO t1 SELECT * FROM t2; | |
| 454 } | |
| 455 } {1 {UNIQUE constraint failed: t1.a}} | |
| 456 do_test insert4-8.6 { | |
| 457 execsql { | |
| 458 SELECT * FROM t1; | |
| 459 } | |
| 460 } {-99 100 1 2} | |
| 461 do_test insert4-8.7 { | |
| 462 execsql { | |
| 463 DROP TABLE IF EXISTS t1; | |
| 464 DROP TABLE IF EXISTS t2; | |
| 465 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); | |
| 466 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); | |
| 467 INSERT INTO t1 VALUES(1,2); | |
| 468 INSERT INTO t2 VALUES(-99,100); | |
| 469 INSERT INTO t2 VALUES(1,3); | |
| 470 SELECT * FROM t1; | |
| 471 } | |
| 472 catchsql { | |
| 473 INSERT INTO t1 SELECT * FROM t2; | |
| 474 } | |
| 475 } {1 {UNIQUE constraint failed: t1.a}} | |
| 476 do_test insert4-8.8 { | |
| 477 execsql { | |
| 478 SELECT * FROM t1; | |
| 479 } | |
| 480 } {1 2} | |
| 481 do_test insert4-8.9 { | |
| 482 execsql { | |
| 483 DROP TABLE IF EXISTS t1; | |
| 484 DROP TABLE IF EXISTS t2; | |
| 485 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); | |
| 486 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); | |
| 487 INSERT INTO t1 VALUES(1,2); | |
| 488 INSERT INTO t2 VALUES(-99,100); | |
| 489 INSERT INTO t2 VALUES(1,3); | |
| 490 SELECT * FROM t1; | |
| 491 } | |
| 492 catchsql { | |
| 493 BEGIN; | |
| 494 INSERT INTO t1 VALUES(2,3); | |
| 495 INSERT INTO t1 SELECT * FROM t2; | |
| 496 } | |
| 497 } {1 {UNIQUE constraint failed: t1.a}} | |
| 498 do_test insert4-8.10 { | |
| 499 catchsql {COMMIT} | |
| 500 } {1 {cannot commit - no transaction is active}} | |
| 501 do_test insert4-8.11 { | |
| 502 execsql { | |
| 503 SELECT * FROM t1; | |
| 504 } | |
| 505 } {1 2} | |
| 506 | |
| 507 do_test insert4-8.21 { | |
| 508 execsql { | |
| 509 DROP TABLE IF EXISTS t1; | |
| 510 DROP TABLE IF EXISTS t2; | |
| 511 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); | |
| 512 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); | |
| 513 INSERT INTO t2 VALUES(1,3); | |
| 514 INSERT INTO t1 SELECT * FROM t2; | |
| 515 SELECT * FROM t1; | |
| 516 } | |
| 517 } {1 3} | |
| 518 do_test insert4-8.22 { | |
| 519 execsql { | |
| 520 DROP TABLE IF EXISTS t1; | |
| 521 DROP TABLE IF EXISTS t2; | |
| 522 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); | |
| 523 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); | |
| 524 INSERT INTO t2 VALUES(1,3); | |
| 525 INSERT INTO t1 SELECT * FROM t2; | |
| 526 SELECT * FROM t1; | |
| 527 } | |
| 528 } {1 3} | |
| 529 do_test insert4-8.23 { | |
| 530 execsql { | |
| 531 DROP TABLE IF EXISTS t1; | |
| 532 DROP TABLE IF EXISTS t2; | |
| 533 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); | |
| 534 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); | |
| 535 INSERT INTO t2 VALUES(1,3); | |
| 536 INSERT INTO t1 SELECT * FROM t2; | |
| 537 SELECT * FROM t1; | |
| 538 } | |
| 539 } {1 3} | |
| 540 do_test insert4-8.24 { | |
| 541 execsql { | |
| 542 DROP TABLE IF EXISTS t1; | |
| 543 DROP TABLE IF EXISTS t2; | |
| 544 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); | |
| 545 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); | |
| 546 INSERT INTO t2 VALUES(1,3); | |
| 547 INSERT INTO t1 SELECT * FROM t2; | |
| 548 SELECT * FROM t1; | |
| 549 } | |
| 550 } {1 3} | |
| 551 do_test insert4-8.25 { | |
| 552 execsql { | |
| 553 DROP TABLE IF EXISTS t1; | |
| 554 DROP TABLE IF EXISTS t2; | |
| 555 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); | |
| 556 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); | |
| 557 INSERT INTO t2 VALUES(1,3); | |
| 558 INSERT INTO t1 SELECT * FROM t2; | |
| 559 SELECT * FROM t1; | |
| 560 } | |
| 561 } {1 3} | |
| 562 | |
| 563 | |
| 564 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
