Import SQLite 3.8.7.4.

third_party/sqlite/src/test/triggerC.test

 # 2009 August 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 139 matching lines @@
     CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c, d, e);
     CREATE INDEX t1cd ON t1(c,d);
     CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END;
     INSERT INTO t1 VALUES(1,2,3,4,5);
     INSERT INTO t1 VALUES(6,7,8,9,10);
     INSERT INTO t1 VALUES(11,12,13,14,15);
   }
 } {}
 do_test triggerC-1.15 {
   catchsql { UPDATE OR ROLLBACK t1 SET a=100 }
-} {1 {PRIMARY KEY must be unique}}
+} {1 {UNIQUE constraint failed: t1.a}}
 
 
 #-------------------------------------------------------------------------
 # This block of tests, triggerC-2.*, tests that recursive trigger
 # programs (triggers that fire themselves) work. More specifically,
 # this block focuses on recursive INSERT triggers.
 #
 do_test triggerC-2.1.0 {
   execsql {
     CREATE TABLE t2(a PRIMARY KEY);
@@ skipping 44 matching lines @@
       INSERT OR IGNORE INTO t2 VALUES(new.a);
     END;
   } {1 {too many levels of trigger recursion}}
 } {
   do_test triggerC-2.1.$n {
     catchsql { DROP TRIGGER t2_trig }
     execsql  { DELETE FROM t2 }
     execsql  $tdefn
     catchsql {
       INSERT INTO t2 VALUES(10);
-      SELECT * FROM t2;
+      SELECT * FROM t2 ORDER BY rowid;
     }
   } $rc
 }
 
 do_test triggerC-2.2 {
-  execsql {
+  execsql "
     CREATE TABLE t22(x);
 
     CREATE TRIGGER t22a AFTER INSERT ON t22 BEGIN
       INSERT INTO t22 SELECT x + (SELECT max(x) FROM t22) FROM t22;
     END;
     CREATE TRIGGER t22b BEFORE INSERT ON t22 BEGIN
-      SELECT CASE WHEN (SELECT count(*) FROM t22) >= 100
+      SELECT CASE WHEN (SELECT count(*) FROM t22) >= [expr $SQLITE_MAX_TRIGGER_DEPTH / 2]
                   THEN RAISE(IGNORE)
                   ELSE NULL END;
     END;
 
     INSERT INTO t22 VALUES(1);
     SELECT count(*) FROM t22;
-  }
-} {100}
+  "
+} [list [expr $SQLITE_MAX_TRIGGER_DEPTH / 2]]
 
 do_test triggerC-2.3 {
-  execsql {
+  execsql "
     CREATE TABLE t23(x PRIMARY KEY);
 
     CREATE TRIGGER t23a AFTER INSERT ON t23 BEGIN
       INSERT INTO t23 VALUES(new.x + 1);
     END;
 
     CREATE TRIGGER t23b BEFORE INSERT ON t23 BEGIN
-      SELECT CASE WHEN new.x>500
+      SELECT CASE WHEN new.x>[expr $SQLITE_MAX_TRIGGER_DEPTH / 2]
                   THEN RAISE(IGNORE)
                   ELSE NULL END;
     END;
 
     INSERT INTO t23 VALUES(1);
     SELECT count(*) FROM t23;
-  }
-} {500}
+  "
+} [list [expr $SQLITE_MAX_TRIGGER_DEPTH / 2]]
  
 
 #-----------------------------------------------------------------------
 # This block of tests, triggerC-3.*, test that SQLite throws an exception
 # when it detects excessive recursion.
 #
 do_test triggerC-3.1.1 {
   execsql {
     CREATE TABLE t3(a, b);
     CREATE TRIGGER t3i AFTER INSERT ON t3 BEGIN
       DELETE FROM t3 WHERE rowid = new.rowid;
     END;
     CREATE TRIGGER t3d AFTER DELETE ON t3 BEGIN
       INSERT INTO t3 VALUES(old.a, old.b);
     END;
   }
 } {}
 do_test triggerC-3.1.2 {
   catchsql { INSERT INTO t3 VALUES(0,0) }
 } {1 {too many levels of trigger recursion}}
 do_test triggerC-3.1.3 {
   execsql { SELECT * FROM t3 }
 } {}
 
 do_test triggerC-3.2.1 {
-  execsql {
+  execsql "
     CREATE TABLE t3b(x);
-    CREATE TRIGGER t3bi AFTER INSERT ON t3b WHEN new.x<2000 BEGIN
+    CREATE TRIGGER t3bi AFTER INSERT ON t3b WHEN new.x<[expr $SQLITE_MAX_TRIGGER_DEPTH * 2] BEGIN
       INSERT INTO t3b VALUES(new.x+1);
     END;
-  }
+  "
   catchsql {
     INSERT INTO t3b VALUES(1);
   }
 } {1 {too many levels of trigger recursion}}
 do_test triggerC-3.2.2 {
   db eval {SELECT * FROM t3b}
 } {}
 
 do_test triggerC-3.3.1 {
-  catchsql {
-    INSERT INTO t3b VALUES(1001);
-  }
+  catchsql "
+    INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH + 1]);
+  "
 } {0 {}}
 do_test triggerC-3.3.2 {
   db eval {SELECT count(*), max(x), min(x) FROM t3b}
-} {1000 2000 1001}
+} [list $SQLITE_MAX_TRIGGER_DEPTH [expr $SQLITE_MAX_TRIGGER_DEPTH * 2] [expr $SQLITE_MAX_TRIGGER_DEPTH + 1]]
 
 do_test triggerC-3.4.1 {
-  catchsql {
+  catchsql "
     DELETE FROM t3b;
-    INSERT INTO t3b VALUES(999);
-  }
+    INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH - 1]);
+  "
 } {1 {too many levels of trigger recursion}}
 do_test triggerC-3.4.2 {
   db eval {SELECT count(*), max(x), min(x) FROM t3b}
 } {0 {} {}}
 
 do_test triggerC-3.5.1 {
-  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 100
-  catchsql {
-    INSERT INTO t3b VALUES(1901);
-  }
+  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH  [expr $SQLITE_MAX_TRIGGER_DEPTH / 10]
+  catchsql "
+    INSERT INTO t3b VALUES([expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - ($SQLITE_MAX_TRIGGER_DEPTH / 10) + 1]);
+  "
 } {0 {}}
 do_test triggerC-3.5.2 {
   db eval {SELECT count(*), max(x), min(x) FROM t3b}
-} {100 2000 1901}
+} [list [expr $SQLITE_MAX_TRIGGER_DEPTH / 10] [expr $SQLITE_MAX_TRIGGER_DEPTH * 2] [expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - ($SQLITE_MAX_TRIGGER_DEPTH / 10) + 1]]
 
 do_test triggerC-3.5.3 {
-  catchsql {
+  catchsql "
     DELETE FROM t3b;
-    INSERT INTO t3b VALUES(1900);
-  }
+    INSERT INTO t3b VALUES([expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - ($SQLITE_MAX_TRIGGER_DEPTH / 10)]);
+  "
 } {1 {too many levels of trigger recursion}}
 do_test triggerC-3.5.4 {
   db eval {SELECT count(*), max(x), min(x) FROM t3b}
 } {0 {} {}}
 
 do_test triggerC-3.6.1 {
   sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1
-  catchsql {
-    INSERT INTO t3b VALUES(2000);
-  }
+  catchsql "
+    INSERT INTO t3b VALUES([expr $SQLITE_MAX_TRIGGER_DEPTH * 2]);
+  "
 } {0 {}}
 do_test triggerC-3.6.2 {
   db eval {SELECT count(*), max(x), min(x) FROM t3b}
-} {1 2000 2000}
+} [list 1 [expr $SQLITE_MAX_TRIGGER_DEPTH * 2] [expr $SQLITE_MAX_TRIGGER_DEPTH * 2]]
 
 do_test triggerC-3.6.3 {
-  catchsql {
+  catchsql "
     DELETE FROM t3b;
-    INSERT INTO t3b VALUES(1999);
-  }
+    INSERT INTO t3b VALUES([expr ($SQLITE_MAX_TRIGGER_DEPTH * 2) - 1]);
+  "
 } {1 {too many levels of trigger recursion}}
 do_test triggerC-3.6.4 {
   db eval {SELECT count(*), max(x), min(x) FROM t3b}
 } {0 {} {}}
-sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000
-      
+sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH $SQLITE_MAX_TRIGGER_DEPTH
+
 
 #-----------------------------------------------------------------------
 # This next block of tests, triggerC-4.*, checks that affinity 
 # transformations and constraint processing is performed at the correct 
 # times relative to BEFORE and AFTER triggers.
 #
 # For an INSERT statement, for each row to be inserted:
 #
 #   1. Apply affinities to non-rowid values to be inserted.
 #   2. Fire BEFORE triggers.
@@ skipping 165 matching lines @@
      2 integer 9   text 9   integer 9.0 real
      2 integer 9.1 text 9.1 real    9.1 real
      2 integer 9   text 9   integer 9.0 real
      2 integer 9.1 text 9.1 real    9.1 real
   }
 } {
   do_test triggerC-4.1.$n {
     eval concat [execsql " 
       DELETE FROM log;
       $insert ; 
-      SELECT * FROM log;
+      SELECT * FROM log ORDER BY rowid;
     "]
   } [join $log " "]
 } 
 
 #-------------------------------------------------------------------------
 # This block of tests, triggerC-5.*, test that DELETE triggers are fired
 # if a row is deleted as a result of OR REPLACE conflict resolution.
 #
 do_test triggerC-5.1.0 {
   execsql {
@@ skipping 16 matching lines @@
   3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3"      {2 b 3} {1 a 2 c}
   4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')"        {2 b 3} {1 a 3 c 4 b}
   5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'"  {2 b 3} {1 a 3 b}
   6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {2 b 3 3 c 2} {1 a 2 c}
   7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 3 2 b 2} {1 b}
 } {
   do_test triggerC-5.1.$n {
     execsql "
       BEGIN;
         $dml ;
-        SELECT * FROM t5g;
-        SELECT * FROM t5;
+        SELECT * FROM t5g ORDER BY rowid;
+        SELECT * FROM t5 ORDER BY rowid;
       ROLLBACK;
     "
   } [concat $t5g $t5]
 }
 do_test triggerC-5.2.0 {
   execsql {
     DROP TRIGGER t5t;
     CREATE TRIGGER t5t AFTER DELETE ON t5 BEGIN
       INSERT INTO t5g VALUES(old.a, old.b, (SELECT count(*) FROM t5));
     END;
   }
 } {}
 foreach {n dml t5g t5} {
   1 "DELETE FROM t5 WHERE a=2"                        {2 b 2} {1 a 3 c}
   2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')"        {2 b 2} {1 a 2 d 3 c}
   3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3"      {2 b 2} {1 a 2 c}
   4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')"        {2 b 2} {1 a 3 c 4 b}
   5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'"  {2 b 2} {1 a 3 b}
   6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {2 b 2 3 c 1} {1 a 2 c}
   7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {1 a 2 2 b 1} {1 b}
 } {
   do_test triggerC-5.2.$n {
     execsql "
       BEGIN;
         $dml ;
-        SELECT * FROM t5g;
-        SELECT * FROM t5;
+        SELECT * FROM t5g ORDER BY rowid;
+        SELECT * FROM t5 ORDER BY rowid;
       ROLLBACK;
     "
   } [concat $t5g $t5]
 }
 do_test triggerC-5.3.0 {
   execsql { PRAGMA recursive_triggers = off }
 } {}
 foreach {n dml t5g t5} {
   1 "DELETE FROM t5 WHERE a=2"                        {2 b 2} {1 a 3 c}
   2 "INSERT OR REPLACE INTO t5 VALUES(2, 'd')"        {} {1 a 2 d 3 c}
   3 "UPDATE OR REPLACE t5 SET a = 2 WHERE a = 3"      {} {1 a 2 c}
   4 "INSERT OR REPLACE INTO t5 VALUES(4, 'b')"        {} {1 a 3 c 4 b}
   5 "UPDATE OR REPLACE t5 SET b = 'b' WHERE b = 'c'"  {} {1 a 3 b}
   6 "INSERT OR REPLACE INTO t5 VALUES(2, 'c')"        {} {1 a 2 c}
   7 "UPDATE OR REPLACE t5 SET a=1, b='b' WHERE a = 3" {} {1 b}
 } {
   do_test triggerC-5.3.$n {
     execsql "
       BEGIN;
         $dml ;
-        SELECT * FROM t5g;
-        SELECT * FROM t5;
+        SELECT * FROM t5g ORDER BY rowid;
+        SELECT * FROM t5 ORDER BY rowid;
       ROLLBACK;
     "
   } [concat $t5g $t5]
 }
 do_test triggerC-5.3.8 {
   execsql { PRAGMA recursive_triggers = on }
 } {}
 
 #-------------------------------------------------------------------------
 # This block of tests, triggerC-6.*, tests that "PRAGMA recursive_triggers"
@@ skipping 263 matching lines @@
     CREATE TRIGGER tv2 INSTEAD OF INSERT ON v2 BEGIN
       INSERT INTO log VALUES(new.a, new.b);
     END;
     INSERT INTO v2 DEFAULT VALUES;
     SELECT a, b, a IS NULL, b IS NULL FROM log;
   }
 } {{} {} 1 1}
 
 do_test triggerC-12.1 {
   db close
-  file delete -force test.db
+  forcedelete test.db
   sqlite3 db test.db
 
   execsql {
     CREATE TABLE t1(a, b);
     INSERT INTO t1 VALUES(1, 2);
     INSERT INTO t1 VALUES(3, 4);
     INSERT INTO t1 VALUES(5, 6);
     CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1 ; END ;
     SELECT count(*) FROM sqlite_master;
   }
@@ skipping 10 matching lines @@
   CREATE TABLE t12(a, b);
   INSERT INTO t12 VALUES(1, 2);
   CREATE TRIGGER tr12 AFTER UPDATE ON t12 BEGIN
     UPDATE t12 SET a=new.a+1, b=new.b+1;
   END;
 } {}
 do_catchsql_test triggerC-13.2 {
   UPDATE t12 SET a=a+1, b=b+1;
 } {1 {too many levels of trigger recursion}}
 
