1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
# name: test/sql/alter/alter_col/test_drop_not_null.test
# description: Test ALTER TABLE ALTER COLUMN column_name DROP NOT NULL
# group: [alter_col]
# Happy path
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE test(i INTEGER, j INTEGER NOT NULL)
statement ok
INSERT INTO test VALUES (1, 1), (2, 2)
statement error
INSERT INTO test VALUES (3, NULL)
----
query II
SELECT * FROM test
----
1 1
2 2
statement ok
ALTER TABLE test ALTER COLUMN j DROP NOT NULL
statement ok
INSERT INTO test VALUES (3, NULL)
query II
SELECT * FROM test
----
1 1
2 2
3 NULL
# Negative path
statement ok
CREATE TABLE test2(i INTEGER, j INTEGER)
statement ok
INSERT INTO test2 VALUES (1, 1), (2, 2)
statement ok
ALTER TABLE test2 ALTER COLUMN j DROP NOT NULL
statement ok
INSERT INTO test VALUES (3, NULL)
# Drop not null with generated column presented
statement ok
DROP TABLE IF EXISTS test
statement ok
CREATE TABLE test(i AS (1), j INTEGER NOT NULL)
statement ok
INSERT INTO test VALUES (1), (2)
statement error
INSERT INTO test VALUES (NULL)
----
query II
SELECT * FROM test
----
1 1
1 2
statement ok
ALTER TABLE test ALTER COLUMN i DROP NOT NULL
statement ok
ALTER TABLE test ALTER COLUMN j DROP NOT NULL
statement ok
INSERT INTO test VALUES (NULL)
query II
SELECT * FROM test
----
1 1
1 2
1 NULL
# test that DROP NOT NULL preserves temporary flag
statement ok
CREATE TEMPORARY TABLE temp_drop_not_null_test(x INTEGER NOT NULL)
query TTI
SELECT table_name, database_name, temporary FROM duckdb_tables() WHERE table_name='temp_drop_not_null_test'
----
temp_drop_not_null_test temp true
statement ok
ALTER TABLE temp_drop_not_null_test ALTER COLUMN x DROP NOT NULL
query TTI
SELECT table_name, database_name, temporary FROM duckdb_tables() WHERE table_name='temp_drop_not_null_test'
----
temp_drop_not_null_test temp true
|
