Adding upstream version 3.45.1.upstream/3.45.1

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

1 files changed, 669 insertions, 0 deletions

diff --git a/test/gencol1.test b/test/gencol1.test
new file mode 100644
index 0000000..ed7ea56
--- /dev/null
+++ b/test/gencol1.test
@@ -0,0 +1,669 @@
+# 2019-10-31
+#
+# 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.
+#
+#***********************************************************************
+# 
+# Test cases for generated columns.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# ticket 830277d9db6c3ba1 on 2019-10-31
+do_execsql_test gencol1-100 {
+  CREATE TABLE t0(c0 AS(TYPEOF(c1)), c1);
+  INSERT INTO t0(c1) VALUES(0);
+  CREATE TABLE t1(x AS (typeof(y)), y);
+  INSERT INTO t1 SELECT * FROM t0;
+  SELECT * FROM t1;
+} {integer 0}
+
+foreach {tn schema} { 
+1 {
+   CREATE TABLE t1(
+     a INT,
+     b TEXT,
+     c ANY,
+     w INT GENERATED ALWAYS AS (a*10),
+     x TEXT AS (typeof(c)),
+     y TEXT AS (substr(b,a,a+2))
+   );
+  }
+2 {
+   CREATE TABLE t1(
+     w INT GENERATED ALWAYS AS (a*10),
+     x TEXT AS (typeof(c)),
+     y TEXT AS (substr(b,a,a+2)),
+     a INT,
+     b TEXT,
+     c ANY
+   );
+  }
+3 {
+   CREATE TABLE t1(
+     w INT GENERATED ALWAYS AS (a*10),
+     a INT,
+     x TEXT AS (typeof(c)) STORED,
+     b TEXT,
+     y TEXT AS (substr(b,a,a+2)),
+     c ANY
+   );
+  }
+4 {
+   CREATE TABLE t1(
+     a INTEGER PRIMARY KEY,
+     w INT GENERATED ALWAYS AS (a*10),
+     b TEXT,
+     x TEXT AS (typeof(c)),
+     y TEXT AS (substr(b,a,a+2)) STORED,
+     c ANY
+   );
+  }
+5 {
+   CREATE TABLE t1(
+     w INT GENERATED ALWAYS AS (a*10),
+     a INT,
+     x TEXT AS (typeof(c)),
+     b TEXT,
+     y TEXT AS (substr(b,a,a+2)) STORED,
+     c ANY,
+     PRIMARY KEY(a,b)
+   ) WITHOUT ROWID;
+  }
+6 {
+   CREATE TABLE t1(
+     w INT GENERATED ALWAYS AS (m*5),
+     m INT AS (a*2) STORED,
+     a INT,
+     x TEXT AS (typeof(c)),
+     b TEXT,
+     y TEXT AS (substr(b,m/2,m/2+2)) STORED,
+     c ANY,
+     PRIMARY KEY(a,b)
+   );
+  }
+7 {
+   CREATE TABLE t1(
+     w INT GENERATED ALWAYS AS (m*5),
+     m INT AS (a*2) NOT NULL,
+     a INT,
+     x TEXT AS (typeof(c)) CHECK (x<>'blank'),
+     b TEXT,
+     y TEXT AS (substr(b,m/2,m/2+2)) STORED,
+     c ANY,
+     PRIMARY KEY(b,a)
+   ) WITHOUT ROWID;
+  }
+} {
+  catch {db close}
+  sqlite3 db :memory:
+  db eval $schema
+  do_execsql_test gencol1-2.$tn.100 {
+    INSERT INTO t1(a,b,c) VALUES(1,'abcdef',5.5),(3,'cantaloupe',NULL);
+    SELECT w, x, y, '|' FROM t1 ORDER BY a;
+  } {10 real abc | 30 null ntalo |}
+  do_execsql_test gencol1-2.$tn.101 {
+    SELECT w, x, y, '|' FROM t1 ORDER BY w;
+  } {10 real abc | 30 null ntalo |}
+  do_execsql_test gencol1-2.$tn.102 {
+    SELECT a FROM t1 WHERE w=30;
+  } {3}
+  do_execsql_test gencol1-2.$tn.103 {
+    SELECT a FROM t1 WHERE x='real';
+  } {1}
+  do_execsql_test gencol1-2.$tn.104 {
+    SELECT a FROM t1 WHERE y LIKE '%tal%' OR x='real' ORDER BY b;
+  } {1 3}
+  do_execsql_test gencol1-2.$tn.110 {
+    CREATE INDEX t1w ON t1(w);
+    SELECT a FROM t1 WHERE w=10;
+  } {1}
+  do_execsql_test gencol1-2.$tn.120 {
+    CREATE INDEX t1x ON t1(x) WHERE w BETWEEN 20 AND 40;
+    SELECT a FROM t1 WHERE x='null' AND w BETWEEN 20 AND 40;
+  } {3}
+  do_execsql_test gencol1-2.$tn.121 {
+    SELECT a FROM t1 WHERE x='real';
+  } {1}
+  do_execsql_test gencol1-2.$tn.130 {
+    VACUUM;
+    PRAGMA integrity_check;
+  } {ok}
+  do_execsql_test gencol1-2.$tn.140 {
+    UPDATE t1 SET a=a+100 WHERE w<20;
+    SELECT a, w, '|' FROM t1 ORDER BY w;
+  } {3 30 | 101 1010 |}
+  do_execsql_test gencol1-2.$tn.150 {
+    INSERT INTO t1 VALUES(4,'jambalaya','Chef John'),(15,87719874135,0);
+    SELECT w, x, y, '|' FROM t1 ORDER BY w;
+  } {30 null ntalo | 40 text balaya | 150 integer {} | 1010 real {} |}
+}
+
+# 2019-10-31 ticket b9befa4b83a660cc
+db close
+sqlite3 db :memory:
+do_execsql_test gencol1-3.100 {
+  PRAGMA foreign_keys = true;
+  CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 AS (c0+c1-c3) REFERENCES t0, c3);
+  INSERT INTO t0 VALUES (0, 0, 0), (11, 5, 5);
+  UPDATE t0 SET c1 = c0, c3 = c0;
+  SELECT *, '|' FROM t0 ORDER BY +c0;
+} {0 0 0 0 | 11 11 11 11 |}
+do_catchsql_test gencol1-3.110 {
+  UPDATE t0 SET c1 = c0, c3 = c0+1;
+} {1 {FOREIGN KEY constraint failed}}
+
+# 2019-11-01 ticket c28a01da72f8957c
+db close
+sqlite3 db :memory:
+do_execsql_test gencol1-4.100 {
+  CREATE TABLE t0 (
+    c0,
+    c1 a UNIQUE AS (1),
+    c2,
+    c3 REFERENCES t0(c1)
+  );
+  PRAGMA foreign_keys = true;
+  INSERT INTO t0(c0,c2,c3) VALUES(0,0,1);
+} {}
+do_catchsql_test gencol1-4.110 {
+  REPLACE INTO t0(c0,c2,c3) VALUES(0,0,0),(0,0,0);
+} {1 {FOREIGN KEY constraint failed}}
+
+# 2019-11-01 Problem found while adding new foreign key test cases in TH3.
+db close
+sqlite3 db :memory:
+do_execsql_test gencol1-5.100 {
+  PRAGMA foreign_keys=ON;
+  CREATE TABLE t1(
+    gcb AS (b*1),
+    a INTEGER PRIMARY KEY,
+    gcc AS (c+0),
+    b UNIQUE,
+    gca AS (1*a+0),
+    c UNIQUE
+  ) WITHOUT ROWID;
+  INSERT INTO t1 VALUES(1,2,3);
+  INSERT INTO t1 VALUES(4,5,6);
+  INSERT INTO t1 VALUES(7,8,9);
+  CREATE TABLE t1a(
+    gcx AS (x+0) REFERENCES t1(a) ON DELETE CASCADE,
+    id,
+    x,
+    gcid AS (1*id)
+  );
+  INSERT INTO t1a VALUES(1, 1);
+  INSERT INTO t1a VALUES(2, 4);
+  INSERT INTO t1a VALUES(3, 7);
+  DELETE FROM t1 WHERE b=5;
+  SELECT id,x,'|' FROM t1a ORDER BY id;
+} {1 1 | 3 7 |}  
+
+do_catchsql_test gencol1-6.10 {
+  DROP TABLE IF EXISTS t0;
+  CREATE TABLE t0(c0 NOT NULL AS(c1), c1);
+  REPLACE INTO t0(c1) VALUES(NULL);
+} {1 {NOT NULL constraint failed: t0.c0}}
+
+# 2019-11-06 ticket https://www.sqlite.org/src/info/2399f5986134f79c
+# 2019-12-27 ticket https://www.sqlite.org/src/info/5fbc159eeb092130
+# 2019-12-27 ticket https://www.sqlite.org/src/info/37823501c68a09f9
+#
+# All of the above tickets deal with NOT NULL ON CONFLICT REPLACE
+# constraints on tables that have generated columns.
+#
+reset_db
+do_execsql_test gencol1-7.10 {
+  CREATE TABLE t0 (c0 GENERATED ALWAYS AS (1), c1 UNIQUE, c2 UNIQUE);
+  INSERT INTO t0(c1) VALUES (1);
+  SELECT quote(0 = t0.c2 OR t0.c1 BETWEEN t0.c2 AND 1) FROM t0;
+} {NULL}
+do_execsql_test gencol1-7.11 {
+  DROP TABLE t0;
+  CREATE TABLE t0(c0 NOT NULL DEFAULT 'xyz', c1 AS(c0) NOT NULL);
+  REPLACE INTO t0(c0) VALUES(NULL);
+  SELECT * FROM t0;
+} {xyz xyz}
+do_execsql_test gencol1-7.12 {
+  DROP TABLE t0;
+  CREATE TABLE t0(c0 NOT NULL DEFAULT 'xyz', c1 AS(c0) STORED NOT NULL);
+  REPLACE INTO t0(c0) VALUES(NULL);
+  SELECT * FROM t0;
+} {xyz xyz}
+do_execsql_test gencol1-7.20 {
+  CREATE TABLE t1(
+   a NOT NULL DEFAULT 'aaa',
+   b AS(c) NOT NULL,
+   c NOT NULL DEFAULT 'ccc');
+  REPLACE INTO t1(a,c) VALUES(NULL,NULL);
+  SELECT * FROM t1;
+} {aaa ccc ccc}
+do_execsql_test gencol1-7.21 {
+  DROP TABLE t1;
+  CREATE TABLE t1(
+   a NOT NULL DEFAULT 'aaa',
+   b AS(c) STORED NOT NULL,
+   c NOT NULL DEFAULT 'ccc');
+  REPLACE INTO t1(a,c) VALUES(NULL,NULL);
+  SELECT * FROM t1;
+} {aaa ccc ccc}
+do_execsql_test gencol1-7.30 {
+  CREATE TABLE t2(
+   a NOT NULL DEFAULT 'aaa',
+   b AS(a) NOT NULL,
+   c NOT NULL DEFAULT 'ccc');
+  REPLACE INTO t2(a,c) VALUES(NULL,NULL);
+  SELECT * FROM t2;
+} {aaa aaa ccc}
+do_execsql_test gencol1-7.31 {
+  DROP TABLE t2;
+  CREATE TABLE t2(
+   a NOT NULL DEFAULT 'aaa',
+   b AS(a) STORED NOT NULL,
+   c NOT NULL DEFAULT 'ccc');
+  REPLACE INTO t2(a,c) VALUES(NULL,NULL);
+  SELECT * FROM t2;
+} {aaa aaa ccc}
+do_execsql_test gencol1-7.40 {
+  CREATE TABLE t3(a NOT NULL DEFAULT 123, b AS(a) UNIQUE);
+  REPLACE INTO t3 VALUES(NULL);
+  SELECT * FROM t3;
+} {123 123}
+do_execsql_test gencol1-7.41 {
+  SELECT * FROM t3 WHERE b=123;
+} {123 123}
+do_execsql_test gencol1-7.50 {
+  CREATE TABLE t4(a NOT NULL DEFAULT 123, b AS(a*10+4) STORED UNIQUE);
+  REPLACE INTO t4 VALUES(NULL);
+  SELECT * FROM t4;
+} {123 1234}
+do_execsql_test gencol1-7.51 {
+  SELECT * FROM t4 WHERE b=1234;
+} {123 1234}
+
+# 2019-11-06 ticket 4fc08501f4e56692
+do_execsql_test gencol1-8.10 {
+  DROP TABLE IF EXISTS t0;
+  CREATE TABLE t0(
+    c0 AS (('a', 9) < ('b', c1)),
+    c1 AS (1),
+    c2 CHECK (1 = c1)
+  );
+  INSERT INTO t0 VALUES (0),(99);
+  SELECT * FROM t0;
+} {1 1 0 1 1 99}
+do_catchsql_test gencol1-8.20 {
+  DROP TABLE IF EXISTS t0;
+  CREATE TABLE t0(
+    c0,
+    c1 AS(c0 + c2),
+    c2 AS(c1) CHECK(c2)
+  );
+  UPDATE t0 SET c0 = NULL;
+} {1 {generated column loop on "c2"}}
+
+# 2019-11-21 Problems in the new generated column logic
+# reported by Yongheng Chen and Rui Zhong
+reset_db
+do_execsql_test gencol1-9.10 {
+  PRAGMA foreign_keys=OFF;
+  CREATE TABLE t1(aa , bb AS (17) UNIQUE);
+  INSERT INTO t1 VALUES(17);
+  CREATE TABLE t2(cc);
+  INSERT INTO t2 VALUES(41);
+  SELECT * FROM t2 JOIN t1 WHERE t1.bb=t1.aa AND t1.bb=17;
+} {41 17 17}
+do_execsql_test gencol1-9.20 {
+  CREATE TABLE t3(aa INT PRIMARY KEY, bb UNIQUE AS(aa));
+  INSERT INTO t3 VALUES(1);
+  SELECT 100, * FROM t3;
+  DELETE FROM t3 WHERE (SELECT bb FROM t3);
+  SELECT 200, * FROM t3;
+} {100 1 1}
+
+# 2019-12-04 Generated column in a CREATE TABLE IF NOT EXISTS that
+# does already exist.
+#
+sqlite3 db :memory:
+do_execsql_test gencol1-10.10 {
+  CREATE TABLE t1(aa,bb);
+  CREATE TABLE IF NOT EXISTS t1(aa, bb AS (aa+1));
+  PRAGMA integrity_check;
+} {ok}
+
+# 2019-12-06 Found by mrigger
+#
+sqlite3 db :memory:
+do_execsql_test gencol1-11.10 {
+  PRAGMA foreign_keys = true;
+  CREATE TABLE t0(
+    c0,
+    c1 INTEGER PRIMARY KEY,
+    c2 BLOB UNIQUE DEFAULT x'00',
+    c3 BLOB GENERATED ALWAYS AS (1), 
+    FOREIGN KEY(c1) REFERENCES t0(c2)
+  );
+}
+do_catchsql_test gencol1-11.20 {
+  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
+} {1 {FOREIGN KEY constraint failed}}
+do_execsql_test gencol1-11.30 {
+  DROP TABLE t0;
+  CREATE TABLE t0(
+    c0,
+    c1 INTEGER PRIMARY KEY,
+    c3 BLOB GENERATED ALWAYS AS (1), 
+    c2 BLOB UNIQUE DEFAULT x'00',
+    FOREIGN KEY(c1) REFERENCES t0(c2)
+  );
+}
+do_catchsql_test gencol1-11.40 {
+  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
+} {1 {FOREIGN KEY constraint failed}}
+do_execsql_test gencol1-11.50 {
+  DROP TABLE t0;
+  CREATE TABLE t0(
+    c0,
+    c3 BLOB GENERATED ALWAYS AS (1), 
+    c1 INTEGER PRIMARY KEY,
+    c2 BLOB UNIQUE DEFAULT x'00',
+    FOREIGN KEY(c1) REFERENCES t0(c2)
+  );
+}
+do_catchsql_test gencol1-11.60 {
+  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
+} {1 {FOREIGN KEY constraint failed}}
+do_execsql_test gencol1-11.70 {
+  DROP TABLE t0;
+  CREATE TABLE t0(
+    c3 BLOB GENERATED ALWAYS AS (1), 
+    c0,
+    c1 INTEGER PRIMARY KEY,
+    c2 BLOB UNIQUE DEFAULT x'00',
+    FOREIGN KEY(c1) REFERENCES t0(c2)
+  );
+}
+do_catchsql_test gencol1-11.80 {
+  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)
+} {1 {FOREIGN KEY constraint failed}}
+
+# 2019-12-09 ticket bd8c280671ba44a7
+# With generated columns, the sqlite3ExprGetColumnOfTable() routine might
+# generate a code sequence that does not end with OP_Column.  So check to
+# make sure that the last instruction generated is an OP_column prior to
+# applying the OPFLAG_TYPEOFARG optimization to NOT NULL checks in the
+# PRAGMA integrity_check code.
+#
+sqlite3 db :memory:
+do_execsql_test gencol1-12.10 {
+  CREATE TABLE t0 (c0, c1 NOT NULL AS (c0==0));
+  INSERT INTO t0(c0) VALUES (0);
+  PRAGMA integrity_check;
+} {ok}
+
+# 2019-12-09 bug report from Yongheng Chen
+# Ensure that the SrcList_item.colUsed field is set correctly when a
+# generated column appears in the USING clause of a join.
+#
+do_execsql_test gencol1-13.10 {
+  CREATE TABLE t1(x, y AS(x+1));
+  INSERT INTO t1 VALUES(10);
+  SELECT y FROM t1 JOIN t1 USING (y,y);
+} {11}
+do_execsql_test gencol1-13.11 {
+  SELECT 123 FROM t1 JOIN t1 USING (x);
+} {123}
+do_execsql_test gencol1-13.11 {
+  SELECT 456 FROM t1 JOIN t1 USING (x,x);
+} {456}
+do_execsql_test gencol1-13.20 {
+  CREATE INDEX t1y ON t1(y);
+  SELECT y FROM t1 JOIN t1 USING (y,y);
+} {11}
+do_execsql_test gencol1-13.21 {
+  CREATE INDEX t1x ON t1(x);
+  SELECT 123 FROM t1 JOIN t1 USING (x);
+} {123}
+do_execsql_test gencol1-13.22 {
+  SELECT 456 FROM t1 JOIN t1 USING (x,x);
+} {456}
+
+# 2019-12-14 ticket b439bfcfb7deedc6
+#
+sqlite3 db :memory:
+do_execsql_test gencol1-14.10 {
+  CREATE TABLE t0(c0 AS(1 >= 1), c1 UNIQUE AS(TYPEOF(c0)), c2);
+  INSERT INTO t0 VALUES(0);
+  REINDEX;
+  SELECT * FROM t0;
+} {1 integer 0}
+do_catchsql_test gencol1-14.10 {
+  INSERT INTO t0 VALUES(2);
+} {1 {UNIQUE constraint failed: t0.c1}}
+
+# 2019-12-14 gramfuzz1 find
+# The schema is malformed in that it has a subquery on a generated
+# column expression.  This will be loaded if writable_schema=ON.  SQLite
+# must not use such an expression during code generation as the code generator
+# will add bits of content to the expression tree that might be allocated
+# from lookaside.  But the schema is not tied to a particular database
+# connection, so the use of lookaside memory is prohibited.  The fix
+# is to change the generated column expression to NULL before adding it
+# to the schema.
+#
+reset_db
+do_test gencol1-15.10 {
+  sqlite3 db {}
+  db deserialize [decode_hexdb {
+| size 8192 pagesize 4096 filename c27.db
+| page 1 offset 0
+|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.
+|     16: 10 00 01 01 00 40 20 20 00 00 00 01 00 00 00 02   .....@  ........
+|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................
+|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................
+|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01   ................
+|     96: 00 2e 3f d8 0d 00 00 00 01 0f ba 00 0f ba 00 00   ..?.............
+|   4016: 00 00 00 00 00 00 00 00 00 00 44 01 06 17 11 11   ..........D.....
+|   4032: 01 75 74 61 62 6c 65 74 31 74 31 02 43 52 45 41   .utablet1t1.CREA
+|   4048: 54 45 20 54 41 42 4c 45 20 74 31 28 61 20 49 4e   TE TABLE t1(a IN
+|   4064: 54 2c 20 62 20 41 53 28 28 56 41 4c 55 45 53 28   T, b AS((VALUES(
+|   4080: 31 29 29 20 49 53 20 75 6e 6b 6e 6f 77 6e 29 29   1)) IS unknown))
+| page 2 offset 4096
+|      0: 0d 00 00 00 00 10 00 00 00 00 00 00 00 00 00 00   ................
+| end c27.db
+}]} {}
+do_execsql_test gencol1-15.20 {
+  PRAGMA writable_schema=ON;
+  REPLACE INTO t1 VALUES(9);
+  SELECT a, quote(b) FROM t1
+} {9 NULL}
+
+# 2019-12-16 ticket 3b84b42943644d6f
+# When a table is the right table of a LEFT JOIN and the ON clause is
+# false, make sure any generated columns evaluate to NULL.
+reset_db
+do_execsql_test gencol1-16.10 {
+  CREATE TABLE t0(c0);
+  CREATE TABLE t1(c1, c2 AS(1));
+  INSERT INTO t0 VALUES(0);
+  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1;
+} {0 {} {}}
+do_execsql_test gencol1-16.20 {
+  DROP TABLE t1;
+  CREATE TABLE t1(c1, c2 AS (c1 ISNULL));
+  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1;
+} {0 {} {}}
+do_execsql_test gencol1-16.30 {
+  INSERT INTO t1(c1) VALUES(1),(NULL);
+  SELECT * FROM t1;
+} {1 0 {} 1}
+do_execsql_test gencol1-16.40 {
+  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1 ON c0=c1;
+} {0 {} {}}
+
+# 2019-12-20 ticket e0a8120553f4b082
+# Generated columns with REAL affinity need to have an OP_RealAffinity
+# opcode applied, even when the column value is extracted from an index.
+#
+reset_db
+do_execsql_test gencol1-17.10 {
+  CREATE TABLE t0(c0 REAL AS(1) UNIQUE, c1 INT);
+  INSERT INTO t0 VALUES('');
+  SELECT quote(c0), quote(c1) from t0;
+} {1.0 ''}
+do_execsql_test gencol1-17.20 {
+  SELECT *, (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0) FROM t0;
+} {1.0 {} 0}
+do_execsql_test gencol1-17.30 {
+  SELECT * FROM t0 WHERE (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0);
+} {}
+do_execsql_test gencol1-17.40 {
+  CREATE TABLE t1(a TEXT AS(b) COLLATE nocase, b TEXT, c INT, d DEFAULT 1);
+  INSERT INTO t1(b,c) VALUES('abc',11),('DEF',22),('ghi',33);
+  SELECT a FROM t1 WHERE b='DEF' AND a='def';
+} {DEF}
+do_execsql_test gencol1-17.50 {
+  CREATE INDEX t1bca ON t1(b,c,a);
+  SELECT a FROM t1 WHERE b='DEF' AND a='def';
+} {DEF}
+
+# 2019-12-26 ticket ec8abb025e78f40c
+# An index on a virtual column with a constant value (why would anybody
+# ever do such a thing?) can cause problems for a one-pass DELETE.
+#
+reset_db
+do_execsql_test gencol1-18.10 {
+  CREATE TABLE t0(c0 UNIQUE AS(0), c1, c2);
+  INSERT INTO t0(c1) VALUES(0);
+  SELECT * FROM t0;
+} {0 0 {}}
+do_execsql_test gencol1-18.20 {
+  UPDATE t0 SET c1=0, c2=0 WHERE c0>=0;
+  SELECT * FROM t0;
+} {0 0 0}
+
+# 2019-12-27 ticket de4b04149b9fdeae
+#
+reset_db
+do_catchsql_test gencol1-19.10 {
+  CREATE TABLE t0(
+    c0 INT AS(2) UNIQUE,
+    c1 TEXT UNIQUE,
+    FOREIGN KEY(c0) REFERENCES t0(c1)
+  );
+  INSERT INTO t0(c1) VALUES(0.16334143182538696), (0);
+} {1 {UNIQUE constraint failed: t0.c0}}
+
+# 2020-06-29 forum bug report.
+# https://sqlite.org/forum/forumpost/73b9a8ccfb
+#
+do_execsql_test gencol1-20.1 {
+  CREATE TEMPORARY TABLE tab (
+    prim DATE PRIMARY KEY,
+    a INTEGER,
+    comp INTEGER AS (a),
+    b INTEGER,
+    x INTEGER
+  );
+  -- Add some data
+  INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0);
+  -- Check that each column is 0 like I expect
+  SELECT * FROM tab;
+} {2001-01-01 0 0 0 {}}
+do_execsql_test gencol1-20.2 {
+  -- Do an UPSERT on the b column
+  INSERT INTO tab (prim, b)
+  VALUES ('2001-01-01',5)
+      ON CONFLICT(prim) DO UPDATE SET  b=excluded.b;
+  -- Now b is NULL rather than 5
+  SELECT * FROM tab;
+} {2001-01-01 0 0 5 {}}
+
+# 2021-07-30 forum https://sqlite.org/forum/forumpost/ff3ffe09251c105b?t=h
+#
+ifcapable vtab {
+reset_db
+  do_execsql_test gencol1-21.1 {
+    CREATE TABLE t1(
+      a integer primary key,
+      b int generated always as (a+5),
+      c text    GENERATED   ALWAYS as (printf('%08x',a)),
+      d Generated
+        Always
+        AS ('xyzzy'),
+      e int                         Always default(5)
+    );
+    INSERT INTO t1(a) VALUES(5);
+    SELECT name, type FROM pragma_table_xinfo('t1');
+  } {a INTEGER b INT c TEXT d {} e INT}
+}
+
+# 2021-09-07 forum https://sqlite.org/forum/forumpost/699b44b3ee
+#
+reset_db
+do_execsql_test gencol1-22.1 {
+  CREATE TABLE t0(a PRIMARY KEY,b TEXT AS ('2') UNIQUE);
+  INSERT INTO t0(a) VALUES(2);
+  SELECT * FROM t0 AS x JOIN t0 AS y
+   WHERE x.b='2'
+     AND (y.a=2 OR (x.b LIKE '2*' AND y.a=x.b));
+} {2 2 2 2}
+
+
+# 2023-03-02 dbsqlfuzz 65f5eb57f8859344d5f1f33e08c77ee12960ed83
+#
+set typelist {ANY INT REAL BLOB TEXT {}}
+set cnt 0
+foreach t1 $typelist {
+  foreach t2 $typelist {
+    incr cnt
+    db eval "
+      DROP TABLE IF EXISTS t1;
+      CREATE TABLE t1(
+        x $t1,
+        a $t2 AS (x) VIRTUAL,
+        b BLOB AS (x) VIRTUAL
+      );
+      CREATE INDEX x2 ON t1(a);
+      INSERT INTO t1(x) VALUES(NULL),('1'),(2),(3.5),('xyz');
+    "
+    set x1 [lsort [db eval {SELECT typeof(b) FROM t1}]]
+    do_test gencol1-23.1.$cnt {
+      lsort [db eval {SELECT typeof(b) FROM t1 INDEXED BY x2}]
+    } $x1
+  }
+}
+do_execsql_test gencol1-23.2 {
+  DROP TABLE t1;
+  CREATE TABLE t1(
+    x,
+    a INT AS (x) VIRTUAL,
+    b BLOB AS (x) VIRTUAL
+  );
+  CREATE INDEX x2 ON t1(a);
+  INSERT INTO t1(x) VALUES(NULL),('1'),('xyz'),(2),(3.5);
+  SELECT quote(a) FROM t1 INDEXED BY x2;
+} {NULL 1 2 3.5 'xyz'}
+do_execsql_test gencol1-23.3 {
+  EXPLAIN SELECT a FROM t1 INDEXED BY x2;
+} {~/Column 0/}
+#    ^^^^^^^^---- verfies that x2 acts like a covering index
+do_execsql_test gencol1-23.4 {
+  EXPLAIN SELECT b FROM t1 INDEXED BY x2;
+} {/Column 0/}
+#  ^^^^^^^^^^--- Must reference the original table in this case because
+# of the different datatype on column b.
+
+# 2023-03-07 https://sqlite.org/forum/forumpost/b312e075b5
+#
+do_catchsql_test gencol1-23.5 {
+  CREATE TABLE v0(c1 INT, c2 AS (RAISE(IGNORE)));
+} {1 {RAISE() may only be used within a trigger-program}}
+
+finish_test