test/altertab2.test

   1 # 2018 September 30
   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 altertab2
  16
  17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
  18 ifcapable !altertable {
  19   finish_test
  20   return
  21 }
  22
  23 ifcapable fts5 {
  24   do_execsql_test 1.0 {
  25     CREATE TABLE rr(a, b);
  26     CREATE VIRTUAL TABLE ff USING fts5(a, b);
  27     CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
  28       INSERT INTO ff VALUES(new.a, new.b);
  29     END;
  30     INSERT INTO rr VALUES('hello', 'world');
  31     SELECT * FROM ff;
  32   } {hello world}
  33
  34   do_execsql_test 1.1 {
  35     ALTER TABLE ff RENAME TO ffff;
  36   }
  37
  38   do_execsql_test 1.2 {
  39     INSERT INTO rr VALUES('in', 'tcl');
  40     SELECT * FROM ffff;
  41   } {hello world in tcl}
  42 }
  43
  44 #-------------------------------------------------------------------------
  45 # Check that table names that appear in REFERENCES clauses are updated
  46 # when a table is renamed unless:
  47 #
  48 #   a) "PRAGMA legacy_alter_table" is true, and
  49 #   b) "PRAGMA foreign_keys" is false.
  50 #
  51 do_execsql_test 2.0 {
  52   CREATE TABLE p1(a PRIMARY KEY, b);
  53   CREATE TABLE c1(x REFERENCES p1);
  54   CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES p1);
  55   CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES p1(a));
  56 }
  57
  58 do_execsql_test 2.1 {
  59   ALTER TABLE p1 RENAME TO p2;
  60   SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
  61 } {
  62   {CREATE TABLE c1(x REFERENCES "p2")}
  63   {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
  64   {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
  65 }
  66
  67 do_execsql_test 2.2 {
  68   PRAGMA legacy_alter_table = 1;
  69   ALTER TABLE p2 RENAME TO p3;
  70   SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
  71 } {
  72   {CREATE TABLE c1(x REFERENCES "p2")}
  73   {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p2")}
  74   {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p2"(a))}
  75 }
  76
  77 do_execsql_test 2.3 {
  78   ALTER TABLE p3 RENAME TO p2;
  79   PRAGMA foreign_keys = 1;
  80   ALTER TABLE p2 RENAME TO p3;
  81   SELECT sql FROM sqlite_master WHERE name LIKE 'c%';
  82 } {
  83   {CREATE TABLE c1(x REFERENCES "p3")}
  84   {CREATE TABLE c2(x, FOREIGN KEY (x) REFERENCES "p3")}
  85   {CREATE TABLE c3(x, FOREIGN KEY (x) REFERENCES "p3"(a))}
  86 }
  87
  88 #-------------------------------------------------------------------------
  89 # Table name in WITH clauses that are part of views or triggers.
  90 #
  91 foreach {tn schema} {
  92   1 {
  93     CREATE TABLE log_entry(col1, y);
  94     CREATE INDEX i1 ON log_entry(col1);
  95   }
  96
  97   2 {
  98     CREATE TABLE t1(a, b, c);
  99     CREATE TABLE t2(x);
 100     CREATE TABLE log_entry(col1);
 101     CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
 102       INSERT INTO t2 SELECT col1 FROM log_entry;
 103     END;
 104   }
 105
 106   3 {
 107     CREATE TABLE t1(a, b, c);
 108     CREATE TABLE t2(x);
 109     CREATE TABLE log_entry(col1);
 110     CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
 111       INSERT INTO t2
 112         WITH xyz(x) AS (SELECT col1 FROM log_entry)
 113         SELECT x FROM xyz;
 114     END;
 115   }
 116
 117   4 {
 118     CREATE TABLE log_entry(col1);
 119     CREATE VIEW ttt AS
 120         WITH xyz(x) AS (SELECT col1 FROM log_entry)
 121         SELECT x FROM xyz;
 122   }
 123 } {
 124   reset_db
 125   do_execsql_test 3.$tn.1 $schema
 126   set expect [db eval "SELECT sql FROM sqlite_master"]
 127   set expect [string map {log_entry {"newname"}} $expect]
 128
 129   do_execsql_test 3.$tn.2 {
 130     ALTER TABLE log_entry RENAME TO newname;
 131     SELECT sql FROM sqlite_master;
 132   } $expect
 133
 134   reset_db
 135   do_execsql_test 3.$tn.3 $schema
 136   set expect [db eval "SELECT sql FROM sqlite_master"]
 137   set expect [string map {col1 newname} $expect]
 138
 139   do_execsql_test 3.$tn.4 {
 140     ALTER TABLE log_entry RENAME col1 TO newname;
 141     SELECT sql FROM sqlite_master;
 142   } $expect
 143 }
 144
 145 #-------------------------------------------------------------------------
 146 reset_db
 147 do_execsql_test 4.0 {
 148   CREATE TABLE t1(a,b,c,d,e,f);
 149   CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
 150     UPDATE t1 SET (c,d)=(a,b);
 151   END;
 152 }
 153
 154 do_execsql_test 4.1 {
 155   ALTER TABLE t1 RENAME TO t1x;
 156   SELECT sql FROM sqlite_master WHERE type = 'trigger';
 157 } {
 158 {CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN
 159     UPDATE "t1x" SET (c,d)=(a,b);
 160   END}
 161 }
 162
 163 do_execsql_test 4.2 {
 164   ALTER TABLE t1x RENAME a TO aaa;
 165   SELECT sql FROM sqlite_master WHERE type = 'trigger';
 166 } {
 167 {CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
 168     UPDATE "t1x" SET (c,d)=(aaa,b);
 169   END}
 170 }
 171
 172 do_execsql_test 4.3 {
 173   ALTER TABLE t1x RENAME d TO ddd;
 174   SELECT sql FROM sqlite_master WHERE type = 'trigger';
 175 } {
 176 {CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
 177     UPDATE "t1x" SET (c,ddd)=(aaa,b);
 178   END}
 179 }
 180
 181 #-------------------------------------------------------------------------
 182 ifcapable windowfunc {
 183 do_execsql_test 5.0 {
 184   CREATE TABLE t2(a);
 185   CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN
 186     SELECT a, sum(a) OVER w1 FROM t2
 187       WINDOW w1 AS (
 188         PARTITION BY a ORDER BY a
 189         ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
 190       ),
 191       w2 AS (
 192         PARTITION BY a
 193         ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 194       );
 195   END;
 196 } {}
 197
 198 do_execsql_test 5.0.1 {
 199   INSERT INTO t2 VALUES(1);
 200 } {}
 201
 202 do_execsql_test 5.1 {
 203   ALTER TABLE t2 RENAME TO t2x;
 204   SELECT sql FROM sqlite_master WHERE name = 'r2';
 205 } {
 206   {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN
 207     SELECT a, sum(a) OVER w1 FROM "t2x"
 208       WINDOW w1 AS (
 209         PARTITION BY a ORDER BY a
 210         ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
 211       ),
 212       w2 AS (
 213         PARTITION BY a
 214         ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 215       );
 216   END}
 217 }
 218
 219 do_execsql_test 5.2 {
 220   ALTER TABLE t2x RENAME a TO aaaa;
 221   SELECT sql FROM sqlite_master WHERE name = 'r2';
 222 } {
 223   {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN
 224     SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x"
 225       WINDOW w1 AS (
 226         PARTITION BY aaaa ORDER BY aaaa
 227         ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
 228       ),
 229       w2 AS (
 230         PARTITION BY aaaa
 231         ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 232       );
 233   END}
 234 }
 235
 236 do_execsql_test 5.3 {
 237   INSERT INTO t2x VALUES(1);
 238 } {}
 239 } ;# windowfunc
 240
 241 #-------------------------------------------------------------------------
 242
 243 do_execsql_test 6.0 {
 244   CREATE TABLE t3(a,b,c,d);
 245   CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN
 246     SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;
 247     SELECT rowid, * FROM t3;
 248   END;
 249 } {}
 250
 251 do_execsql_test 6.1 {
 252   ALTER TABLE t3 RENAME TO t3x;
 253   SELECT sql FROM sqlite_master WHERE name = 'r3';
 254 } {
 255   {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN
 256     SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a;
 257     SELECT rowid, * FROM "t3x";
 258   END}
 259 }
 260
 261 do_execsql_test 6.2 {
 262   ALTER TABLE t3x RENAME a TO abcd;
 263   SELECT sql FROM sqlite_master WHERE name = 'r3';
 264 } {
 265   {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN
 266     SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd;
 267     SELECT rowid, * FROM "t3x";
 268   END}
 269 }
 270
 271 #-------------------------------------------------------------------------
 272 reset_db
 273
 274 do_execsql_test 7.0 {
 275   CREATE TABLE t1(a,b,c,d,e,f);
 276   INSERT INTO t1 VALUES(1,2,3,4,5,6);
 277   CREATE TABLE t2(x,y,z);
 278 }
 279
 280 do_execsql_test 7.1 {
 281   SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
 282 } {1 2 3 4 5 6}
 283
 284 do_execsql_test 7.2 {
 285   CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
 286     INSERT INTO t2
 287     SELECT a,b,c FROM t1 UNION SELECT d,e,f FROM t1 ORDER BY b,c;
 288   END;
 289   INSERT INTO t1 VALUES(2,3,4,5,6,7);
 290   SELECT * FROM t2;
 291 } {1 2 3  2 3 4  4 5 6  5 6 7}
 292
 293 do_execsql_test 7.3 {
 294   ALTER TABLE t1 RENAME TO xyzzy;
 295   SELECT sql FROM sqlite_master WHERE name='r1'
 296 } {
 297   {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
 298     INSERT INTO t2
 299     SELECT a,b,c FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,c;
 300   END}
 301 }
 302
 303 do_execsql_test 7.3 {
 304   ALTER TABLE xyzzy RENAME c TO ccc;
 305   SELECT sql FROM sqlite_master WHERE name='r1'
 306 } {
 307   {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
 308     INSERT INTO t2
 309     SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;
 310   END}
 311 }
 312
 313 #-------------------------------------------------------------------------
 314 reset_db
 315 do_execsql_test 8.0 {
 316   CREATE TABLE t1(a, b, c);
 317   CREATE TABLE t2(a, b, c);
 318   CREATE TABLE t3(d, e, f);
 319   CREATE VIEW v1 AS SELECT * FROM t1;
 320   CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
 321     UPDATE t2 SET a = new.d;
 322     SELECT a, b, c FROM v1;
 323   END;
 324 }
 325
 326 do_execsql_test 8.1 {
 327   INSERT INTO t3 VALUES(1, 2, 3);
 328 }
 329
 330 # The following ALTER TABLE fails as if column "t1.a" is renamed the "a"
 331 # in the "SELECT a, b, c FROM v1" within the trigger can no longer be
 332 # resolved. But at one point there was a bug allowing the ALTER TABLE
 333 # succeed. Which meant the subsequent INSERT statement would fail.
 334 do_catchsql_test 8.2 {
 335   ALTER TABLE t1 RENAME a TO aaa;
 336 } {1 {error in trigger tr after rename: no such column: a}}
 337 do_execsql_test 8.3 {
 338   INSERT INTO t3 VALUES(4, 5, 6);
 339 }
 340
 341 do_execsql_test 8.4 {
 342   CREATE TABLE t4(a, b);
 343   CREATE VIEW v4 AS SELECT * FROM t4 WHERE (a=1 AND 0) OR b=2;
 344 }
 345
 346 # Branches of an expression tree that are optimized out by the AND
 347 # optimization are renamed.
 348 #
 349 do_execsql_test 8.5 {
 350   ALTER TABLE t4 RENAME a TO c;
 351   SELECT sql FROM sqlite_master WHERE name = 'v4'
 352 } {{CREATE VIEW v4 AS SELECT * FROM t4 WHERE (c=1 AND 0) OR b=2}}
 353
 354 # 2019-06-10 https://www.sqlite.org/src/info/533010b8cacebe82
 355 reset_db
 356 do_catchsql_test 8.6 {
 357   CREATE TABLE t0(c0);
 358   CREATE INDEX i0 ON t0(likelihood(1,2) AND 0);
 359   ALTER TABLE t0 RENAME TO t1;
 360   SELECT sql FROM sqlite_master WHERE name='i0';
 361 } {1 {error in index i0: second argument to likelihood() must be a constant between 0.0 and 1.0}}
 362
 363 finish_test