Debian Codegraph @

mysql-test/suite/vcol/r/vcol_sql_mode_upgrade.result

raw
1 call mtr.add_suppression("Table rebuild required");
2 #
3 # Opening a Maria-10.2.26 table with a stored VARCHAR column
4 #
5 # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR
6 SHOW CREATE TABLE t1;
7 Table Create Table
8 t1 CREATE TABLE `t1` (
9 `a` char(5) DEFAULT NULL,
10 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED
11 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
12 Warnings:
13 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
14 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
15 SHOW CREATE TABLE t1;
16 Table Create Table
17 t1 CREATE TABLE `t1` (
18 `a` char(5) DEFAULT NULL,
19 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED
20 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
21 FLUSH TABLES;
22 SHOW CREATE TABLE t1;
23 Table Create Table
24 t1 CREATE TABLE `t1` (
25 `a` char(5) DEFAULT NULL,
26 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED
27 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
28 Warnings:
29 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
30 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
31 FLUSH TABLES;
32 SELECT * FROM t1;
33 a v
34 1 1
35 2 2
36 3 3
37 Warnings:
38 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
39 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
40 SELECT * FROM t1;
41 a v
42 1 1
43 2 2
44 3 3
45 FLUSH TABLES;
46 SELECT * FROM t1;
47 a v
48 1 1
49 2 2
50 3 3
51 Warnings:
52 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
53 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
54 CREATE TABLE t2 LIKE t1;
55 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
56 FLUSH TABLES;
57 CREATE TABLE t2 LIKE t1;
58 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
59 SHOW CREATE TABLE t1;
60 Table Create Table
61 t1 CREATE TABLE `t1` (
62 `a` char(5) DEFAULT NULL,
63 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED
64 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
65 Warnings:
66 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
67 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
68 ALTER TABLE t1 ADD b INT DEFAULT a;
69 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
70 SHOW CREATE TABLE t1;
71 Table Create Table
72 t1 CREATE TABLE `t1` (
73 `a` char(5) DEFAULT NULL,
74 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED
75 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
76 Warnings:
77 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
78 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
79 SELECT * FROM t1;
80 a v
81 1 1
82 2 2
83 3 3
84 FLUSH TABLES;
85 ALTER TABLE t1 ADD c INT DEFAULT a;
86 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
87 SELECT * FROM t1;
88 a v
89 1 1
90 2 2
91 3 3
92 Warnings:
93 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
94 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
95 DROP TABLE t1;
96 #
97 # Fixing a Maria-10.2.26 table with a stored VARCHAR column
98 #
99 # Fixing by dropping the generated stored column
100 # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR
101 ALTER TABLE t1 DROP v;
102 Warnings:
103 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
104 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
105 SELECT * FROM t1;
106 a
107 1
108 2
109 3
110 DROP TABLE t1;
111 # Fixing by altering the generation expression of the stored column
112 # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR
113 SHOW CREATE TABLE t1;
114 Table Create Table
115 t1 CREATE TABLE `t1` (
116 `a` char(5) DEFAULT NULL,
117 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED
118 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
119 Warnings:
120 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
121 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
122 ALTER TABLE t1 MODIFY v VARCHAR(5) AS (RTRIM(a)) PERSISTENT;
123 SHOW CREATE TABLE t1;
124 Table Create Table
125 t1 CREATE TABLE `t1` (
126 `a` char(5) DEFAULT NULL,
127 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) STORED
128 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
129 DROP TABLE t1;
130 #
131 # Opening a Maria-10.2.26 table with a virtual VARCHAR column
132 #
133 # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
134 SHOW CREATE TABLE t1;
135 Table Create Table
136 t1 CREATE TABLE `t1` (
137 `a` char(5) DEFAULT NULL,
138 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
139 KEY `v` (`v`)
140 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
141 Warnings:
142 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
143 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
144 SHOW CREATE TABLE t1;
145 Table Create Table
146 t1 CREATE TABLE `t1` (
147 `a` char(5) DEFAULT NULL,
148 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
149 KEY `v` (`v`)
150 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
151 FLUSH TABLES;
152 SHOW CREATE TABLE t1;
153 Table Create Table
154 t1 CREATE TABLE `t1` (
155 `a` char(5) DEFAULT NULL,
156 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
157 KEY `v` (`v`)
158 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
159 Warnings:
160 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
161 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
162 FLUSH TABLES;
163 SELECT * FROM t1;
164 a v
165 1 1
166 2 2
167 3 3
168 Warnings:
169 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
170 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
171 SELECT * FROM t1;
172 a v
173 1 1
174 2 2
175 3 3
176 FLUSH TABLES;
177 SELECT * FROM t1;
178 a v
179 1 1
180 2 2
181 3 3
182 Warnings:
183 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
184 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
185 CREATE TABLE t2 LIKE t1;
186 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
187 FLUSH TABLES;
188 CREATE TABLE t2 LIKE t1;
189 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
190 SHOW CREATE TABLE t1;
191 Table Create Table
192 t1 CREATE TABLE `t1` (
193 `a` char(5) DEFAULT NULL,
194 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
195 KEY `v` (`v`)
196 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
197 Warnings:
198 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
199 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
200 ALTER TABLE t1 ADD b INT DEFAULT a;
201 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
202 FLUSH TABLES;
203 ALTER TABLE t1 ADD c INT DEFAULT a;
204 ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
205 SELECT * FROM t1;
206 a v
207 1 1
208 2 2
209 3 3
210 Warnings:
211 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
212 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
213 DROP TABLE t1;
214 #
215 # Fixing a Maria-10.2.26 table with a virtual VARCHAR column
216 #
217 # Fixing by dropping the virtual column
218 # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
219 SHOW CREATE TABLE t1;
220 Table Create Table
221 t1 CREATE TABLE `t1` (
222 `a` char(5) DEFAULT NULL,
223 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
224 KEY `v` (`v`)
225 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
226 Warnings:
227 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
228 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
229 ALTER TABLE t1 DROP v;
230 SELECT * FROM t1;
231 a
232 1
233 2
234 3
235 DROP TABLE t1;
236 # Fixing by dropping a key on a virtual column, using ALTER TABLE
237 # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
238 SHOW CREATE TABLE t1;
239 Table Create Table
240 t1 CREATE TABLE `t1` (
241 `a` char(5) DEFAULT NULL,
242 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
243 KEY `v` (`v`)
244 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
245 Warnings:
246 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
247 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
248 ALTER TABLE t1 DROP KEY v;
249 SHOW CREATE TABLE t1;
250 Table Create Table
251 t1 CREATE TABLE `t1` (
252 `a` char(5) DEFAULT NULL,
253 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL
254 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
255 SELECT * FROM t1;
256 a v
257 1 1
258 2 2
259 3 3
260 DROP TABLE t1;
261 # Fixing by dropping a key on a virtual column, using DROP INDEX
262 # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
263 DROP INDEX v ON t1;
264 Warnings:
265 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
266 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
267 SHOW CREATE TABLE t1;
268 Table Create Table
269 t1 CREATE TABLE `t1` (
270 `a` char(5) DEFAULT NULL,
271 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL
272 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
273 SELECT * FROM t1;
274 a v
275 1 1
276 2 2
277 3 3
278 DROP TABLE t1;
279 # Fixing by altering the generation expression of a virtual column
280 # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
281 SHOW CREATE TABLE t1;
282 Table Create Table
283 t1 CREATE TABLE `t1` (
284 `a` char(5) DEFAULT NULL,
285 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL,
286 KEY `v` (`v`)
287 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
288 Warnings:
289 Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
290 Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
291 ALTER TABLE t1 MODIFY v VARCHAR(5) AS(RTRIM(a)) VIRTUAL;
292 SHOW CREATE TABLE t1;
293 Table Create Table
294 t1 CREATE TABLE `t1` (
295 `a` char(5) DEFAULT NULL,
296 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL,
297 KEY `v` (`v`)
298 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
299 SELECT * FROM t1;
300 a v
301 1 1
302 2 2
303 3 3
304 CREATE TABLE t2 LIKE t1;
305 SHOW CREATE TABLE t2;
306 Table Create Table
307 t2 CREATE TABLE `t2` (
308 `a` char(5) DEFAULT NULL,
309 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL,
310 KEY `v` (`v`)
311 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
312 DROP TABLE t2;
313 DROP TABLE t1;
314 #
315 # Upgrading a Maria-10.2.26 table with a stored column
316 #
317 # Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR
318 CHECK TABLE t1 FOR UPGRADE;
319 Table Op Msg_type Msg_text
320 test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
321 test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
322 test.t1 check status OK
323 FLUSH TABLES;
324 CHECK TABLE t1 FOR UPGRADE;
325 Table Op Msg_type Msg_text
326 test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
327 test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
328 test.t1 check status OK
329 DROP TABLE t1;
330 #
331 # Upgrading a Maria-10.2.26 table with a virtual column
332 #
333 # Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR
334 CHECK TABLE t1 FOR UPGRADE;
335 Table Op Msg_type Msg_text
336 test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
337 test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
338 test.t1 check status OK
339 FLUSH TABLES;
340 CHECK TABLE t1 FOR UPGRADE;
341 Table Op Msg_type Msg_text
342 test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
343 test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
344 test.t1 check status OK
345 DROP TABLE t1;