gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/gencol1.test (about) 1 # 2019-10-31 2 # 3 # The author disclaims copyright to this source code. In place of 4 # 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 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # ticket 830277d9db6c3ba1 on 2019-10-31 19 do_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 27 foreach {tn schema} { 28 1 { 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 } 38 2 { 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 ANY 46 ); 47 } 48 3 { 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 ANY 56 ); 57 } 58 4 { 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 ANY 66 ); 67 } 68 5 { 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 } 79 6 { 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 } 91 7 { 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 $schema 107 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 b9befa4b83a660cc 149 db close 150 sqlite3 db :memory: 151 do_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 |} 158 do_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 c28a01da72f8957c 163 db close 164 sqlite3 db :memory: 165 do_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 } {} 175 do_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. 180 db close 181 sqlite3 db :memory: 182 do_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 UNIQUE 191 ) 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 208 do_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://www.sqlite.org/src/info/2399f5986134f79c 215 # 2019-12-27 ticket https://www.sqlite.org/src/info/5fbc159eeb092130 216 # 2019-12-27 ticket https://www.sqlite.org/src/info/37823501c68a09f9 217 # 218 # All of the above tickets deal with NOT NULL ON CONFLICT REPLACE 219 # constraints on tables that have generated columns. 220 # 221 reset_db 222 do_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} 227 do_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} 233 do_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} 239 do_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} 247 do_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} 256 do_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} 264 do_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} 273 do_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} 278 do_execsql_test gencol1-7.41 { 279 SELECT * FROM t3 WHERE b=123; 280 } {123 123} 281 do_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} 286 do_execsql_test gencol1-7.51 { 287 SELECT * FROM t4 WHERE b=1234; 288 } {123 1234} 289 290 # 2019-11-06 ticket 4fc08501f4e56692 291 do_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} 301 do_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 logic 312 # reported by Yongheng Chen and Rui Zhong 313 reset_db 314 do_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} 322 do_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 that 331 # does already exist. 332 # 333 sqlite3 db :memory: 334 do_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 mrigger 341 # 342 sqlite3 db :memory: 343 do_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 } 353 do_catchsql_test gencol1-11.20 { 354 INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0) 355 } {1 {FOREIGN KEY constraint failed}} 356 do_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 } 366 do_catchsql_test gencol1-11.40 { 367 INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0) 368 } {1 {FOREIGN KEY constraint failed}} 369 do_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 } 379 do_catchsql_test gencol1-11.60 { 380 INSERT OR REPLACE INTO t0(c0, c1) VALUES (2, 1), (1, 0) 381 } {1 {FOREIGN KEY constraint failed}} 382 do_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 } 392 do_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 bd8c280671ba44a7 397 # With generated columns, the sqlite3ExprGetColumnOfTable() routine might 398 # generate a code sequence that does not end with OP_Column. So check to 399 # make sure that the last instruction generated is an OP_column prior to 400 # applying the OPFLAG_TYPEOFARG optimization to NOT NULL checks in the 401 # PRAGMA integrity_check code. 402 # 403 sqlite3 db :memory: 404 do_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 Chen 411 # Ensure that the SrcList_item.colUsed field is set correctly when a 412 # generated column appears in the USING clause of a join. 413 # 414 do_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} 419 do_execsql_test gencol1-13.11 { 420 SELECT 123 FROM t1 JOIN t1 USING (x); 421 } {123} 422 do_execsql_test gencol1-13.11 { 423 SELECT 456 FROM t1 JOIN t1 USING (x,x); 424 } {456} 425 do_execsql_test gencol1-13.20 { 426 CREATE INDEX t1y ON t1(y); 427 SELECT y FROM t1 JOIN t1 USING (y,y); 428 } {11} 429 do_execsql_test gencol1-13.21 { 430 CREATE INDEX t1x ON t1(x); 431 SELECT 123 FROM t1 JOIN t1 USING (x); 432 } {123} 433 do_execsql_test gencol1-13.22 { 434 SELECT 456 FROM t1 JOIN t1 USING (x,x); 435 } {456} 436 437 # 2019-12-14 ticket b439bfcfb7deedc6 438 # 439 sqlite3 db :memory: 440 do_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} 446 do_catchsql_test gencol1-14.10 { 447 INSERT INTO t0 VALUES(2); 448 } {1 {UNIQUE constraint failed: t0.c1}} 449 450 # 2019-12-14 gramfuzz1 find 451 # The schema is malformed in that it has a subquery on a generated 452 # column expression. This will be loaded if writable_schema=ON. SQLite 453 # must not use such an expression during code generation as the code generator 454 # will add bits of content to the expression tree that might be allocated 455 # from lookaside. But the schema is not tied to a particular database 456 # connection, so the use of lookaside memory is prohibited. The fix 457 # is to change the generated column expression to NULL before adding it 458 # to the schema. 459 # 460 reset_db 461 do_test gencol1-15.10 { 462 sqlite3 db {} 463 db deserialize [decode_hexdb { 464 | size 8192 pagesize 4096 filename c27.db 465 | page 1 offset 0 466 | 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.CREA 474 | 4048: 54 45 20 54 41 42 4c 45 20 74 31 28 61 20 49 4e TE TABLE t1(a IN 475 | 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 4096 478 | 0: 0d 00 00 00 00 10 00 00 00 00 00 00 00 00 00 00 ................ 479 | end c27.db 480 }]} {} 481 do_execsql_test gencol1-15.20 { 482 PRAGMA writable_schema=ON; 483 REPLACE INTO t1 VALUES(9); 484 SELECT a, quote(b) FROM t1 485 } {9 NULL} 486 487 # 2019-12-16 ticket 3b84b42943644d6f 488 # When a table is the right table of a LEFT JOIN and the ON clause is 489 # false, make sure any generated columns evaluate to NULL. 490 reset_db 491 do_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 {} {}} 497 do_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 {} {}} 502 do_execsql_test gencol1-16.30 { 503 INSERT INTO t1(c1) VALUES(1),(NULL); 504 SELECT * FROM t1; 505 } {1 0 {} 1} 506 do_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 e0a8120553f4b082 511 # Generated columns with REAL affinity need to have an OP_RealAffinity 512 # opcode applied, even when the column value is extracted from an index. 513 # 514 reset_db 515 do_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 ''} 520 do_execsql_test gencol1-17.20 { 521 SELECT *, (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0) FROM t0; 522 } {1.0 {} 0} 523 do_execsql_test gencol1-17.30 { 524 SELECT * FROM t0 WHERE (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0); 525 } {} 526 do_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} 531 do_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 ec8abb025e78f40c 537 # An index on a virtual column with a constant value (why would anybody 538 # ever do such a thing?) can cause problems for a one-pass DELETE. 539 # 540 reset_db 541 do_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 {}} 546 do_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 de4b04149b9fdeae 552 # 553 reset_db 554 do_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/73b9a8ccfb 565 # 566 do_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 INTEGER 573 ); 574 -- Add some data 575 INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0); 576 -- Check that each column is 0 like I expect 577 SELECT * FROM tab; 578 } {2001-01-01 0 0 0 {}} 579 do_execsql_test gencol1-20.2 { 580 -- Do an UPSERT on the b column 581 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 5 585 SELECT * FROM tab; 586 } {2001-01-01 0 0 5 {}} 587 588 # 2021-07-30 forum https://sqlite.org/forum/forumpost/ff3ffe09251c105b?t=h 589 # 590 ifcapable vtab { 591 reset_db 592 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 Generated 598 Always 599 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/699b44b3ee 608 # 609 reset_db 610 do_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 y 614 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 finish_test