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 

   305 

   306 finish_test