| 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 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix insert4 |
| 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 |
sqlite3_db_config db LEGACY_FILE_FORMAT 0 |
| 37 |
execsql { |
| 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: b>a}} |
| 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: b>a}} |
| 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: b>a}} |
| 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: x<>'abc' COLLATE nocase}} |
| 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: x COLLATE nocase <>'abc'}} |
| 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 |
do_catchsql_test insert4-9.1 { |
| 564 |
DROP TABLE IF EXISTS t1; |
| 565 |
CREATE TABLE t1(x); |
| 566 |
INSERT INTO t1(x) VALUES(5 COLLATE xyzzy) UNION SELECT 0; |
| 567 |
} {1 {no such collation sequence: xyzzy}} |
| 568 |
|
| 569 |
#------------------------------------------------------------------------- |
| 570 |
# Check that running an integrity-check does not disable the xfer |
| 571 |
# optimization for tables with CHECK constraints. |
| 572 |
# |
| 573 |
do_execsql_test 10.1 { |
| 574 |
CREATE TABLE t8( |
| 575 |
rid INTEGER, |
| 576 |
pid INTEGER, |
| 577 |
mid INTEGER, |
| 578 |
px INTEGER DEFAULT(0) CHECK(px IN(0, 1)) |
| 579 |
); |
| 580 |
CREATE TEMP TABLE x( |
| 581 |
rid INTEGER, |
| 582 |
pid INTEGER, |
| 583 |
mid INTEGER, |
| 584 |
px INTEGER DEFAULT(0) CHECK(px IN(0, 1)) |
| 585 |
); |
| 586 |
} |
| 587 |
do_test 10.2 { |
| 588 |
set sqlite3_xferopt_count 0 |
| 589 |
execsql { INSERT INTO x SELECT * FROM t8 } |
| 590 |
set sqlite3_xferopt_count |
| 591 |
} {1} |
| 592 |
|
| 593 |
do_test 10.3 { |
| 594 |
execsql { PRAGMA integrity_check } |
| 595 |
set sqlite3_xferopt_count 0 |
| 596 |
execsql { INSERT INTO x SELECT * FROM t8 } |
| 597 |
set sqlite3_xferopt_count |
| 598 |
} {1} |
| 599 |
|
| 600 |
do_test 10.4 { |
| 601 |
execsql { PRAGMA integrity_check } |
| 602 |
set sqlite3_xferopt_count 0 |
| 603 |
execsql { INSERT INTO x SELECT * FROM t8 RETURNING * } |
| 604 |
set sqlite3_xferopt_count |
| 605 |
} {0} |
| 606 |
|
| 607 |
#------------------------------------------------------------------------- |
| 608 |
# xfer transfer between tables where the source has an empty partial index. |
| 609 |
# |
| 610 |
do_execsql_test 11.0 { |
| 611 |
CREATE TABLE t9(a, b, c); |
| 612 |
CREATE INDEX t9a ON t9(a); |
| 613 |
CREATE INDEX t9b ON t9(b) WHERE c=0; |
| 614 |
|
| 615 |
INSERT INTO t9 VALUES(1, 1, 1); |
| 616 |
INSERT INTO t9 VALUES(2, 2, 2); |
| 617 |
INSERT INTO t9 VALUES(3, 3, 3); |
| 618 |
|
| 619 |
CREATE TABLE t10(a, b, c); |
| 620 |
CREATE INDEX t10a ON t10(a); |
| 621 |
CREATE INDEX t10b ON t10(b) WHERE c=0; |
| 622 |
|
| 623 |
INSERT INTO t10 SELECT * FROM t9; |
| 624 |
SELECT * FROM t10; |
| 625 |
PRAGMA integrity_check; |
| 626 |
} {1 1 1 2 2 2 3 3 3 ok} |
| 627 |
|
| 628 |
finish_test |