Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 363 insertions, 0 deletions

diff --git a/test/altertab2.test b/test/altertab2.test
new file mode 100644
index 0000000..def9e56
--- /dev/null
+++ b/test/altertab2.test
@@ -0,0 +1,363 @@
+# 2018 September 30
+#
+# 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.
+#
+#*************************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix altertab2
+
+# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
+ifcapable !altertable {
+  finish_test
+  return
+}
+
+ifcapable fts5 {
+  do_execsql_test 1.0 {
+    CREATE TABLE rr(a, b);
+    CREATE VIRTUAL TABLE ff USING fts5(a, b);
+    CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
+      INSERT INTO ff VALUES(new.a, new.b);
+    END;
+    INSERT INTO rr VALUES('hello', 'world');
+    SELECT * FROM ff;
+  } {hello world}
+
+  do_execsql_test 1.1 {
+    ALTER TABLE ff RENAME TO ffff;
+  }
+
+  do_execsql_test 1.2 {
+    INSERT INTO rr VALUES('in', 'tcl');
+    SELECT * FROM ffff;
+  } {hello world in tcl}
+}
+
+#-------------------------------------------------------------------------
+# Check that table names that appear in REFERENCES clauses are updated
+# when a table is renamed unless:
+#
+#   a) "PRAGMA legacy_alter_table" is true, and
+#   b) "PRAGMA foreign_keys" is false.
+#
+do_execsql_test 2.0 {
+  CREATE TABLE p1(a PRIMARY KEY, b);
+  CREATE TABLE c1(x REFERENCES p1);
+  CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1);
+  CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a));
+}
+
+do_execsql_test 2.1 {
+  ALTER TABLE p1 RENAME TO p2;
+  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
+} {
+  {CREATE TABLE c1(x REFERENCES "p2")}
+  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
+  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
+}
+
+do_execsql_test 2.2 {
+  PRAGMA legacy_alter_table = 1;
+  ALTER TABLE p2 RENAME TO p3;
+  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
+} {
+  {CREATE TABLE c1(x REFERENCES "p2")}
+  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
+  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
+}
+
+do_execsql_test 2.3 {
+  ALTER TABLE p3 RENAME TO p2;
+  PRAGMA foreign_keys = 1;
+  ALTER TABLE p2 RENAME TO p3;
+  SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
+} {
+  {CREATE TABLE c1(x REFERENCES "p3")}
+  {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")}
+  {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))}
+}
+
+#-------------------------------------------------------------------------
+# Table name in WITH clauses that are part of views or triggers.
+#
+foreach {tn schema} {
+  1 {
+    CREATE TABLE log_entry(col1, y);
+    CREATE INDEX i1 ON log_entry(col1);
+  }
+
+  2 {
+    CREATE TABLE t1(a, b, c);
+    CREATE TABLE t2(x);
+    CREATE TABLE log_entry(col1);
+    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
+      INSERT INTO t2 SELECT col1 FROM log_entry;
+    END;
+  }
+
+  3 {
+    CREATE TABLE t1(a, b, c);
+    CREATE TABLE t2(x);
+    CREATE TABLE log_entry(col1);
+    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
+      INSERT INTO t2
+        WITH xyz(x) AS (SELECT col1 FROM log_entry)
+        SELECT x FROM xyz;
+    END;
+  }
+
+  4 {
+    CREATE TABLE log_entry(col1);
+    CREATE VIEW ttt AS
+        WITH xyz(x) AS (SELECT col1 FROM log_entry)
+        SELECT x FROM xyz;
+  }
+} {
+  reset_db
+  do_execsql_test 3.$tn.1 $schema
+  set expect [db eval "SELECT sql FROM sqlite_master"]
+  set expect [string map {log_entry {"newname"}} $expect]
+
+  do_execsql_test 3.$tn.2 {
+    ALTER TABLE log_entry RENAME TO newname;
+    SELECT sql FROM sqlite_master;
+  } $expect
+
+  reset_db
+  do_execsql_test 3.$tn.3 $schema
+  set expect [db eval "SELECT sql FROM sqlite_master"]
+  set expect [string map {col1 newname} $expect]
+
+  do_execsql_test 3.$tn.4 {
+    ALTER TABLE log_entry RENAME col1 TO newname;
+    SELECT sql FROM sqlite_master;
+  } $expect
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 4.0 {
+  CREATE TABLE t1(a,b,c,d,e,f);
+  CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
+    UPDATE t1 SET (c,d)=(a,b);
+  END;
+}
+
+do_execsql_test 4.1 {
+  ALTER TABLE t1 RENAME TO t1x;
+  SELECT sql FROM sqlite_master WHERE type = 'trigger';
+} {
+{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN
+    UPDATE "t1x" SET (c,d)=(a,b);
+  END}
+}
+
+do_execsql_test 4.2 {
+  ALTER TABLE t1x RENAME a TO aaa;
+  SELECT sql FROM sqlite_master WHERE type = 'trigger';
+} {
+{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
+    UPDATE "t1x" SET (c,d)=(aaa,b);
+  END}
+}
+
+do_execsql_test 4.3 {
+  ALTER TABLE t1x RENAME d TO ddd;
+  SELECT sql FROM sqlite_master WHERE type = 'trigger';
+} {
+{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
+    UPDATE "t1x" SET (c,ddd)=(aaa,b);
+  END}
+}
+
+#-------------------------------------------------------------------------
+ifcapable windowfunc {
+do_execsql_test 5.0 {
+  CREATE TABLE t2(a);
+  CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN
+    SELECT a, sum(a) OVER w1 FROM t2
+      WINDOW w1 AS (
+        PARTITION BY a ORDER BY a 
+        ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
+      ),
+      w2 AS (
+        PARTITION BY a
+        ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+      );
+  END;
+} {}
+
+do_execsql_test 5.0.1 {
+  INSERT INTO t2 VALUES(1);
+} {}
+
+do_execsql_test 5.1 {
+  ALTER TABLE t2 RENAME TO t2x;
+  SELECT sql FROM sqlite_master WHERE name = 'r2';
+} {
+  {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN
+    SELECT a, sum(a) OVER w1 FROM "t2x"
+      WINDOW w1 AS (
+        PARTITION BY a ORDER BY a 
+        ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
+      ),
+      w2 AS (
+        PARTITION BY a
+        ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+      );
+  END}
+}
+
+do_execsql_test 5.2 {
+  ALTER TABLE t2x RENAME a TO aaaa;
+  SELECT sql FROM sqlite_master WHERE name = 'r2';
+} {
+  {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN
+    SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x"
+      WINDOW w1 AS (
+        PARTITION BY aaaa ORDER BY aaaa 
+        ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
+      ),
+      w2 AS (
+        PARTITION BY aaaa
+        ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+      );
+  END}
+}
+
+do_execsql_test 5.3 {
+  INSERT INTO t2x VALUES(1);
+} {}
+} ;# windowfunc
+
+#-------------------------------------------------------------------------
+
+do_execsql_test 6.0 {
+  CREATE TABLE t3(a,b,c,d);
+  CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN
+    SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;
+    SELECT rowid, * FROM t3;
+  END;
+} {}
+
+do_execsql_test 6.1 {
+  ALTER TABLE t3 RENAME TO t3x;
+  SELECT sql FROM sqlite_master WHERE name = 'r3';
+} {
+  {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN
+    SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a;
+    SELECT rowid, * FROM "t3x";
+  END}
+}
+
+do_execsql_test 6.2 {
+  ALTER TABLE t3x RENAME a TO abcd;
+  SELECT sql FROM sqlite_master WHERE name = 'r3';
+} {
+  {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN
+    SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd;
+    SELECT rowid, * FROM "t3x";
+  END}
+}
+
+#-------------------------------------------------------------------------
+reset_db
+
+do_execsql_test 7.0 {
+  CREATE TABLE t1(a,b,c,d,e,f);
+  INSERT INTO t1 VALUES(1,2,3,4,5,6);
+  CREATE TABLE t2(x,y,z);
+}
+
+do_execsql_test 7.1 {
+  SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
+} {1 2 3 4 5 6}
+
+do_execsql_test 7.2 {
+  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
+    INSERT INTO t2
+    SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
+  END;
+  INSERT INTO t1 VALUES(2,3,4,5,6,7);
+  SELECT * FROM t2;
+} {1 2 3  2 3 4  4 5 6  5 6 7}
+
+do_execsql_test 7.3 {
+  ALTER TABLE t1 RENAME TO xyzzy;
+  SELECT sql FROM sqlite_master WHERE name='r1'
+} {
+  {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
+    INSERT INTO t2
+    SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c;
+  END}
+}
+
+do_execsql_test 7.3 {
+  ALTER TABLE xyzzy RENAME c TO ccc;
+  SELECT sql FROM sqlite_master WHERE name='r1'
+} {
+  {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
+    INSERT INTO t2
+    SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;
+  END}
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 8.0 {
+  CREATE TABLE t1(a, b, c); 
+  CREATE TABLE t2(a, b, c); 
+  CREATE TABLE t3(d, e, f);
+  CREATE VIEW v1 AS SELECT * FROM t1;
+  CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
+    UPDATE t2 SET a = new.d;
+    SELECT a, b, c FROM v1;
+  END;
+}
+
+do_execsql_test 8.1 {
+  INSERT INTO t3 VALUES(1, 2, 3);
+}
+
+# The following ALTER TABLE fails as if column "t1.a" is renamed the "a"
+# in the "SELECT a, b, c FROM v1" within the trigger can no longer be
+# resolved. But at one point there was a bug allowing the ALTER TABLE
+# succeed. Which meant the subsequent INSERT statement would fail.
+do_catchsql_test 8.2 {
+  ALTER TABLE t1 RENAME a TO aaa;
+} {1 {error in trigger tr after rename: no such column: a}}
+do_execsql_test 8.3 {
+  INSERT INTO t3 VALUES(4, 5, 6);
+}
+
+do_execsql_test 8.4 {
+  CREATE TABLE t4(a, b);
+  CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2;
+}
+
+# Branches of an expression tree that are optimized out by the AND 
+# optimization are renamed.
+#
+do_execsql_test 8.5 {
+  ALTER TABLE t4 RENAME a TO c;
+  SELECT sql FROM sqlite_master WHERE name = 'v4'
+} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}}
+
+# 2019-06-10 https://www.sqlite.org/src/info/533010b8cacebe82
+reset_db
+do_catchsql_test 8.6 {
+  CREATE TABLE t0(c0);
+  CREATE INDEX i0 ON t0(likelihood(1,2) AND 0);
+  ALTER TABLE t0 RENAME TO t1;
+  SELECT sql FROM sqlite_master WHERE name='i0';
+} {1 {error in index i0: second argument to likelihood() must be a constant between 0.0 and 1.0}}
+
+finish_test