AryaWu/sqlite
0
1# 2021 February 162#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 alterdropcol16 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, 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 34do_catchsql_test 1.1 {35 ALTER TABLE nosuch DROP COLUMN z;36} {1 {no such table: nosuch}}37 38do_catchsql_test 1.2 {39 ALTER TABLE v1 DROP COLUMN c;40} {1 {cannot drop column from view "v1"}}41 42ifcapable 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 51do_catchsql_test 1.4 {52 ALTER TABLE sqlite_schema DROP COLUMN sql;53} {1 {table sqlite_master may not be altered}}54 55do_catchsql_test 1.5 {56 ALTER TABLE t1 DROP COLUMN d;57} {1 {no such column: "d"}}58 59do_execsql_test 1.6.1 {60 ALTER TABLE t1 DROP COLUMN b;61}62do_execsql_test 1.6.2 {63 SELECT sql FROM sqlite_schema WHERE name = 't1'64} {{CREATE TABLE t1(a, c)}}65 66do_execsql_test 1.7.1 {67 ALTER TABLE t1 DROP COLUMN c;68}69do_execsql_test 1.7.2 {70 SELECT sql FROM sqlite_schema WHERE name = 't1'71} {{CREATE TABLE t1(a)}}72 73do_catchsql_test 1.7.3 {74 ALTER TABLE t1 DROP COLUMN a;75} {1 {cannot drop column "a": no other columns exist}}76 77 78do_catchsql_test 1.8 {79 ALTER TABLE t2 DROP COLUMN z80} {1 {cannot drop UNIQUE column: "z"}}81 82do_catchsql_test 1.9 {83 ALTER TABLE t2 DROP COLUMN x84} {1 {cannot drop PRIMARY KEY column: "x"}}85 86do_catchsql_test 1.10 {87 ALTER TABLE t2 DROP COLUMN y88} {1 {error in index t2y after drop column: no such column: y}}89 90do_catchsql_test 1.11 {91 ALTER TABLE t3 DROP COLUMN s92} {1 {error in index t3rs after drop column: no such column: s}}93 94#-------------------------------------------------------------------------95 96foreach {tn wo} {97 1 {}98 2 {WITHOUT ROWID}99} { eval [string map [list %TN% $tn %WO% $wo] {100 101 reset_db102 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 9114 }115 do_execsql_test 2.%TN%.2 {116 ALTER TABLE t1 DROP COLUMN z;117 SELECT * FROM t1;118 } {119 2 5 8120 }121}]}122 123#-------------------------------------------------------------------------124reset_db125 126do_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}130do_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 134do_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 from140# tables that contain generated columns can be dropped.141#142foreach {tn wo vs} {143 1 "" ""144 2 "" VIRTUAL145 3 "" STORED146 4 "WITHOUT ROWID" STORED147 5 "WITHOUT ROWID" VIRTUAL148} {149 reset_db150 151 do_execsql_test 4.$tn.0 "152 CREATE TABLE 'my table'(a, b PRIMARY KEY, c AS (a+b) $vs, d) $wo153 "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 hello161 3 4 7 world162 }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 hello171 3 4 world172 }173 174 do_execsql_test 4.$tn.4 "175 CREATE TABLE x1(a, b, c PRIMARY KEY, d AS (b+c) $vs, e) $wo176 "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 4184 5 6 7 13 8185 9 10 11 21 12186 }187 188 do_execsql_test 4.$tn.6 {189 ALTER TABLE x1 DROP COLUMN a190 }191 do_execsql_test 4.$tn.7 {192 SELECT * FROM x1193 } {194 2 3 5 4195 6 7 13 8196 10 11 21 12197 }198 do_execsql_test 4.$tn.8 {199 ALTER TABLE x1 DROP COLUMN e200 }201 do_execsql_test 4.$tn.9 {202 SELECT * FROM x1203 } {204 2 3 5205 6 7 13206 10 11 21207 }208}209 210#-------------------------------------------------------------------------211reset_db212do_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}217do_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 226do_execsql_test 5.2.1 {227 CREATE VIEW v1 AS SELECT d, e FROM p1228}229do_catchsql_test 5.2.2 {230 ALTER TABLE c1 DROP COLUMN x231} {1 {error in view v1: no such column: d}}232do_execsql_test 5.3.1 {233 DROP VIEW v1;234 CREATE VIEW v1 AS SELECT x, y FROM c1;235}236do_catchsql_test 5.3.2 {237 ALTER TABLE c1 DROP COLUMN x238} {1 {error in view v1 after drop column: no such column: x}}239 240do_execsql_test 5.4.1 {241 CREATE TRIGGER tr AFTER INSERT ON c1 BEGIN242 INSERT INTO p1 VALUES(new.y, new.xyz);243 END;244}245do_catchsql_test 5.4.2 {246 ALTER TABLE c1 DROP COLUMN y247} {1 {error in trigger tr: no such column: new.xyz}}248do_execsql_test 5.5.1 {249 DROP TRIGGER tr;250 CREATE TRIGGER tr AFTER INSERT ON c1 BEGIN251 INSERT INTO p1 VALUES(new.y, new.z);252 END;253}254do_catchsql_test 5.5.2 {255 ALTER TABLE c1 DROP COLUMN y256} {1 {error in trigger tr: no such column: new.z}}257 258# 2021-03-06 dbsqlfuzz crash-419aa525df93db6e463772c686ac6da27b46da9e259reset_db260do_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}}268reset_db269do_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-331c5c29bb76257b198f1318eef3288f9624c8ce280reset_db281do_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}286do_execsql_test 7.1 {287 ALTER TABLE t1 DROP COLUMN c; 288}289do_execsql_test 7.2 {290 SELECT sql FROM sqlite_schema;291} {{CREATE TABLE t1(a, b, PRIMARY KEY(a COLLATE nocase, a)) WITHOUT ROWID}}292do_execsql_test 7.3 {293 SELECT * FROM t1;294} {1 2 4 5}295 296reset_db297do_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}303db close304sqlite3 db test.db305do_execsql_test 8.1 {306 ALTER TABLE t1 DROP COLUMN b; 307}308do_execsql_test 8.2 {309 SELECT sql FROM sqlite_schema;310} {{CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT)}}311 312#-------------------------------------------------------------------------313 314foreach {tn wo} {315 1 {}316 2 {WITHOUT ROWID}317} {318 reset_db319 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<50000325 )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 339finish_test340