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 set testprefix insert4
  19
  20 ifcapable !view||!subquery {
  21   finish_test
  22   return
  23 }
  24
  25 # The sqlite3_xferopt_count variable is incremented whenever the
  26 # insert transfer optimization applies.
  27 #
  28 # This procedure runs a test to see if the sqlite3_xferopt_count is
  29 # set to N.
  30 #
  31 proc xferopt_test {testname N} {
  32   do_test $testname {set ::sqlite3_xferopt_count} $N
  33 }
  34
  35 # Create tables used for testing.
  36 #
  37 execsql {
  38   PRAGMA legacy_file_format = 0;
  39   CREATE TABLE t1(a int, b int, check(b>a));
  40   CREATE TABLE t2(x int, y int);
  41   CREATE VIEW v2 AS SELECT y, x FROM t2;
  42   CREATE TABLE t3(a int, b int);
  43 }
  44
  45 # Ticket #2252.  Make sure the an INSERT from identical tables
  46 # does not violate constraints.
  47 #
  48 do_test insert4-1.1 {
  49   set sqlite3_xferopt_count 0
  50   execsql {
  51     DELETE FROM t1;
  52     DELETE FROM t2;
  53     INSERT INTO t2 VALUES(9,1);
  54   }
  55   catchsql {
  56     INSERT INTO t1 SELECT * FROM t2;
  57   }
  58 } {1 {CHECK constraint failed: t1}}
  59 xferopt_test insert4-1.2 0
  60 do_test insert4-1.3 {
  61   execsql {
  62     SELECT * FROM t1;
  63   }
  64 } {}
  65
  66 # Tests to make sure that the transfer optimization is not occurring
  67 # when it is not a valid optimization.
  68 #
  69 # The SELECT must be against a real table.
  70 do_test insert4-2.1.1 {
  71   execsql {
  72     DELETE FROM t1;
  73     INSERT INTO t1 SELECT 4, 8;
  74     SELECT * FROM t1;
  75   }
  76 } {4 8}
  77 xferopt_test insert4-2.1.2  0
  78 do_test insert4-2.2.1 {
  79   catchsql {
  80     DELETE FROM t1;
  81     INSERT INTO t1 SELECT * FROM v2;
  82     SELECT * FROM t1;
  83   }
  84 } {0 {1 9}}
  85 xferopt_test insert4-2.2.2 0
  86
  87 # Do not run the transfer optimization if there is a LIMIT clause
  88 #
  89 do_test insert4-2.3.1 {
  90   execsql {
  91     DELETE FROM t2;
  92     INSERT INTO t2 VALUES(9,1);
  93     INSERT INTO t2 SELECT y, x FROM t2;
  94     INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
  95     SELECT * FROM t3;
  96   }
  97 } {9 1}
  98 xferopt_test insert4-2.3.2  0
  99 do_test insert4-2.3.3 {
 100   catchsql {
 101     DELETE FROM t1;
 102     INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
 103     SELECT * FROM t1;
 104   }
 105 } {1 {CHECK constraint failed: t1}}
 106 xferopt_test insert4-2.3.4 0
 107
 108 # Do not run the transfer optimization if there is a DISTINCT
 109 #
 110 do_test insert4-2.4.1 {
 111   execsql {
 112     DELETE FROM t3;
 113     INSERT INTO t3 SELECT DISTINCT * FROM t2;
 114     SELECT * FROM t3;
 115   }
 116 } {9 1 1 9}
 117 xferopt_test insert4-2.4.2 0
 118 do_test insert4-2.4.3 {
 119   catchsql {
 120     DELETE FROM t1;
 121     INSERT INTO t1 SELECT DISTINCT * FROM t2;
 122   }
 123 } {1 {CHECK constraint failed: t1}}
 124 xferopt_test insert4-2.4.4 0
 125
 126 # The following procedure constructs two tables then tries to transfer
 127 # data from one table to the other.  Checks are made to make sure the
 128 # transfer is successful and that the transfer optimization was used or
 129 # not, as appropriate.
 130 #
 131 #     xfer_check TESTID  XFER-USED   INIT-DATA   DEST-SCHEMA   SRC-SCHEMA
 132 #
 133 # The TESTID argument is the symbolic name for this test.  The XFER-USED
 134 # argument is true if the transfer optimization should be employed and
 135 # false if not.  INIT-DATA is a single row of data that is to be
 136 # transfered.  DEST-SCHEMA and SRC-SCHEMA are table declarations for
 137 # the destination and source tables.
 138 #
 139 proc xfer_check {testid xferused initdata destschema srcschema} {
 140   execsql "CREATE TABLE dest($destschema)"
 141   execsql "CREATE TABLE src($srcschema)"
 142   execsql "INSERT INTO src VALUES([join $initdata ,])"
 143   set ::sqlite3_xferopt_count 0
 144   do_test $testid.1 {
 145     execsql {
 146       INSERT INTO dest SELECT * FROM src;
 147       SELECT * FROM dest;
 148     }
 149   } $initdata
 150   do_test $testid.2 {
 151     set ::sqlite3_xferopt_count
 152   } $xferused
 153   execsql {
 154     DROP TABLE dest;
 155     DROP TABLE src;
 156   }
 157 }
 158
 159
 160 # Do run the transfer optimization if tables have identical
 161 # CHECK constraints.
 162 #
 163 xfer_check insert4-3.1 1 {1 9} \
 164     {a int, b int CHECK(b>a)} \
 165     {x int, y int CHECK(y>x)}
 166 xfer_check insert4-3.2 1 {1 9} \
 167     {a int, b int CHECK(b>a)} \
 168     {x int CHECK(y>x), y int}
 169
 170 # Do run the transfer optimization if the destination table lacks
 171 # any CHECK constraints regardless of whether or not there are CHECK
 172 # constraints on the source table.
 173 #
 174 xfer_check insert4-3.3 1 {1 9} \
 175     {a int, b int} \
 176     {x int, y int CHECK(y>x)}
 177
 178 # Do run the transfer optimization if the destination table omits
 179 # NOT NULL constraints that the source table has.
 180 #
 181 xfer_check insert4-3.4 0 {1 9} \
 182     {a int, b int CHECK(b>a)} \
 183     {x int, y int}
 184
 185 # Do not run the optimization if the destination has NOT NULL
 186 # constraints that the source table lacks.
 187 #
 188 xfer_check insert4-3.5 0 {1 9} \
 189     {a int, b int NOT NULL} \
 190     {x int, y int}
 191 xfer_check insert4-3.6 0 {1 9} \
 192     {a int, b int NOT NULL} \
 193     {x int NOT NULL, y int}
 194 xfer_check insert4-3.7 0 {1 9} \
 195     {a int NOT NULL, b int NOT NULL} \
 196     {x int NOT NULL, y int}
 197 xfer_check insert4-3.8 0 {1 9} \
 198     {a int NOT NULL, b int} \
 199     {x int, y int}
 200
 201
 202 # Do run the transfer optimization if the destination table and
 203 # source table have the same NOT NULL constraints or if the
 204 # source table has extra NOT NULL constraints.
 205 #
 206 xfer_check insert4-3.9 1 {1 9} \
 207     {a int, b int} \
 208     {x int NOT NULL, y int}
 209 xfer_check insert4-3.10 1 {1 9} \
 210     {a int, b int} \
 211     {x int NOT NULL, y int NOT NULL}
 212 xfer_check insert4-3.11 1 {1 9} \
 213     {a int NOT NULL, b int} \
 214     {x int NOT NULL, y int NOT NULL}
 215 xfer_check insert4-3.12 1 {1 9} \
 216     {a int, b int NOT NULL} \
 217     {x int NOT NULL, y int NOT NULL}
 218
 219 # Do not run the optimization if any corresponding table
 220 # columns have different affinities.
 221 #
 222 xfer_check insert4-3.20 0 {1 9} \
 223     {a text, b int} \
 224     {x int, b int}
 225 xfer_check insert4-3.21 0 {1 9} \
 226     {a int, b int} \
 227     {x text, b int}
 228
 229 # "int" and "integer" are equivalent so the optimization should
 230 # run here.
 231 #
 232 xfer_check insert4-3.22 1 {1 9} \
 233     {a int, b int} \
 234     {x integer, b int}
 235
 236 # Ticket #2291.
 237 #
 238
 239 do_test insert4-4.1a {
 240   execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
 241 } {}
 242 ifcapable vacuum {
 243   do_test insert4-4.1b {
 244     execsql {
 245       INSERT INTO t4 VALUES(NULL,0);
 246       INSERT INTO t4 VALUES(NULL,1);
 247       INSERT INTO t4 VALUES(NULL,1);
 248       VACUUM;
 249     }
 250   } {}
 251 }
 252
 253 # Check some error conditions:
 254 #
 255 do_test insert4-5.1 {
 256   # Table does not exist.
 257   catchsql { INSERT INTO t2 SELECT a, b FROM nosuchtable }
 258 } {1 {no such table: nosuchtable}}
 259 do_test insert4-5.2 {
 260   # Number of columns does not match.
 261   catchsql {
 262     CREATE TABLE t5(a, b, c);
 263     INSERT INTO t4 SELECT * FROM t5;
 264   }
 265 } {1 {table t4 has 2 columns but 3 values were supplied}}
 266
 267 do_test insert4-6.1 {
 268   set ::sqlite3_xferopt_count 0
 269   execsql {
 270     CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase);
 271     CREATE INDEX t2_i1 ON t2(x ASC, y DESC);
 272     CREATE INDEX t3_i1 ON t3(a, b);
 273     INSERT INTO t2 SELECT * FROM t3;
 274   }
 275   set ::sqlite3_xferopt_count
 276 } {0}
 277 do_test insert4-6.2 {
 278   set ::sqlite3_xferopt_count 0
 279   execsql {
 280     DROP INDEX t2_i2;
 281     INSERT INTO t2 SELECT * FROM t3;
 282   }
 283   set ::sqlite3_xferopt_count
 284 } {0}
 285 do_test insert4-6.3 {
 286   set ::sqlite3_xferopt_count 0
 287   execsql {
 288     DROP INDEX t2_i1;
 289     CREATE INDEX t2_i1 ON t2(x ASC, y ASC);
 290     INSERT INTO t2 SELECT * FROM t3;
 291   }
 292   set ::sqlite3_xferopt_count
 293 } {1}
 294 do_test insert4-6.4 {
 295   set ::sqlite3_xferopt_count 0
 296   execsql {
 297     DROP INDEX t2_i1;
 298     CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
 299     INSERT INTO t2 SELECT * FROM t3;
 300   }
 301   set ::sqlite3_xferopt_count
 302 } {0}
 303
 304
 305 do_test insert4-6.5 {
 306   execsql {
 307     CREATE TABLE t6a(x CHECK( x<>'abc' ));
 308     INSERT INTO t6a VALUES('ABC');
 309     SELECT * FROM t6a;
 310   }
 311 } {ABC}
 312 do_test insert4-6.6 {
 313   execsql {
 314     CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase ));
 315   }
 316   catchsql {
 317     INSERT INTO t6b SELECT * FROM t6a;
 318   }
 319 } {1 {CHECK constraint failed: t6b}}
 320 do_test insert4-6.7 {
 321   execsql {
 322     DROP TABLE t6b;
 323     CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
 324   }
 325   catchsql {
 326     INSERT INTO t6b SELECT * FROM t6a;
 327   }
 328 } {1 {CHECK constraint failed: t6b}}
 329
 330 # Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
 331 # Disable the xfer optimization if the destination table contains
 332 # a foreign key constraint
 333 #
 334 ifcapable foreignkey {
 335   do_test insert4-7.1 {
 336     set ::sqlite3_xferopt_count 0
 337     execsql {
 338       CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123);
 339       CREATE TABLE t7b(y INTEGER REFERENCES t7a);
 340       CREATE TABLE t7c(z INT);  INSERT INTO t7c VALUES(234);
 341       INSERT INTO t7b SELECT * FROM t7c;
 342       SELECT * FROM t7b;
 343     }
 344   } {234}
 345   do_test insert4-7.2 {
 346     set ::sqlite3_xferopt_count
 347   } {1}
 348   do_test insert4-7.3 {
 349     set ::sqlite3_xferopt_count 0
 350     execsql {
 351       DELETE FROM t7b;
 352       PRAGMA foreign_keys=ON;
 353     }
 354     catchsql {
 355       INSERT INTO t7b SELECT * FROM t7c;
 356     }
 357   } {1 {FOREIGN KEY constraint failed}}
 358   do_test insert4-7.4 {
 359     execsql {SELECT * FROM t7b}
 360   } {}
 361   do_test insert4-7.5 {
 362     set ::sqlite3_xferopt_count
 363   } {0}
 364   do_test insert4-7.6 {
 365     set ::sqlite3_xferopt_count 0
 366     execsql {
 367       DELETE FROM t7b; DELETE FROM t7c;
 368       INSERT INTO t7c VALUES(123);
 369       INSERT INTO t7b SELECT * FROM t7c;
 370       SELECT * FROM t7b;
 371     }
 372   } {123}
 373   do_test insert4-7.7 {
 374     set ::sqlite3_xferopt_count
 375   } {0}
 376   do_test insert4-7.7 {
 377     set ::sqlite3_xferopt_count 0
 378     execsql {
 379       PRAGMA foreign_keys=OFF;
 380       DELETE FROM t7b;
 381       INSERT INTO t7b SELECT * FROM t7c;
 382       SELECT * FROM t7b;
 383     }
 384   } {123}
 385   do_test insert4-7.8 {
 386     set ::sqlite3_xferopt_count
 387   } {1}
 388 }
 389
 390 # Ticket [676bc02b87176125635cb174d110b431581912bb]
 391 # Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer
 392 # optimization.
 393 #
 394 do_test insert4-8.1 {
 395   execsql {
 396     DROP TABLE IF EXISTS t1;
 397     DROP TABLE IF EXISTS t2;
 398     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
 399     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
 400     INSERT INTO t1 VALUES(1,2);
 401     INSERT INTO t2 VALUES(1,3);
 402     INSERT INTO t1 SELECT * FROM t2;
 403     SELECT * FROM t1;
 404   }
 405 } {1 3}
 406 do_test insert4-8.2 {
 407   execsql {
 408     DROP TABLE IF EXISTS t1;
 409     DROP TABLE IF EXISTS t2;
 410     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
 411     CREATE TABLE t2(x, y);
 412     INSERT INTO t1 VALUES(1,2);
 413     INSERT INTO t2 VALUES(1,3);
 414     INSERT INTO t1 SELECT * FROM t2;
 415     SELECT * FROM t1;
 416   }
 417 } {1 3}
 418 do_test insert4-8.3 {
 419   execsql {
 420     DROP TABLE IF EXISTS t1;
 421     DROP TABLE IF EXISTS t2;
 422     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
 423     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
 424     INSERT INTO t1 VALUES(1,2);
 425     INSERT INTO t2 VALUES(1,3);
 426     INSERT INTO t1 SELECT * FROM t2;
 427     SELECT * FROM t1;
 428   }
 429 } {1 2}
 430 do_test insert4-8.4 {
 431   execsql {
 432     DROP TABLE IF EXISTS t1;
 433     DROP TABLE IF EXISTS t2;
 434     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
 435     CREATE TABLE t2(x, y);
 436     INSERT INTO t1 VALUES(1,2);
 437     INSERT INTO t2 VALUES(1,3);
 438     INSERT INTO t1 SELECT * FROM t2;
 439     SELECT * FROM t1;
 440   }
 441 } {1 2}
 442 do_test insert4-8.5 {
 443   execsql {
 444     DROP TABLE IF EXISTS t1;
 445     DROP TABLE IF EXISTS t2;
 446     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
 447     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
 448     INSERT INTO t1 VALUES(1,2);
 449     INSERT INTO t2 VALUES(-99,100);
 450     INSERT INTO t2 VALUES(1,3);
 451     SELECT * FROM t1;
 452   }
 453   catchsql {
 454     INSERT INTO t1 SELECT * FROM t2;
 455   }
 456 } {1 {UNIQUE constraint failed: t1.a}}
 457 do_test insert4-8.6 {
 458   execsql {
 459     SELECT * FROM t1;
 460   }
 461 } {-99 100 1 2}
 462 do_test insert4-8.7 {
 463   execsql {
 464     DROP TABLE IF EXISTS t1;
 465     DROP TABLE IF EXISTS t2;
 466     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
 467     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
 468     INSERT INTO t1 VALUES(1,2);
 469     INSERT INTO t2 VALUES(-99,100);
 470     INSERT INTO t2 VALUES(1,3);
 471     SELECT * FROM t1;
 472   }
 473   catchsql {
 474     INSERT INTO t1 SELECT * FROM t2;
 475   }
 476 } {1 {UNIQUE constraint failed: t1.a}}
 477 do_test insert4-8.8 {
 478   execsql {
 479     SELECT * FROM t1;
 480   }
 481 } {1 2}
 482 do_test insert4-8.9 {
 483   execsql {
 484     DROP TABLE IF EXISTS t1;
 485     DROP TABLE IF EXISTS t2;
 486     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
 487     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
 488     INSERT INTO t1 VALUES(1,2);
 489     INSERT INTO t2 VALUES(-99,100);
 490     INSERT INTO t2 VALUES(1,3);
 491     SELECT * FROM t1;
 492   }
 493   catchsql {
 494     BEGIN;
 495     INSERT INTO t1 VALUES(2,3);
 496     INSERT INTO t1 SELECT * FROM t2;
 497   }
 498 } {1 {UNIQUE constraint failed: t1.a}}
 499 do_test insert4-8.10 {
 500   catchsql {COMMIT}
 501 } {1 {cannot commit - no transaction is active}}
 502 do_test insert4-8.11 {
 503   execsql {
 504     SELECT * FROM t1;
 505   }
 506 } {1 2}
 507
 508 do_test insert4-8.21 {
 509   execsql {
 510     DROP TABLE IF EXISTS t1;
 511     DROP TABLE IF EXISTS t2;
 512     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
 513     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
 514     INSERT INTO t2 VALUES(1,3);
 515     INSERT INTO t1 SELECT * FROM t2;
 516     SELECT * FROM t1;
 517   }
 518 } {1 3}
 519 do_test insert4-8.22 {
 520   execsql {
 521     DROP TABLE IF EXISTS t1;
 522     DROP TABLE IF EXISTS t2;
 523     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
 524     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
 525     INSERT INTO t2 VALUES(1,3);
 526     INSERT INTO t1 SELECT * FROM t2;
 527     SELECT * FROM t1;
 528   }
 529 } {1 3}
 530 do_test insert4-8.23 {
 531   execsql {
 532     DROP TABLE IF EXISTS t1;
 533     DROP TABLE IF EXISTS t2;
 534     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
 535     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
 536     INSERT INTO t2 VALUES(1,3);
 537     INSERT INTO t1 SELECT * FROM t2;
 538     SELECT * FROM t1;
 539   }
 540 } {1 3}
 541 do_test insert4-8.24 {
 542   execsql {
 543     DROP TABLE IF EXISTS t1;
 544     DROP TABLE IF EXISTS t2;
 545     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
 546     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
 547     INSERT INTO t2 VALUES(1,3);
 548     INSERT INTO t1 SELECT * FROM t2;
 549     SELECT * FROM t1;
 550   }
 551 } {1 3}
 552 do_test insert4-8.25 {
 553   execsql {
 554     DROP TABLE IF EXISTS t1;
 555     DROP TABLE IF EXISTS t2;
 556     CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
 557     CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
 558     INSERT INTO t2 VALUES(1,3);
 559     INSERT INTO t1 SELECT * FROM t2;
 560     SELECT * FROM t1;
 561   }
 562 } {1 3}
 563
 564 do_catchsql_test insert4-9.1 {
 565   DROP TABLE IF EXISTS t1;
 566   CREATE TABLE t1(x);
 567   INSERT INTO t1(x) VALUES(5 COLLATE xyzzy) UNION SELECT 0;
 568 } {1 {no such collation sequence: xyzzy}}
 569
 570 #-------------------------------------------------------------------------
 571 # Check that running an integrity-check does not disable the xfer
 572 # optimization for tables with CHECK constraints.
 573 #
 574 do_execsql_test 10.1 {
 575   CREATE TABLE t8(
 576     rid INTEGER,
 577     pid INTEGER,
 578     mid INTEGER,
 579     px INTEGER DEFAULT(0) CHECK(px IN(0, 1))
 580   );
 581   CREATE TEMP TABLE x(
 582     rid INTEGER,
 583     pid INTEGER,
 584     mid INTEGER,
 585     px INTEGER DEFAULT(0) CHECK(px IN(0, 1))
 586   );
 587 }
 588 do_test 10.2 {
 589   set sqlite3_xferopt_count 0
 590   execsql { INSERT INTO x SELECT * FROM t8 }
 591   set sqlite3_xferopt_count
 592 } {1}
 593
 594 do_test 10.3 {
 595   execsql { PRAGMA integrity_check }
 596   set sqlite3_xferopt_count 0
 597   execsql { INSERT INTO x     SELECT * FROM t8 }
 598   set sqlite3_xferopt_count
 599 } {1}
 600
 601
 602 finish_test