+#-------------------------------------------------------------------------
+# The following tests seek to verify that constant values (i.e. literals)
+# are not factored out of loops within trigger programs. SQLite does
+# not factor constants out of loops within trigger programs as it may only
+# do so in code generated before the first table or index is opened. And
+# by the time a trigger program is coded, at least one table or index has
+# always been opened.
+#
+# At one point, due to a bug allowing constant factoring within triggers,
+# the following SQL would produce the wrong result.
+#
+set SQL {
+  CREATE TABLE t1(a, b, c);
+  CREATE INDEX i1 ON t1(a, c);
+  CREATE INDEX i2 ON t1(b, c);
+  INSERT INTO t1 VALUES(1, 2, 3);
 
+  CREATE TABLE t2(e, f);
+  CREATE INDEX i3 ON t2(e);
+  INSERT INTO t2 VALUES(1234567, 3);
+
+  CREATE TABLE empty(x);
+  CREATE TABLE not_empty(x);
+  INSERT INTO not_empty VALUES(2);
+
+  CREATE TABLE t4(x);
+  CREATE TABLE t5(g, h, i);
+
+  CREATE TRIGGER trig BEFORE INSERT ON t4 BEGIN
+    INSERT INTO t5 SELECT * FROM t1 WHERE 
+        (a IN (SELECT x FROM empty) OR b IN (SELECT x FROM not_empty)) 
+        AND c IN (SELECT f FROM t2 WHERE e=1234567);
+  END;
+
+  INSERT INTO t4 VALUES(0);
+  SELECT * FROM t5;
+}
+
+reset_db
+do_execsql_test triggerC-14.1 $SQL {1 2 3}
+reset_db
+optimization_control db factor-constants 0
+do_execsql_test triggerC-14.2 $SQL {1 2 3}
 
 finish_test