1 files changed, 105 insertions, 0 deletions
diff --git a/test/fts4upfrom.test b/test/fts4upfrom.test
new file mode 100644
index 000000000..4c72aff01
--- /dev/null
+++ b/test/fts4upfrom.test
@@ -0,0 +1,105 @@
+# 2020 February 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.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script is testing UPDATE statements with FROM clauses
+# against FTS4 tables.
+#
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix fts4upfrom
+
+# If SQLITE_ENABLE_FTS3 is defined, omit this file.
+ifcapable !fts3 {
+  finish_test
+  return
+}
+
+foreach {tn create_table} {
+  1 { CREATE VIRTUAL TABLE ft USING fts3(a, b, c) }
+  2 { CREATE TABLE ft(a, b, c) }
+  3 { 
+    CREATE TABLE real(a, b, c);
+    CREATE INDEX i1 ON real(a);
+    CREATE VIEW ft AS SELECT rowid, a, b, c FROM real;
+    CREATE TRIGGER tr1 INSTEAD OF INSERT ON ft BEGIN
+      INSERT INTO real(rowid, a, b, c) VALUES(new.rowid, new.a, new.b, new.c);
+    END;
+    CREATE TRIGGER tr2 INSTEAD OF UPDATE ON ft BEGIN
+      UPDATE real SET rowid=new.rowid, a=new.a, b=new.b, c=new.c 
+      WHERE rowid=old.rowid;
+    END;
+  }
+} {
+  catchsql { DROP VIEW IF EXISTS changes }
+  catchsql { DROP TABLE IF EXISTS ft }
+  catchsql { DROP VIEW IF EXISTS ft }
+  execsql $create_table
+
+  do_execsql_test 1.$tn.0 {
+    INSERT INTO ft(a, b, c) VALUES('a', NULL, 'apple');
+    INSERT INTO ft(a, b, c) VALUES('b', NULL, 'banana');
+    INSERT INTO ft(a, b, c) VALUES('c', NULL, 'cherry');
+    INSERT INTO ft(a, b, c) VALUES('d', NULL, 'damson plum');
+  }
+  
+  do_execsql_test 1.$tn.1 {
+    SELECT a, b, c FROM ft ORDER BY rowid;
+  } {
+    a {} apple
+    b {} banana
+    c {} cherry
+    d {} {damson plum}
+  }
+  
+  do_execsql_test 1.$tn.2 {
+    UPDATE ft SET b=o.c FROM ft AS o WHERE (ft.a == char(unicode(o.a)+1))
+  }
+  
+  do_execsql_test 1.$tn.3 {
+    SELECT a, b, c FROM ft ORDER BY rowid;
+  } {
+    a {} apple
+    b apple banana
+    c banana cherry
+    d cherry {damson plum}
+  }
+
+  do_catchsql_test 1.$tn.4 {
+    UPDATE ft SET c=v FROM changes WHERE a=k;
+  } {1 {no such table: changes}}
+
+  do_execsql_test 1.$tn.5 {
+    create view changes(k, v) AS 
+      VALUES( 'd', 'dewberry' ) UNION ALL
+      VALUES( 'c', 'clementine' ) UNION ALL
+      VALUES( 'b', 'blueberry' ) UNION ALL
+      VALUES( 'a', 'apricot' ) 
+    ;
+  }
+
+  do_execsql_test 1.$tn.6 {
+    UPDATE ft SET c=v FROM changes WHERE a=k;
+  }
+
+  do_execsql_test 1.$tn.7 {
+    SELECT a, b, c FROM ft ORDER BY rowid;
+  } {
+    a {} apricot
+    b apple blueberry
+    c banana clementine
+    d cherry dewberry
+  }
+}
+
+finish_test
+