test/alterdropcol.test

   1 # 2021 February 16
   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 alterdropcol
  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, c);
  25   CREATE VIEW v1 AS SELECT * FROM t1;
  26
  27   CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z UNIQUE);
  28   CREATE INDEX t2y ON t2(y);
  29
  30   CREATE TABLE t3(q, r, s);
  31   CREATE INDEX t3rs ON t3(r+s);
  32 }
  33
  34 do_catchsql_test 1.1 {
  35   ALTER TABLE nosuch DROP COLUMN z;
  36 } {1 {no such table: nosuch}}
  37
  38 do_catchsql_test 1.2 {
  39   ALTER TABLE v1 DROP COLUMN c;
  40 } {1 {cannot drop column from view "v1"}}
  41
  42 ifcapable fts5 {
  43   do_execsql_test 1.3.1 {
  44     CREATE VIRTUAL TABLE ft1 USING fts5(one, two);
  45   }
  46   do_catchsql_test 1.3.2 {
  47     ALTER TABLE ft1 DROP COLUMN two;
  48   } {1 {cannot drop column from virtual table "ft1"}}
  49 }
  50
  51 do_catchsql_test 1.4 {
  52   ALTER TABLE sqlite_schema DROP COLUMN sql;
  53 } {1 {table sqlite_master may not be altered}}
  54
  55 do_catchsql_test 1.5 {
  56   ALTER TABLE t1 DROP COLUMN d;
  57 } {1 {no such column: "d"}}
  58
  59 do_execsql_test 1.6.1 {
  60   ALTER TABLE t1 DROP COLUMN b;
  61 }
  62 do_execsql_test 1.6.2 {
  63   SELECT sql FROM sqlite_schema WHERE name = 't1'
  64 } {{CREATE TABLE t1(a, c)}}
  65
  66 do_execsql_test 1.7.1 {
  67   ALTER TABLE t1 DROP COLUMN c;
  68 }
  69 do_execsql_test 1.7.2 {
  70   SELECT sql FROM sqlite_schema WHERE name = 't1'
  71 } {{CREATE TABLE t1(a)}}
  72
  73 do_catchsql_test 1.7.3 {
  74   ALTER TABLE t1 DROP COLUMN a;
  75 } {1 {cannot drop column "a": no other columns exist}}
  76
  77
  78 do_catchsql_test 1.8 {
  79   ALTER TABLE t2 DROP COLUMN z
  80 } {1 {cannot drop UNIQUE column: "z"}}
  81
  82 do_catchsql_test 1.9 {
  83   ALTER TABLE t2 DROP COLUMN x
  84 } {1 {cannot drop PRIMARY KEY column: "x"}}
  85
  86 do_catchsql_test 1.10 {
  87   ALTER TABLE t2 DROP COLUMN y
  88 } {1 {error in index t2y after drop column: no such column: y}}
  89
  90 do_catchsql_test 1.11 {
  91   ALTER TABLE t3 DROP COLUMN s
  92 } {1 {error in index t3rs after drop column: no such column: s}}
  93
  94 #-------------------------------------------------------------------------
  95
  96 foreach {tn wo} {
  97   1 {}
  98   2 {WITHOUT ROWID}
  99 } { eval [string map [list %TN% $tn %WO% $wo] {
 100
 101   reset_db
 102   do_execsql_test 2.%TN%.0 {
 103     CREATE TABLE t1(x, y INTEGER PRIMARY KEY, z) %WO% ;
 104     INSERT INTO t1 VALUES(1, 2, 3);
 105     INSERT INTO t1 VALUES(4, 5, 6);
 106     INSERT INTO t1 VALUES(7, 8, 9);
 107   }
 108
 109   do_execsql_test 2.%TN%.1 {
 110     ALTER TABLE t1 DROP COLUMN x;
 111     SELECT * FROM t1;
 112   } {
 113     2 3  5 6  8 9
 114   }
 115   do_execsql_test 2.%TN%.2 {
 116     ALTER TABLE t1 DROP COLUMN z;
 117     SELECT * FROM t1;
 118   } {
 119     2 5 8
 120   }
 121 }]}
 122
 123 #-------------------------------------------------------------------------
 124 reset_db
 125
 126 do_execsql_test 3.0 {
 127   CREATE TABLE t12(a, b, c, CHECK(c>10));
 128   CREATE TABLE t13(a, b, c CHECK(c>10));
 129 }
 130 do_catchsql_test 3.1 {
 131   ALTER TABLE t12 DROP COLUMN c;
 132 } {1 {error in table t12 after drop column: no such column: c}}
 133
 134 do_catchsql_test 3.2 {
 135   ALTER TABLE t13 DROP COLUMN c;
 136 } {0 {}}
 137
 138 #-------------------------------------------------------------------------
 139 # Test that generated columns can be dropped. And that other columns from
 140 # tables that contain generated columns can be dropped.
 141 #
 142 foreach {tn wo vs} {
 143   1 ""              ""
 144   2 ""              VIRTUAL
 145   3 ""              STORED
 146   4 "WITHOUT ROWID" STORED
 147   5 "WITHOUT ROWID" VIRTUAL
 148 } {
 149   reset_db
 150
 151   do_execsql_test 4.$tn.0 "
 152     CREATE TABLE 'my table'(a, b PRIMARY KEY, c AS (a+b) $vs, d) $wo
 153   "
 154   do_execsql_test 4.$tn.1 {
 155     INSERT INTO "my table"(a, b, d) VALUES(1, 2, 'hello');
 156     INSERT INTO "my table"(a, b, d) VALUES(3, 4, 'world');
 157
 158     SELECT * FROM "my table"
 159   } {
 160     1 2 3 hello
 161     3 4 7 world
 162   }
 163
 164   do_execsql_test 4.$tn.2 {
 165     ALTER TABLE "my table" DROP COLUMN c;
 166   }
 167   do_execsql_test 4.$tn.3 {
 168     SELECT * FROM "my table"
 169   } {
 170     1 2 hello
 171     3 4 world
 172   }
 173
 174   do_execsql_test 4.$tn.4 "
 175     CREATE TABLE x1(a, b, c PRIMARY KEY, d AS (b+c) $vs, e) $wo
 176   "
 177   do_execsql_test 4.$tn.5 {
 178     INSERT INTO x1(a, b, c, e) VALUES(1, 2, 3, 4);
 179     INSERT INTO x1(a, b, c, e) VALUES(5, 6, 7, 8);
 180     INSERT INTO x1(a, b, c, e) VALUES(9, 10, 11, 12);
 181     SELECT * FROM x1;
 182   } {
 183     1 2 3 5 4
 184     5 6 7 13 8
 185     9 10 11 21 12
 186   }
 187
 188   do_execsql_test 4.$tn.6 {
 189     ALTER TABLE x1 DROP COLUMN a
 190   }
 191   do_execsql_test 4.$tn.7 {
 192     SELECT * FROM x1
 193   } {
 194     2 3 5 4
 195     6 7 13 8
 196     10 11 21 12
 197   }
 198   do_execsql_test 4.$tn.8 {
 199     ALTER TABLE x1 DROP COLUMN e
 200   }
 201   do_execsql_test 4.$tn.9 {
 202     SELECT * FROM x1
 203   } {
 204     2 3 5
 205     6 7 13
 206     10 11 21
 207   }
 208 }
 209
 210 #-------------------------------------------------------------------------
 211 reset_db
 212 do_execsql_test 5.0 {
 213   CREATE TABLE p1(a PRIMARY KEY, b UNIQUE);
 214   CREATE TABLE c1(x, y, z REFERENCES p1(c));
 215   CREATE TABLE c2(x, y, z, w REFERENCES p1(b));
 216 }
 217 do_execsql_test 5.1 {
 218   ALTER TABLE c1 DROP COLUMN z;
 219   ALTER TABLE c2 DROP COLUMN z;
 220   SELECT sql FROM sqlite_schema WHERE name IN ('c1', 'c2');
 221 } {
 222   {CREATE TABLE c1(x, y)}
 223   {CREATE TABLE c2(x, y, w REFERENCES p1(b))}
 224 }
 225
 226 do_execsql_test 5.2.1 {
 227   CREATE VIEW v1 AS SELECT d, e FROM p1
 228 }
 229 do_catchsql_test 5.2.2 {
 230   ALTER TABLE c1 DROP COLUMN x
 231 } {1 {error in view v1: no such column: d}}
 232 do_execsql_test 5.3.1 {
 233   DROP VIEW v1;
 234   CREATE VIEW v1 AS SELECT x, y FROM c1;
 235 }
 236 do_catchsql_test 5.3.2 {
 237   ALTER TABLE c1 DROP COLUMN x
 238 } {1 {error in view v1 after drop column: no such column: x}}
 239
 240 do_execsql_test 5.4.1 {
 241   CREATE TRIGGER tr AFTER INSERT ON c1 BEGIN
 242     INSERT INTO p1 VALUES(new.y, new.xyz);
 243   END;
 244 }
 245 do_catchsql_test 5.4.2 {
 246   ALTER TABLE c1 DROP COLUMN y
 247 } {1 {error in trigger tr: no such column: new.xyz}}
 248 do_execsql_test 5.5.1 {
 249   DROP TRIGGER tr;
 250   CREATE TRIGGER tr AFTER INSERT ON c1 BEGIN
 251     INSERT INTO p1 VALUES(new.y, new.z);
 252   END;
 253 }
 254 do_catchsql_test 5.5.2 {
 255   ALTER TABLE c1 DROP COLUMN y
 256 } {1 {error in trigger tr: no such column: new.z}}
 257
 258 # 2021-03-06 dbsqlfuzz crash-419aa525df93db6e463772c686ac6da27b46da9e
 259 reset_db
 260 do_catchsql_test 6.0 {
 261   CREATE TABLE t1(a,b,c);
 262   CREATE TABLE t2(x,y,z);
 263   PRAGMA writable_schema=ON;
 264   UPDATE sqlite_schema SET sql='CREATE INDEX t1b ON t1(b)' WHERE name='t2';
 265   PRAGMA writable_schema=OFF;
 266   ALTER TABLE t2 DROP COLUMN z;
 267 } {1 {database disk image is malformed}}
 268 reset_db
 269 do_catchsql_test 6.1 {
 270   CREATE TABLE t1(a,b,c);
 271   CREATE TABLE t2(x,y,z);
 272   PRAGMA writable_schema=ON;
 273   UPDATE sqlite_schema SET sql='CREATE VIEW t2(x,y,z) AS SELECT b,a,c FROM t1'
 274    WHERE name='t2';
 275   PRAGMA writable_schema=OFF;
 276   ALTER TABLE t2 DROP COLUMN z;
 277 } {1 {database disk image is malformed}}
 278
 279 # 2021-04-06 dbsqlfuzz crash-331c5c29bb76257b198f1318eef3288f9624c8ce
 280 reset_db
 281 do_execsql_test 7.0 {
 282   CREATE TABLE t1(a, b, c, PRIMARY KEY(a COLLATE nocase, a)) WITHOUT ROWID;
 283   INSERT INTO t1 VALUES(1, 2, 3);
 284   INSERT INTO t1 VALUES(4, 5, 6);
 285 }
 286 do_execsql_test 7.1 {
 287   ALTER TABLE t1 DROP COLUMN c;
 288 }
 289 do_execsql_test 7.2 {
 290   SELECT sql FROM sqlite_schema;
 291 } {{CREATE TABLE t1(a, b, PRIMARY KEY(a COLLATE nocase, a)) WITHOUT ROWID}}
 292 do_execsql_test 7.3 {
 293   SELECT * FROM t1;
 294 } {1 2 4 5}
 295
 296 reset_db
 297 do_execsql_test 8.0 {
 298   CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
 299   PRAGMA writable_schema = 1;
 300   UPDATE sqlite_schema
 301   SET sql = 'CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b)'
 302 }
 303 db close
 304 sqlite3 db test.db
 305 do_execsql_test 8.1 {
 306   ALTER TABLE t1 DROP COLUMN b;
 307 }
 308 do_execsql_test 8.2 {
 309   SELECT sql FROM sqlite_schema;
 310 } {{CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT)}}
 311
 312 #-------------------------------------------------------------------------
 313
 314 foreach {tn wo} {
 315   1 {}
 316   2 {WITHOUT ROWID}
 317 } {
 318   reset_db
 319   do_execsql_test 9.$tn.0 "
 320     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) $wo;
 321   "
 322   do_execsql_test 9.$tn.1 {
 323     WITH s(i) AS (
 324         SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000
 325     )
 326     INSERT INTO t1(a, b, c) SELECT i, 123, 456 FROM s;
 327   }
 328   do_execsql_test 9.$tn.2 {
 329     ALTER TABLE t1 DROP COLUMN b;
 330   }
 331
 332   do_execsql_test 9.$tn.3 {
 333     SELECT count(*), c FROM t1 GROUP BY c;
 334   } {50000 456}
 335 }
 336
 337
 338
 339 finish_test