1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test ALTER TABLE RENAME COLUMN", "[alter]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 // CREATE TABLE AND ALTER IT TO RENAME A COLUMN
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
14 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
15
16 result = con.Query("SELECT * FROM test");
17 REQUIRE(result->names.size() == 2);
18 REQUIRE(result->names[0] == "k");
19 REQUIRE(result->names[1] == "j");
20
21 // DROP TABLE IF EXISTS
22 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS test"));
23}
24
25TEST_CASE("Test ALTER TABLE RENAME COLUMN and dependencies", "[alter]") {
26 unique_ptr<QueryResult> result;
27 DuckDB db(nullptr);
28 Connection con(db);
29
30 // prepared statements
31 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
32 REQUIRE_NO_FAIL(con.Query("PREPARE v AS SELECT * FROM test"));
33 // we don't allow altering of tables when there are dependencies
34 REQUIRE_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
35 // deallocate the dependency
36 REQUIRE_NO_FAIL(con.Query("DEALLOCATE v"));
37 // now we can alter the table
38 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
39 result = con.Query("SELECT * FROM test");
40 REQUIRE(result->names.size() == 2);
41 REQUIRE(result->names[0] == "k");
42 REQUIRE(result->names[1] == "j");
43 REQUIRE_NO_FAIL(con.Query("DROP TABLE IF EXISTS test"));
44}
45
46TEST_CASE("Test ALTER TABLE RENAME COLUMN with transactions", "[alter]") {
47 unique_ptr<QueryResult> result;
48 DuckDB db(nullptr);
49 Connection con(db);
50 Connection con2(db);
51
52 // CREATE TABLE
53 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
54
55 // start two transactions
56 REQUIRE_NO_FAIL(con.Query("START TRANSACTION"));
57 REQUIRE_NO_FAIL(con2.Query("START TRANSACTION"));
58
59 // rename column in first transaction
60 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
61
62 // first transaction should see the new name
63 REQUIRE_FAIL(con.Query("SELECT i FROM test"));
64 REQUIRE_NO_FAIL(con.Query("SELECT k FROM test"));
65
66 // second transaction should still consider old name
67 REQUIRE_NO_FAIL(con2.Query("SELECT i FROM test"));
68 REQUIRE_FAIL(con2.Query("SELECT k FROM test"));
69
70 // now commit
71 REQUIRE_NO_FAIL(con.Query("COMMIT"));
72
73 // second transaction should still see old name
74 REQUIRE_NO_FAIL(con2.Query("SELECT i FROM test"));
75 REQUIRE_FAIL(con2.Query("SELECT k FROM test"));
76
77 // now rollback the second transasction
78 // it should now see the new name
79 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
80
81 REQUIRE_FAIL(con.Query("SELECT i FROM test"));
82 REQUIRE_NO_FAIL(con.Query("SELECT k FROM test"));
83}
84
85TEST_CASE("Test ALTER TABLE RENAME COLUMN with rollback", "[alter]") {
86 unique_ptr<QueryResult> result;
87 DuckDB db(nullptr);
88 Connection con(db);
89
90 // CREATE TABLE
91 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
92
93 // rename the column
94 REQUIRE_NO_FAIL(con.Query("START TRANSACTION"));
95
96 // rename column in first transaction
97 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
98
99 // now we should see the new name
100 REQUIRE_FAIL(con.Query("SELECT i FROM test"));
101 REQUIRE_NO_FAIL(con.Query("SELECT k FROM test"));
102
103 // rollback
104 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
105
106 // now we should see the old name again
107 REQUIRE_NO_FAIL(con.Query("SELECT i FROM test"));
108 REQUIRE_FAIL(con.Query("SELECT k FROM test"));
109}
110
111TEST_CASE("Test failure conditions of ALTER TABLE", "[alter]") {
112 unique_ptr<QueryResult> result;
113 DuckDB db(nullptr);
114 Connection con(db);
115
116 // CREATE TABLE AND ALTER IT TO RENAME A COLUMN
117 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
118
119 // rename a column that does not exist
120 REQUIRE_FAIL(con.Query("ALTER TABLE test RENAME COLUMN blablabla TO k"));
121
122 // rename a column to an already existing column
123 REQUIRE_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO j"));
124
125 // after failure original columns should still be there
126 REQUIRE_NO_FAIL(con.Query("SELECT i, j FROM test"));
127}
128
129TEST_CASE("Test ALTER TABLE RENAME COLUMN on a table with constraints", "[alter]") {
130 unique_ptr<QueryResult> result;
131 DuckDB db(nullptr);
132 Connection con(db);
133
134 SECTION("CHECK constraint") {
135 // create a table with a check constraint referencing the to-be-renamed column
136 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER CHECK(i < 10), j INTEGER)"));
137 // insert some elements
138 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (i, j) VALUES (1, 2), (2, 3)"));
139 REQUIRE_FAIL(con.Query("INSERT INTO test (i, j) VALUES (100, 2)"));
140 // now alter the column name
141 // currently, we don't support altering tables with constraints
142 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
143 // the check should still work after the alter table
144 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (k, j) VALUES (1, 2), (2, 3)"));
145 REQUIRE_FAIL(con.Query("INSERT INTO test (k, j) VALUES (100, 2)"));
146 }
147 SECTION("NOT NULL constraint") {
148 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER NOT NULL, j INTEGER)"));
149 // insert some elements
150 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (i, j) VALUES (1, 2), (2, 3)"));
151 REQUIRE_FAIL(con.Query("INSERT INTO test (i, j) VALUES (NULL, 2)"));
152 // now alter the column name
153 // currently, we don't support altering tables with constraints
154 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
155 // the check should still work after the alter table
156 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (k, j) VALUES (1, 2), (2, 3)"));
157 REQUIRE_FAIL(con.Query("INSERT INTO test (k, j) VALUES (NULL, 2)"));
158 }
159 SECTION("UNIQUE constraint") {
160 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER, PRIMARY KEY(i, j))"));
161 // insert some elements
162 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (i, j) VALUES (1, 1), (2, 2)"));
163 REQUIRE_FAIL(con.Query("INSERT INTO test (i, j) VALUES (1, 1)"));
164 // now alter the column name
165 // currently, we don't support altering tables with constraints
166 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN i TO k"));
167 // the check should still work after the alter table
168 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (k, j) VALUES (3, 3), (4, 4)"));
169 REQUIRE_FAIL(con.Query("INSERT INTO test (k, j) VALUES (1, 1)"));
170 }
171}
172
173TEST_CASE("Test ALTER TABLE ADD COLUMN", "[alter]") {
174 unique_ptr<QueryResult> result;
175 DuckDB db(nullptr);
176 Connection con(db);
177
178 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
179 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
180
181 SECTION("Standard ADD COLUMN") {
182 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
183
184 result = con.Query("SELECT * FROM test");
185 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
186 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
187 REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value()}));
188 }
189 SECTION("ADD COLUMN with default value") {
190 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN l INTEGER DEFAULT 3"));
191
192 result = con.Query("SELECT * FROM test");
193 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
194 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
195 REQUIRE(CHECK_COLUMN(result, 2, {3, 3}));
196 }
197 SECTION("ADD COLUMN with sequence as default value") {
198 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq"));
199 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN m INTEGER DEFAULT nextval('seq')"));
200
201 result = con.Query("SELECT * FROM test");
202 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
203 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
204 REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
205 }
206 SECTION("ADD COLUMN with data inside local storage") {
207 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
208 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3)"));
209 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
210
211 result = con.Query("SELECT * FROM test");
212 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
213 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
214 REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value()}));
215
216 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
217
218 result = con.Query("SELECT * FROM test");
219 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
220 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
221 REQUIRE(result->names.size() == 2);
222
223 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3)"));
224 result = con.Query("SELECT * FROM test");
225 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
226 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
227 }
228 SECTION("multiple ADD COLUMN in the same transaction") {
229 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
230 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3)"));
231 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
232 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN l INTEGER"));
233 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN m INTEGER DEFAULT 3"));
234
235 result = con.Query("SELECT * FROM test");
236 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
237 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
238 REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value()}));
239 REQUIRE(CHECK_COLUMN(result, 3, {Value(), Value(), Value()}));
240 REQUIRE(CHECK_COLUMN(result, 4, {3, 3, 3}));
241
242 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
243
244 result = con.Query("SELECT * FROM test");
245 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
246 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
247 REQUIRE(result->names.size() == 2);
248 }
249 SECTION("ADD COLUMN with index") {
250 // what if we create an index on the new column, then rollback
251 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
252 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER DEFAULT 2"));
253 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test(k)"));
254 REQUIRE_NO_FAIL(con.Query("COMMIT"));
255
256 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3, 3)"));
257
258 result = con.Query("SELECT * FROM test WHERE k=2");
259 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
260 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
261 REQUIRE(CHECK_COLUMN(result, 2, {2, 2}));
262
263 result = con.Query("SELECT * FROM test WHERE k=3");
264 REQUIRE(CHECK_COLUMN(result, 0, {3}));
265 REQUIRE(CHECK_COLUMN(result, 1, {3}));
266 REQUIRE(CHECK_COLUMN(result, 2, {3}));
267 }
268 SECTION("ADD COLUMN rollback with index") {
269 // what if we create an index on the new column, then rollback
270 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
271 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
272 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test(k)"));
273 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
274
275 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3)"));
276
277 result = con.Query("SELECT * FROM test");
278 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
279 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
280 }
281 SECTION("Incorrect usage") {
282 // cannot add a column that already exists!
283 REQUIRE_FAIL(con.Query("ALTER TABLE test ADD COLUMN i INTEGER"));
284 }
285}
286
287TEST_CASE("Test ALTER TABLE ADD COLUMN with multiple transactions", "[alter]") {
288 unique_ptr<QueryResult> result;
289 DuckDB db(nullptr);
290 Connection con(db), con2(db);
291
292 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
293 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
294
295 SECTION("Only one pending table alter can be active at a time") {
296 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
297 // con adds a column to test
298 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
299 // con2 cannot add a new column now!
300 REQUIRE_FAIL(con2.Query("ALTER TABLE test ADD COLUMN l INTEGER"));
301 REQUIRE_NO_FAIL(con.Query("COMMIT"));
302 // after a commit, con2 can add a new column again
303 REQUIRE_NO_FAIL(con2.Query("ALTER TABLE test ADD COLUMN l INTEGER"));
304 }
305 SECTION("Can only append to newest table") {
306 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
307 // con adds a column to test
308 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
309
310 // con2 cannot append now!
311 REQUIRE_FAIL(con2.Query("INSERT INTO test (i, j) VALUES (3, 3)"));
312 // but we can delete rows!
313 REQUIRE_NO_FAIL(con2.Query("DELETE FROM test WHERE i=1"));
314
315 result = con.Query("SELECT * FROM test");
316 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
317 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
318 REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value()}));
319
320 result = con2.Query("SELECT * FROM test");
321 REQUIRE(CHECK_COLUMN(result, 0, {2}));
322 REQUIRE(CHECK_COLUMN(result, 1, {2}));
323
324 // we can also update rows
325 REQUIRE_NO_FAIL(con2.Query("UPDATE test SET j=100"));
326
327 result = con.Query("SELECT * FROM test");
328 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
329 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
330 REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value()}));
331
332 result = con2.Query("SELECT * FROM test");
333 REQUIRE(CHECK_COLUMN(result, 0, {2}));
334 REQUIRE(CHECK_COLUMN(result, 1, {100}));
335
336 REQUIRE_NO_FAIL(con.Query("COMMIT"));
337
338 result = con.Query("SELECT * FROM test");
339 REQUIRE(CHECK_COLUMN(result, 0, {2}));
340 REQUIRE(CHECK_COLUMN(result, 1, {100}));
341 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
342 }
343 SECTION("Alter table while other transaction still has pending appends") {
344 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
345 REQUIRE_NO_FAIL(con2.Query("INSERT INTO test VALUES (3, 3)"));
346
347 // now con adds a column
348 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
349
350 // cannot commit con2! conflict on append
351 REQUIRE_FAIL(con2.Query("COMMIT"));
352 }
353}
354
355TEST_CASE("Test ALTER TABLE DROP COLUMN", "[alter]") {
356 unique_ptr<QueryResult> result;
357 DuckDB db(nullptr);
358 Connection con(db);
359
360 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
361 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
362
363 SECTION("Standard DROP COLUMN") {
364 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
365
366 result = con.Query("SELECT * FROM test");
367 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
368 REQUIRE(result->names.size() == 1);
369 }
370 SECTION("Rollback of DROP COLUMN") {
371 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
372 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
373
374 result = con.Query("SELECT * FROM test");
375 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
376 REQUIRE(result->names.size() == 1);
377 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
378
379 result = con.Query("SELECT * FROM test");
380 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
381 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
382 REQUIRE(result->names.size() == 2);
383 }
384 SECTION("Cannot DROP COLUMN which has an index built on it") {
385 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test(j)"));
386 REQUIRE_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
387
388 // we can remove the column after dropping the index
389 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
390 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
391 }
392 SECTION("DROP COLUMN with check constraint on single column") {
393 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER, j INTEGER CHECK(j < 10))"));
394 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
395 result = con.Query("SELECT * FROM test2");
396 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
397 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
398
399 // we can drop a column that has a single check constraint on it
400 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN j"));
401 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (3)"));
402
403 result = con.Query("SELECT * FROM test2");
404 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
405 REQUIRE(result->names.size() == 1);
406 }
407 SECTION("DROP COLUMN with check constraint on multiple columns") {
408 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER, j INTEGER CHECK(i+j < 10))"));
409 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
410 result = con.Query("SELECT * FROM test2");
411 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
412 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
413
414 // we CANNOT drop one of the columns, because the CHECK constraint depends on both
415 REQUIRE_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN j"));
416 }
417 SECTION("DROP COLUMN with NOT NULL constraint") {
418 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER, j INTEGER NOT NULL)"));
419 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
420 result = con.Query("SELECT * FROM test2");
421 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
422 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
423
424 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN j"));
425 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (3)"));
426
427 result = con.Query("SELECT * FROM test2");
428 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
429 REQUIRE(result->names.size() == 1);
430 }
431 SECTION("DROP COLUMN with check constraint on subsequent column") {
432 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER, j INTEGER CHECK(j < 10))"));
433 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
434 result = con.Query("SELECT * FROM test2");
435 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
436 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
437
438 // we can drop a column that has a single check constraint on it
439 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN i"));
440 REQUIRE_FAIL(con.Query("INSERT INTO test2 VALUES (20)"));
441 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (3)"));
442
443 result = con.Query("SELECT * FROM test2");
444 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
445 REQUIRE(result->names.size() == 1);
446 }
447 SECTION("DROP COLUMN with NOT NULL constraint on subsequent column") {
448 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER, j INTEGER, k INTEGER NOT NULL)"));
449 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1, 11), (2, 2, 12)"));
450 result = con.Query("SELECT * FROM test2");
451 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
452 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
453 REQUIRE(CHECK_COLUMN(result, 2, {11, 12}));
454
455 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN j"));
456 REQUIRE_FAIL(con.Query("INSERT INTO test2 VALUES (3, NULL)"));
457 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (3, 13)"));
458
459 result = con.Query("SELECT * FROM test2");
460 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
461 REQUIRE(CHECK_COLUMN(result, 1, {11, 12, 13}));
462 REQUIRE(result->names.size() == 2);
463 }
464 SECTION("DROP COLUMN with index built on subsequent column") {
465 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test(j)"));
466
467 // cannot drop indexed column
468 REQUIRE_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
469 // we also cannot drop the column i (for now) because an index depends on a subsequent column
470 REQUIRE_FAIL(con.Query("ALTER TABLE test DROP COLUMN i"));
471 }
472 SECTION("DROP COLUMN from table with primary key constraint") {
473 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER PRIMARY KEY, j INTEGER)"));
474 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
475
476 // cannot drop primary key column
477 REQUIRE_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN i"));
478 // but we can drop column "i"
479 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 DROP COLUMN j"));
480 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (3)"));
481
482 result = con.Query("SELECT * FROM test2");
483 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
484 REQUIRE(result->names.size() == 1);
485 }
486 SECTION("DROP COLUMN errors") {
487 // cannot drop column which does not exist
488 REQUIRE_FAIL(con.Query("ALTER TABLE test DROP COLUMN blabla"));
489 // unless IF EXISTS is specified
490 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN IF EXISTS blabla"));
491
492 // cannot drop ALL columns of a table
493 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN i"));
494 REQUIRE_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
495 }
496}
497
498TEST_CASE("Test ALTER TABLE DROP COLUMN with multiple transactions", "[alter]") {
499 unique_ptr<QueryResult> result;
500 DuckDB db(nullptr);
501 Connection con(db), con2(db);
502
503 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
504 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
505
506 SECTION("Only one pending table alter can be active at a time") {
507 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
508 // con removes a column to test
509 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN j"));
510 // con2 cannot add a new column now!
511 REQUIRE_FAIL(con2.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
512 REQUIRE_NO_FAIL(con.Query("COMMIT"));
513 // we can add the column after the commit
514 REQUIRE_NO_FAIL(con2.Query("ALTER TABLE test ADD COLUMN k INTEGER"));
515 }
516 SECTION("Can only append to newest table") {
517 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
518 // con removes a column from test
519 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN i"));
520
521 // con2 cannot append now!
522 REQUIRE_FAIL(con2.Query("INSERT INTO test (i, j) VALUES (3, 3)"));
523 // but we can delete rows!
524 REQUIRE_NO_FAIL(con2.Query("DELETE FROM test WHERE i=1"));
525
526 result = con.Query("SELECT * FROM test");
527 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
528
529 result = con2.Query("SELECT * FROM test");
530 REQUIRE(CHECK_COLUMN(result, 0, {2}));
531 REQUIRE(CHECK_COLUMN(result, 1, {2}));
532
533 // we can also update rows
534 REQUIRE_NO_FAIL(con2.Query("UPDATE test SET j=100"));
535
536 result = con.Query("SELECT * FROM test");
537 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
538
539 result = con2.Query("SELECT * FROM test");
540 REQUIRE(CHECK_COLUMN(result, 0, {2}));
541 REQUIRE(CHECK_COLUMN(result, 1, {100}));
542
543 REQUIRE_NO_FAIL(con.Query("COMMIT"));
544
545 result = con.Query("SELECT * FROM test");
546 REQUIRE(CHECK_COLUMN(result, 0, {100}));
547 }
548 SECTION("Alter table while other transaction still has pending appends") {
549 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
550 REQUIRE_NO_FAIL(con2.Query("INSERT INTO test VALUES (3, 3)"));
551
552 // now con adds a column
553 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN i"));
554
555 // cannot commit con2! conflict on append
556 REQUIRE_FAIL(con2.Query("COMMIT"));
557 }
558 SECTION("Create index on column that has been removed by other transaction") {
559 // con2 removes a column
560 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
561 REQUIRE_NO_FAIL(con2.Query("ALTER TABLE test DROP COLUMN j"));
562
563 // now con tries to add an index to that column: this should fail
564 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON test(j"));
565 }
566}
567
568TEST_CASE("Test ALTER TABLE SET DEFAULT", "[alter]") {
569 unique_ptr<QueryResult> result;
570 DuckDB db(nullptr);
571 Connection con(db);
572
573 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
574 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
575
576 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER j SET DEFAULT 3"));
577
578 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (i) VALUES (3)"));
579 result = con.Query("SELECT * FROM test");
580 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
581 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
582
583 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER COLUMN j DROP DEFAULT"));
584 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (i) VALUES (4)"));
585 result = con.Query("SELECT * FROM test");
586 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
587 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, Value()}));
588
589 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq"));
590 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER j SET DEFAULT nextval('seq')"));
591 REQUIRE_NO_FAIL(con.Query("INSERT INTO test (i) VALUES (5), (6)"));
592 result = con.Query("SELECT * FROM test");
593 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6}));
594 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, Value(), 1, 2}));
595
596 // fail when column does not exist
597 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER blabla SET DEFAULT 3"));
598 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER blabla DROP DEFAULT"));
599}
600
601TEST_CASE("Test ALTER TABLE ALTER TYPE", "[alter]") {
602 unique_ptr<QueryResult> result;
603 DuckDB db(nullptr);
604 Connection con(db);
605
606 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
607 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
608
609 SECTION("Standard ALTER TYPE") {
610 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i SET DATA TYPE VARCHAR"));
611
612 result = con.Query("SELECT * FROM test");
613 REQUIRE(CHECK_COLUMN(result, 0, {"1", "2"}));
614 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
615 }
616 SECTION("ALTER TYPE with expression") {
617 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i TYPE BIGINT USING i+100"));
618
619 result = con.Query("SELECT * FROM test");
620 REQUIRE(CHECK_COLUMN(result, 0, {101, 102}));
621 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
622 }
623 SECTION("Rollback ALTER TYPE") {
624 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
625 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i SET DATA TYPE VARCHAR"));
626 REQUIRE_NO_FAIL(con.Query("UPDATE test SET i='hello'"));
627
628 result = con.Query("SELECT * FROM test");
629 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "hello"}));
630 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
631 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
632
633 result = con.Query("SELECT * FROM test");
634 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
635 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
636 }
637 SECTION("ALTER TYPE with transaction local data") {
638 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
639 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 3)"));
640 // not currently supported
641 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER i SET DATA TYPE BIGINT"));
642 }
643 SECTION("ALTER TYPE with expression using multiple columns") {
644 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i TYPE INTEGER USING 2*(i+j)"));
645
646 result = con.Query("SELECT * FROM test");
647 REQUIRE(CHECK_COLUMN(result, 0, {4, 8}));
648 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
649 }
650 SECTION("ALTER TYPE with NOT NULL constraint") {
651 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER NOT NULL, j INTEGER)"));
652 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
653 REQUIRE_FAIL(con.Query("INSERT INTO test2 VALUES (NULL, 4)"));
654
655 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 ALTER i SET DATA TYPE VARCHAR"));
656 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES ('hello', 3)"));
657 REQUIRE_FAIL(con.Query("INSERT INTO test2 VALUES (NULL, 4)"));
658
659 result = con.Query("SELECT * FROM test2");
660 REQUIRE(CHECK_COLUMN(result, 0, {"1", "2", "hello"}));
661 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
662 }
663 SECTION("ALTER TYPE with CHECK constraint") {
664 // we disallow ALTER TYPE on a column with a CHECK constraint
665 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER CHECK(i < 10), j INTEGER)"));
666 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
667
668 REQUIRE_FAIL(con.Query("ALTER TABLE test2 ALTER i SET DATA TYPE VARCHAR"));
669 }
670 SECTION("ALTER TYPE with UNIQUE constraint") {
671 // we disallow ALTER TYPE on a column with a UNIQUE constraint
672 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(i INTEGER UNIQUE, j INTEGER)"));
673 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 1), (2, 2)"));
674
675 REQUIRE_FAIL(con.Query("ALTER TABLE test2 ALTER i SET DATA TYPE VARCHAR"));
676 // but we CAN change the other column
677 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test2 ALTER j SET DATA TYPE VARCHAR"));
678 result = con.Query("SELECT * FROM test2");
679 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
680 REQUIRE(CHECK_COLUMN(result, 1, {"1", "2"}));
681 }
682 SECTION("ALTER TYPE with INDEX") {
683 // we disallow ALTER TYPE on a column with an index on it
684 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test(i)"));
685 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER i SET DATA TYPE VARCHAR"));
686
687 // we can alter the table after the index is dropped, however
688 REQUIRE_NO_FAIL(con.Query("DROP INDEX i_index"));
689 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i SET DATA TYPE VARCHAR"));
690 }
691 SECTION("ALTER TYPE with unknown columns") {
692 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER blabla SET TYPE VARCHAR"));
693 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER i SET TYPE VARCHAR USING blabla"));
694 // cannot use aggregates or window functions
695 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER i SET TYPE VARCHAR USING SUM(i)"));
696 REQUIRE_FAIL(con.Query("ALTER TABLE test ALTER i SET TYPE VARCHAR USING row_id() OVER ()"));
697 }
698}
699
700TEST_CASE("Test ALTER TABLE ALTER TYPE with multiple transactions", "[alter]") {
701 unique_ptr<QueryResult> result;
702 DuckDB db(nullptr);
703 Connection con(db), con2(db);
704
705 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(i INTEGER, j INTEGER)"));
706 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 1), (2, 2)"));
707
708 SECTION("Only one pending table alter can be active at a time") {
709 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
710 // con alters a column to test
711 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER j TYPE VARCHAR"));
712 // con2 cannot alter another column now!
713 REQUIRE_FAIL(con2.Query("ALTER TABLE test ALTER i TYPE VARCHAR"));
714 REQUIRE_NO_FAIL(con.Query("COMMIT"));
715 // we can alter the column after the commit
716 REQUIRE_NO_FAIL(con2.Query("ALTER TABLE test ALTER i TYPE VARCHAR"));
717 }
718 SECTION("Can only append to newest table") {
719 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
720 // con removes a column from test
721 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i TYPE VARCHAR"));
722
723 // con2 cannot append now!
724 REQUIRE_FAIL(con2.Query("INSERT INTO test (i, j) VALUES (3, 3)"));
725 // but we can delete rows!
726 REQUIRE_NO_FAIL(con2.Query("DELETE FROM test WHERE i=1"));
727
728 result = con.Query("SELECT * FROM test");
729 REQUIRE(CHECK_COLUMN(result, 0, {"1", "2"}));
730 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
731
732 result = con2.Query("SELECT * FROM test");
733 REQUIRE(CHECK_COLUMN(result, 0, {2}));
734 REQUIRE(CHECK_COLUMN(result, 1, {2}));
735
736 // we can also update rows, but updates to i will not be seen...
737 // should we check this somehow?
738 REQUIRE_NO_FAIL(con2.Query("UPDATE test SET i=1000"));
739 REQUIRE_NO_FAIL(con2.Query("UPDATE test SET j=100"));
740
741 result = con.Query("SELECT * FROM test");
742 REQUIRE(CHECK_COLUMN(result, 0, {"1", "2"}));
743 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
744
745 result = con2.Query("SELECT * FROM test");
746 REQUIRE(CHECK_COLUMN(result, 0, {1000}));
747 REQUIRE(CHECK_COLUMN(result, 1, {100}));
748
749 REQUIRE_NO_FAIL(con.Query("COMMIT"));
750
751 result = con.Query("SELECT * FROM test");
752 REQUIRE(CHECK_COLUMN(result, 0, {"2"}));
753 REQUIRE(CHECK_COLUMN(result, 1, {100}));
754 }
755 SECTION("Alter table while other transaction still has pending appends") {
756 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
757 REQUIRE_NO_FAIL(con2.Query("INSERT INTO test VALUES (3, 3)"));
758
759 // now con adds a column
760 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER i TYPE VARCHAR"));
761
762 // cannot commit con2! conflict on append
763 REQUIRE_FAIL(con2.Query("COMMIT"));
764 }
765 SECTION("Create index on column that has been altered by other transaction") {
766 // con2 removes a column
767 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
768 REQUIRE_NO_FAIL(con2.Query("ALTER TABLE test ALTER j TYPE VARCHAR"));
769
770 // now con tries to add an index to that column: this should fail
771 REQUIRE_FAIL(con.Query("CREATE INDEX i_index ON test(j"));
772 }
773}
774