sl@0: # 2007 January 24
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is testing the INSERT transfer optimization.
sl@0: #
sl@0: # $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: ifcapable !view||!subquery {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: # The sqlite3_xferopt_count variable is incremented whenever the 
sl@0: # insert transfer optimization applies.
sl@0: #
sl@0: # This procedure runs a test to see if the sqlite3_xferopt_count is
sl@0: # set to N.
sl@0: #
sl@0: proc xferopt_test {testname N} {
sl@0:   do_test $testname {set ::sqlite3_xferopt_count} $N
sl@0: }
sl@0: 
sl@0: # Create tables used for testing.
sl@0: #
sl@0: execsql {
sl@0:   PRAGMA legacy_file_format = 0;
sl@0:   CREATE TABLE t1(a int, b int, check(b>a));
sl@0:   CREATE TABLE t2(x int, y int);
sl@0:   CREATE VIEW v2 AS SELECT y, x FROM t2;
sl@0:   CREATE TABLE t3(a int, b int);
sl@0: }
sl@0: 
sl@0: # Ticket #2252.  Make sure the an INSERT from identical tables
sl@0: # does not violate constraints.
sl@0: #
sl@0: do_test insert4-1.1 {
sl@0:   set sqlite3_xferopt_count 0
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:     DELETE FROM t2;
sl@0:     INSERT INTO t2 VALUES(9,1);
sl@0:   }
sl@0:   catchsql {
sl@0:     INSERT INTO t1 SELECT * FROM t2;
sl@0:   }
sl@0: } {1 {constraint failed}}
sl@0: xferopt_test insert4-1.2 0
sl@0: do_test insert4-1.3 {
sl@0:   execsql {
sl@0:     SELECT * FROM t1;
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: # Tests to make sure that the transfer optimization is not occurring
sl@0: # when it is not a valid optimization.
sl@0: #
sl@0: # The SELECT must be against a real table.
sl@0: do_test insert4-2.1.1 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1 SELECT 4, 8;
sl@0:     SELECT * FROM t1;
sl@0:   }
sl@0: } {4 8}
sl@0: xferopt_test insert4-2.1.2  0
sl@0: do_test insert4-2.2.1 {
sl@0:   catchsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1 SELECT * FROM v2;
sl@0:     SELECT * FROM t1;
sl@0:   }
sl@0: } {0 {1 9}}
sl@0: xferopt_test insert4-2.2.2 0
sl@0: 
sl@0: # Do not run the transfer optimization if there is a LIMIT clause
sl@0: #
sl@0: do_test insert4-2.3.1 {
sl@0:   execsql {
sl@0:     DELETE FROM t2;
sl@0:     INSERT INTO t2 VALUES(9,1);
sl@0:     INSERT INTO t2 SELECT y, x FROM t2;
sl@0:     INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
sl@0:     SELECT * FROM t3;
sl@0:   }
sl@0: } {9 1}
sl@0: xferopt_test insert4-2.3.2  0
sl@0: do_test insert4-2.3.3 {
sl@0:   catchsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
sl@0:     SELECT * FROM t1;
sl@0:   }
sl@0: } {1 {constraint failed}}
sl@0: xferopt_test insert4-2.3.4 0
sl@0: 
sl@0: # Do not run the transfer optimization if there is a DISTINCT
sl@0: #
sl@0: do_test insert4-2.4.1 {
sl@0:   execsql {
sl@0:     DELETE FROM t3;
sl@0:     INSERT INTO t3 SELECT DISTINCT * FROM t2;
sl@0:     SELECT * FROM t3;
sl@0:   }
sl@0: } {1 9 9 1}
sl@0: xferopt_test insert4-2.4.2 0
sl@0: do_test insert4-2.4.3 {
sl@0:   catchsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1 SELECT DISTINCT * FROM t2;
sl@0:   }
sl@0: } {1 {constraint failed}}
sl@0: xferopt_test insert4-2.4.4 0
sl@0: 
sl@0: # The following procedure constructs two tables then tries to transfer
sl@0: # data from one table to the other.  Checks are made to make sure the
sl@0: # transfer is successful and that the transfer optimization was used or
sl@0: # not, as appropriate.
sl@0: #
sl@0: #     xfer_check TESTID  XFER-USED   INIT-DATA   DEST-SCHEMA   SRC-SCHEMA 
sl@0: #
sl@0: # The TESTID argument is the symbolic name for this test.  The XFER-USED
sl@0: # argument is true if the transfer optimization should be employed and
sl@0: # false if not.  INIT-DATA is a single row of data that is to be 
sl@0: # transfered.  DEST-SCHEMA and SRC-SCHEMA are table declarations for
sl@0: # the destination and source tables.
sl@0: #
sl@0: proc xfer_check {testid xferused initdata destschema srcschema} {
sl@0:   execsql "CREATE TABLE dest($destschema)"
sl@0:   execsql "CREATE TABLE src($srcschema)"
sl@0:   execsql "INSERT INTO src VALUES([join $initdata ,])"
sl@0:   set ::sqlite3_xferopt_count 0
sl@0:   do_test $testid.1 {
sl@0:     execsql {
sl@0:       INSERT INTO dest SELECT * FROM src;
sl@0:       SELECT * FROM dest;
sl@0:     }
sl@0:   } $initdata
sl@0:   do_test $testid.2 {
sl@0:     set ::sqlite3_xferopt_count
sl@0:   } $xferused
sl@0:   execsql {
sl@0:     DROP TABLE dest;
sl@0:     DROP TABLE src;
sl@0:   }
sl@0: }
sl@0: 
sl@0: 
sl@0: # Do run the transfer optimization if tables have identical
sl@0: # CHECK constraints.
sl@0: #
sl@0: xfer_check insert4-3.1 1 {1 9} \
sl@0:     {a int, b int CHECK(b>a)} \
sl@0:     {x int, y int CHECK(y>x)}
sl@0: xfer_check insert4-3.2 1 {1 9} \
sl@0:     {a int, b int CHECK(b>a)} \
sl@0:     {x int CHECK(y>x), y int}
sl@0: 
sl@0: # Do run the transfer optimization if the destination table lacks
sl@0: # any CHECK constraints regardless of whether or not there are CHECK
sl@0: # constraints on the source table.
sl@0: #
sl@0: xfer_check insert4-3.3 1 {1 9} \
sl@0:     {a int, b int} \
sl@0:     {x int, y int CHECK(y>x)}
sl@0: 
sl@0: # Do run the transfer optimization if the destination table omits
sl@0: # NOT NULL constraints that the source table has.
sl@0: #
sl@0: xfer_check insert4-3.4 0 {1 9} \
sl@0:     {a int, b int CHECK(b>a)} \
sl@0:     {x int, y int}
sl@0: 
sl@0: # Do not run the optimization if the destination has NOT NULL
sl@0: # constraints that the source table lacks.
sl@0: #
sl@0: xfer_check insert4-3.5 0 {1 9} \
sl@0:     {a int, b int NOT NULL} \
sl@0:     {x int, y int}
sl@0: xfer_check insert4-3.6 0 {1 9} \
sl@0:     {a int, b int NOT NULL} \
sl@0:     {x int NOT NULL, y int}
sl@0: xfer_check insert4-3.7 0 {1 9} \
sl@0:     {a int NOT NULL, b int NOT NULL} \
sl@0:     {x int NOT NULL, y int}
sl@0: xfer_check insert4-3.8 0 {1 9} \
sl@0:     {a int NOT NULL, b int} \
sl@0:     {x int, y int}
sl@0: 
sl@0: 
sl@0: # Do run the transfer optimization if the destination table and
sl@0: # source table have the same NOT NULL constraints or if the 
sl@0: # source table has extra NOT NULL constraints.
sl@0: #
sl@0: xfer_check insert4-3.9 1 {1 9} \
sl@0:     {a int, b int} \
sl@0:     {x int NOT NULL, y int}
sl@0: xfer_check insert4-3.10 1 {1 9} \
sl@0:     {a int, b int} \
sl@0:     {x int NOT NULL, y int NOT NULL}
sl@0: xfer_check insert4-3.11 1 {1 9} \
sl@0:     {a int NOT NULL, b int} \
sl@0:     {x int NOT NULL, y int NOT NULL}
sl@0: xfer_check insert4-3.12 1 {1 9} \
sl@0:     {a int, b int NOT NULL} \
sl@0:     {x int NOT NULL, y int NOT NULL}
sl@0: 
sl@0: # Do not run the optimization if any corresponding table
sl@0: # columns have different affinities.
sl@0: #
sl@0: xfer_check insert4-3.20 0 {1 9} \
sl@0:     {a text, b int} \
sl@0:     {x int, b int}
sl@0: xfer_check insert4-3.21 0 {1 9} \
sl@0:     {a int, b int} \
sl@0:     {x text, b int}
sl@0: 
sl@0: # "int" and "integer" are equivalent so the optimization should
sl@0: # run here.
sl@0: #
sl@0: xfer_check insert4-3.22 1 {1 9} \
sl@0:     {a int, b int} \
sl@0:     {x integer, b int}
sl@0: 
sl@0: # Ticket #2291.
sl@0: #
sl@0: 
sl@0: do_test insert4-4.1a {
sl@0:   execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))}
sl@0: } {}
sl@0: ifcapable vacuum {
sl@0:   do_test insert4-4.1b {
sl@0:     execsql {
sl@0:       INSERT INTO t4 VALUES(NULL,0);
sl@0:       INSERT INTO t4 VALUES(NULL,1);
sl@0:       INSERT INTO t4 VALUES(NULL,1);
sl@0:       VACUUM;   
sl@0:     }
sl@0:   } {}
sl@0: }
sl@0: 
sl@0: # Check some error conditions:
sl@0: #
sl@0: do_test insert4-5.1 {
sl@0:   # Table does not exist.
sl@0:   catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
sl@0: } {1 {no such table: nosuchtable}}
sl@0: do_test insert4-5.2 {
sl@0:   # Number of columns does not match.
sl@0:   catchsql { 
sl@0:     CREATE TABLE t5(a, b, c);
sl@0:     INSERT INTO t4 SELECT * FROM t5;
sl@0:   }
sl@0: } {1 {table t4 has 2 columns but 3 values were supplied}}
sl@0: 
sl@0: do_test insert4-6.1 {
sl@0:   set ::sqlite3_xferopt_count 0
sl@0:   execsql {
sl@0:     CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase); 
sl@0:     CREATE INDEX t2_i1 ON t2(x ASC, y DESC);
sl@0:     CREATE INDEX t3_i1 ON t3(a, b);
sl@0:     INSERT INTO t2 SELECT * FROM t3;
sl@0:   }
sl@0:   set ::sqlite3_xferopt_count
sl@0: } {0}
sl@0: do_test insert4-6.2 {
sl@0:   set ::sqlite3_xferopt_count 0
sl@0:   execsql {
sl@0:     DROP INDEX t2_i2;
sl@0:     INSERT INTO t2 SELECT * FROM t3;
sl@0:   }
sl@0:   set ::sqlite3_xferopt_count
sl@0: } {0}
sl@0: do_test insert4-6.3 {
sl@0:   set ::sqlite3_xferopt_count 0
sl@0:   execsql {
sl@0:     DROP INDEX t2_i1;
sl@0:     CREATE INDEX t2_i1 ON t2(x ASC, y ASC);
sl@0:     INSERT INTO t2 SELECT * FROM t3;
sl@0:   }
sl@0:   set ::sqlite3_xferopt_count
sl@0: } {1}
sl@0: do_test insert4-6.4 {
sl@0:   set ::sqlite3_xferopt_count 0
sl@0:   execsql {
sl@0:     DROP INDEX t2_i1;
sl@0:     CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
sl@0:     INSERT INTO t2 SELECT * FROM t3;
sl@0:   }
sl@0:   set ::sqlite3_xferopt_count
sl@0: } {0}
sl@0: 
sl@0: 
sl@0: 
sl@0: 
sl@0: finish_test