Import SQLite 3.8.7.4.

third_party/sqlite/src/test/insert4.test

 # 2007 January 24
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
@@ skipping 36 matching lines @@
 do_test insert4-1.1 {
   set sqlite3_xferopt_count 0
   execsql {
     DELETE FROM t1;
     DELETE FROM t2;
     INSERT INTO t2 VALUES(9,1);
   }
   catchsql {
     INSERT INTO t1 SELECT * FROM t2;
   }
-} {1 {constraint failed}}
+} {1 {CHECK constraint failed: t1}}
 xferopt_test insert4-1.2 0
 do_test insert4-1.3 {
   execsql {
     SELECT * FROM t1;
   }
 } {}
 
 # Tests to make sure that the transfer optimization is not occurring
 # when it is not a valid optimization.
 #
@@ skipping 26 matching lines @@
     SELECT * FROM t3;
   }
 } {9 1}
 xferopt_test insert4-2.3.2  0
 do_test insert4-2.3.3 {
   catchsql {
     DELETE FROM t1;
     INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
     SELECT * FROM t1;
   }
-} {1 {constraint failed}}
+} {1 {CHECK constraint failed: t1}}
 xferopt_test insert4-2.3.4 0
 
 # Do not run the transfer optimization if there is a DISTINCT
 #
 do_test insert4-2.4.1 {
   execsql {
     DELETE FROM t3;
     INSERT INTO t3 SELECT DISTINCT * FROM t2;
     SELECT * FROM t3;
   }
-} {1 9 9 1}
+} {9 1 1 9}
 xferopt_test insert4-2.4.2 0
 do_test insert4-2.4.3 {
   catchsql {
     DELETE FROM t1;
     INSERT INTO t1 SELECT DISTINCT * FROM t2;
   }
-} {1 {constraint failed}}
+} {1 {CHECK constraint failed: t1}}
 xferopt_test insert4-2.4.4 0
 
 # The following procedure constructs two tables then tries to transfer
 # data from one table to the other.  Checks are made to make sure the
 # transfer is successful and that the transfer optimization was used or
 # not, as appropriate.
 #
 #     xfer_check TESTID  XFER-USED   INIT-DATA   DEST-SCHEMA   SRC-SCHEMA 
 #
 # The TESTID argument is the symbolic name for this test.  The XFER-USED
@@ skipping 113 matching lines @@
       INSERT INTO t4 VALUES(NULL,1);
       VACUUM;   
     }
   } {}
 }
 
 # Check some error conditions:
 #
 do_test insert4-5.1 {
   # Table does not exist.
-  catchsql { INSERT INTO t2 SELECT * FROM nosuchtable }
+  catchsql { INSERT INTO t2 SELECT a, b FROM nosuchtable }
 } {1 {no such table: nosuchtable}}
 do_test insert4-5.2 {
   # Number of columns does not match.
   catchsql { 
     CREATE TABLE t5(a, b, c);
     INSERT INTO t4 SELECT * FROM t5;
   }
 } {1 {table t4 has 2 columns but 3 values were supplied}}
 
 do_test insert4-6.1 {
@@ skipping 41 matching lines @@
     SELECT * FROM t6a;
   }
 } {ABC}
 do_test insert4-6.6 {
   execsql {
     CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase ));
   }
   catchsql {
     INSERT INTO t6b SELECT * FROM t6a;
   }
