gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/altertab.test (about) 1 # 2018 August 24 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 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix altertab 16 17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18 ifcapable !altertable { 19 finish_test 20 return 21 } 22 23 do_execsql_test 1.0 { 24 CREATE TABLE t1(a, b, CHECK(t1.a != t1.b)); 25 26 CREATE TABLE t2(a, b); 27 CREATE INDEX t2expr ON t2(a) WHERE t2.b>0; 28 } 29 30 do_execsql_test 1.1 { 31 SELECT sql FROM sqlite_master 32 } { 33 {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))} 34 {CREATE TABLE t2(a, b)} 35 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0} 36 } 37 38 do_execsql_test 1.2 { 39 ALTER TABLE t1 RENAME TO t1new; 40 } 41 42 do_execsql_test 1.3 { 43 CREATE TABLE t3(c, d); 44 ALTER TABLE t3 RENAME TO t3new; 45 DROP TABLE t3new; 46 } 47 48 do_execsql_test 1.4 { 49 SELECT sql FROM sqlite_master 50 } { 51 {CREATE TABLE "t1new"(a, b, CHECK("t1new".a != "t1new".b))} 52 {CREATE TABLE t2(a, b)} 53 {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0} 54 } 55 56 57 do_execsql_test 1.3 { 58 ALTER TABLE t2 RENAME TO t2new; 59 } 60 do_execsql_test 1.4 { 61 SELECT sql FROM sqlite_master 62 } { 63 {CREATE TABLE "t1new"(a, b, CHECK("t1new".a != "t1new".b))} 64 {CREATE TABLE "t2new"(a, b)} 65 {CREATE INDEX t2expr ON "t2new"(a) WHERE "t2new".b>0} 66 } 67 68 69 #------------------------------------------------------------------------- 70 reset_db 71 ifcapable vtab { 72 register_echo_module db 73 74 do_execsql_test 2.0 { 75 CREATE TABLE abc(a, b, c); 76 INSERT INTO abc VALUES(1, 2, 3); 77 CREATE VIRTUAL TABLE eee USING echo('abc'); 78 SELECT * FROM eee; 79 } {1 2 3} 80 81 do_execsql_test 2.1 { 82 ALTER TABLE eee RENAME TO fff; 83 SELECT * FROM fff; 84 } {1 2 3} 85 86 db close 87 sqlite3 db test.db 88 89 do_catchsql_test 2.2 { 90 ALTER TABLE fff RENAME TO ggg; 91 } {1 {no such module: echo}} 92 } 93 94 #------------------------------------------------------------------------- 95 reset_db 96 97 do_execsql_test 3.0 { 98 CREATE TABLE txx(a, b, c); 99 INSERT INTO txx VALUES(1, 2, 3); 100 CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx; 101 CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one; 102 CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx; 103 } 104 105 do_execsql_test 3.1.1 { 106 SELECT * FROM vvv; 107 } {1 2 3} 108 do_execsql_test 3.1.2 { 109 ALTER TABLE txx RENAME TO "t xx"; 110 SELECT * FROM vvv; 111 } {1 2 3} 112 do_execsql_test 3.1.3 { 113 SELECT sql FROM sqlite_master WHERE name='vvv'; 114 } {{CREATE VIEW vvv AS SELECT main."t xx".a, "t xx".b, c FROM "t xx"}} 115 116 117 do_execsql_test 3.2.1 { 118 SELECT * FROM uuu; 119 } {1 2 3} 120 do_execsql_test 3.2.2 { 121 SELECT sql FROM sqlite_master WHERE name='uuu';; 122 } {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM "t xx" AS one}} 123 124 do_execsql_test 3.3.1 { 125 SELECT * FROM ttt; 126 } {1 2 2 1} 127 do_execsql_test 3.3.2 { 128 SELECT sql FROM sqlite_temp_master WHERE name='ttt'; 129 } {{CREATE VIEW ttt AS SELECT main."t xx".a, "t xx".b, one.b, main.one.a FROM "t xx" AS one, "t xx"}} 130 131 #------------------------------------------------------------------------- 132 reset_db 133 do_execsql_test 4.0 { 134 CREATE table t1(x, y); 135 CREATE table t2(a, b); 136 137 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 138 SELECT t1.x, * FROM t1, t2; 139 INSERT INTO t2 VALUES(new.x, new.y); 140 END; 141 } 142 143 do_execsql_test 4.1 { 144 INSERT INTO t1 VALUES(1, 1); 145 ALTER TABLE t1 RENAME TO t11; 146 INSERT INTO t11 VALUES(2, 2); 147 ALTER TABLE t2 RENAME TO t22; 148 INSERT INTO t11 VALUES(3, 3); 149 } 150 151 proc squish {a} { 152 string trim [regsub -all {[[:space:]][[:space:]]*} $a { }] 153 } 154 db func squish squish 155 do_test 4.2 { 156 execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' } 157 } [list [squish { 158 CREATE TRIGGER tr1 AFTER INSERT ON "t11" BEGIN 159 SELECT "t11".x, * FROM "t11", "t22"; 160 INSERT INTO "t22" VALUES(new.x, new.y); 161 END 162 }]] 163 164 #------------------------------------------------------------------------- 165 reset_db 166 do_execsql_test 5.0 { 167 CREATE TABLE t9(a, b, c); 168 CREATE TABLE t10(a, b, c); 169 CREATE TEMP TABLE t9(a, b, c); 170 171 CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN 172 INSERT INTO t10 VALUES(new.a, new.b, new.c); 173 END; 174 175 INSERT INTO temp.t9 VALUES(1, 2, 3); 176 SELECT * FROM t10; 177 } {1 2 3} 178 179 do_execsql_test 5.1 { 180 ALTER TABLE temp.t9 RENAME TO 't1234567890' 181 } 182 183 do_execsql_test 5.2 { 184 CREATE TABLE t1(a, b); 185 CREATE TABLE t2(a, b); 186 INSERT INTO t1 VALUES(1, 2); 187 INSERT INTO t2 VALUES(3, 4); 188 CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; 189 SELECT * FROM v; 190 } {1 2 3 4} 191 192 do_catchsql_test 5.3 { 193 ALTER TABLE t2 RENAME TO one; 194 } {1 {error in view v after rename: ambiguous column name: one.a}} 195 196 do_execsql_test 5.4 { 197 SELECT * FROM v 198 } {1 2 3 4} 199 200 do_execsql_test 5.5 { 201 DROP VIEW v; 202 CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2; 203 SELECT * FROM vv; 204 } {1 2 3 4} 205 206 do_catchsql_test 5.6 { 207 ALTER TABLE t2 RENAME TO one; 208 } {1 {error in view vv after rename: ambiguous column name: one.a}} 209 210 #------------------------------------------------------------------------- 211 212 ifcapable vtab { 213 register_tcl_module db 214 proc tcl_command {method args} { 215 switch -- $method { 216 xConnect { 217 return "CREATE TABLE t1(a, b, c)" 218 } 219 } 220 return {} 221 } 222 223 do_execsql_test 6.0 { 224 CREATE VIRTUAL TABLE x1 USING tcl(tcl_command); 225 } 226 227 do_execsql_test 6.1 { 228 ALTER TABLE x1 RENAME TO x2; 229 SELECT sql FROM sqlite_master WHERE name = 'x2' 230 } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}} 231 232 do_execsql_test 7.1 { 233 CREATE TABLE ddd(db, sql, zOld, zNew, bTemp); 234 INSERT INTO ddd VALUES( 235 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0 236 ), ( 237 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0 238 ), ( 239 'main', NULL, 'ddd', 'eee', 0 240 ); 241 } {} 242 243 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 244 do_execsql_test 7.2 { 245 SELECT 246 sqlite_rename_table(db, 0, 0, sql, zOld, zNew, bTemp) 247 FROM ddd; 248 } {{} {} {}} 249 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db 250 } 251 252 #------------------------------------------------------------------------- 253 # 254 reset_db 255 forcedelete test.db2 256 do_execsql_test 8.1 { 257 ATTACH 'test.db2' AS aux; 258 PRAGMA foreign_keys = on; 259 CREATE TABLE aux.p1(a INTEGER PRIMARY KEY, b); 260 CREATE TABLE aux.c1(x INTEGER PRIMARY KEY, y REFERENCES p1(a)); 261 INSERT INTO aux.p1 VALUES(1, 1); 262 INSERT INTO aux.p1 VALUES(2, 2); 263 INSERT INTO aux.c1 VALUES(NULL, 2); 264 CREATE TABLE aux.c2(x INTEGER PRIMARY KEY, y REFERENCES c1(a)); 265 } 266 267 do_execsql_test 8.2 { 268 ALTER TABLE aux.p1 RENAME TO ppp; 269 } 270 271 do_execsql_test 8.2 { 272 INSERT INTO aux.c1 VALUES(NULL, 1); 273 SELECT sql FROM aux.sqlite_master WHERE name = 'c1'; 274 } {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}} 275 276 reset_db 277 do_execsql_test 9.0 { 278 CREATE TABLE t1(a, b, c); 279 CREATE VIEW v1 AS SELECT * FROM t2; 280 } 281 do_catchsql_test 9.1 { 282 ALTER TABLE t1 RENAME TO t3; 283 } {1 {error in view v1: no such table: main.t2}} 284 do_execsql_test 9.2 { 285 DROP VIEW v1; 286 CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN 287 INSERT INTO t2 VALUES(new.a); 288 END; 289 } 290 do_catchsql_test 9.3 { 291 ALTER TABLE t1 RENAME TO t3; 292 } {1 {error in trigger tr: no such table: main.t2}} 293 294 forcedelete test.db2 295 do_execsql_test 9.4 { 296 DROP TRIGGER tr; 297 298 ATTACH 'test.db2' AS aux; 299 CREATE TRIGGER tr AFTER INSERT ON t1 WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END; 300 301 CREATE TABLE aux.t1(x); 302 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END; 303 } 304 do_execsql_test 9.5 { 305 ALTER TABLE main.t1 RENAME TO t3; 306 } 307 do_execsql_test 9.6 { 308 SELECT sql FROM sqlite_temp_master; 309 SELECT sql FROM sqlite_master WHERE type='trigger'; 310 } { 311 {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END} 312 {CREATE TRIGGER tr AFTER INSERT ON "t3" WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END} 313 } 314 315 #------------------------------------------------------------------------- 316 reset_db 317 ifcapable fts5 { 318 do_execsql_test 10.0 { 319 CREATE VIRTUAL TABLE fff USING fts5(x, y, z); 320 } 321 322 do_execsql_test 10.1 { 323 BEGIN; 324 INSERT INTO fff VALUES('a', 'b', 'c'); 325 ALTER TABLE fff RENAME TO ggg; 326 COMMIT; 327 } 328 329 do_execsql_test 10.2 { 330 SELECT * FROM ggg; 331 } {a b c} 332 } 333 334 #------------------------------------------------------------------------- 335 reset_db 336 forcedelete test.db2 337 db func trigger trigger 338 set ::trigger [list] 339 proc trigger {args} { 340 lappend ::trigger $args 341 } 342 do_execsql_test 11.0 { 343 ATTACH 'test.db2' AS aux; 344 CREATE TABLE aux.t1(a, b, c); 345 CREATE TABLE main.t1(a, b, c); 346 CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN 347 SELECT trigger(new.a, new.b, new.c); 348 END; 349 } 350 351 do_execsql_test 11.1 { 352 INSERT INTO main.t1 VALUES(1, 2, 3); 353 INSERT INTO aux.t1 VALUES(4, 5, 6); 354 } 355 do_test 11.2 { set ::trigger } {{4 5 6}} 356 357 do_execsql_test 11.3 { 358 SELECT name, tbl_name FROM sqlite_temp_master; 359 } {tr t1} 360 361 do_execsql_test 11.4 { 362 ALTER TABLE main.t1 RENAME TO t2; 363 SELECT name, tbl_name FROM sqlite_temp_master; 364 } {tr t1} 365 366 do_execsql_test 11.5 { 367 ALTER TABLE aux.t1 RENAME TO t2; 368 SELECT name, tbl_name FROM sqlite_temp_master; 369 } {tr t2} 370 371 do_execsql_test 11.6 { 372 INSERT INTO aux.t2 VALUES(7, 8, 9); 373 } 374 do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}} 375 376 #------------------------------------------------------------------------- 377 reset_db 378 do_execsql_test 12.0 { 379 CREATE TABLE t1(a); 380 CREATE TABLE t2(w); 381 CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN 382 INSERT INTO t1(a) VALUES(new.w); 383 END; 384 CREATE TEMP TABLE t2(x); 385 } 386 387 do_execsql_test 12.1 { 388 ALTER TABLE main.t2 RENAME TO t3; 389 } 390 391 do_execsql_test 12.2 { 392 INSERT INTO t3 VALUES('WWW'); 393 SELECT * FROM t1; 394 } {WWW} 395 396 397 #------------------------------------------------------------------------- 398 reset_db 399 do_execsql_test 13.0 { 400 CREATE TABLE t1(x, y); 401 CREATE TABLE t2(a, b); 402 CREATE TABLE log(c); 403 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 404 INSERT INTO log SELECT y FROM t1, t2; 405 END; 406 } 407 408 do_execsql_test 13.1 { 409 INSERT INTO t1 VALUES(1, 2); 410 } 411 412 do_catchsql_test 13.2 { 413 ALTER TABLE t2 RENAME b TO y; 414 } {1 {error in trigger tr1 after rename: ambiguous column name: y}} 415 416 #------------------------------------------------------------------------- 417 reset_db 418 419 ifcapable rtree { 420 do_execsql_test 14.0 { 421 CREATE VIRTUAL TABLE rt USING rtree(id, minx, maxx, miny, maxy); 422 423 CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB); 424 425 CREATE TRIGGER tr1 AFTER UPDATE OF "geom" ON "mytable" 426 WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN 427 DELETE FROM rt WHERE id = OLD."fid"; 428 END; 429 430 INSERT INTO mytable VALUES(1, X'abcd'); 431 } 432 433 do_execsql_test 14.1 { 434 UPDATE mytable SET geom = X'1234' 435 } 436 437 do_execsql_test 14.2 { 438 ALTER TABLE mytable RENAME TO mytable_renamed; 439 } 440 441 do_execsql_test 14.3 { 442 CREATE TRIGGER tr2 AFTER INSERT ON mytable_renamed BEGIN 443 DELETE FROM rt WHERE id=(SELECT min(id) FROM rt); 444 END; 445 } 446 447 do_execsql_test 14.4 { 448 ALTER TABLE mytable_renamed RENAME TO mytable2; 449 } 450 } 451 452 reset_db 453 do_execsql_test 14.5 { 454 CREATE TABLE t1(a, b, c); 455 CREATE VIEW v1 AS SELECT * FROM t1; 456 CREATE TRIGGER xyz AFTER INSERT ON t1 BEGIN 457 SELECT a, b FROM v1; 458 END; 459 } 460 do_execsql_test 14.6 { 461 ALTER TABLE t1 RENAME TO tt1; 462 } 463 464 #------------------------------------------------------------------------- 465 reset_db 466 do_execsql_test 15.0 { 467 CREATE TABLE t1(a integer NOT NULL PRIMARY KEY); 468 CREATE VIEW v1 AS SELECT a FROM t1; 469 CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN 470 UPDATE t1 SET a = NEW.a; 471 END; 472 CREATE TRIGGER tr2 INSTEAD OF INSERT ON v1 BEGIN 473 SELECT new.a; 474 END; 475 CREATE TABLE t2 (b); 476 } 477 478 do_execsql_test 15.1 { 479 INSERT INTO v1 VALUES(1); 480 ALTER TABLE t2 RENAME TO t3; 481 } 482 483 do_execsql_test 15.2 { 484 CREATE TABLE x(f1 integer NOT NULL); 485 CREATE VIEW y AS SELECT f1 AS f1 FROM x; 486 CREATE TRIGGER t INSTEAD OF UPDATE OF f1 ON y BEGIN 487 UPDATE x SET f1 = NEW.f1; 488 END; 489 CREATE TABLE z (f1 integer NOT NULL PRIMARY KEY); 490 ALTER TABLE z RENAME TO z2; 491 } 492 493 do_execsql_test 15.3 { 494 INSERT INTO x VALUES(1), (2), (3); 495 ALTER TABLE x RENAME f1 TO f2; 496 SELECT * FROM x; 497 } {1 2 3} 498 499 do_execsql_test 15.4 { 500 UPDATE y SET f1 = 'x' WHERE f1 = 1; 501 SELECT * FROM x; 502 } {x x x} 503 504 do_execsql_test 15.5 { 505 SELECT sql FROM sqlite_master WHERE name = 'y'; 506 } {{CREATE VIEW y AS SELECT f2 AS f1 FROM x}} 507 508 #------------------------------------------------------------------------- 509 # Test that it is not possible to rename a shadow table in DEFENSIVE mode. 510 # 511 ifcapable fts3 { 512 proc vtab_command {method args} { 513 switch -- $method { 514 xConnect { 515 if {[info exists ::vtab_connect_sql]} { 516 execsql $::vtab_connect_sql 517 } 518 return "CREATE TABLE t1(a, b, c)" 519 } 520 521 xBestIndex { 522 set clist [lindex $args 0] 523 if {[llength $clist]!=1} { error "unexpected constraint list" } 524 catch { array unset C } 525 array set C [lindex $clist 0] 526 if {$C(usable)} { 527 return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!" 528 } else { 529 return "cost 1000000 rows 0 idxnum 0 idxstr scan..." 530 } 531 } 532 } 533 534 return {} 535 } 536 537 register_tcl_module db 538 539 sqlite3_db_config db DEFENSIVE 1 540 541 do_execsql_test 16.0 { 542 CREATE VIRTUAL TABLE y1 USING fts3; 543 VACUUM; 544 } 545 546 do_catchsql_test 16.10 { 547 INSERT INTO y1_segments VALUES(1, X'1234567890'); 548 } {1 {table y1_segments may not be modified}} 549 550 do_catchsql_test 16.20 { 551 DROP TABLE y1_segments; 552 } {1 {table y1_segments may not be dropped}} 553 554 do_catchsql_test 16.20 { 555 ALTER TABLE y1_segments RENAME TO abc; 556 } {1 {table y1_segments may not be altered}} 557 sqlite3_db_config db DEFENSIVE 0 558 do_catchsql_test 16.22 { 559 ALTER TABLE y1_segments RENAME TO abc; 560 } {0 {}} 561 sqlite3_db_config db DEFENSIVE 1 562 do_catchsql_test 16.23 { 563 CREATE TABLE y1_segments AS SELECT * FROM abc; 564 } {1 {object name reserved for internal use: y1_segments}} 565 do_catchsql_test 16.24 { 566 CREATE VIEW y1_segments AS SELECT * FROM abc; 567 } {1 {object name reserved for internal use: y1_segments}} 568 sqlite3_db_config db DEFENSIVE 0 569 do_catchsql_test 16.25 { 570 ALTER TABLE abc RENAME TO y1_segments; 571 } {0 {}} 572 sqlite3_db_config db DEFENSIVE 1 573 574 do_execsql_test 16.30 { 575 ALTER TABLE y1 RENAME TO z1; 576 } 577 578 do_execsql_test 16.40 { 579 SELECT * FROM z1_segments; 580 } 581 } 582 583 #------------------------------------------------------------------------- 584 reset_db 585 do_execsql_test 17.0 { 586 CREATE TABLE sqlite1234 (id integer); 587 ALTER TABLE sqlite1234 RENAME TO User; 588 SELECT name, sql FROM sqlite_master WHERE sql IS NOT NULL; 589 } { 590 User {CREATE TABLE "User" (id integer)} 591 } 592 593 #------------------------------------------------------------------------- 594 reset_db 595 do_execsql_test 18.1.0 { 596 CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY(c0)) WITHOUT ROWID; 597 } 598 do_execsql_test 18.1.1 { 599 ALTER TABLE t0 RENAME COLUMN c0 TO c1; 600 } 601 do_execsql_test 18.1.2 { 602 SELECT sql FROM sqlite_master; 603 } {{CREATE TABLE t0 (c1 INTEGER, PRIMARY KEY(c1)) WITHOUT ROWID}} 604 605 reset_db 606 do_execsql_test 18.2.0 { 607 CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY(c0)); 608 } 609 do_execsql_test 18.2.1 { 610 ALTER TABLE t0 RENAME COLUMN c0 TO c1; 611 } 612 do_execsql_test 18.2.2 { 613 SELECT sql FROM sqlite_master; 614 } {{CREATE TABLE t0 (c1 INTEGER, PRIMARY KEY(c1))}} 615 616 # 2020-02-23 ticket f50af3e8a565776b 617 reset_db 618 do_execsql_test 19.100 { 619 CREATE TABLE t1(x); 620 CREATE VIEW t2 AS SELECT 1 FROM t1, (t1 AS a0, t1); 621 ALTER TABLE t1 RENAME TO t3; 622 SELECT sql FROM sqlite_master; 623 } {{CREATE TABLE "t3"(x)} {CREATE VIEW t2 AS SELECT 1 FROM "t3", ("t3" AS a0, "t3")}} 624 do_execsql_test 19.110 { 625 INSERT INTO t3(x) VALUES(123); 626 SELECT * FROM t2; 627 } {1} 628 do_execsql_test 19.120 { 629 INSERT INTO t3(x) VALUES('xyz'); 630 SELECT * FROM t2; 631 } {1 1 1 1 1 1 1 1} 632 633 # Ticket 4722bdab08cb14 634 reset_db 635 do_execsql_test 20.0 { 636 CREATE TABLE a(a); 637 CREATE VIEW b AS SELECT(SELECT *FROM c JOIN a USING(d, a, a, a) JOIN a) IN(); 638 } 639 do_execsql_test 20.1 { 640 ALTER TABLE a RENAME a TO e; 641 } {} 642 643 reset_db 644 do_execsql_test 21.0 { 645 CREATE TABLE a(b); 646 CREATE VIEW c AS 647 SELECT NULL INTERSECT 648 SELECT NULL ORDER BY 649 likelihood(NULL, (d, (SELECT c))); 650 } {} 651 do_catchsql_test 21.1 { 652 SELECT likelihood(NULL, (d, (SELECT c))); 653 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} 654 do_catchsql_test 21.2 { 655 SELECT * FROM c; 656 } {1 {1st ORDER BY term does not match any column in the result set}} 657 658 do_catchsql_test 21.3 { 659 ALTER TABLE a RENAME TO e; 660 } {1 {error in view c: 1st ORDER BY term does not match any column in the result set}} 661 662 # After forum thread https://sqlite.org/forum/forumpost/ddbe1c7efa 663 # Ensure that PRAGMA schema_version=N causes a full schema reload. 664 # 665 reset_db 666 do_execsql_test 22.0 { 667 CREATE TABLE t1(a INT, b TEXT NOT NULL); 668 INSERT INTO t1 VALUES(1,2),('a','b'); 669 BEGIN; 670 PRAGMA writable_schema=ON; 671 UPDATE sqlite_schema SET sql='CREATE TABLE t1(a INT, b TEXT)' WHERE name LIKE 't1'; 672 PRAGMA schema_version=1234; 673 COMMIT; 674 PRAGMA integrity_check; 675 } {ok} 676 do_execsql_test 22.1 { 677 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 78; 678 SELECT * FROM t1; 679 } {1 2 78 a b 78} 680 681 #------------------------------------------------------------------------- 682 reset_db 683 db collate compare64 compare64 684 685 do_execsql_test 23.1 { 686 CREATE TABLE gigo(a text); 687 CREATE TABLE idx(x text COLLATE compare64); 688 CREATE VIEW v1 AS SELECT * FROM idx WHERE x='abc'; 689 } 690 db close 691 sqlite3 db test.db 692 693 do_execsql_test 23.2 { 694 alter table gigo rename to ggiiggoo; 695 alter table idx rename to idx2; 696 } 697 698 do_execsql_test 23.3 { 699 SELECT sql FROM sqlite_master; 700 } { 701 {CREATE TABLE "ggiiggoo"(a text)} 702 {CREATE TABLE "idx2"(x text COLLATE compare64)} 703 {CREATE VIEW v1 AS SELECT * FROM "idx2" WHERE x='abc'} 704 } 705 706 do_execsql_test 23.4 { 707 ALTER TABLE idx2 RENAME x TO y; 708 SELECT sql FROM sqlite_master; 709 } { 710 {CREATE TABLE "ggiiggoo"(a text)} 711 {CREATE TABLE "idx2"(y text COLLATE compare64)} 712 {CREATE VIEW v1 AS SELECT * FROM "idx2" WHERE y='abc'} 713 } 714 715 #------------------------------------------------------------------------- 716 # 717 reset_db 718 do_execsql_test 24.1.0 { 719 CREATE TABLE t1(a, b); 720 CREATE TRIGGER AFTER INSERT ON t1 BEGIN 721 INSERT INTO nosuchtable VALUES(new.a) ON CONFLICT(a) DO NOTHING; 722 END; 723 } 724 do_catchsql_test 24.1.1 { 725 ALTER TABLE t1 RENAME TO t2; 726 } {1 {error in trigger AFTER: no such table: main.nosuchtable}} 727 728 reset_db 729 do_execsql_test 24.2.0 { 730 CREATE TABLE t1(a, b); 731 CREATE TRIGGER AFTER INSERT ON t1 BEGIN 732 INSERT INTO v1 VALUES(new.a) ON CONFLICT(a) DO NOTHING; 733 END; 734 CREATE VIEW v1 AS SELECT * FROM nosuchtable; 735 } 736 do_catchsql_test 24.2.1 { 737 ALTER TABLE t1 RENAME TO t2; 738 } {1 {error in trigger AFTER: no such table: main.nosuchtable}} 739 740 #-------------------------------------------------------------------------- 741 # 742 reset_db 743 do_execsql_test 25.1 { 744 CREATE TABLE xx(x); 745 CREATE VIEW v3(b) AS WITH b AS (SELECT b FROM (SELECT * FROM t2)) VALUES(1); 746 } 747 748 ifcapable json1&&vtab { 749 do_catchsql_test 25.2 { 750 ALTER TABLE json_each RENAME TO t4; 751 } {1 {table json_each may not be altered}} 752 } 753 754 # 2021-05-01 dbsqlfuzz bc17a306a09329bba0ecc61547077f6178bcf321 755 # Remove a NEVER() inserted on 2019-12-09 that is reachable after all. 756 # 757 reset_db 758 do_execsql_test 26.1 { 759 CREATE TABLE t1(k,v); 760 CREATE TABLE t2_a(k,v); 761 CREATE VIEW t2 AS SELECT * FROM t2_a; 762 CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN 763 UPDATE t1 764 SET (k,v)=((WITH cte1(a) AS (SELECT 1 FROM t2) SELECT t2.k FROM t2, cte1),1); 765 END; 766 ALTER TABLE t1 RENAME TO t1x; 767 INSERT INTO t2_a VALUES(2,3); 768 INSERT INTO t1x VALUES(98,99); 769 SELECT * FROM t1x; 770 } {2 1} 771 772 #------------------------------------------------------------------------- 773 reset_db 774 775 do_execsql_test 27.1 { 776 777 create table t_sa ( 778 c_muyat INTEGER NOT NULL, 779 c_d4u TEXT 780 ); 781 782 create table t2 ( abc ); 783 784 CREATE TRIGGER trig AFTER DELETE ON t_sa 785 BEGIN 786 DELETE FROM t_sa WHERE ( 787 SELECT 123 FROM t2 788 WINDOW oamat7fzf AS ( PARTITION BY t_sa.c_d4u ) 789 ); 790 END; 791 } 792 793 do_execsql_test 27.2 { 794 alter table t_sa rename column c_muyat to c_dg; 795 } 796 797 #------------------------------------------------------------------------- 798 reset_db 799 do_execsql_test 29.1 { 800 CREATE TABLE t1(a, b, c); 801 INSERT INTO t1 VALUES('a', 'b', 'c'); 802 803 CREATE VIEW v0 AS 804 WITH p AS ( SELECT 1 FROM t1 ), 805 g AS ( SELECT 1 FROM p, t1 ) 806 SELECT 1 FROM g; 807 } 808 809 do_execsql_test 29.2 { 810 SELECT * FROM v0 811 } 1 812 813 do_execsql_test 29.2 { 814 ALTER TABLE t1 RENAME TO t2 815 } 816 817 do_execsql_test 29.3 { 818 SELECT sql FROM sqlite_schema WHERE name='v0' 819 } {{CREATE VIEW v0 AS 820 WITH p AS ( SELECT 1 FROM "t2" ), 821 g AS ( SELECT 1 FROM p, "t2" ) 822 SELECT 1 FROM g}} 823 824 do_execsql_test 29.4 { 825 CREATE VIEW v2 AS 826 WITH p AS ( SELECT 1 FROM t2 ), 827 g AS ( SELECT 1 FROM ( 828 WITH i AS (SELECT 1 FROM p, t2) 829 SELECT * FROM i 830 ) 831 ) 832 SELECT 1 FROM g; 833 } 834 835 do_execsql_test 29.4 { 836 SELECT * FROM v2; 837 } 1 838 839 do_execsql_test 29.5 { 840 ALTER TABLE t2 RENAME TO t3; 841 } 842 843 do_execsql_test 29.5 { 844 SELECT sql FROM sqlite_schema WHERE name='v2' 845 } {{CREATE VIEW v2 AS 846 WITH p AS ( SELECT 1 FROM "t3" ), 847 g AS ( SELECT 1 FROM ( 848 WITH i AS (SELECT 1 FROM p, "t3") 849 SELECT * FROM i 850 ) 851 ) 852 SELECT 1 FROM g}} 853 854 855 #------------------------------------------------------------------------- 856 reset_db 857 do_execsql_test 28.1 { 858 CREATE TABLE t1(a); 859 CREATE TABLE t2(b,c); 860 CREATE TABLE t4(b,c); 861 INSERT INTO t2 VALUES(1,2),(1,3),(2,5); 862 INSERT INTO t4 VALUES(1,2),(1,3),(2,5); 863 864 CREATE VIEW v3 AS 865 WITH RECURSIVE t3(x,y,z) AS ( 866 SELECT b,c,NULL FROM t4 867 UNION 868 SELECT x,y,NULL FROM t3, t2 869 ) 870 SELECT * FROM t3 AS xyz; 871 } 872 873 do_execsql_test 28.2 { 874 SELECT * FROM v3 875 } { 876 1 2 {} 1 3 {} 2 5 {} 877 } 878 879 do_execsql_test 28.3 { 880 ALTER TABLE t1 RENAME a TO a2; -- fails in v3 881 } 882 883 do_execsql_test 28.4 { 884 ALTER TABLE t2 RENAME TO t5; 885 } 886 887 do_execsql_test 28.5 { 888 SELECT sql FROM sqlite_schema WHERE name='v3' 889 } {{CREATE VIEW v3 AS 890 WITH RECURSIVE t3(x,y,z) AS ( 891 SELECT b,c,NULL FROM t4 892 UNION 893 SELECT x,y,NULL FROM t3, "t5" 894 ) 895 SELECT * FROM t3 AS xyz}} 896 897 #------------------------------------------------------------------------- 898 reset_db 899 do_execsql_test 30.0 { 900 CREATE TABLE t1(a,b,c,d,e,f); 901 CREATE TABLE t2(a,b,c); 902 CREATE INDEX t1abc ON t1(a,b,c+d+e); 903 CREATE VIEW v1(x,y) AS 904 SELECT t1.b,t2.b FROM t1,t2 WHERE t1.a=t2.a 905 GROUP BY 1 HAVING t2.c NOT NULL LIMIT 10; 906 CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN 'no' NOT NULL BEGIN 907 INSERT INTO t2(a,a,b,c) VALUES(new.b,new.a,new.c-7); 908 WITH c1(x) AS ( 909 VALUES(0) 910 UNION ALL 911 SELECT current_time+x FROM c1 WHERE x 912 UNION ALL 913 SELECT 1+x FROM c1 WHERE x<1 914 ), c2(x) AS (VALUES(0),(1)) 915 SELECT * FROM c1 AS x1, c2 AS x2, ( 916 SELECT x+1 FROM c1 WHERE x IS NOT TRUE 917 UNION ALL 918 SELECT 1+x FROM c1 WHERE 1<x 919 ) AS x3, c2 x5; 920 END; 921 } 922 923 do_execsql_test 30.1 { 924 ALTER TABLE t1 RENAME TO t1x; 925 } 926 927 do_execsql_test 30.2 { 928 SELECT sql FROM sqlite_schema ORDER BY rowid 929 } { 930 {CREATE TABLE "t1x"(a,b,c,d,e,f)} 931 {CREATE TABLE t2(a,b,c)} 932 {CREATE INDEX t1abc ON "t1x"(a,b,c+d+e)} 933 {CREATE VIEW v1(x,y) AS 934 SELECT "t1x".b,t2.b FROM "t1x",t2 WHERE "t1x".a=t2.a 935 GROUP BY 1 HAVING t2.c NOT NULL LIMIT 10} 936 {CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN 'no' NOT NULL BEGIN 937 INSERT INTO t2(a,a,b,c) VALUES(new.b,new.a,new.c-7); 938 WITH c1(x) AS ( 939 VALUES(0) 940 UNION ALL 941 SELECT current_time+x FROM c1 WHERE x 942 UNION ALL 943 SELECT 1+x FROM c1 WHERE x<1 944 ), c2(x) AS (VALUES(0),(1)) 945 SELECT * FROM c1 AS x1, c2 AS x2, ( 946 SELECT x+1 FROM c1 WHERE x IS NOT TRUE 947 UNION ALL 948 SELECT 1+x FROM c1 WHERE 1<x 949 ) AS x3, c2 x5; 950 END} 951 } 952 953 #------------------------------------------------------------------------- 954 reset_db 955 do_execsql_test 31.0 { 956 CREATE TABLE t1(q); 957 CREATE VIEW vvv AS WITH x AS (WITH y AS (SELECT * FROM x) SELECT 1) SELECT 1; 958 } 959 960 do_execsql_test 31.1 { 961 SELECT * FROM vvv; 962 } {1} 963 964 do_execsql_test 31.2 { 965 ALTER TABLE t1 RENAME TO t1x; 966 } 967 968 do_execsql_test 31.3 { 969 ALTER TABLE t1x RENAME q TO x; 970 } 971 972 # 2021-07-02 OSSFuzz https://oss-fuzz.com/testcase-detail/5517690440646656 973 # Bad assert() statement 974 # 975 reset_db 976 do_catchsql_test 32.0 { 977 CREATE TABLE t1(x); 978 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN 979 UPDATE t1 SET x=x FROM (SELECT*); 980 END; 981 ALTER TABLE t1 RENAME TO x; 982 } {1 {error in trigger r1: no tables specified}} 983 984 finish_test