Modelpublic
AryaWu/sqlite

sourceHugging Faceupdated 9mo agoView on Hugging Face
0likes
gencol1.test670 linesDownload Raw Back to test
1# 2019-10-312#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# 12# Test cases for generated columns.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18# ticket 830277d9db6c3ba1 on 2019-10-3119do_execsql_test gencol1-100 {20  CREATE TABLE t0(c0 AS(TYPEOF(c1)), c1);21  INSERT INTO t0(c1) VALUES(0);22  CREATE TABLE t1(x AS (typeof(y)), y);23  INSERT INTO t1 SELECT * FROM t0;24  SELECT * FROM t1;25} {integer 0}26 27foreach {tn schema} { 281 {29   CREATE TABLE t1(30     a INT,31     b TEXT,32     c ANY,33     w INT GENERATED ALWAYS AS (a*10),34     x TEXT AS (typeof(c)),35     y TEXT AS (substr(b,a,a+2))36   );37  }382 {39   CREATE TABLE t1(40     w INT GENERATED ALWAYS AS (a*10),41     x TEXT AS (typeof(c)),42     y TEXT AS (substr(b,a,a+2)),43     a INT,44     b TEXT,45     c ANY46   );47  }483 {49   CREATE TABLE t1(50     w INT GENERATED ALWAYS AS (a*10),51     a INT,52     x TEXT AS (typeof(c)) STORED,53     b TEXT,54     y TEXT AS (substr(b,a,a+2)),55     c ANY56   );57  }584 {59   CREATE TABLE t1(60     a INTEGER PRIMARY KEY,61     w INT GENERATED ALWAYS AS (a*10),62     b TEXT,63     x TEXT AS (typeof(c)),64     y TEXT AS (substr(b,a,a+2)) STORED,65     c ANY66   );67  }685 {69   CREATE TABLE t1(70     w INT GENERATED ALWAYS AS (a*10),71     a INT,72     x TEXT AS (typeof(c)),73     b TEXT,74     y TEXT AS (substr(b,a,a+2)) STORED,75     c ANY,76     PRIMARY KEY(a,b)77   ) WITHOUT ROWID;78  }796 {80   CREATE TABLE t1(81     w INT GENERATED ALWAYS AS (m*5),82     m INT AS (a*2) STORED,83     a INT,84     x TEXT AS (typeof(c)),85     b TEXT,86     y TEXT AS (substr(b,m/2,m/2+2)) STORED,87     c ANY,88     PRIMARY KEY(a,b)89   );90  }917 {92   CREATE TABLE t1(93     w INT GENERATED ALWAYS AS (m*5),94     m INT AS (a*2) NOT NULL,95     a INT,96     x TEXT AS (typeof(c)) CHECK (x<>'blank'),97     b TEXT,98     y TEXT AS (substr(b,m/2,m/2+2)) STORED,99     c ANY,100     PRIMARY KEY(b,a)101   ) WITHOUT ROWID;102  }103} {104  catch {db close}105  sqlite3 db :memory:106  db eval $schema107  do_execsql_test gencol1-2.$tn.100 {108    INSERT INTO t1(a,b,c) VALUES(1,'abcdef',5.5),(3,'cantaloupe',NULL);109    SELECT w, x, y, '|' FROM t1 ORDER BY a;110  } {10 real abc | 30 null ntalo |}111  do_execsql_test gencol1-2.$tn.101 {112    SELECT w, x, y, '|' FROM t1 ORDER BY w;113  } {10 real abc | 30 null ntalo |}114  do_execsql_test gencol1-2.$tn.102 {115    SELECT a FROM t1 WHERE w=30;116  } {3}117  do_execsql_test gencol1-2.$tn.103 {118    SELECT a FROM t1 WHERE x='real';119  } {1}120  do_execsql_test gencol1-2.$tn.104 {121    SELECT a FROM t1 WHERE y LIKE '%tal%' OR x='real' ORDER BY b;122  } {1 3}123  do_execsql_test gencol1-2.$tn.110 {124    CREATE INDEX t1w ON t1(w);125    SELECT a FROM t1 WHERE w=10;126  } {1}127  do_execsql_test gencol1-2.$tn.120 {128    CREATE INDEX t1x ON t1(x) WHERE w BETWEEN 20 AND 40;129    SELECT a FROM t1 WHERE x='null' AND w BETWEEN 20 AND 40;130  } {3}131  do_execsql_test gencol1-2.$tn.121 {132    SELECT a FROM t1 WHERE x='real';133  } {1}134  do_execsql_test gencol1-2.$tn.130 {135    VACUUM;136    PRAGMA integrity_check;137  } {ok}138  do_execsql_test gencol1-2.$tn.140 {139    UPDATE t1 SET a=a+100 WHERE w<20;140    SELECT a, w, '|' FROM t1 ORDER BY w;141  } {3 30 | 101 1010 |}142  do_execsql_test gencol1-2.$tn.150 {143    INSERT INTO t1 VALUES(4,'jambalaya','Chef John'),(15,87719874135,0);144    SELECT w, x, y, '|' FROM t1 ORDER BY w;145  } {30 null ntalo | 40 text balaya | 150 integer {} | 1010 real {} |}146}147 148# 2019-10-31 ticket b9befa4b83a660cc149db close150sqlite3 db :memory:151do_execsql_test gencol1-3.100 {152  PRAGMA foreign_keys = true;153  CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 AS (c0+c1-c3) REFERENCES t0, c3);154  INSERT INTO t0 VALUES (0, 0, 0), (11, 5, 5);155  UPDATE t0 SET c1 = c0, c3 = c0;156  SELECT *, '|' FROM t0 ORDER BY +c0;157} {0 0 0 0 | 11 11 11 11 |}158do_catchsql_test gencol1-3.110 {159  UPDATE t0 SET c1 = c0, c3 = c0+1;160} {1 {FOREIGN KEY constraint failed}}161 162# 2019-11-01 ticket c28a01da72f8957c163db close164sqlite3 db :memory:165do_execsql_test gencol1-4.100 {166  CREATE TABLE t0 (167    c0,168    c1 a UNIQUE AS (1),169    c2,170    c3 REFERENCES t0(c1)171  );172  PRAGMA foreign_keys = true;173  INSERT INTO t0(c0,c2,c3) VALUES(0,0,1);174} {}175do_catchsql_test gencol1-4.110 {176  REPLACE INTO t0(c0,c2,c3) VALUES(0,0,0),(0,0,0);177} {1 {FOREIGN KEY constraint failed}}178 179# 2019-11-01 Problem found while adding new foreign key test cases in TH3.180db close181sqlite3 db :memory:182do_execsql_test gencol1-5.100 {183  PRAGMA foreign_keys=ON;184  CREATE TABLE t1(185    gcb AS (b*1),186    a INTEGER PRIMARY KEY,187    gcc AS (c+0),188    b UNIQUE,189    gca AS (1*a+0),190    c UNIQUE191  ) WITHOUT ROWID;192  INSERT INTO t1 VALUES(1,2,3);193  INSERT INTO t1 VALUES(4,5,6);194  INSERT INTO t1 VALUES(7,8,9);195  CREATE TABLE t1a(196    gcx AS (x+0) REFERENCES t1(a) ON DELETE CASCADE,197    id,198    x,199    gcid AS (1*id)200  );201  INSERT INTO t1a VALUES(1, 1);202  INSERT INTO t1a VALUES(2, 4);203  INSERT INTO t1a VALUES(3, 7);204  DELETE FROM t1 WHERE b=5;205  SELECT id,x,'|' FROM t1a ORDER BY id;206} {1 1 | 3 7 |}  207 208do_catchsql_test gencol1-6.10 {209  DROP TABLE IF EXISTS t0;210  CREATE TABLE t0(c0 NOT NULL AS(c1), c1);211  REPLACE INTO t0(c1) VALUES(NULL);212} {1 {NOT NULL constraint failed: t0.c0}}213 214# 2019-11-06 ticket https://sqlite.org/src/info/2399f5986134f79c215# 2019-12-27 ticket https://sqlite.org/src/info/5fbc159eeb092130216# 2019-12-27 ticket https://sqlite.org/src/info/37823501c68a09f9217#218# All of the above tickets deal with NOT NULL ON CONFLICT REPLACE219# constraints on tables that have generated columns.220#221reset_db222do_execsql_test gencol1-7.10 {223  CREATE TABLE t0 (c0 GENERATED ALWAYS AS (1), c1 UNIQUE, c2 UNIQUE);224  INSERT INTO t0(c1) VALUES (1);225  SELECT quote(0 = t0.c2 OR t0.c1 BETWEEN t0.c2 AND 1) FROM t0;226} {NULL}227do_execsql_test gencol1-7.11 {228  DROP TABLE t0;229  CREATE TABLE t0(c0 NOT NULL DEFAULT 'xyz', c1 AS(c0) NOT NULL);230  REPLACE INTO t0(c0) VALUES(NULL);231  SELECT * FROM t0;232} {xyz xyz}233do_execsql_test gencol1-7.12 {234  DROP TABLE t0;235  CREATE TABLE t0(c0 NOT NULL DEFAULT 'xyz', c1 AS(c0) STORED NOT NULL);236  REPLACE INTO t0(c0) VALUES(NULL);237  SELECT * FROM t0;238} {xyz xyz}239do_execsql_test gencol1-7.20 {240  CREATE TABLE t1(241   a NOT NULL DEFAULT 'aaa',242   b AS(c) NOT NULL,243   c NOT NULL DEFAULT 'ccc');244  REPLACE INTO t1(a,c) VALUES(NULL,NULL);245  SELECT * FROM t1;246} {aaa ccc ccc}247do_execsql_test gencol1-7.21 {248  DROP TABLE t1;249  CREATE TABLE t1(250   a NOT NULL DEFAULT 'aaa',251   b AS(c) STORED NOT NULL,252   c NOT NULL DEFAULT 'ccc');253  REPLACE INTO t1(a,c) VALUES(NULL,NULL);254  SELECT * FROM t1;255} {aaa ccc ccc}256do_execsql_test gencol1-7.30 {257  CREATE TABLE t2(258   a NOT NULL DEFAULT 'aaa',259   b AS(a) NOT NULL,260   c NOT NULL DEFAULT 'ccc');261  REPLACE INTO t2(a,c) VALUES(NULL,NULL);262  SELECT * FROM t2;263} {aaa aaa ccc}264do_execsql_test gencol1-7.31 {265  DROP TABLE t2;266  CREATE TABLE t2(267   a NOT NULL DEFAULT 'aaa',268   b AS(a) STORED NOT NULL,269   c NOT NULL DEFAULT 'ccc');270  REPLACE INTO t2(a,c) VALUES(NULL,NULL);271  SELECT * FROM t2;272} {aaa aaa ccc}273do_execsql_test gencol1-7.40 {274  CREATE TABLE t3(a NOT NULL DEFAULT 123, b AS(a) UNIQUE);275  REPLACE INTO t3 VALUES(NULL);276  SELECT * FROM t3;277} {123 123}278do_execsql_test gencol1-7.41 {279  SELECT * FROM t3 WHERE b=123;280} {123 123}281do_execsql_test gencol1-7.50 {282  CREATE TABLE t4(a NOT NULL DEFAULT 123, b AS(a*10+4) STORED UNIQUE);283  REPLACE INTO t4 VALUES(NULL);284  SELECT * FROM t4;285} {123 1234}286do_execsql_test gencol1-7.51 {287  SELECT * FROM t4 WHERE b=1234;288} {123 1234}289 290# 2019-11-06 ticket 4fc08501f4e56692291do_execsql_test gencol1-8.10 {292  DROP TABLE IF EXISTS t0;293  CREATE TABLE t0(294    c0 AS (('a', 9) < ('b', c1)),295    c1 AS (1),296    c2 CHECK (1 = c1)297  );298  INSERT INTO t0 VALUES (0),(99);299  SELECT * FROM t0;300} {1 1 0 1 1 99}301do_catchsql_test gencol1-8.20 {302  DROP TABLE IF EXISTS t0;303  CREATE TABLE t0(304    c0,305    c1 AS(c0 + c2),306    c2 AS(c1) CHECK(c2)307  );308  UPDATE t0 SET c0 = NULL;309} {1 {generated column loop on "c2"}}310 311# 2019-11-21 Problems in the new generated column logic312# reported by Yongheng Chen and Rui Zhong313reset_db314do_execsql_test gencol1-9.10 {315  PRAGMA foreign_keys=OFF;316  CREATE TABLE t1(aa , bb AS (17) UNIQUE);317  INSERT INTO t1 VALUES(17);318  CREATE TABLE t2(cc);319  INSERT INTO t2 VALUES(41);320  SELECT * FROM t2 JOIN t1 WHERE t1.bb=t1.aa AND t1.bb=17;321} {41 17 17}322do_execsql_test gencol1-9.20 {323  CREATE TABLE t3(aa INT PRIMARY KEY, bb UNIQUE AS(aa));324  INSERT INTO t3 VALUES(1);325  SELECT 100, * FROM t3;326  DELETE FROM t3 WHERE (SELECT bb FROM t3);327  SELECT 200, * FROM t3;328} {100 1 1}329 330# 2019-12-04 Generated column in a CREATE TABLE IF NOT EXISTS that331# does already exist.332#333sqlite3 db :memory:334do_execsql_test gencol1-10.10 {335  CREATE TABLE t1(aa,bb);336  CREATE TABLE IF NOT EXISTS t1(aa, bb AS (aa+1));337  PRAGMA integrity_check;338} {ok}339 340# 2019-12-06 Found by mrigger341#342sqlite3 db :memory:343do_execsql_test gencol1-11.10 {344  PRAGMA foreign_keys = true;345  CREATE TABLE t0(346    c0,347    c1 INTEGER PRIMARY KEY,348    c2 BLOB UNIQUE DEFAULT x'00',349    c3 BLOB GENERATED ALWAYS AS (1), 350    FOREIGN KEY(c1) REFERENCES t0(c2)351  );352}353do_catchsql_test gencol1-11.20 {354  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)355} {1 {FOREIGN KEY constraint failed}}356do_execsql_test gencol1-11.30 {357  DROP TABLE t0;358  CREATE TABLE t0(359    c0,360    c1 INTEGER PRIMARY KEY,361    c3 BLOB GENERATED ALWAYS AS (1), 362    c2 BLOB UNIQUE DEFAULT x'00',363    FOREIGN KEY(c1) REFERENCES t0(c2)364  );365}366do_catchsql_test gencol1-11.40 {367  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)368} {1 {FOREIGN KEY constraint failed}}369do_execsql_test gencol1-11.50 {370  DROP TABLE t0;371  CREATE TABLE t0(372    c0,373    c3 BLOB GENERATED ALWAYS AS (1), 374    c1 INTEGER PRIMARY KEY,375    c2 BLOB UNIQUE DEFAULT x'00',376    FOREIGN KEY(c1) REFERENCES t0(c2)377  );378}379do_catchsql_test gencol1-11.60 {380  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)381} {1 {FOREIGN KEY constraint failed}}382do_execsql_test gencol1-11.70 {383  DROP TABLE t0;384  CREATE TABLE t0(385    c3 BLOB GENERATED ALWAYS AS (1), 386    c0,387    c1 INTEGER PRIMARY KEY,388    c2 BLOB UNIQUE DEFAULT x'00',389    FOREIGN KEY(c1) REFERENCES t0(c2)390  );391}392do_catchsql_test gencol1-11.80 {393  INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0)394} {1 {FOREIGN KEY constraint failed}}395 396# 2019-12-09 ticket bd8c280671ba44a7397# With generated columns, the sqlite3ExprGetColumnOfTable() routine might398# generate a code sequence that does not end with OP_Column.  So check to399# make sure that the last instruction generated is an OP_column prior to400# applying the OPFLAG_TYPEOFARG optimization to NOT NULL checks in the401# PRAGMA integrity_check code.402#403sqlite3 db :memory:404do_execsql_test gencol1-12.10 {405  CREATE TABLE t0 (c0, c1 NOT NULL AS (c0==0));406  INSERT INTO t0(c0) VALUES (0);407  PRAGMA integrity_check;408} {ok}409 410# 2019-12-09 bug report from Yongheng Chen411# Ensure that the SrcList_item.colUsed field is set correctly when a412# generated column appears in the USING clause of a join.413#414do_execsql_test gencol1-13.10 {415  CREATE TABLE t1(x, y AS(x+1));416  INSERT INTO t1 VALUES(10);417  SELECT y FROM t1 JOIN t1 USING (y,y);418} {11}419do_execsql_test gencol1-13.11 {420  SELECT 123 FROM t1 JOIN t1 USING (x);421} {123}422do_execsql_test gencol1-13.11 {423  SELECT 456 FROM t1 JOIN t1 USING (x,x);424} {456}425do_execsql_test gencol1-13.20 {426  CREATE INDEX t1y ON t1(y);427  SELECT y FROM t1 JOIN t1 USING (y,y);428} {11}429do_execsql_test gencol1-13.21 {430  CREATE INDEX t1x ON t1(x);431  SELECT 123 FROM t1 JOIN t1 USING (x);432} {123}433do_execsql_test gencol1-13.22 {434  SELECT 456 FROM t1 JOIN t1 USING (x,x);435} {456}436 437# 2019-12-14 ticket b439bfcfb7deedc6438#439sqlite3 db :memory:440do_execsql_test gencol1-14.10 {441  CREATE TABLE t0(c0 AS(1 >= 1), c1 UNIQUE AS(TYPEOF(c0)), c2);442  INSERT INTO t0 VALUES(0);443  REINDEX;444  SELECT * FROM t0;445} {1 integer 0}446do_catchsql_test gencol1-14.10 {447  INSERT INTO t0 VALUES(2);448} {1 {UNIQUE constraint failed: t0.c1}}449 450# 2019-12-14 gramfuzz1 find451# The schema is malformed in that it has a subquery on a generated452# column expression.  This will be loaded if writable_schema=ON.  SQLite453# must not use such an expression during code generation as the code generator454# will add bits of content to the expression tree that might be allocated455# from lookaside.  But the schema is not tied to a particular database456# connection, so the use of lookaside memory is prohibited.  The fix457# is to change the generated column expression to NULL before adding it458# to the schema.459#460reset_db461do_test gencol1-15.10 {462  sqlite3 db {}463  db deserialize [decode_hexdb {464| size 8192 pagesize 4096 filename c27.db465| page 1 offset 0466|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.467|     16: 10 00 01 01 00 40 20 20 00 00 00 01 00 00 00 02   .....@  ........468|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................469|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................470|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01   ................471|     96: 00 2e 3f d8 0d 00 00 00 01 0f ba 00 0f ba 00 00   ..?.............472|   4016: 00 00 00 00 00 00 00 00 00 00 44 01 06 17 11 11   ..........D.....473|   4032: 01 75 74 61 62 6c 65 74 31 74 31 02 43 52 45 41   .utablet1t1.CREA474|   4048: 54 45 20 54 41 42 4c 45 20 74 31 28 61 20 49 4e   TE TABLE t1(a IN475|   4064: 54 2c 20 62 20 41 53 28 28 56 41 4c 55 45 53 28   T, b AS((VALUES(476|   4080: 31 29 29 20 49 53 20 75 6e 6b 6e 6f 77 6e 29 29   1)) IS unknown))477| page 2 offset 4096478|      0: 0d 00 00 00 00 10 00 00 00 00 00 00 00 00 00 00   ................479| end c27.db480}]} {}481do_execsql_test gencol1-15.20 {482  PRAGMA writable_schema=ON;483  REPLACE INTO t1 VALUES(9);484  SELECT a, quote(b) FROM t1485} {9 NULL}486 487# 2019-12-16 ticket 3b84b42943644d6f488# When a table is the right table of a LEFT JOIN and the ON clause is489# false, make sure any generated columns evaluate to NULL.490reset_db491do_execsql_test gencol1-16.10 {492  CREATE TABLE t0(c0);493  CREATE TABLE t1(c1, c2 AS(1));494  INSERT INTO t0 VALUES(0);495  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1;496} {0 {} {}}497do_execsql_test gencol1-16.20 {498  DROP TABLE t1;499  CREATE TABLE t1(c1, c2 AS (c1 ISNULL));500  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1;501} {0 {} {}}502do_execsql_test gencol1-16.30 {503  INSERT INTO t1(c1) VALUES(1),(NULL);504  SELECT * FROM t1;505} {1 0 {} 1}506do_execsql_test gencol1-16.40 {507  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1 ON c0=c1;508} {0 {} {}}509 510# 2019-12-20 ticket e0a8120553f4b082511# Generated columns with REAL affinity need to have an OP_RealAffinity512# opcode applied, even when the column value is extracted from an index.513#514reset_db515do_execsql_test gencol1-17.10 {516  CREATE TABLE t0(c0 REAL AS(1) UNIQUE, c1 INT);517  INSERT INTO t0 VALUES('');518  SELECT quote(c0), quote(c1) from t0;519} {1.0 ''}520do_execsql_test gencol1-17.20 {521  SELECT *, (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0) FROM t0;522} {1.0 {} 0}523do_execsql_test gencol1-17.30 {524  SELECT * FROM t0 WHERE (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0);525} {}526do_execsql_test gencol1-17.40 {527  CREATE TABLE t1(a TEXT AS(b) COLLATE nocase, b TEXT, c INT, d DEFAULT 1);528  INSERT INTO t1(b,c) VALUES('abc',11),('DEF',22),('ghi',33);529  SELECT a FROM t1 WHERE b='DEF' AND a='def';530} {DEF}531do_execsql_test gencol1-17.50 {532  CREATE INDEX t1bca ON t1(b,c,a);533  SELECT a FROM t1 WHERE b='DEF' AND a='def';534} {DEF}535 536# 2019-12-26 ticket ec8abb025e78f40c537# An index on a virtual column with a constant value (why would anybody538# ever do such a thing?) can cause problems for a one-pass DELETE.539#540reset_db541do_execsql_test gencol1-18.10 {542  CREATE TABLE t0(c0 UNIQUE AS(0), c1, c2);543  INSERT INTO t0(c1) VALUES(0);544  SELECT * FROM t0;545} {0 0 {}}546do_execsql_test gencol1-18.20 {547  UPDATE t0 SET c1=0, c2=0 WHERE c0>=0;548  SELECT * FROM t0;549} {0 0 0}550 551# 2019-12-27 ticket de4b04149b9fdeae552#553reset_db554do_catchsql_test gencol1-19.10 {555  CREATE TABLE t0(556    c0 INT AS(2) UNIQUE,557    c1 TEXT UNIQUE,558    FOREIGN KEY(c0) REFERENCES t0(c1)559  );560  INSERT INTO t0(c1) VALUES(0.16334143182538696), (0);561} {1 {UNIQUE constraint failed: t0.c0}}562 563# 2020-06-29 forum bug report.564# https://sqlite.org/forum/forumpost/73b9a8ccfb565#566do_execsql_test gencol1-20.1 {567  CREATE TEMPORARY TABLE tab (568    prim DATE PRIMARY KEY,569    a INTEGER,570    comp INTEGER AS (a),571    b INTEGER,572    x INTEGER573  );574  -- Add some data575  INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0);576  -- Check that each column is 0 like I expect577  SELECT * FROM tab;578} {2001-01-01 0 0 0 {}}579do_execsql_test gencol1-20.2 {580  -- Do an UPSERT on the b column581  INSERT INTO tab (prim, b)582  VALUES ('2001-01-01',5)583      ON CONFLICT(prim) DO UPDATE SET  b=excluded.b;584  -- Now b is NULL rather than 5585  SELECT * FROM tab;586} {2001-01-01 0 0 5 {}}587 588# 2021-07-30 forum https://sqlite.org/forum/forumpost/ff3ffe09251c105b?t=h589#590ifcapable vtab {591reset_db592  do_execsql_test gencol1-21.1 {593    CREATE TABLE t1(594      a integer primary key,595      b int generated always as (a+5),596      c text    GENERATED   ALWAYS as (printf('%08x',a)),597      d Generated598        Always599        AS ('xyzzy'),600      e int                         Always default(5)601    );602    INSERT INTO t1(a) VALUES(5);603    SELECT name, type FROM pragma_table_xinfo('t1');604  } {a INTEGER b INT c TEXT d {} e INT}605}606 607# 2021-09-07 forum https://sqlite.org/forum/forumpost/699b44b3ee608#609reset_db610do_execsql_test gencol1-22.1 {611  CREATE TABLE t0(a PRIMARY KEY,b TEXT AS ('2') UNIQUE);612  INSERT INTO t0(a) VALUES(2);613  SELECT * FROM t0 AS x JOIN t0 AS y614   WHERE x.b='2'615     AND (y.a=2 OR (x.b LIKE '2*' AND y.a=x.b));616} {2 2 2 2}617 618 619# 2023-03-02 dbsqlfuzz 65f5eb57f8859344d5f1f33e08c77ee12960ed83620#621set typelist {ANY INT REAL BLOB TEXT {}}622set cnt 0623foreach t1 $typelist {624  foreach t2 $typelist {625    incr cnt626    db eval "627      DROP TABLE IF EXISTS t1;628      CREATE TABLE t1(629        x $t1,630        a $t2 AS (x) VIRTUAL,631        b BLOB AS (x) VIRTUAL632      );633      CREATE INDEX x2 ON t1(a);634      INSERT INTO t1(x) VALUES(NULL),('1'),(2),(3.5),('xyz');635    "636    set x1 [lsort [db eval {SELECT typeof(b) FROM t1}]]637    do_test gencol1-23.1.$cnt {638      lsort [db eval {SELECT typeof(b) FROM t1 INDEXED BY x2}]639    } $x1640  }641}642do_execsql_test gencol1-23.2 {643  DROP TABLE t1;644  CREATE TABLE t1(645    x,646    a INT AS (x) VIRTUAL,647    b BLOB AS (x) VIRTUAL648  );649  CREATE INDEX x2 ON t1(a);650  INSERT INTO t1(x) VALUES(NULL),('1'),('xyz'),(2),(3.5);651  SELECT quote(a) FROM t1 INDEXED BY x2;652} {NULL 1 2 3.5 'xyz'}653do_execsql_test gencol1-23.3 {654  EXPLAIN SELECT a FROM t1 INDEXED BY x2;655} {~/Column 0/}656#    ^^^^^^^^---- verfies that x2 acts like a covering index657do_execsql_test gencol1-23.4 {658  EXPLAIN SELECT b FROM t1 INDEXED BY x2;659} {/Column 0/}660#  ^^^^^^^^^^--- Must reference the original table in this case because661# of the different datatype on column b.662 663# 2023-03-07 https://sqlite.org/forum/forumpost/b312e075b5664#665do_catchsql_test gencol1-23.5 {666  CREATE TABLE v0(c1 INT, c2 AS (RAISE(IGNORE)));667} {1 {RAISE() may only be used within a trigger-program}}668 669finish_test670