AryaWu/sqlite
0
1# 2018 August 242#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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix altertab16 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19 finish_test20 return21}22 23do_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 30do_execsql_test 1.1 {31 SELECT sql FROM sqlite_master32} {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 38do_execsql_test 1.2 {39 ALTER TABLE t1 RENAME TO t1new;40}41 42do_execsql_test 1.3 {43 CREATE TABLE t3(c, d);44 ALTER TABLE t3 RENAME TO t3new;45 DROP TABLE t3new;46}47 48do_execsql_test 1.4 {49 SELECT sql FROM sqlite_master50} {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 57do_execsql_test 1.3 {58 ALTER TABLE t2 RENAME TO t2new;59}60do_execsql_test 1.4 {61 SELECT sql FROM sqlite_master62} {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#-------------------------------------------------------------------------70reset_db71ifcapable vtab {72 register_echo_module db73 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 close87 sqlite3 db test.db88 89 do_catchsql_test 2.2 {90 ALTER TABLE fff RENAME TO ggg;91 } {1 {no such module: echo}}92}93 94#-------------------------------------------------------------------------95reset_db96 97do_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 105do_execsql_test 3.1.1 {106 SELECT * FROM vvv;107} {1 2 3}108do_execsql_test 3.1.2 {109 ALTER TABLE txx RENAME TO "t xx";110 SELECT * FROM vvv;111} {1 2 3}112do_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 117do_execsql_test 3.2.1 {118 SELECT * FROM uuu;119} {1 2 3}120do_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 124do_execsql_test 3.3.1 {125 SELECT * FROM ttt;126} {1 2 2 1}127do_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#-------------------------------------------------------------------------132reset_db133do_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 BEGIN138 SELECT t1.x, * FROM t1, t2;139 INSERT INTO t2 VALUES(new.x, new.y);140 END;141}142 143do_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 151proc squish {a} {152 string trim [regsub -all {[[:space:]][[:space:]]*} $a { }]153}154db func squish squish155do_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" BEGIN159 SELECT "t11".x, * FROM "t11", "t22";160 INSERT INTO "t22" VALUES(new.x, new.y);161 END162}]]163 164#-------------------------------------------------------------------------165reset_db166do_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 BEGIN172 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 179do_execsql_test 5.1 {180 ALTER TABLE temp.t9 RENAME TO 't1234567890'181}182 183do_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 192do_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 196do_execsql_test 5.4 {197 SELECT * FROM v198} {1 2 3 4}199 200do_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 206do_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 212ifcapable vtab {213 register_tcl_module db214 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, 0236 ), (237 'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0238 ), (239 'main', NULL, 'ddd', 'eee', 0240 );241 } {}242 243 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db244 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 db250}251 252#-------------------------------------------------------------------------253#254reset_db255forcedelete test.db2256do_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 267do_execsql_test 8.2 {268 ALTER TABLE aux.p1 RENAME TO ppp;269}270 271do_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 276reset_db277do_execsql_test 9.0 {278 CREATE TABLE t1(a, b, c);279 CREATE VIEW v1 AS SELECT * FROM t2;280}281do_catchsql_test 9.1 {282 ALTER TABLE t1 RENAME TO t3;283} {1 {error in view v1: no such table: main.t2}}284do_execsql_test 9.2 {285 DROP VIEW v1;286 CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN287 INSERT INTO t2 VALUES(new.a);288 END;289}290do_catchsql_test 9.3 {291 ALTER TABLE t1 RENAME TO t3;292} {1 {error in trigger tr: no such table: main.t2}}293 294forcedelete test.db2295do_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}304do_execsql_test 9.5 {305 ALTER TABLE main.t1 RENAME TO t3;306}307do_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#-------------------------------------------------------------------------316reset_db317ifcapable 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#-------------------------------------------------------------------------335reset_db336forcedelete test.db2337db func trigger trigger338set ::trigger [list]339proc trigger {args} {340 lappend ::trigger $args341}342do_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 BEGIN347 SELECT trigger(new.a, new.b, new.c);348 END;349}350 351do_execsql_test 11.1 {352 INSERT INTO main.t1 VALUES(1, 2, 3);353 INSERT INTO aux.t1 VALUES(4, 5, 6);354}355do_test 11.2 { set ::trigger } {{4 5 6}}356 357do_execsql_test 11.3 {358 SELECT name, tbl_name FROM sqlite_temp_master;359} {tr t1}360 361do_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 366do_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 371do_execsql_test 11.6 {372 INSERT INTO aux.t2 VALUES(7, 8, 9);373}374do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}}375 376#-------------------------------------------------------------------------377reset_db378do_execsql_test 12.0 {379 CREATE TABLE t1(a);380 CREATE TABLE t2(w);381 CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN382 INSERT INTO t1(a) VALUES(new.w);383 END;384 CREATE TEMP TABLE t2(x);385}386 387do_execsql_test 12.1 {388 ALTER TABLE main.t2 RENAME TO t3;389}390 391do_execsql_test 12.2 {392 INSERT INTO t3 VALUES('WWW');393 SELECT * FROM t1;394} {WWW}395 396 397#-------------------------------------------------------------------------398reset_db399do_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 BEGIN404 INSERT INTO log SELECT y FROM t1, t2;405 END;406}407 408do_execsql_test 13.1 {409 INSERT INTO t1 VALUES(1, 2);410}411 412do_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#-------------------------------------------------------------------------417reset_db418 419ifcapable 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 BEGIN443 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 452reset_db453do_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 BEGIN457 SELECT a, b FROM v1;458 END;459}460do_execsql_test 14.6 {461 ALTER TABLE t1 RENAME TO tt1;462}463 464#-------------------------------------------------------------------------465reset_db466do_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 478do_execsql_test 15.1 {479 INSERT INTO v1 VALUES(1);480 ALTER TABLE t2 RENAME TO t3;481}482 483do_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 493do_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 499do_execsql_test 15.4 {500 UPDATE y SET f1 = 'x' WHERE f1 = 1;501 SELECT * FROM x;502} {x x x}503 504do_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#511ifcapable fts3 {512 proc vtab_command {method args} {513 switch -- $method {514 xConnect {515 if {[info exists ::vtab_connect_sql]} {516 execsql $::vtab_connect_sql517 }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 db538 539 sqlite3_db_config db DEFENSIVE 1540 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 0558 do_catchsql_test 16.22 {559 ALTER TABLE y1_segments RENAME TO abc;560 } {0 {}}561 sqlite3_db_config db DEFENSIVE 1562 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 0569 do_catchsql_test 16.25 {570 ALTER TABLE abc RENAME TO y1_segments;571 } {0 {}}572 sqlite3_db_config db DEFENSIVE 1573 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#-------------------------------------------------------------------------584reset_db585do_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#-------------------------------------------------------------------------594reset_db595do_execsql_test 18.1.0 {596 CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY(c0)) WITHOUT ROWID;597}598do_execsql_test 18.1.1 {599 ALTER TABLE t0 RENAME COLUMN c0 TO c1;600}601do_execsql_test 18.1.2 {602 SELECT sql FROM sqlite_master;603} {{CREATE TABLE t0 (c1 INTEGER, PRIMARY KEY(c1)) WITHOUT ROWID}}604 605reset_db606do_execsql_test 18.2.0 {607 CREATE TABLE t0 (c0 INTEGER, PRIMARY KEY(c0));608}609do_execsql_test 18.2.1 {610 ALTER TABLE t0 RENAME COLUMN c0 TO c1;611}612do_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 f50af3e8a565776b617reset_db618do_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")}}624do_execsql_test 19.110 {625 INSERT INTO t3(x) VALUES(123);626 SELECT * FROM t2;627} {1}628do_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 4722bdab08cb14634reset_db635do_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}639do_execsql_test 20.1 {640 ALTER TABLE a RENAME a TO e;641} {}642 643reset_db644do_execsql_test 21.0 {645 CREATE TABLE a(b);646 CREATE VIEW c AS 647 SELECT NULL INTERSECT 648 SELECT NULL ORDER BY649 likelihood(NULL, (d, (SELECT c)));650} {}651do_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}}654do_catchsql_test 21.2 {655 SELECT * FROM c;656} {1 {1st ORDER BY term does not match any column in the result set}}657 658do_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/ddbe1c7efa663# Ensure that PRAGMA schema_version=N causes a full schema reload.664#665reset_db666do_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}676do_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#-------------------------------------------------------------------------682reset_db683db collate compare64 compare64684 685do_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}690db close691sqlite3 db test.db692 693do_execsql_test 23.2 {694 alter table gigo rename to ggiiggoo;695 alter table idx rename to idx2;696}697 698do_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 706do_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#717reset_db718do_execsql_test 24.1.0 {719 CREATE TABLE t1(a, b);720 CREATE TRIGGER AFTER INSERT ON t1 BEGIN721 INSERT INTO nosuchtable VALUES(new.a) ON CONFLICT(a) DO NOTHING;722 END;723}724do_catchsql_test 24.1.1 {725 ALTER TABLE t1 RENAME TO t2;726} {1 {error in trigger AFTER: no such table: main.nosuchtable}}727 728reset_db729do_execsql_test 24.2.0 {730 CREATE TABLE t1(a, b);731 CREATE TRIGGER AFTER INSERT ON t1 BEGIN732 INSERT INTO v1 VALUES(new.a) ON CONFLICT(a) DO NOTHING;733 END;734 CREATE VIEW v1 AS SELECT * FROM nosuchtable;735}736do_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#742reset_db743do_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 748ifcapable 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 bc17a306a09329bba0ecc61547077f6178bcf321755# Remove a NEVER() inserted on 2019-12-09 that is reachable after all.756#757reset_db758do_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 BEGIN763 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#-------------------------------------------------------------------------773reset_db774 775do_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_sa785 BEGIN786 DELETE FROM t_sa WHERE (787 SELECT 123 FROM t2788 WINDOW oamat7fzf AS ( PARTITION BY t_sa.c_d4u )789 );790 END;791}792 793do_execsql_test 27.2 {794 alter table t_sa rename column c_muyat to c_dg;795}796 797#-------------------------------------------------------------------------798reset_db799do_execsql_test 29.1 {800 CREATE TABLE t1(a, b, c);801 INSERT INTO t1 VALUES('a', 'b', 'c');802 803 CREATE VIEW v0 AS804 WITH p AS ( SELECT 1 FROM t1 ),805 g AS ( SELECT 1 FROM p, t1 )806 SELECT 1 FROM g;807}808 809do_execsql_test 29.2 {810 SELECT * FROM v0811} 1812 813do_execsql_test 29.2 {814 ALTER TABLE t1 RENAME TO t2815} 816 817do_execsql_test 29.3 {818 SELECT sql FROM sqlite_schema WHERE name='v0'819} {{CREATE VIEW v0 AS820 WITH p AS ( SELECT 1 FROM "t2" ),821 g AS ( SELECT 1 FROM p, "t2" )822 SELECT 1 FROM g}}823 824do_execsql_test 29.4 {825 CREATE VIEW v2 AS826 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 i830 )831 )832 SELECT 1 FROM g;833}834 835do_execsql_test 29.4 {836 SELECT * FROM v2;837} 1838 839do_execsql_test 29.5 {840 ALTER TABLE t2 RENAME TO t3;841} 842 843do_execsql_test 29.5 {844 SELECT sql FROM sqlite_schema WHERE name='v2'845} {{CREATE VIEW v2 AS846 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 i850 )851 )852 SELECT 1 FROM g}}853 854 855#-------------------------------------------------------------------------856reset_db857do_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 t4867 UNION868 SELECT x,y,NULL FROM t3, t2869 )870 SELECT * FROM t3 AS xyz;871}872 873do_execsql_test 28.2 {874 SELECT * FROM v3875} {876 1 2 {} 1 3 {} 2 5 {}877}878 879do_execsql_test 28.3 {880 ALTER TABLE t1 RENAME a TO a2; -- fails in v3881} 882 883do_execsql_test 28.4 {884 ALTER TABLE t2 RENAME TO t5;885} 886 887do_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 t4892 UNION893 SELECT x,y,NULL FROM t3, "t5"894 )895 SELECT * FROM t3 AS xyz}}896 897#-------------------------------------------------------------------------898reset_db899do_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 BEGIN907 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<1914 ), 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<x919 ) AS x3, c2 x5;920 END;921}922 923do_execsql_test 30.1 {924 ALTER TABLE t1 RENAME TO t1x;925}926 927do_execsql_test 30.2 {928 SELECT sql FROM sqlite_schema ORDER BY rowid929} {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 BEGIN937 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<1944 ), 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<x949 ) AS x3, c2 x5;950 END}951}952 953#-------------------------------------------------------------------------954reset_db955do_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 960do_execsql_test 31.1 {961 SELECT * FROM vvv;962} {1}963 964do_execsql_test 31.2 {965 ALTER TABLE t1 RENAME TO t1x;966}967 968do_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/5517690440646656973# Bad assert() statement974#975reset_db976do_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# 2023-04-13 https://sqlite.org/forum/forumpost/ff3840145a985#986reset_db987do_execsql_test 33.0 {988 CREATE TABLE t1(a TEXT);989 INSERT INTO t1(a) VALUES('abc'),('def'),(NULL);990 CREATE TABLE t2(b TEXT);991 CREATE TRIGGER r3 AFTER INSERT ON t1 BEGIN992 UPDATE t2 SET (b,a)=(SELECT 1) FROM t1 JOIN t2 ON (SELECT * FROM (SELECT a));993 END;994}995do_catchsql_test 33.1 {996 ALTER TABLE t1 RENAME COLUMN a TO b;997} {1 {error in trigger r3 after rename: no such column: a}}998do_execsql_test 33.2 {999 SELECT quote(a) FROM t1 ORDER BY +a;1000} {NULL 'abc' 'def'}1001 1002finish_test1003