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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
SET SESSION debug= '+d,skip_dd_table_access_check';
#
# Tests column with functions set as DEFAULT. Test needs debug mode
# in order to be able to check for the changes in DD.
#
#
# CREATE TABLE - column with func as DEFAULT then test DROP column.
#
CREATE TABLE t1 (i int,a TIMESTAMP DEFAULT CURRENT_TIMESTAMP, b JSON DEFAULT (JSON_OBJECT("key", i)));
SELECT CONCAT(t.name, ".", c.name) as col_name,
c.default_option, c.default_value_utf8
FROM mysql.tables AS t JOIN mysql.columns as c ON t.id = c.table_id
WHERE t.name = 't1' ORDER BY c.id;
col_name default_option default_value_utf8
t1.i NULL NULL
t1.a CURRENT_TIMESTAMP CURRENT_TIMESTAMP
t1.b json_object(_utf8mb4'key',`i`) json_object(_utf8mb4\'key\',`i`)
t1.DB_ROW_ID NULL NULL
t1.DB_TRX_ID NULL NULL
t1.DB_ROLL_PTR NULL NULL
SET timestamp= 1038401397;
INSERT INTO t1(i) VALUES (1);
INSERT INTO t1(i, b) VALUES (2, DEFAULT);
INSERT INTO t1(i, b) VALUES (3, JSON_OBJECT("key", 3));
SELECT * FROM t1;
i a b
1 2002-11-27 15:49:57 {"key": 1}
2 2002-11-27 15:49:57 {"key": 2}
3 2002-11-27 15:49:57 {"key": 3}
ALTER TABLE t1 DROP COLUMN b;
SELECT CONCAT(t.name, ".", c.name) as col_name,
c.default_option, c.default_value_utf8
FROM mysql.tables AS t JOIN mysql.columns as c ON t.id = c.table_id
WHERE t.name = 't1' ORDER BY c.id;
col_name default_option default_value_utf8
t1.i NULL NULL
t1.a CURRENT_TIMESTAMP CURRENT_TIMESTAMP
t1.DB_ROW_ID NULL NULL
t1.DB_TRX_ID NULL NULL
t1.DB_ROLL_PTR NULL NULL
t1.!hidden!_dropped_v1_p5_b NULL NULL
DROP TABLE t1;
#
# Test ALTER table ADD column with DEFAULT
#
CREATE TABLE t1 (i int);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN b JSON DEFAULT (JSON_OBJECT("key",i));
SELECT CONCAT(t.name, ".", c.name) as col_name,
c.default_option, c.default_value_utf8
FROM mysql.tables AS t JOIN mysql.columns as c ON t.id = c.table_id
WHERE t.name = 't1' ORDER BY c.id;
col_name default_option default_value_utf8
t1.i NULL NULL
t1.b json_object(_utf8mb4'key',`i`) json_object(_utf8mb4\'key\',`i`)
t1.DB_ROW_ID NULL NULL
t1.DB_TRX_ID NULL NULL
t1.DB_ROLL_PTR NULL NULL
INSERT INTO t1(i) VALUES (3);
INSERT INTO t1(i, b) VALUES (4, DEFAULT);
INSERT INTO t1(i, b) VALUES (5, JSON_OBJECT("key", 5));
SELECT * FROM t1;
i b
1 {"key": 1}
2 {"key": 2}
3 {"key": 3}
4 {"key": 4}
5 {"key": 5}
DROP TABLE t1;
#
# Test ALTER table ADD column with DEFAULT then ALTER TABLE SET DEFAULT func
#
CREATE TABLE t1 (i int);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN b JSON;
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT (JSON_OBJECT("key",i));
SELECT CONCAT(t.name, ".", c.name) as col_name,
c.default_option, c.default_value_utf8
FROM mysql.tables AS t JOIN mysql.columns as c ON t.id = c.table_id
WHERE t.name = 't1' ORDER BY c.id;
col_name default_option default_value_utf8
t1.i NULL NULL
t1.b json_object(_utf8mb4'key',`i`) json_object(_utf8mb4\'key\',`i`)
t1.DB_ROW_ID NULL NULL
t1.DB_TRX_ID NULL NULL
t1.DB_ROLL_PTR NULL NULL
INSERT INTO t1(i) VALUES (3);
INSERT INTO t1(i, b) VALUES (4, DEFAULT);
INSERT INTO t1(i, b) VALUES (5, JSON_OBJECT("key", 5));
SELECT * FROM t1;
i b
1 NULL
2 NULL
3 {"key": 3}
4 {"key": 4}
5 {"key": 5}
DROP TABLE t1;
#
# Test CREATE TABLE with column without default then SET a DEFAULT.
# ALSO TEST DROP DEFAULT
#
CREATE TABLE t1 (i int, b JSON);
INSERT INTO t1(i) VALUES (1),(2);
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT (JSON_OBJECT("key",i));
INSERT INTO t1(i) VALUES (3);
INSERT INTO t1(i, b) VALUES (4, DEFAULT);
INSERT INTO t1(i, b) VALUES (5, JSON_OBJECT("key", 5));
ALTER TABLE t1 ALTER COLUMN b DROP DEFAULT;
SELECT CONCAT(t.name, ".", c.name) as col_name,
c.default_option, c.default_value_utf8
FROM mysql.tables AS t JOIN mysql.columns as c ON t.id = c.table_id
WHERE t.name = 't1' ORDER BY c.id;
col_name default_option default_value_utf8
t1.i NULL NULL
t1.b NULL NULL
t1.DB_ROW_ID NULL NULL
t1.DB_TRX_ID NULL NULL
t1.DB_ROLL_PTR NULL NULL
INSERT INTO t1(i, b) VALUES (6, NULL);
SELECT * FROM t1;
i b
1 NULL
2 NULL
3 {"key": 3}
4 {"key": 4}
5 {"key": 5}
6 NULL
DROP TABLE t1;
#
# Test SHOW COLUMNS for table with NOT NULL DEFAULT (expr).
#
CREATE TABLE t1 (f1 INT NOT NULL DEFAULT (32));
SHOW COLUMNS FROM t1;
Field Type Null Key Default Extra
f1 int NO 32 DEFAULT_GENERATED
SELECT CONCAT(t.name, ".", c.name) as col_name,
c.default_option, c.default_value_utf8
FROM mysql.tables AS t JOIN mysql.columns as c ON t.id = c.table_id
WHERE t.name = 't1' ORDER BY c.id;
col_name default_option default_value_utf8
t1.f1 32 32
t1.DB_ROW_ID NULL NULL
t1.DB_TRX_ID NULL NULL
t1.DB_ROLL_PTR NULL NULL
DROP TABLE t1;
SET SESSION debug= '-d,skip_dd_table_access_check';
|
