third_party/sqlite/src/test/insert4.test

   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