From 18657a960e125336f704ea058e25c27bd3900dcb Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 5 May 2024 19:28:19 +0200 Subject: Adding upstream version 3.40.1. Signed-off-by: Daniel Baumann --- test/altertab2.test | 363 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 363 insertions(+) create mode 100644 test/altertab2.test (limited to 'test/altertab2.test') diff --git a/test/altertab2.test b/test/altertab2.test new file mode 100644 index 0000000..def9e56 --- /dev/null +++ b/test/altertab2.test @@ -0,0 +1,363 @@ +# 2018 September 30 +# +# 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. +# +#************************************************************************* +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix altertab2 + +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. +ifcapable !altertable { + finish_test + return +} + +ifcapable fts5 { + do_execsql_test 1.0 { + CREATE TABLE rr(a, b); + CREATE VIRTUAL TABLE ff USING fts5(a, b); + CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN + INSERT INTO ff VALUES(new.a, new.b); + END; + INSERT INTO rr VALUES('hello', 'world'); + SELECT * FROM ff; + } {hello world} + + do_execsql_test 1.1 { + ALTER TABLE ff RENAME TO ffff; + } + + do_execsql_test 1.2 { + INSERT INTO rr VALUES('in', 'tcl'); + SELECT * FROM ffff; + } {hello world in tcl} +} + +#------------------------------------------------------------------------- +# Check that table names that appear in REFERENCES clauses are updated +# when a table is renamed unless: +# +# a) "PRAGMA legacy_alter_table" is true, and +# b) "PRAGMA foreign_keys" is false. +# +do_execsql_test 2.0 { + CREATE TABLE p1(a PRIMARY KEY, b); + CREATE TABLE c1(x REFERENCES p1); + CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1); + CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a)); +} + +do_execsql_test 2.1 { + ALTER TABLE p1 RENAME TO p2; + SELECT sql FROM sqlite_master WHERE name LIKE 'c%'; +} { + {CREATE TABLE c1(x REFERENCES "p2")} + {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")} + {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))} +} + +do_execsql_test 2.2 { + PRAGMA legacy_alter_table = 1; + ALTER TABLE p2 RENAME TO p3; + SELECT sql FROM sqlite_master WHERE name LIKE 'c%'; +} { + {CREATE TABLE c1(x REFERENCES "p2")} + {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")} + {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))} +} + +do_execsql_test 2.3 { + ALTER TABLE p3 RENAME TO p2; + PRAGMA foreign_keys = 1; + ALTER TABLE p2 RENAME TO p3; + SELECT sql FROM sqlite_master WHERE name LIKE 'c%'; +} { + {CREATE TABLE c1(x REFERENCES "p3")} + {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")} + {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))} +} + +#------------------------------------------------------------------------- +# Table name in WITH clauses that are part of views or triggers. +# +foreach {tn schema} { + 1 { + CREATE TABLE log_entry(col1, y); + CREATE INDEX i1 ON log_entry(col1); + } + + 2 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(x); + CREATE TABLE log_entry(col1); + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + INSERT INTO t2 SELECT col1 FROM log_entry; + END; + } + + 3 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(x); + CREATE TABLE log_entry(col1); + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + INSERT INTO t2 + WITH xyz(x) AS (SELECT col1 FROM log_entry) + SELECT x FROM xyz; + END; + } + + 4 { + CREATE TABLE log_entry(col1); + CREATE VIEW ttt AS + WITH xyz(x) AS (SELECT col1 FROM log_entry) + SELECT x FROM xyz; + } +} { + reset_db + do_execsql_test 3.$tn.1 $schema + set expect [db eval "SELECT sql FROM sqlite_master"] + set expect [string map {log_entry {"newname"}} $expect] + + do_execsql_test 3.$tn.2 { + ALTER TABLE log_entry RENAME TO newname; + SELECT sql FROM sqlite_master; + } $expect + + reset_db + do_execsql_test 3.$tn.3 $schema + set expect [db eval "SELECT sql FROM sqlite_master"] + set expect [string map {col1 newname} $expect] + + do_execsql_test 3.$tn.4 { + ALTER TABLE log_entry RENAME col1 TO newname; + SELECT sql FROM sqlite_master; + } $expect +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 4.0 { + CREATE TABLE t1(a,b,c,d,e,f); + CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN + UPDATE t1 SET (c,d)=(a,b); + END; +} + +do_execsql_test 4.1 { + ALTER TABLE t1 RENAME TO t1x; + SELECT sql FROM sqlite_master WHERE type = 'trigger'; +} { +{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN + UPDATE "t1x" SET (c,d)=(a,b); + END} +} + +do_execsql_test 4.2 { + ALTER TABLE t1x RENAME a TO aaa; + SELECT sql FROM sqlite_master WHERE type = 'trigger'; +} { +{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN + UPDATE "t1x" SET (c,d)=(aaa,b); + END} +} + +do_execsql_test 4.3 { + ALTER TABLE t1x RENAME d TO ddd; + SELECT sql FROM sqlite_master WHERE type = 'trigger'; +} { +{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN + UPDATE "t1x" SET (c,ddd)=(aaa,b); + END} +} + +#------------------------------------------------------------------------- +ifcapable windowfunc { +do_execsql_test 5.0 { + CREATE TABLE t2(a); + CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN + SELECT a, sum(a) OVER w1 FROM t2 + WINDOW w1 AS ( + PARTITION BY a ORDER BY a + ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING + ), + w2 AS ( + PARTITION BY a + ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ); + END; +} {} + +do_execsql_test 5.0.1 { + INSERT INTO t2 VALUES(1); +} {} + +do_execsql_test 5.1 { + ALTER TABLE t2 RENAME TO t2x; + SELECT sql FROM sqlite_master WHERE name = 'r2'; +} { + {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN + SELECT a, sum(a) OVER w1 FROM "t2x" + WINDOW w1 AS ( + PARTITION BY a ORDER BY a + ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING + ), + w2 AS ( + PARTITION BY a + ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ); + END} +} + +do_execsql_test 5.2 { + ALTER TABLE t2x RENAME a TO aaaa; + SELECT sql FROM sqlite_master WHERE name = 'r2'; +} { + {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN + SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x" + WINDOW w1 AS ( + PARTITION BY aaaa ORDER BY aaaa + ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING + ), + w2 AS ( + PARTITION BY aaaa + ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ); + END} +} + +do_execsql_test 5.3 { + INSERT INTO t2x VALUES(1); +} {} +} ;# windowfunc + +#------------------------------------------------------------------------- + +do_execsql_test 6.0 { + CREATE TABLE t3(a,b,c,d); + CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN + SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a; + SELECT rowid, * FROM t3; + END; +} {} + +do_execsql_test 6.1 { + ALTER TABLE t3 RENAME TO t3x; + SELECT sql FROM sqlite_master WHERE name = 'r3'; +} { + {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN + SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a; + SELECT rowid, * FROM "t3x"; + END} +} + +do_execsql_test 6.2 { + ALTER TABLE t3x RENAME a TO abcd; + SELECT sql FROM sqlite_master WHERE name = 'r3'; +} { + {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN + SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd; + SELECT rowid, * FROM "t3x"; + END} +} + +#------------------------------------------------------------------------- +reset_db + +do_execsql_test 7.0 { + CREATE TABLE t1(a,b,c,d,e,f); + INSERT INTO t1 VALUES(1,2,3,4,5,6); + CREATE TABLE t2(x,y,z); +} + +do_execsql_test 7.1 { + SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c; +} {1 2 3 4 5 6} + +do_execsql_test 7.2 { + CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN + INSERT INTO t2 + SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c; + END; + INSERT INTO t1 VALUES(2,3,4,5,6,7); + SELECT * FROM t2; +} {1 2 3 2 3 4 4 5 6 5 6 7} + +do_execsql_test 7.3 { + ALTER TABLE t1 RENAME TO xyzzy; + SELECT sql FROM sqlite_master WHERE name='r1' +} { + {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN + INSERT INTO t2 + SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c; + END} +} + +do_execsql_test 7.3 { + ALTER TABLE xyzzy RENAME c TO ccc; + SELECT sql FROM sqlite_master WHERE name='r1' +} { + {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN + INSERT INTO t2 + SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc; + END} +} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 8.0 { + CREATE TABLE t1(a, b, c); + CREATE TABLE t2(a, b, c); + CREATE TABLE t3(d, e, f); + CREATE VIEW v1 AS SELECT * FROM t1; + CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN + UPDATE t2 SET a = new.d; + SELECT a, b, c FROM v1; + END; +} + +do_execsql_test 8.1 { + INSERT INTO t3 VALUES(1, 2, 3); +} + +# The following ALTER TABLE fails as if column "t1.a" is renamed the "a" +# in the "SELECT a, b, c FROM v1" within the trigger can no longer be +# resolved. But at one point there was a bug allowing the ALTER TABLE +# succeed. Which meant the subsequent INSERT statement would fail. +do_catchsql_test 8.2 { + ALTER TABLE t1 RENAME a TO aaa; +} {1 {error in trigger tr after rename: no such column: a}} +do_execsql_test 8.3 { + INSERT INTO t3 VALUES(4, 5, 6); +} + +do_execsql_test 8.4 { + CREATE TABLE t4(a, b); + CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2; +} + +# Branches of an expression tree that are optimized out by the AND +# optimization are renamed. +# +do_execsql_test 8.5 { + ALTER TABLE t4 RENAME a TO c; + SELECT sql FROM sqlite_master WHERE name = 'v4' +} {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}} + +# 2019-06-10 https://www.sqlite.org/src/info/533010b8cacebe82 +reset_db +do_catchsql_test 8.6 { + CREATE TABLE t0(c0); + CREATE INDEX i0 ON t0(likelihood(1,2) AND 0); + ALTER TABLE t0 RENAME TO t1; + SELECT sql FROM sqlite_master WHERE name='i0'; +} {1 {error in index i0: second argument to likelihood() must be a constant between 0.0 and 1.0}} + +finish_test -- cgit v1.2.3