1 files changed, 261 insertions, 0 deletions
diff --git a/test/upfrom3.test b/test/upfrom3.test
new file mode 100644
index 0000000..9936716
--- /dev/null
+++ b/test/upfrom3.test
@@ -0,0 +1,261 @@
+# 2020 July 14
+#
+# 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 upfrom3
+
+# Test plan:
+#
+#   1.*: Test UPDATE ... FROM statements that modify IPK fields. And that
+#        modify "INTEGER PRIMARY KEY" fields on WITHOUT ROWID tables.
+#
+#   2.*: Test UPDATE ... FROM statements that modify PK fields of WITHOUT
+#        ROWID tables.
+#
+#   3.*: Test that UPDATE ... FROM statements are not confused if there
+#        are multiple tables of the same name in attached databases.
+#
+#   4.*: Tests for UPDATE ... FROM statements and foreign keys.
+#
+
+foreach {tn wo} {
+  1 ""
+  2 "WITHOUT ROWID"
+} {
+  reset_db
+  eval [string map [list %WO% $wo %TN% $tn] {
+
+  do_execsql_test 1.%TN%.0 {
+    CREATE TABLE log(t TEXT);
+    CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z UNIQUE) %WO%;
+    CREATE INDEX t1y ON t1(y);
+
+    INSERT INTO t1 VALUES(1, 'i',   'one');
+    INSERT INTO t1 VALUES(2, 'ii',  'two');
+    INSERT INTO t1 VALUES(3, 'iii', 'three');
+    INSERT INTO t1 VALUES(4, 'iv',  'four');
+  }
+
+  do_execsql_test 1.%TN%.1 {
+    CREATE TABLE x1(o, n);
+    INSERT INTO x1 VALUES(1, 11);
+    INSERT INTO x1 VALUES(2, 12);
+    INSERT INTO x1 VALUES(3, 13);
+    INSERT INTO x1 VALUES(4, 14);
+    UPDATE t1 SET x=n FROM x1 WHERE x=o;
+    SELECT x, y, z FROM t1 ORDER BY 1;
+  } {
+    11 i one
+    12 ii two
+    13 iii three
+    14 iv four
+  }
+
+  do_test 1.%TN%.2 { db changes } 4
+
+  do_execsql_test 1.%TN%.3 {
+    INSERT INTO x1 VALUES(11, 21);
+    INSERT INTO x1 VALUES(12, 22);
+    INSERT INTO x1 VALUES(13, 23);
+    INSERT INTO x1 VALUES(14, 24);
+
+    INSERT INTO x1 VALUES(21, 31);
+    INSERT INTO x1 VALUES(22, 32);
+    INSERT INTO x1 VALUES(23, 33);
+    INSERT INTO x1 VALUES(24, 34);
+    UPDATE t1 SET x=n FROM x1 WHERE x=o;
+    SELECT x, y, z FROM t1 ORDER BY 1;
+  } {
+    21 i one
+    22 ii two
+    23 iii three
+    24 iv four
+  }
+
+  do_execsql_test 1.%TN%.4 {
+    UPDATE t1 SET x=n FROM x1 WHERE x=o;
+    SELECT x, y, z FROM t1 ORDER BY 1;
+  } {
+    31 i one
+    32 ii two
+    33 iii three
+    34 iv four
+  }
+
+  do_execsql_test 1.%TN%.5 {
+    INSERT INTO x1 VALUES(31, 32);
+    INSERT INTO x1 VALUES(33, 34);
+    UPDATE OR REPLACE t1 SET x=n FROM x1 WHERE x=o;
+    SELECT x, y, z FROM t1 ORDER BY 1;
+  } {
+    32 i one
+    34 iii three
+  }
+
+  do_execsql_test 1.%TN%.6 {
+    INSERT INTO t1 VALUES(33, 'ii', 'two');
+    INSERT INTO t1 VALUES(35, 'iv', 'four');
+  }
+
+  do_execsql_test 1.%TN%.7 {
+    CREATE TABLE x2(o, n, zz);
+    INSERT INTO x2 VALUES(32, 41, 'four');
+    INSERT INTO x2 VALUES(33, 42, 'three');
+    UPDATE OR IGNORE t1 SET x=n, z=zz FROM x2 WHERE x=o;
+    SELECT x, y, z FROM t1 ORDER BY 1;
+  } {
+    32 i one 
+    33 ii two 
+    34 iii three 
+    35 iv four
+  }
+
+  do_execsql_test 1.%TN%.8 {
+    UPDATE OR REPLACE t1 SET x=n, z=zz FROM x2 WHERE x=o;
+    SELECT x, y, z FROM t1 ORDER BY 1;
+  } {
+    41 i four
+    42 ii three
+  }
+
+  }]
+}
+
+do_execsql_test 2.1.1 {
+  CREATE TABLE u1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
+  INSERT INTO u1 VALUES(0, 0, 0);
+  INSERT INTO u1 VALUES(1, 0, 1);
+  INSERT INTO u1 VALUES(2, 1, 0);
+  INSERT INTO u1 VALUES(3, 1, 1);
+}
+
+do_execsql_test 2.1.2 {
+  CREATE TABLE map(f, t);
+  INSERT INTO map VALUES(0, 10);
+  INSERT INTO map VALUES(1, 11);
+  UPDATE u1 SET c=t FROM map WHERE c=f;
+  SELECT * FROM u1 ORDER BY a;
+} {
+  0 0 10
+  1 0 11
+  2 1 10
+  3 1 11
+}
+
+do_execsql_test 2.1.3 {
+  UPDATE u1 SET b=t FROM map WHERE b=f;
+  SELECT * FROM u1 ORDER BY a;
+} {
+  0 10 10
+  1 10 11
+  2 11 10
+  3 11 11
+}
+
+do_execsql_test 2.1.4 {
+  CREATE TABLE map2(o1, o2, n1, n2);
+  INSERT INTO map2 VALUES
+    (10, 10, 50, 50), (10, 11, 50, 60), 
+    (11, 10, 60, 50), (11, 11, 60, 60);
+  UPDATE u1 SET b=n1, c=n2 FROM map2 WHERE b=o1 AND c=o2;
+  SELECT * FROM u1 ORDER BY a;
+} {
+  0 50 50
+  1 50 60
+  2 60 50
+  3 60 60
+}
+
+#-------------------------------------------------------------------------
+foreach {tn wo} {
+  1 ""
+  2 "WITHOUT ROWID"
+} {
+  reset_db
+  forcedelete test.db2
+  eval [string map [list %WO% $wo %TN% $tn] {
+    do_execsql_test 3.$tn.1 {
+      CREATE TABLE g1(a, b, c, PRIMARY KEY(a, b)) %WO%;
+      INSERT INTO g1 VALUES(1, 1, 1);
+
+      ATTACH 'test.db2' AS aux;
+      CREATE TABLE aux.g1(a, b, c, PRIMARY KEY(a, b)) %WO%;
+      INSERT INTO aux.g1 VALUES(10, 1, 10);
+      INSERT INTO aux.g1 VALUES(20, 2, 20);
+      INSERT INTO aux.g1 VALUES(30, 3, 30);
+    }
+
+    do_execsql_test 3.$tn.2 {
+      UPDATE aux.g1 SET c=101 FROM main.g1;
+    }
+    do_execsql_test 3.$tn.3 {
+      SELECT * FROM aux.g1;
+    } {10 1 101  20 2 101  30 3 101}
+
+    do_execsql_test 3.$tn.4 {
+      UPDATE g1 SET c=101 FROM g1 AS g2;
+    }
+    do_execsql_test 3.$tn.5 {
+      SELECT * FROM g1;
+    } {1 1 101}
+  }]
+}
+
+#-------------------------------------------------------------------------
+reset_db
+foreach {tn wo} {
+  1 ""
+  2 "WITHOUT ROWID"
+} {
+  reset_db
+  forcedelete test.db2
+  eval [string map [list %WO% $wo %TN% $tn] {
+
+    do_execsql_test 4.$tn.1 {
+      CREATE TABLE p1(a INTEGER PRIMARY KEY, b) %WO%;
+      CREATE TABLE c1(x PRIMARY KEY, y REFERENCES p1 ON UPDATE CASCADE) %WO%;
+      PRAGMA foreign_keys = 1;
+
+      INSERT INTO p1 VALUES(1, 'one');
+      INSERT INTO p1 VALUES(11, 'eleven');
+      INSERT INTO p1 VALUES(111, 'eleventyone');
+
+      INSERT INTO c1 VALUES('a', 1);
+      INSERT INTO c1 VALUES('b', 11);
+      INSERT INTO c1 VALUES('c', 111);
+    }
+
+    do_execsql_test 4.$tn.2 {
+      CREATE TABLE map(f, t);
+      INSERT INTO map VALUES('a', 111);
+      INSERT INTO map VALUES('c', 112);
+    }
+
+    do_catchsql_test 4.$tn.3 {
+      UPDATE c1 SET y=t FROM map WHERE x=f;
+    } {1 {FOREIGN KEY constraint failed}}
+
+    do_execsql_test 4.$tn.4 {
+      INSERT INTO map VALUES('eleven', 12);
+      INSERT INTO map VALUES('eleventyone', 112);
+      UPDATE p1 SET a=t FROM map WHERE b=f;
+    }
+
+    do_execsql_test 4.$tn.5 {
+      SELECT * FROM c1
+    } {a 1  b 12  c 112}
+
+  }]
+}
+
+finish_test