-} {1 {constraint failed}}
+} {1 {CHECK constraint failed: t6b}}
 do_test insert4-6.7 {
   execsql {
     DROP TABLE t6b;
     CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
   }
   catchsql {
     INSERT INTO t6b SELECT * FROM t6a;
   }
-} {1 {constraint failed}}
+} {1 {CHECK constraint failed: t6b}}
+
+# Ticket [6284df89debdfa61db8073e062908af0c9b6118e]
+# Disable the xfer optimization if the destination table contains
+# a foreign key constraint
+#
+ifcapable foreignkey {
+  do_test insert4-7.1 {
+    set ::sqlite3_xferopt_count 0
+    execsql {
+      CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123);
+      CREATE TABLE t7b(y INTEGER REFERENCES t7a);
+      CREATE TABLE t7c(z INT);  INSERT INTO t7c VALUES(234);
+      INSERT INTO t7b SELECT * FROM t7c;
+      SELECT * FROM t7b;
+    }
+  } {234}
+  do_test insert4-7.2 {
+    set ::sqlite3_xferopt_count
+  } {1}
+  do_test insert4-7.3 {
+    set ::sqlite3_xferopt_count 0
+    execsql {
+      DELETE FROM t7b;
+      PRAGMA foreign_keys=ON;
+    }
+    catchsql {
+      INSERT INTO t7b SELECT * FROM t7c;
+    }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test insert4-7.4 {
+    execsql {SELECT * FROM t7b}
+  } {}
+  do_test insert4-7.5 {
+    set ::sqlite3_xferopt_count
+  } {0}
+  do_test insert4-7.6 {
+    set ::sqlite3_xferopt_count 0
+    execsql {
+      DELETE FROM t7b; DELETE FROM t7c;
+      INSERT INTO t7c VALUES(123);
+      INSERT INTO t7b SELECT * FROM t7c;
+      SELECT * FROM t7b;
+    }
+  } {123}
+  do_test insert4-7.7 {
+    set ::sqlite3_xferopt_count
+  } {0}
+  do_test insert4-7.7 {
+    set ::sqlite3_xferopt_count 0
+    execsql {
+      PRAGMA foreign_keys=OFF;
+      DELETE FROM t7b;
+      INSERT INTO t7b SELECT * FROM t7c;
+      SELECT * FROM t7b;
+    }
+  } {123}
+  do_test insert4-7.8 {
+    set ::sqlite3_xferopt_count
+  } {1}
+}
+
+# Ticket [676bc02b87176125635cb174d110b431581912bb]
+# Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer
+# optimization.
+#
+do_test insert4-8.1 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+do_test insert4-8.2 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+    CREATE TABLE t2(x, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+do_test insert4-8.3 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 2}
+do_test insert4-8.4 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
+    CREATE TABLE t2(x, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 2}
+do_test insert4-8.5 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(-99,100);
+    INSERT INTO t2 VALUES(1,3);
+    SELECT * FROM t1;
+  }
+  catchsql {
+    INSERT INTO t1 SELECT * FROM t2;
+  }
+} {1 {UNIQUE constraint failed: t1.a}}
+do_test insert4-8.6 {
+  execsql {
+    SELECT * FROM t1;
+  }
+} {-99 100 1 2} 
+do_test insert4-8.7 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(-99,100);
+    INSERT INTO t2 VALUES(1,3);
+    SELECT * FROM t1;
+  }
+  catchsql {
+    INSERT INTO t1 SELECT * FROM t2;
+  }
+} {1 {UNIQUE constraint failed: t1.a}}
+do_test insert4-8.8 {
+  execsql {
+    SELECT * FROM t1;
+  }
+} {1 2} 
+do_test insert4-8.9 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
+    INSERT INTO t1 VALUES(1,2);
+    INSERT INTO t2 VALUES(-99,100);
+    INSERT INTO t2 VALUES(1,3);
+    SELECT * FROM t1;
+  }
+  catchsql {
+    BEGIN;
+    INSERT INTO t1 VALUES(2,3);
+    INSERT INTO t1 SELECT * FROM t2;
+  }
+} {1 {UNIQUE constraint failed: t1.a}}
+do_test insert4-8.10 {
+  catchsql {COMMIT}
+} {1 {cannot commit - no transaction is active}}
+do_test insert4-8.11 {
+  execsql {
+    SELECT * FROM t1;
+  }
+} {1 2} 
+
+do_test insert4-8.21 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+do_test insert4-8.22 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+do_test insert4-8.23 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+do_test insert4-8.24 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+do_test insert4-8.25 {
+  execsql {
+    DROP TABLE IF EXISTS t1;
+    DROP TABLE IF EXISTS t2;
+    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
+    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
+    INSERT INTO t2 VALUES(1,3);
+    INSERT INTO t1 SELECT * FROM t2;
+    SELECT * FROM t1;
+  }
+} {1 3}
+
 
 finish_test