Debian Codegraph @

mysql-test/suite/gcol/r/gcol_bugfixes.result

raw
1 # Bug#21230709: Alter table statement fails with division by zero
2 CREATE TABLE t1 (
3 col1 INTEGER NOT NULL,
4 col2 INTEGER NOT NULL,
5 col3 INTEGER NOT NULL,
6 gcol1 INTEGER GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
7 col4 INTEGER DEFAULT NULL,
8 col5 INTEGER DEFAULT NULL,
9 col6 INTEGER DEFAULT NULL,
10 col7 INTEGER DEFAULT NULL,
11 col8 INTEGER DEFAULT NULL,
12 col9 INTEGER DEFAULT NULL,
13 col10 INTEGER DEFAULT NULL,
14 col11 INTEGER DEFAULT NULL,
15 col12 INTEGER DEFAULT NULL,
16 col13 INTEGER DEFAULT NULL,
17 col14 INTEGER DEFAULT NULL,
18 col15 INTEGER DEFAULT NULL,
19 col16 INTEGER DEFAULT NULL,
20 col17 INTEGER DEFAULT NULL,
21 col18 INTEGER DEFAULT NULL,
22 col19 INTEGER DEFAULT NULL,
23 col20 INTEGER DEFAULT NULL,
24 col21 INTEGER DEFAULT NULL,
25 col22 INTEGER DEFAULT NULL,
26 col23 INTEGER DEFAULT NULL,
27 col24 INTEGER DEFAULT NULL,
28 col25 INTEGER DEFAULT NULL,
29 col26 INTEGER DEFAULT NULL,
30 col27 INTEGER DEFAULT NULL,
31 col28 INTEGER DEFAULT NULL,
32 col29 INTEGER DEFAULT NULL,
33 col30 INTEGER DEFAULT NULL,
34 col31 INTEGER DEFAULT NULL,
35 col32 INTEGER DEFAULT NULL,
36 col33 INTEGER DEFAULT NULL,
37 gcol2 INTEGER GENERATED ALWAYS AS (col2 + col2) VIRTUAL,
38 gcol3 INTEGER GENERATED ALWAYS AS (gcol2 / gcol2) VIRTUAL,
39 PRIMARY KEY (col1),
40 KEY idx1 (gcol1)
41 ) engine=innodb;
42 INSERT INTO t1 (col1, col2, col3)
43 VALUES (0,1,2), (1,2,3), (2,3,4), (3,4,5), (4,5,6);
44 FLUSH TABLE t1;
45 ALTER TABLE t1 ADD COLUMN extra INTEGER;
46 DROP TABLE t1;
47 #
48 # Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED
49 #
50 CREATE TABLE t1 (
51 c_blob BLOB,
52 c_blob_key BLOB GENERATED ALWAYS AS (REPEAT(c_blob,15)) STORED,
53 KEY (c_blob_key(200))
54 );
55 INSERT INTO t1 (c_blob) VALUES ('xceks');
56 DROP TABLE t1;
57 #
58 # Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED.
59 #
60 CREATE TABLE c (
61 pk INTEGER AUTO_INCREMENT,
62 col_int_nokey INTEGER /*! NULL */,
63 col_int_key INTEGER GENERATED ALWAYS AS
64 (col_int_nokey + col_int_nokey) VIRTUAL not null,
65 col_date_nokey DATE /*! NULL */,
66 col_date_key DATE GENERATED ALWAYS AS
67 (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null,
68 col_datetime_nokey DATETIME /*! NULL */,
69 col_time_nokey TIME /*! NULL */,
70 col_datetime_key DATETIME GENERATED ALWAYS AS
71 (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
72 col_time_key TIME GENERATED ALWAYS AS
73 (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
74 col_varchar_nokey VARCHAR(1) /*! NULL */,
75 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
76 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null,
77 PRIMARY KEY (pk),
78 UNIQUE KEY (col_int_key),
79 UNIQUE KEY (col_varchar_key),
80 UNIQUE KEY (col_date_key),
81 KEY (col_time_key),
82 KEY (col_datetime_key),
83 UNIQUE KEY (col_int_key, col_varchar_key),
84 KEY (col_int_key, col_int_nokey),
85 KEY(col_int_key,col_date_key),
86 KEY(col_int_key, col_time_key),
87 KEY(col_int_key, col_datetime_key),
88 UNIQUE
89 KEY(col_date_key,col_time_key,col_datetime_key),
90 UNIQUE KEY (col_varchar_key, col_varchar_nokey),
91 UNIQUE KEY (col_int_key, col_varchar_key,
92 col_date_key, col_time_key, col_datetime_key)
93 ) ENGINE=INNODB;
94 INSERT /*! IGNORE */ INTO c (
95 col_int_nokey,
96 col_date_nokey,
97 col_time_nokey,
98 col_datetime_nokey,
99 col_varchar_nokey
100 ) VALUES (7, '2004-04-09', '14:03:03.042673',
101 '2001-11-28 00:50:27.051028', 'c'),(1, '2006-05-13', '01:46:09.016386',
102 '2007-10-09 19:53:04.008332', NULL);
103 Warnings:
104 Warning 1048 Column 'col_varchar_key' cannot be null
105 CREATE TABLE bb (
106 pk INTEGER AUTO_INCREMENT,
107 col_int_nokey INTEGER /*! NULL */,
108 col_int_key INTEGER GENERATED ALWAYS AS
109 (col_int_nokey + col_int_nokey) VIRTUAL not null,
110 col_date_nokey DATE /*! NULL */,
111 col_date_key DATE GENERATED ALWAYS AS
112 (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null,
113 col_datetime_nokey DATETIME /*! NULL */,
114 col_time_nokey TIME /*! NULL */,
115 col_datetime_key DATETIME GENERATED ALWAYS AS
116 (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
117 col_time_key TIME GENERATED ALWAYS AS
118 (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
119 col_varchar_nokey VARCHAR(1) /*! NULL */,
120 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
121 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null,
122 PRIMARY KEY (pk),
123 UNIQUE KEY (col_int_key),
124 UNIQUE KEY (col_varchar_key),
125 UNIQUE KEY (col_date_key),
126 KEY (col_time_key),
127 KEY (col_datetime_key),
128 UNIQUE KEY (col_int_key, col_varchar_key),
129 KEY (col_int_key, col_int_nokey),
130 KEY(col_int_key,col_date_key),
131 KEY(col_int_key, col_time_key),
132 KEY(col_int_key, col_datetime_key),
133 UNIQUE
134 KEY(col_date_key,col_time_key,col_datetime_key),
135 UNIQUE KEY (col_varchar_key, col_varchar_nokey),
136 UNIQUE KEY (col_int_key, col_varchar_key,
137 col_date_key, col_time_key, col_datetime_key)
138 ) AUTO_INCREMENT=10 ENGINE=INNODB;
139 INSERT /*! IGNORE */ INTO bb (
140 col_int_nokey,
141 col_date_nokey,
142 col_time_nokey,
143 col_datetime_nokey,
144 col_varchar_nokey
145 ) VALUES (0, '2003-08-04', '01:48:05.048577',
146 '2006-11-03 00:00:00', 'p'),(2, '2007-11-06', '00:00:00', '2009-11-26 19:28:11.005115', 'n');
147 CREATE TABLE cc (
148 pk INTEGER AUTO_INCREMENT,
149 col_int_nokey INTEGER /*! NULL */,
150 col_int_key INTEGER GENERATED ALWAYS AS
151 (col_int_nokey + col_int_nokey) VIRTUAL not null,
152 col_date_nokey DATE /*! NULL */,
153 col_date_key DATE GENERATED ALWAYS AS
154 (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null,
155 col_datetime_nokey DATETIME /*! NULL */,
156 col_time_nokey TIME /*! NULL */,
157 col_datetime_key DATETIME GENERATED ALWAYS AS
158 (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
159 col_time_key TIME GENERATED ALWAYS AS
160 (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
161 col_varchar_nokey VARCHAR(1) /*! NULL */,
162 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
163 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null,
164 PRIMARY KEY (pk),
165 UNIQUE KEY (col_int_key),
166 UNIQUE KEY (col_varchar_key),
167 UNIQUE KEY (col_date_key),
168 KEY (col_time_key),
169 KEY (col_datetime_key),
170 UNIQUE KEY (col_int_key, col_varchar_key),
171 KEY (col_int_key, col_int_nokey),
172 KEY(col_int_key,col_date_key),
173 KEY(col_int_key, col_time_key),
174 KEY(col_int_key, col_datetime_key),
175 UNIQUE
176 KEY(col_date_key,col_time_key,col_datetime_key),
177 UNIQUE KEY (col_varchar_key, col_varchar_nokey),
178 UNIQUE KEY (col_int_key, col_varchar_key,
179 col_date_key, col_time_key, col_datetime_key)
180 ) AUTO_INCREMENT=10 ENGINE=INNODB;
181 INSERT /*! IGNORE */ INTO cc (
182 col_int_nokey,
183 col_date_nokey,
184 col_time_nokey,
185 col_datetime_nokey,
186 col_varchar_nokey
187 ) VALUES (172, '2009-04-23', '00:00:00', '2000-12-07 10:17:40.013275', 'h'),(NULL, '2002-10-06', '00:50:49.017545', NULL, 'm');
188 Warnings:
189 Warning 1048 Column 'col_int_key' cannot be null
190 Warning 1048 Column 'col_datetime_key' cannot be null
191 Warning 1048 Column 'col_time_key' cannot be null
192 EXPLAIN SELECT
193 gp1 . col_datetime_key AS g1
194 FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . col_datetime_key <> gp1 .
195 col_time_nokey )
196 WHERE
197 gp1 . col_varchar_nokey IN
198 (
199 SELECT
200 DISTINCT p1 . col_varchar_key AS p1
201 FROM bb AS p1 LEFT JOIN bb AS p2
202 ON ( p1 . col_int_key = p2 . pk )
203 )
204 AND gp1 . col_varchar_nokey = 'b'
205 HAVING g1 > 6;
206 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
207 1 SIMPLE gp1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
208 1 SIMPLE p1 NULL const col_varchar_key,col_varchar_key_2 col_varchar_key 10 const 1 100.00 NULL
209 1 SIMPLE p2 NULL eq_ref PRIMARY PRIMARY 4 test.p1.col_int_key 1 100.00 Using index; FirstMatch(gp1)
210 1 SIMPLE gp2 NULL index NULL col_datetime_key 5 NULL 2 100.00 Using where; Using index; Using join buffer (hash join)
211 Warnings:
212 Warning 1292 Incorrect datetime value: '6' for column 'col_datetime_key' at row 1
213 Note 1003 /* select#1 */ select `test`.`gp1`.`col_datetime_key` AS `g1` from `test`.`cc` `gp1` left join `test`.`c` `gp2` on((`test`.`gp2`.`col_datetime_key` <> cast(`test`.`gp1`.`col_time_nokey` as datetime))) semi join (`test`.`bb` `p1` left join `test`.`bb` `p2` on((`test`.`p1`.`col_int_key` = `test`.`p2`.`pk`))) where ((`test`.`gp1`.`col_varchar_nokey` = 'b') and ('b' = `test`.`p1`.`col_varchar_key`)) having (`g1` > 6)
214 SELECT
215 gp1 . col_datetime_key AS g1
216 FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . col_datetime_key <> gp1 .
217 col_time_nokey )
218 WHERE
219 gp1 . col_varchar_nokey IN
220 (
221 SELECT
222 DISTINCT p1 . col_varchar_key AS p1
223 FROM bb AS p1 LEFT JOIN bb AS p2
224 ON ( p1 . col_int_key = p2 . pk )
225 )
226 AND gp1 . col_varchar_nokey = 'b'
227 HAVING g1 > 6;
228 g1
229 Warnings:
230 Warning 1292 Incorrect datetime value: '6' for column 'col_datetime_key' at row 1
231 DROP TABLE bb, c, cc;
232 # Bug#21284646: Assertion !(table || table->read_set || bitmap_is_set())
233 CREATE TABLE c (
234 pk INTEGER AUTO_INCREMENT,
235 col_int_nokey INTEGER NOT NULL,
236 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL not null,
237 col_date_nokey DATE NOT NULL,
238 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null,
239 col_datetime_nokey DATETIME NOT NULL,
240 col_time_nokey TIME NOT NULL,
241 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
242 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null,
243 col_varchar_nokey VARCHAR(1) NOT NULL,
244 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null,
245 PRIMARY KEY (pk,col_int_nokey),
246 UNIQUE KEY (col_int_key),
247 UNIQUE KEY (col_varchar_key),
248 UNIQUE KEY (col_date_key),
249 KEY (col_time_key),
250 KEY (col_datetime_key),
251 UNIQUE KEY (col_int_key, col_varchar_key),
252 KEY (col_int_key, col_int_nokey),
253 KEY(col_int_key,col_date_key),
254 KEY(col_int_key, col_time_key),
255 KEY(col_int_key, col_datetime_key),
256 UNIQUE KEY (col_date_key,col_time_key,col_datetime_key),
257 UNIQUE KEY (col_varchar_key, col_varchar_nokey),
258 UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key)
259 ) ENGINE=INNODB;
260 INSERT INTO c (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) VALUES
261 (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
262 (8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
263 (9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
264 (6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
265 (2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'),
266 (4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r');
267 ANALYZE TABLE c;
268 Table Op Msg_type Msg_text
269 test.c analyze status OK
270 explain SELECT COUNT(DISTINCT col_varchar_key) AS x
271 FROM c
272 WHERE col_varchar_key IN ('rr', 'rr') OR
273 col_int_nokey <> 9 AND
274 pk >= 8
275 HAVING x > '2000-02-06'
276 ORDER BY col_time_nokey, pk;
277 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
278 1 SIMPLE c NULL index_merge PRIMARY,col_varchar_key,col_int_key_2,col_varchar_key_2,col_int_key_7 col_varchar_key,PRIMARY 10,4 NULL 2 100.00 Using sort_union(col_varchar_key,PRIMARY); Using where
279 Warnings:
280 Note 1003 /* select#1 */ select count(distinct `test`.`c`.`col_varchar_key`) AS `x` from `test`.`c` where ((`test`.`c`.`col_varchar_key` in ('rr','rr')) or ((`test`.`c`.`col_int_nokey` <> 9) and (`test`.`c`.`pk` >= 8))) having (`x` > '2000-02-06')
281 SELECT COUNT(DISTINCT col_varchar_key) AS x
282 FROM c
283 WHERE col_varchar_key IN ('rr', 'rr') OR
284 col_int_nokey <> 9 AND
285 pk >= 8
286 HAVING x > '2000-02-06'
287 ORDER BY col_time_nokey, pk;
288 x
289 Warnings:
290 Warning 1292 Truncated incorrect DOUBLE value: '2000-02-06'
291 DROP TABLE c;
292 # Bug#21341044: Conditional jump at sort_param::make_sort_key
293 CREATE TABLE t1 (
294 pk INTEGER AUTO_INCREMENT,
295 col_int_nokey INTEGER /*! NULL */,
296 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
297 col_blob_nokey BLOB /*! NULL */,
298 col_blob_key BLOB GENERATED ALWAYS AS (REPEAT(col_blob_nokey,15)) VIRTUAL,
299 col_longblob_nokey LONGBLOB /*! NULL */,
300 col_longtext_nokey LONGTEXT /*! NULL */,
301 col_longblob_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 20)) VIRTUAL,
302 col_longtext_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 18)) VIRTUAL,
303 col_text_nokey TEXT /*! NULL */,
304 col_text_key TEXT GENERATED ALWAYS AS (REPEAT(col_text_nokey, 30)) VIRTUAL,
305 PRIMARY KEY (pk),
306 KEY (col_int_key),
307 KEY (col_text_key(50)),
308 KEY (col_blob_key(200)),
309 KEY (col_longtext_key(200)),
310 KEY (col_longblob_key(200)),
311 KEY (col_int_key, col_text_key(100)),
312 KEY (col_int_key, col_longtext_key(100)),
313 KEY (col_int_key, col_blob_key(100)),
314 KEY (col_int_key, col_longblob_key(100)),
315 KEY (col_longtext_key(10), col_longblob_key(100)),
316 KEY (col_int_key, col_text_key(10), col_blob_key(100), col_longtext_key(50), col_longblob_key(50))
317 ) engine=innodb;
318 INSERT INTO t1 (col_int_nokey,col_blob_nokey,col_longtext_nokey,col_longblob_nokey,col_text_nokey)
319 VALUES
320 (0, 'ijcszxw', 'ijcszxw', 'ijcszxw', 'ijcszxw'),
321 (5, 'jcszxwb', 'jcszxwb', 'jcszxwb', 'jcszxwb'),
322 (4, 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv'),
323 (3, 'szxw', 'szxw', 'szxw', 'szxw'),
324 (7, 'zxwb', 'zxwb', 'zxwb', 'zxwb'),
325 (42, 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky'),
326 (142, 'wbjj', 'wbjj', 'wbjj', 'wbjj'),
327 (5, 'bjjv', 'bjjv', 'bjjv', 'bjjv'),
328 (0, 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu'),
329 (3, 'j', 'j', 'j', 'j');
330 SELECT alias1.pk AS field1
331 FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2
332 ON alias1.col_int_key = alias2.col_int_key
333 WHERE alias2.col_int_key BETWEEN 8 AND (8 + 1 ) OR
334 alias2.col_int_key BETWEEN 8 AND (8 + 5 ) AND
335 alias2.col_int_key != 20 OR
336 alias2.col_int_key IN (8, 5, 8) AND
337 alias2.col_int_key >= 0 AND
338 alias2.col_int_key <= ( 8 + 75 ) AND
339 alias1.pk IS NOT NULL
340 ORDER BY field1;
341 field1
342 2
343 2
344 3
345 8
346 8
347 DROP TABLE t1;
348 # bug#21487651: gcols: memory leak after failed alter table
349 CREATE TABLE t(a int);
350 ALTER TABLE t ADD COLUMN b int GENERATED ALWAYS AS (
351 date_sub(a,interval a month)) VIRTUAL;
352 ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a));
353 ERROR HY000: Invalid use of group function
354 DROP TABLE t;
355 #
356 # Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
357 # (II)
358 #
359 CREATE TABLE t1( a INT ) ENGINE = INNODB;
360 INSERT INTO t1( a ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );
361 ALTER TABLE t1 ADD COLUMN b INT GENERATED ALWAYS AS (a - 1) STORED;
362 ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS (b + 1) VIRTUAL;
363 # Used to cause valgrind warning.
364 ALTER TABLE t1 ADD INDEX( c );
365 ANALYZE TABLE t1;
366 Table Op Msg_type Msg_text
367 test.t1 analyze status OK
368 # Make sure the index is correct. That's kinda important.
369 EXPLAIN
370 SELECT c FROM t1;
371 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
372 1 SIMPLE t1 NULL index NULL c 5 NULL 5 100.00 Using index
373 Warnings:
374 Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
375 SELECT c FROM t1;
376 c
377 1
378 2
379 3
380 4
381 5
382 DROP TABLE t1;
383 #
384 # Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED.
385 #
386 CREATE TABLE C (
387 col_int_1 INT,
388 col_int_2 INT GENERATED ALWAYS AS (col_int_1 + col_int_1) STORED,
389 col_int_3 INT GENERATED ALWAYS AS (col_int_2 + col_int_1) VIRTUAL
390 );
391 CREATE ALGORITHM=TEMPTABLE VIEW v1 AS
392 SELECT
393 col_int_2 AS field1, col_int_2 AS field2,
394 col_int_3 AS field3, col_int_3 AS field4
395 FROM C;
396 SELECT * FROM v1;
397 field1 field2 field3 field4
398 DROP TABLE C;
399 DROP VIEW v1;
400 #
401 # Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
402 #
403 CREATE TABLE t (a INT);
404 CREATE TABLE v (
405 a INT,
406 c INT,
407 b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL,
408 KEY(c,b(1)));
409 INSERT INTO v (a,c) VALUES (1,1);
410 EXPLAIN SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
411 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
412 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
413 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Not optimized, outer query is empty
414 2 SUBQUERY t NULL ALL NULL NULL NULL NULL 1 100.00 NULL
415 Warnings:
416 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t` where false
417 SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
418 1
419 EXPLAIN SELECT (SELECT MAX(c) FROM v);
420 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
421 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
422 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
423 Warnings:
424 Note 1003 /* select#1 */ select (/* select#2 */ select max(`test`.`v`.`c`) from `test`.`v`) AS `(SELECT MAX(c) FROM v)`
425 SELECT (SELECT MAX(c) FROM v);
426 (SELECT MAX(c) FROM v)
427 1
428 DROP TABLE t, v;
429 CREATE TABLE v (
430 a INT,
431 c INT,
432 b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, KEY(c,b(1)));
433 INSERT INTO v (a,c) VALUES (1,1);
434 SELECT MAX(c), COUNT(b) FROM v;
435 MAX(c) COUNT(b)
436 1 1
437 DROP TABLE v;
438 CREATE TABLE v (
439 a INT PRIMARY KEY,
440 b INT, KEY(b));
441 INSERT INTO v (a,b) VALUES (1,1);
442 SELECT MAX(a) FROM v WHERE b=1;
443 MAX(a)
444 1
445 DROP TABLE v;
446 #
447 # Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS
448 # VIRTUAL GENERATED COLUMN
449 #
450 CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL);
451 CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW BEGIN END;
452 INSERT INTO t (a) VALUES (1);
453 SELECT * FROM t;
454 a b
455 1 1
456 DROP TRIGGER tr;
457 SELECT * FROM t;
458 a b
459 1 1
460 CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t);
461 SELECT f();
462 f()
463 1
464 DROP FUNCTION f;
465 SELECT * FROM t;
466 a b
467 1 1
468 DROP TABLE t;
469 #
470 # Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED.
471 #
472 CREATE TABLE C(
473 c1 INT AUTO_INCREMENT,
474 c8 DATETIME,
475 c9 TIME,
476 c11 TIME GENERATED ALWAYS AS(ADDTIME(c8,c9)) VIRTUAL,
477 c13 TIME GENERATED ALWAYS AS(ADDTIME(c8,c11)) VIRTUAL,
478 PRIMARY KEY(c1),
479 UNIQUE KEY(c13)
480 );
481 INSERT INTO C (c8,c9) VALUES('1970-01-01',0),('1970-01-01',1);
482 CREATE VIEW view_C AS SELECT * FROM C;
483 SELECT /*+ NO_BNL(t1) */ t1.c13 FROM C AS t2 STRAIGHT_JOIN C AS t1 FORCE INDEX(c13);
484 c13
485 00:00:00
486 00:00:00
487 00:00:01
488 00:00:01
489 SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2;
490 c13
491 00:00:00
492 00:00:01
493 DROP TABLE C;
494 DROP VIEW view_C;
495 #
496 # Bug #21808680: JSON + GENERATED COLUMN CORRUPTS TABLE CACHE
497 # MEMORY, CRASHES
498 #
499 CREATE TABLE t (a INT, b JSON, c TEXT GENERATED ALWAYS AS (REPEAT(a=b, 2)));
500 INSERT INTO t (a, b) VALUES (1, '2'), (3, '3');
501 SELECT * FROM t;
502 a b c
503 1 2 00
504 3 3 11
505 DROP TABLE t;
506 #
507 # Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE
508 # LOCATION
509 #
510 CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL);
511 ALTER TABLE t CHANGE COLUMN q w INT;
512 ERROR 42S22: Unknown column 'q' in 't'
513 ALTER TABLE t CHANGE COLUMN q w INT;
514 ERROR 42S22: Unknown column 'q' in 't'
515 ALTER TABLE t CHANGE COLUMN q w INT;
516 ERROR 42S22: Unknown column 'q' in 't'
517 ALTER TABLE t CHANGE COLUMN q w INT;
518 ERROR 42S22: Unknown column 'q' in 't'
519 DROP TABLE t;
520 #
521 # Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED
522 #
523 CREATE TABLE t(b BLOB);
524 ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL;
525 ERROR HY000: Expression of generated column 'c' cannot refer to a row value.
526 DROP TABLE t;
527 CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL);
528 ERROR HY000: Expression of generated column 'c' cannot refer to a row value.
529 #
530 # Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
531 #
532 CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
533 Warnings:
534 Warning 1287 '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead
535 SHOW CREATE TABLE t1;
536 Table Create Table
537 t1 CREATE TABLE `t1` (
538 `a` char(1) DEFAULT NULL,
539 `b` char(1) DEFAULT NULL,
540 `c` char(2) GENERATED ALWAYS AS (((0 <> `a`) or (0 <> `b`))) VIRTUAL
541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
542 INSERT INTO t1 (a,b) VALUES('1','1');
543 SELECT * FROM t1;
544 a b c
545 1 1 1
546 SET SQL_MODE=PIPES_AS_CONCAT;
547 SELECT * FROM t1;
548 a b c
549 1 1 1
550 FLUSH TABLES;
551 SELECT * FROM t1;
552 a b c
553 1 1 1
554 DROP TABLE t1;
555 CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
556 SHOW CREATE TABLE t1;
557 Table Create Table
558 t1 CREATE TABLE `t1` (
559 `a` char(1) DEFAULT NULL,
560 `b` char(1) DEFAULT NULL,
561 `c` char(2) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
563 INSERT INTO t1 (a,b) VALUES('1','1');
564 SELECT * FROM t1;
565 a b c
566 1 1 11
567 SET SQL_MODE=DEFAULT;
568 SELECT * FROM t1;
569 a b c
570 1 1 11
571 FLUSH TABLES;
572 SELECT * FROM t1;
573 a b c
574 1 1 11
575 DROP TABLE t1;
576 # Bug#22018999: gcols: assertion failed: !error
577 SET @save_old_sql_mode= @@sql_mode;
578 SET sql_mode="";
579 CREATE TABLE t (a INTEGER AS (SUBSTR('','a',1))) engine=innodb;
580 ERROR 22007: Truncated incorrect INTEGER value: 'a'
581 CREATE TABLE t (a INTEGER) engine=innodb;
582 ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1));
583 ERROR 22007: Truncated incorrect INTEGER value: 'a'
584 DROP TABLE t;
585 set sql_mode= @save_old_sql_mode;
586 #
587 # Bug#22157531 WITH NO MORE .FRM 64K LIMIT WE CAN CREATE MASSIVE TABLES,
588 # BUT TOO SLOWLY..
589 #
590 set @s:=concat("CREATE TABLE t(a LONGBLOB GENERATED ALWAYS AS
591 ('",REPEAT("a",1024*1024),"') STORED) ENGINE=INNODB;");
592 PREPARE r FROM @s;
593 EXECUTE r;
594 DEALLOCATE PREPARE r;
595 DROP TABLE t;
596 #
597 # Bug#22239803 WITH NO MORE .FRM 64K LIMIT WE CAN CREATE MASSIVE TABLES,
598 # BUT TOO SLOW (II)
599 #
600 set @s:=concat("CREATE TABLE t(w INT, A INT GENERATED ALWAYS
601 AS('",REPEAT("\\0",1024*1024),"') STORED) ENGINE=INNODB;");
602 PREPARE r FROM @s;
603 EXECUTE r;
604 DEALLOCATE PREPARE r;
605 DROP TABLE t;
606 # Bug#21875520 Problems with virtual column indexes
607 CREATE TABLE t(
608 a TIMESTAMP,
609 b BLOB,
610 c TIMESTAMP GENERATED ALWAYS AS (GREATEST(a, '2000-01-01 00:00:00')) VIRTUAL,
611 UNIQUE KEY(c)
612 );
613 INSERT INTO t(b) VALUES ('');
614 UPDATE t SET a='2001-01-01 00:00:00';
615 SELECT c FROM t;
616 c
617 2001-01-01 00:00:00
618 SELECT c, a FROM t;
619 c a
620 2001-01-01 00:00:00 2001-01-01 00:00:00
621 UPDATE t SET b='xyz';
622 DO (SELECT @c1:= c FROM t);
623 Warnings:
624 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
625 DO (SELECT (@c2:= c) - a FROM t);
626 Warnings:
627 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
628 Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01 00:00:00'
629 SELECT @c2 - @c1;
630 @c2 - @c1
631 0
632 Warnings:
633 Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01 00:00:00'
634 Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01 00:00:00'
635 DROP TABLE t;
636 #
637 # Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA
638 #
639 CREATE TABLE t (
640 a INT,
641 b INT GENERATED ALWAYS AS (1) VIRTUAL,
642 c INT GENERATED ALWAYS AS (1) VIRTUAL,
643 d INT GENERATED ALWAYS AS (1) VIRTUAL,
644 KEY (b,d)
645 ) ENGINE=INNODB;
646 INSERT INTO t VALUES();
647 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
648 SELECT 1 FROM t WHERE c GROUP BY b;
649 1
650 1
651 COMMIT;
652 DROP TABLE t;
653 #
654 # bug#21982792: TOO MUCH SPAM: 'GOT ERROR 1 WHEN READING TABLE'
655 #
656 CREATE TABLE t (
657 a INT GENERATED ALWAYS AS (1) VIRTUAL NOT NULL,
658 b DATE GENERATED ALWAYS AS (1) VIRTUAL NOT NULL,
659 KEY(b)
660 );
661 SET @old_sql_mode=@@sql_mode;
662 SET sql_mode='';
663 INSERT INTO t VALUES();
664 Warnings:
665 Warning 1265 Data truncated for column 'b' at row 1
666 SET sql_mode= STRICT_ALL_TABLES;
667 Warnings:
668 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
669 DELETE d2 FROM t AS d1, t AS d2 WHERE d1.a = d2.b;
670 ERROR 22007: Incorrect date value: '1' for column 'b' at row 1
671 DROP TABLE t;
672 SET sql_mode=@old_sql_mode;
673 CREATE TABLE t (
674 a INT,
675 b INT GENERATED ALWAYS AS (1),
676 KEY i(a,b)
677 );
678 INSERT INTO t (a) VALUES(1), (2);
679 SELECT 1 FROM t a RIGHT JOIN t USING(b)
680 WHERE 1 NOT LIKE(SELECT a FROM t WHERE a.b);
681 ERROR 21000: Subquery returns more than 1 row
682 DROP TABLE t;
683 # Bug#22748217 gcols: assert in Item_func_in::resolve_type
684 SET @old_sql_mode= @@sql_mode;
685 SET sql_mode="";
686 CREATE TABLE t (a TIMESTAMP AS (0) VIRTUAL) engine=innodb;
687 ALTER TABLE t ADD COLUMN b JSON AS (a NOT IN (1,1)) STORED;
688 ERROR 22007: Incorrect datetime value: '1' for column 'a' at row 1
689 DROP TABLE t;
690 SET sql_mode= @old_sql_mode;
691 #
692 # Bug#22987899: ALTER TABLE MODIFY COLUMN DOES NOT SET
693 # ALTER_CHANGE_COLUMN_DEFAULT FLAG
694 #
695 CREATE TABLE t1(
696 a INTEGER DEFAULT 5,
697 b INTEGER NOT NULL,
698 gc1 INTEGER AS (a + DEFAULT(a)) VIRTUAL,
699 gc2 INTEGER AS (a + DEFAULT(a)) STORED,
700 KEY k1(gc1)
701 );
702 INSERT INTO t1(a, b) VALUES (1 ,1);
703 SELECT * FROM t1;
704 a b gc1 gc2
705 1 1 6 6
706 SELECT gc1 FROM t1;
707 gc1
708 6
709 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 6;
710 affected rows: 1
711 info: Records: 1 Duplicates: 0 Warnings: 0
712 SELECT * FROM t1;
713 a b gc1 gc2
714 1 1 7 7
715 SELECT gc1 FROM t1;
716 gc1
717 7
718 ALTER TABLE t1 MODIFY COLUMN b INTEGER NULL;
719 affected rows: 0
720 info: Records: 0 Duplicates: 0 Warnings: 0
721 SELECT * FROM t1;
722 a b gc1 gc2
723 1 1 7 7
724 SELECT gc1 FROM t1;
725 gc1
726 7
727 DROP TABLE t1;
728 #
729 # Bug#22991924 GCOLS: UTF16 STRING OPERATIONS GIVE DOUBLED \0\0 STRINGS
730 # ON EACH TABLE REBUILD
731 #
732 CREATE TABLE t1 (
733 a TEXT CHARSET utf16,
734 b BLOB GENERATED ALWAYS AS ( concat( 'ABC', a ) ) VIRTUAL
735 );
736 # Results change due to bug fix.
737 INSERT INTO t1 ( a ) VALUES ( 'abc' );
738 SHOW CREATE TABLE t1;
739 Table Create Table
740 t1 CREATE TABLE `t1` (
741 `a` text CHARACTER SET utf16 COLLATE utf16_general_ci,
742 `b` blob GENERATED ALWAYS AS (concat(_utf16'\0A\0B\0C',`a`)) VIRTUAL
743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
744 SELECT HEX(b) FROM t1;
745 HEX(b)
746 004100420043006100620063
747 OPTIMIZE TABLE t1;
748 Table Op Msg_type Msg_text
749 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
750 test.t1 optimize status OK
751 SELECT HEX(b) FROM t1;
752 HEX(b)
753 004100420043006100620063
754 SHOW CREATE TABLE t1;
755 Table Create Table
756 t1 CREATE TABLE `t1` (
757 `a` text CHARACTER SET utf16 COLLATE utf16_general_ci,
758 `b` blob GENERATED ALWAYS AS (concat(_utf16'\0A\0B\0C',`a`)) VIRTUAL
759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
760 SELECT
761 length( convert( b USING utf8 ) ),
762 char_length ( convert( b USING utf8 ) )
763 FROM t1;
764 length( convert( b USING utf8 ) ) char_length ( convert( b USING utf8 ) )
765 12 12
766 Warnings:
767 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
768 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
769 SELECT
770 length( convert( b USING utf16 ) ),
771 char_length ( convert( b USING utf16 ) )
772 FROM t1;
773 length( convert( b USING utf16 ) ) char_length ( convert( b USING utf16 ) )
774 12 6
775 SELECT
776 length( convert( b USING latin1 ) ),
777 char_length ( convert( b USING latin1 ) )
778 FROM t1;
779 length( convert( b USING latin1 ) ) char_length ( convert( b USING latin1 ) )
780 12 12
781 DROP TABLE t1;
782 #
783 # Bug#25615803: ASSERT IN SQL/FIELD.CC:...
784 # VIRTUAL STRING* FIELD_VARSTRING::VAL_STR
785 #
786 CREATE TABLE t1 (
787 a INT,
788 b INT,
789 c INT,
790 d INT GENERATED ALWAYS AS ( b + 1 ) STORED
791 )
792 PARTITION BY RANGE( a ) ( PARTITION p0 VALUES LESS THAN (1) );
793 INSERT INTO t1 ( b ) VALUES ( 1 );
794 UPDATE t1 SET c = d;
795 DROP TABLE t1;
796 #
797 # Bug #25793677 INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN ....
798 #
799 CREATE TABLE v (
800 a INT,
801 c INT,
802 b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL,
803 KEY(c,b(1))) charset utf8mb4;
804 INSERT INTO v (a,c) VALUES (1,1);
805 SELECT (SELECT MAX(c) FROM v);
806 (SELECT MAX(c) FROM v)
807 1
808 DROP TABLE v;
809 # Bug#23021693 gcol:assertion '!table->in_use->is_error()' failed
810 CREATE TABLE t1 (
811 pk INTEGER AUTO_INCREMENT,
812 c1 INTEGER,
813 gc1 INTEGER GENERATED ALWAYS AS (c1+c1) VIRTUAL,
814 gc2 INTEGER GENERATED ALWAYS AS (gc1 + c1) VIRTUAL,
815 c2 VARCHAR(5),
816 gc3 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(c2, c2)) VIRTUAL,
817 gc4 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(gc3, 'x')) VIRTUAL,
818 PRIMARY KEY (pk DESC),
819 KEY(gc1),
820 KEY(gc3),
821 UNIQUE KEY (gc1, gc3),
822 UNIQUE KEY (gc2, gc4)
823 ) ENGINE=InnoDB;
824 INSERT INTO t1 (c1, c2) VALUES (3, 'cek'),(3,'eks');
825 CREATE TABLE t2 (
826 pk INTEGER AUTO_INCREMENT,
827 c1 INTEGER,
828 gc1 INTEGER GENERATED ALWAYS AS (c1 + c1) VIRTUAL,
829 gc2 INTEGER GENERATED ALWAYS AS (gc1 + c1) VIRTUAL,
830 c2 VARCHAR(5),
831 gc3 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(c2, c2)) VIRTUAL,
832 gc4 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(gc3, 'x')) VIRTUAL,
833 PRIMARY KEY (pk DESC),
834 KEY(gc1),
835 KEY(gc3),
836 UNIQUE KEY (gc1, gc3),
837 UNIQUE KEY (gc2, gc4)
838 ) ENGINE=InnoDB;
839 INSERT INTO t2 (c1, c2) VALUES
840 (3, 'ksa'), (6,'sate'), (3, 'a'), (NULL, 't'), (9, 'efq'),
841 (NULL, 'f'), (6, 'qsdk'), (63, NULL), (3, 's'), (1,NULL);
842 DELETE outr1.*
843 FROM t2 AS outr1 INNER JOIN t1 AS outr2
844 ON outr1.gc4 = outr2.gc4
845 WHERE outr1.pk > (SELECT innr1.gc2 AS y
846 FROM t1 AS innr1
847 WHERE innr1.pk = 5 OR outr2.pk <> 7
848 );
849 ERROR 21000: Subquery returns more than 1 row
850 DROP TABLE t1, t2;
851 #
852 # Bug#23321196: GCOLS: ASSERTION FAILURE:
853 # DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA
854 #
855 CREATE TABLE t(gc TEXT GENERATED ALWAYS AS (NULL) NOT NULL, KEY (gc(1)));
856 INSERT IGNORE INTO t VALUES ();
857 Warnings:
858 Warning 1048 Column 'gc' cannot be null
859 SELECT * FROM t;
860 gc
861
862 DELETE FROM t;
863 SELECT * FROM t;
864 gc
865 DROP TABLE t;
866 #
867 # Bug #27041382: ASSERTION `!TABLE->IN_USE->IS_ERROR()' FAILED.
868 #
869 CREATE TABLE t1 (c1 VARCHAR(100),
870 c2 VARCHAR(400) GENERATED ALWAYS AS (CONCAT(c1,c1)));
871 CREATE TRIGGER trg1 BEFORE INSERT ON t1
872 FOR EACH ROW INSERT/*!INTO*/t1 VALUES (1);
873 INSERT INTO t1 VALUES (),();
874 ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1'
875 INSERT INTO t1(c1) SELECT 1 FROM DUAL;
876 ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1'
877 LOAD DATA INFILE '../../std_data/loaddata5.dat'
878 INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (c1) ;
879 ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1'
880 DROP TABLE t1;
881 #
882 # Bug#27452082 ASSERTION FAILED: !TABLE->IN_USE->IS_ERROR() IN UPDATE_GENERATED_READ_FIELDS
883 #
884 CREATE TABLE t (
885 a INT,
886 b INT GENERATED ALWAYS AS (MAKETIME(1,1,1)) STORED,
887 KEY (a)
888 );
889 INSERT INTO t (a) VALUES (32767),(-1);
890 SELECT * FROM t WHERE a>-19106 AND a-0x4d;
891 ERROR 22003: BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - 0x4d)'
892 DROP TABLE t;
893 #
894 # Bug#27954073 ASSERTION FAILED: !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET
895 #
896 SET BINLOG_FORMAT=STATEMENT;
897 Warnings:
898 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
899 CREATE TABLE t(
900 a INT,
901 b INT GENERATED ALWAYS AS (0) VIRTUAL NOT NULL,
902 KEY (a)
903 ) ENGINE=INNODB PARTITION BY KEY(b) PARTITIONS 2;
904 INSERT INTO t(a) VALUES(1),(1),(1),(1);
905 DELETE FROM t WHERE a=1;
906 DROP TABLE t;
907 SET BINLOG_FORMAT=DEFAULT;
908 Warnings:
909 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
910 #
911 # Bug #28652826 THE CONTINUATION OF BUG 27968952 .. CRASH & CORRUPTION
912 # WITH GCOLS
913 #
914 CREATE TABLE t(
915 a INT NOT NULL,
916 b INT NOT NULL,
917 c INT NOT NULL,
918 g MEDIUMTEXT GENERATED ALWAYS AS((a <> b)) VIRTUAL,
919 UNIQUE KEY i0000 (a),
920 UNIQUE KEY i0001 (g(78))
921 ) ENGINE=INNODB;
922 REPLACE INTO t(a, b, c) VALUES (1,8,9);
923 REPLACE INTO t(a, b, c) VALUES (0,0,10);
924 REPLACE INTO t(a, b, c) VALUES (4,4,11);
925 REPLACE INTO t(a, b, c) VALUES (0,7,12);
926 REPLACE INTO t(a, b, c) VALUES (0,0,13);
927 SELECT * FROM t;
928 a b c g
929 0 0 13 0
930 DROP TABLE t;
931 #
932 # Bug #28772251 "SWAPPING COLUMN BY ALTER TABLE RENAME COLUMN BREAKS
933 # STORED GENERATED COLUMN".
934 #
935 #
936 # 0) Original test case.
937 #
938 CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 1) STORED);
939 SHOW CREATE TABLE t1;
940 Table Create Table
941 t1 CREATE TABLE `t1` (
942 `a` int DEFAULT NULL,
943 `b` int DEFAULT NULL,
944 `gcol` int GENERATED ALWAYS AS ((`a` + 1)) STORED
945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
946 INSERT INTO t1 (a, b) VALUES (1, 2);
947 SELECT * FROM t1;
948 a b gcol
949 1 2 2
950 # We block renaming of columns on which generated columns depend.
951 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a;
952 ERROR HY000: Column 'a' has a generated column dependency.
953 # However, it is OK to rename such column in dependent expression
954 # is redefined in the same ALTER TABLE.
955 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 1) STORED;
956 SHOW CREATE TABLE t1;
957 Table Create Table
958 t1 CREATE TABLE `t1` (
959 `b` int DEFAULT NULL,
960 `a` int DEFAULT NULL,
961 `gcol` int GENERATED ALWAYS AS ((`a` + 1)) STORED
962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
963 # Check that generated column value reflect the fact that columns
964 # were swapped.
965 SELECT * FROM t1;
966 b a gcol
967 1 2 3
968 # Similarly, we disallow replacement (via DROP + ADD) of columns on
969 # which generated columns depend. Replacement is allowed if
970 # dependent expression is updated within the same ALTER.
971 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 3;
972 ERROR HY000: Column 'a' has a generated column dependency.
973 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 3, MODIFY gcol INT AS (a + 1) STORED;
974 SHOW CREATE TABLE t1;
975 Table Create Table
976 t1 CREATE TABLE `t1` (
977 `b` int DEFAULT NULL,
978 `gcol` int GENERATED ALWAYS AS ((`a` + 1)) STORED,
979 `a` int DEFAULT '3'
980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
981 # Check that generated column value reflect the fact that column
982 # was replaced.
983 SELECT * FROM t1;
984 b gcol a
985 1 4 3
986 DROP TABLE t1;
987 #
988 # More systematic test coverage.
989 #
990 # 1) Renaming columns on which generated columns depend.
991 #
992 # 1.1) Renaming column on which generated column depends is
993 # prohibited (for both stored and virtual columns).
994 CREATE TABLE t1 (a INT, gcol INT AS (a + 1) STORED);
995 CREATE TABLE t2 (a INT, gcol INT AS (a + 1) VIRTUAL);
996 ALTER TABLE t1 RENAME COLUMN a TO b;
997 ERROR HY000: Column 'a' has a generated column dependency.
998 ALTER TABLE t2 RENAME COLUMN a TO b;
999 ERROR HY000: Column 'a' has a generated column dependency.
1000 # Cases when rename happens through CHANGE COLUMN are also detected.
1001 ALTER TABLE t1 CHANGE COLUMN a b INT;
1002 ERROR HY000: Column 'a' has a generated column dependency.
1003 ALTER TABLE t2 CHANGE COLUMN a b INT;
1004 ERROR HY000: Column 'a' has a generated column dependency.
1005 #
1006 # 1.2) It is OK to rename such columns if dependent generated column
1007 # is dropped in the same ALTER.
1008 ALTER TABLE t1 RENAME COLUMN a TO b, DROP COLUMN gcol;
1009 ALTER TABLE t2 RENAME COLUMN a TO b, DROP COLUMN gcol;
1010 # This works even if dropped column comes earlier than renamed column.
1011 ALTER TABLE t1 ADD gcol INT AS (b + 1) STORED FIRST;
1012 ALTER TABLE t2 ADD gcol INT AS (b + 1) VIRTUAL FIRST;
1013 ALTER TABLE t1 DROP COLUMN gcol, RENAME COLUMN b TO c;
1014 ALTER TABLE t2 DROP COLUMN gcol, RENAME COLUMN b TO c;
1015 #
1016 # 1.3) It is also OK to rename such columns if dependent generated
1017 # column expression is updated/redefined.
1018 ALTER TABLE t1 ADD COLUMN gcol INT AS (c + 1) STORED;
1019 ALTER TABLE t2 ADD COLUMN gcol INT AS (c + 1) VIRTUAL;
1020 ALTER TABLE t1 RENAME COLUMN c TO d, MODIFY gcol INT AS (d + 1) STORED;
1021 ALTER TABLE t2 RENAME COLUMN c TO d, MODIFY gcol INT AS (d + 1) VIRTUAL;
1022 # And again this works even if generated column comes earlier than
1023 # renamed column.
1024 ALTER TABLE t1 DROP COLUMN gcol, ADD COLUMN gcol INT AS (d + 1) STORED FIRST;
1025 ALTER TABLE t2 DROP COLUMN gcol, ADD COLUMN gcol INT AS (d + 1) VIRTUAL FIRST;
1026 ALTER TABLE t1 MODIFY gcol INT AS (e + 1) STORED, RENAME COLUMN d TO e;
1027 ALTER TABLE t2 MODIFY gcol INT AS (e + 1) VIRTUAL, RENAME COLUMN d TO e;
1028 # If updated expression uses wrong column name, error is reported.
1029 # Note that error code and message are different because technically
1030 # it is issue with new generation expression.
1031 ALTER TABLE t1 MODIFY gcol INT AS (e + 1) STORED, RENAME COLUMN e TO f;
1032 ERROR 42S22: Unknown column 'e' in 'generated column function'
1033 ALTER TABLE t2 MODIFY gcol INT AS (e + 1) VIRTUAL, RENAME COLUMN e TO f;
1034 ERROR 42S22: Unknown column 'e' in 'generated column function'
1035 DROP TABLES t1, t2;
1036 #
1037 # 1.4) Complex case. Renaming of columns which causes generated
1038 # column re-evaluation.
1039 CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 10) STORED);
1040 INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6);
1041 CREATE TABLE t2 (a INT, b INT, gcol INT AS (a + 10) VIRTUAL, KEY(gcol));
1042 INSERT INTO t2 (a, b) VALUES (1, 2), (3, 4), (5, 6);
1043 # We block simple renaming of columns even if dependent generation
1044 # expression stays valid. This is intentional -- we want user to
1045 # realize and explicitly confirm what is done (in most cases such
1046 # rename is likely to be an error).
1047 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a;
1048 ERROR HY000: Column 'a' has a generated column dependency.
1049 ALTER TABLE t2 RENAME COLUMN a TO b, RENAME COLUMN b TO a;
1050 ERROR HY000: Column 'a' has a generated column dependency.
1051 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) STORED;
1052 ALTER TABLE t2 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) VIRTUAL;
1053 # Contents of generated column should be updated accordingly.
1054 SELECT * FROM t1;
1055 b a gcol
1056 1 2 12
1057 3 4 14
1058 5 6 16
1059 SELECT * FROM t2;
1060 b a gcol
1061 1 2 12
1062 3 4 14
1063 5 6 16
1064 SELECT * FROM t2 WHERE gcol = 14;
1065 b a gcol
1066 3 4 14
1067 # Such ALTER TABLEs are not inplace/instant due to generated
1068 # column re-evaluation.
1069 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) STORED, ALGORITHM=INPLACE;
1070 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1071 ALTER TABLE t2 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) VIRTUAL, ALGORITHM=INPLACE;
1072 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1073 DROP TABLES t1, t2;
1074 #
1075 # 2) Renaming columns on which generated default depends.
1076 #
1077 # 2.1) Renaming column on which generated default depends is
1078 # prohibited.
1079 CREATE TABLE t1 (a INT, def INT DEFAULT (a + 1));
1080 ALTER TABLE t1 RENAME COLUMN a TO b;
1081 ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed.
1082 # Cases when rename happens through CHANGE COLUMN are also detected.
1083 ALTER TABLE t1 CHANGE COLUMN a b INT;
1084 ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed.
1085 #
1086 # 2.2) It is OK to rename such columns if dependent generated default
1087 # is dropped:
1088 #
1089 # a) By dropping column
1090 ALTER TABLE t1 RENAME COLUMN a TO b, DROP COLUMN def;
1091 # b) By dropping default using DROP DEFAULT
1092 ALTER TABLE t1 ADD COLUMN def INT DEFAULT (b + 1);
1093 ALTER TABLE t1 RENAME COLUMN b TO c, ALTER COLUMN def DROP DEFAULT;
1094 # c) By dropping default using CHANGE/MODIFY COLUMN
1095 ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (c + 1);
1096 ALTER TABLE t1 RENAME COLUMN c TO d, MODIFY COLUMN def INT;
1097 # This works even if column with generated default comes earlier than
1098 # renamed column.
1099 ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (d + 1) FIRST;
1100 ALTER TABLE t1 DROP COLUMN def, RENAME COLUMN d TO e;
1101 ALTER TABLE t1 ADD COLUMN def INT DEFAULT (e + 1) FIRST;
1102 ALTER TABLE t1 ALTER COLUMN def DROP DEFAULT, RENAME COLUMN e TO f;
1103 ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (f + 1);
1104 ALTER TABLE t1 MODIFY COLUMN def INT, RENAME COLUMN f TO g;
1105 DROP TABLE t1;
1106 #
1107 # 2.3) It is also OK to rename such columns if dependent generated
1108 # default value is updated/redefined:
1109 #
1110 # a) Using SET DEFAULT
1111 CREATE TABLE t1 (a INT, def INT DEFAULT (a + 1));
1112 ALTER TABLE t1 RENAME COLUMN a TO b, ALTER COLUMN def SET DEFAULT (b + 1);
1113 # b) Using MODIFY/CHANGE column
1114 ALTER TABLE t1 RENAME COLUMN b TO c, MODIFY COLUMN def INT DEFAULT (c + 1);
1115 # And again this works even if generated default comes earlier than
1116 # renamed column.
1117 ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (c + 1) FIRST;
1118 ALTER TABLE t1 ALTER COLUMN def SET DEFAULT (d + 1), RENAME COLUMN c TO d;
1119 ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (e + 1), RENAME COLUMN d TO e;
1120 # If updated expression uses wrong column name, error is reported.
1121 # Again, error code and message are different because technically
1122 # it is issue with new generated default.
1123 ALTER TABLE t1 MODIFY def INT DEFAULT (e + 1), RENAME COLUMN e TO f;
1124 ERROR 42S22: Unknown column 'e' in 'default value expression'
1125 DROP TABLES t1;
1126 #
1127 # 2.4) Complex case. Swapping of columns on which generated
1128 # default depends.
1129 CREATE TABLE t1 (a INT, b INT, def INT DEFAULT (a));
1130 INSERT INTO t1 (a, b) VALUES (1, 2);
1131 # Similarly to the above we block simple renaming of columns even
1132 # if dependent default expression stays valid. This is intentional
1133 # -- we want user to realize and explicitly confirm what is done
1134 # (in most cases such rename is likely to be an error).
1135 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a;
1136 ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed.
1137 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, ALTER COLUMN def SET DEFAULT (a);
1138 # Old rows should stay the same, new default will only affect new rows.
1139 INSERT INTO t1 (a, b) VALUES (3, 4);
1140 SELECT * FROM t1;
1141 b a def
1142 1 2 1
1143 4 3 3
1144 # Unlike for generated columns case, such ALTER TABLE can be done
1145 # inplace, since it doesn't affect table data.
1146 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, ALTER COLUMN def SET DEFAULT (a), ALGORITHM=INPLACE;
1147 DROP TABLES t1;
1148 #
1149 # 3) Renaming columns on which functional index depends.
1150 #
1151 # 3.1) Renaming column on which functional index depends is
1152 # prohibited.
1153 CREATE TABLE t1 (a INT, KEY fi((a + 1)));
1154 ALTER TABLE t1 RENAME COLUMN a TO b;
1155 ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed.
1156 # Cases when rename happens through CHANGE COLUMN are also detected.
1157 ALTER TABLE t1 CHANGE COLUMN a b INT;
1158 ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed.
1159 #
1160 # 3.2) It is OK to rename such columns if dependent index is dropped.
1161 ALTER TABLE t1 RENAME COLUMN a TO b, DROP KEY fi;
1162 # This works even if hidden column for functional index processed
1163 # earlier than renamed column.
1164 ALTER TABLE t1 ADD KEY fi ((b + 1));
1165 # Move column 'b' to the table's end first.
1166 ALTER TABLE t1 ADD c INT;
1167 ALTER TABLE t1 MODIFY b INT AFTER c;
1168 ALTER TABLE t1 DROP KEY fi, RENAME COLUMN b TO d;
1169 #
1170 # Note that the only way to update/redefine functional index is to
1171 # drop and re-create it. So the this section also covers the case
1172 # when functional index is updated/redefined along with rename.
1173 # Let us check such scenario for completeness.
1174 ALTER TABLE t1 ADD KEY fi ((d + 1));
1175 ALTER TABLE t1 RENAME COLUMN d TO e, DROP KEY fi, ADD KEY fi ((e + 1));
1176 # If updated expression uses wrong column name, error is reported.
1177 # Again, error code and message are different because technically
1178 # it is issue with new functional index.
1179 ALTER TABLE t1 RENAME COLUMN e TO f, DROP KEY fi, ADD KEY fi ((e + 1));
1180 ERROR 42S22: Unknown column 'e' in 'functional index'
1181 DROP TABLE t1;
1182 #
1183 # 3.3) Complex case. Swapping of columns on which functional index
1184 # depends.
1185 CREATE TABLE t1 (a INT, b INT, KEY fi ((a + 10)));
1186 INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6);
1187 SELECT * FROM t1 WHERE a + 10 = 13;
1188 a b
1189 3 4
1190 # We block simple renaming of columns even if dependent functional
1191 # index stays valid. This is intentional -- we want user to realize
1192 # and explicitly confirm what is done (in most cases such rename is
1193 # likely to be an error).
1194 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a;
1195 ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed.
1196 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, DROP KEY fi, ADD KEY fi((a + 10));
1197 # Contents of functional index/hidden generated column should be
1198 # updated accordingly.
1199 SELECT * FROM t1 WHERE a + 10 = 14;
1200 b a
1201 3 4
1202 # Such ALTER TABLEs are not inplace/instant due to re-evaluation
1203 # (and other reasons).
1204 ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, DROP KEY fi, ADD KEY fi((a + 10)), ALGORITHM=INPLACE;
1205 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1206 DROP TABLES t1;
1207 #
1208 # 4) Dropping columns on which generated columns depend.
1209 #
1210 # 4.1) Dropping column on which generated column depends is
1211 # prohibited (for both stored and virtual columns).
1212 CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 1) STORED);
1213 CREATE TABLE t2 (a INT, b INT, gcol INT AS (a + 1) VIRTUAL);
1214 ALTER TABLE t1 DROP COLUMN a;
1215 ERROR HY000: Column 'a' has a generated column dependency.
1216 ALTER TABLE t2 DROP COLUMN a;
1217 ERROR HY000: Column 'a' has a generated column dependency.
1218 #
1219 # 4.2) It is OK to drop such columns if dependent generated column
1220 # is dropped in the same ALTER.
1221 ALTER TABLE t1 DROP COLUMN a, DROP COLUMN gcol;
1222 ALTER TABLE t2 DROP COLUMN a, DROP COLUMN gcol;
1223 # This works even if generated column is processed earlier than
1224 # base column.
1225 ALTER TABLE t1 ADD gcol INT AS (a + 1) STORED FIRST, ADD COLUMN a INT;
1226 ALTER TABLE t1 DROP COLUMN gcol, DROP COLUMN a;
1227 #
1228 # 4.3) It is also OK to drop such columns if dependent generated
1229 # column expression is updated/redefined.
1230 ALTER TABLE t1 ADD COLUMN gcol INT AS (a + 1) STORED, ADD COLUMN a INT;
1231 ALTER TABLE t2 ADD COLUMN gcol INT AS (a + 1) VIRTUAL, ADD COLUMN a INT;
1232 ALTER TABLE t1 DROP COLUMN a, MODIFY gcol INT AS (b + 1) STORED;
1233 ALTER TABLE t2 DROP COLUMN a, MODIFY gcol INT AS (b + 1) VIRTUAL;
1234 # And again this works even if generated column is processed earlier
1235 # than base column.
1236 ALTER TABLE t1 DROP COLUMN gcol, ADD COLUMN gcol INT AS (a + 1) STORED FIRST, ADD COLUMN a INT;
1237 ALTER TABLE t1 MODIFY gcol INT AS (b + 1) STORED, DROP COLUMN a;
1238 ALTER TABLE t2 DROP COLUMN gcol, ADD COLUMN gcol INT AS (a + 1) VIRTUAL FIRST, ADD COLUMN a INT;
1239 ALTER TABLE t2 MODIFY gcol INT AS (b + 1) VIRTUAL, DROP COLUMN a;
1240 # If updated expression uses wrong column name, error is reported.
1241 # Note that error code and message are different because technically
1242 # it is issue with new generation expression.
1243 ALTER TABLE t1 ADD COLUMN c INT;
1244 ALTER TABLE t2 ADD COLUMN c INT;
1245 ALTER TABLE t1 MODIFY gcol INT AS (e + 1) STORED, DROP COLUMN b;
1246 ERROR 42S22: Unknown column 'e' in 'generated column function'
1247 ALTER TABLE t2 MODIFY gcol INT AS (e + 1) VIRTUAL, DROP COLUMN b;
1248 ERROR 42S22: Unknown column 'e' in 'generated column function'
1249 DROP TABLES t1, t2;
1250 #
1251 # 4.4) Complex case. Replacement (dropping and addition) of columns
1252 # which causes generated column re-evaluation.
1253 CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 10) STORED);
1254 INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6);
1255 CREATE TABLE t2 (a INT, b INT, gcol INT AS (a + 10) VIRTUAL, KEY(gcol));
1256 INSERT INTO t2 (a, b) VALUES (1, 2), (3, 4), (5, 6);
1257 # We block simple replacement of columns even if dependent generation
1258 # expression stays valid. This is intentional -- we want user to
1259 # realize and explicitly confirm what is done (in most cases this
1260 # is likely to be an error).
1261 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0;
1262 ERROR HY000: Column 'a' has a generated column dependency.
1263 ALTER TABLE t2 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0;
1264 ERROR HY000: Column 'a' has a generated column dependency.
1265 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) STORED;
1266 ALTER TABLE t2 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) VIRTUAL;
1267 # Contents of generated column should be updated accordingly.
1268 SELECT * FROM t1;
1269 b gcol a
1270 2 10 0
1271 4 10 0
1272 6 10 0
1273 SELECT * FROM t2;
1274 b gcol a
1275 2 10 0
1276 4 10 0
1277 6 10 0
1278 SELECT * FROM t2 WHERE gcol = 10;
1279 b gcol a
1280 2 10 0
1281 4 10 0
1282 6 10 0
1283 # Such ALTER TABLEs are not inplace/instant due to generated
1284 # column re-evaluation.
1285 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) STORED, ALGORITHM=INPLACE;
1286 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1287 ALTER TABLE t2 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) VIRTUAL, ALGORITHM=INPLACE;
1288 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1289 DROP TABLES t1, t2;
1290 #
1291 # 5) Dropping column on which generated default depends.
1292 #
1293 # 5.1) Dropping column on which generated default depends is
1294 # prohibited.
1295 CREATE TABLE t1 (a INT, b INT, def INT DEFAULT (a + 1));
1296 ALTER TABLE t1 DROP COLUMN a;
1297 ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed.
1298 #
1299 # 5.2) It is OK to drop such columns if dependent generated default
1300 # is dropped:
1301 #
1302 # a) By dropping column
1303 ALTER TABLE t1 DROP COLUMN a, DROP COLUMN def;
1304 # b) By dropping default using DROP DEFAULT
1305 ALTER TABLE t1 ADD COLUMN a INT, ADD COLUMN def INT DEFAULT (a + 1);
1306 ALTER TABLE t1 DROP COLUMN a, ALTER COLUMN def DROP DEFAULT;
1307 # c) By dropping default using CHANGE/MODIFY COLUMN
1308 ALTER TABLE t1 ADD COLUMN a INT, MODIFY COLUMN def INT DEFAULT (a + 1);
1309 ALTER TABLE t1 DROP COLUMN a, MODIFY COLUMN def INT;
1310 # This works even if column with generated default comes earlier than
1311 # dropped column.
1312 ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (a + 1) FIRST, ADD COLUMN a INT;
1313 ALTER TABLE t1 DROP COLUMN def, DROP COLUMN a;
1314 ALTER TABLE t1 ADD COLUMN def INT DEFAULT (a + 1) FIRST, ADD COLUMN a INT;
1315 ALTER TABLE t1 ALTER COLUMN def DROP DEFAULT, DROP COLUMN a;
1316 ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (a + 1), ADD COLUMN a INT;
1317 ALTER TABLE t1 MODIFY COLUMN def INT, DROP COLUMN a;
1318 DROP TABLE t1;
1319 #
1320 # 5.3) It is also OK to drop such columns if dependent generated
1321 # default value is updated/redefined:
1322 #
1323 # a) Using SET DEFAULT
1324 CREATE TABLE t1 (a INT, b INT, c INT, d INT, def INT DEFAULT (a + 1));
1325 ALTER TABLE t1 DROP COLUMN a, ALTER COLUMN def SET DEFAULT (b + 1);
1326 # b) Using MODIFY/CHANGE column
1327 ALTER TABLE t1 DROP COLUMN b, MODIFY COLUMN def INT DEFAULT (c + 1);
1328 # And again this works even if generated default comes earlier than
1329 # dropped column.
1330 ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (a + 1) FIRST, ADD COLUMN a INT, ADD COLUMN b INT;
1331 ALTER TABLE t1 ALTER COLUMN def SET DEFAULT (b + 1), DROP COLUMN a;
1332 ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (c + 1), DROP COLUMN b;
1333 # If updated expression uses wrong column name, error is reported.
1334 # Again, error code and message are different because technically
1335 # it is issue with new generated default.
1336 ALTER TABLE t1 MODIFY def INT DEFAULT (e + 1), DROP COLUMN c;
1337 ERROR 42S22: Unknown column 'e' in 'default value expression'
1338 DROP TABLES t1;
1339 #
1340 # 5.4) Complex case. Replacement of column on which generated
1341 # default depends.
1342 CREATE TABLE t1 (a INT, b INT, def INT DEFAULT (a));
1343 INSERT INTO t1 (a, b) VALUES (1, 2);
1344 # Similarly to the above we block simple replacement of columns even
1345 # if dependent default expression stays valid. This is intentional
1346 # -- we want user to realize and explicitly confirm what is done
1347 # (in most cases such replacement is likely to be an error).
1348 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT;
1349 ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed.
1350 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 10, ALTER COLUMN def SET DEFAULT (a);
1351 # Old rows should stay the same, new generated default will only affect new rows.
1352 INSERT INTO t1 (a, b) VALUES (3, 4);
1353 SELECT * FROM t1;
1354 b def a
1355 2 1 10
1356 4 3 3
1357 # Unlike for generated columns case, such ALTER TABLE can be done
1358 # inplace, since it doesn't affect table data.
1359 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 42, ALTER COLUMN def SET DEFAULT (a), ALGORITHM=INPLACE;
1360 DROP TABLES t1;
1361 #
1362 # 6) Dropping column on which functional index depends.
1363 #
1364 # 6.1) Dropping column on which functional index depends is
1365 # prohibited.
1366 CREATE TABLE t1 (a INT, b INT, KEY fi((a + 1)));
1367 ALTER TABLE t1 DROP COLUMN a;
1368 ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed.
1369 #
1370 # 6.2) It is OK to drop such column if dependent index is dropped.
1371 ALTER TABLE t1 DROP COLUMN a, DROP KEY fi;
1372 # This works even if hidden column for functional index processed
1373 # earlier than dropped column.
1374 ALTER TABLE t1 ADD KEY fi ((b + 1));
1375 # Move column 'b' to the table's end first.
1376 ALTER TABLE t1 ADD c INT;
1377 ALTER TABLE t1 MODIFY b INT AFTER c;
1378 ALTER TABLE t1 DROP KEY fi, DROP COLUMN b;
1379 #
1380 # Note that the only way to update/redefine functional index is to
1381 # drop and re-create it. So the this section also covers the case
1382 # when functional index is updated/redefined along with drop of
1383 # base column. Let us check such scenario for completeness.
1384 ALTER TABLE t1 ADD KEY fi ((c + 1)), ADD COLUMN d INT, ADD COLUMN e INT;
1385 ALTER TABLE t1 DROP COLUMN c, DROP KEY fi, ADD KEY fi ((d + 1));
1386 # If updated expression uses wrong column name, error is reported.
1387 # Again, error code and message are different because technically
1388 # it is issue with new functional index.
1389 ALTER TABLE t1 DROP COLUMN d, DROP KEY fi, ADD KEY fi ((f + 1));
1390 ERROR 42S22: Unknown column 'f' in 'functional index'
1391 DROP TABLE t1;
1392 #
1393 # 6.3) Complex case. Replacing column on which functional index
1394 # depends.
1395 CREATE TABLE t1 (a INT, b INT, KEY fi ((a + 10)));
1396 INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6);
1397 SELECT * FROM t1 WHERE a + 10 = 13;
1398 a b
1399 3 4
1400 # We block simple replacement of column even if dependent functional
1401 # index stays valid. This is intentional -- we want user to realize
1402 # and explicitly confirm what is done (in most cases such replacement
1403 # is likely to be an error).
1404 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0;
1405 ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed.
1406 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, DROP KEY fi, ADD KEY fi((a + 10));
1407 # Contents of functional index/hidden generated column should be
1408 # updated accordingly.
1409 SELECT * FROM t1 WHERE a + 10 = 10;
1410 b a
1411 2 0
1412 4 0
1413 6 0
1414 # Such ALTER TABLEs are not inplace/instant due to re-evaluation
1415 # (and other reasons).
1416 ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, DROP KEY fi, ADD KEY fi((a + 10)), ALGORITHM=INPLACE;
1417 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1418 DROP TABLES t1;
1419 #
1420 # Bug #30417361: MAKE THE SORTING CODE INDEPENDENT OF QEP_TABS
1421 #
1422 CREATE TABLE t1 (i INTEGER, a INTEGER, vgc INTEGER GENERATED ALWAYS AS (-a) VIRTUAL);
1423 INSERT INTO t1 (i, a) VALUES (1, 1), (2, 2);
1424 SELECT i FROM t1 ORDER BY vgc;
1425 i
1426 2
1427 1
1428 DROP TABLE t1;
1429 #
1430 # Bug #30664660 GCOLS, CRASH AND CORRUPTIONS WITH DELETE IGNORE + JSON
1431 #
1432 CREATE TABLE t (
1433 a JSON NOT NULL,
1434 b TIMESTAMP GENERATED ALWAYS AS (a) VIRTUAL NOT NULL,
1435 KEY (b)
1436 ) ENGINE=INNODB;
1437 INSERT IGNORE INTO t(a) VALUES ('{}');
1438 Warnings:
1439 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column a at row 1
1440 Warning 1264 Out of range value for column 'b' at row 1
1441 DELETE IGNORE FROM t;
1442 Warnings:
1443 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column a at row 1
1444 Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'b' at row 1
1445 Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column a at row 1
1446 Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'b' at row 1
1447 CHECK TABLE t;
1448 Table Op Msg_type Msg_text
1449 test.t check status OK
1450 DROP TABLE t;
1451 #
1452 # Bug #31463511: NATURAL JOIN RETURNS WRONG RESULT WHEN EXPRESSION INDEX IS USED
1453 #
1454 CREATE TABLE t1 (a INTEGER);
1455 CREATE TABLE t2 (a INTEGER);
1456 CREATE INDEX idx ON t1 ((a+1));
1457 CREATE INDEX idx ON t2 ((a+2));
1458 INSERT INTO t1 VALUES (1);
1459 INSERT INTO t2 VALUES (1);
1460 SELECT * FROM t1 NATURAL JOIN t2;
1461 a
1462 1
1463 DROP TABLE t1, t2;
1464 #
1465 # Bug#31982292: ITEM_JSON_FUNC.CC:1406: BOOL SQL_SCALAR_TO_JSON(ITEM*,
1466 # CONST CHAR*, STRING*, STR
1467 #
1468 CREATE TABLE t(x VARCHAR(10),
1469 y INT GENERATED ALWAYS AS (x),
1470 z JSON GENERATED ALWAYS AS (JSON_ARRAY(x, y)));
1471 INSERT INTO t (x) VALUES ('abc');
1472 ERROR HY000: Incorrect integer value: 'abc' for column 'y' at row 1
1473 DROP TABLE t;
1474 CREATE TABLE t(x VARCHAR(10));
1475 INSERT INTO t VALUES ('');
1476 ALTER TABLE t
1477 ADD COLUMN g1 JSON GENERATED ALWAYS AS (x),
1478 ADD COLUMN g2 JSON GENERATED ALWAYS AS (JSON_ARRAY(x));
1479 SELECT * FROM t;
1480 ERROR 22032: Invalid JSON text: "The document is empty." at position 0 in value for column 't.g1'.
1481 DROP TABLE t;
1482 #
1483 # Bug#32234773: UPDATE_GENERATED_READ_FIELDS:
1484 # ASSERTION `!TABLE->IN_USE->IS_ERROR()' FAILED
1485 #
1486 CREATE TABLE t (
1487 id INT PRIMARY KEY AUTO_INCREMENT,
1488 i INT,
1489 gc INT GENERATED ALWAYS AS (1) STORED,
1490 KEY (i)
1491 );
1492 INSERT INTO t(i) VALUES (5853267), (32766);
1493 SELECT * FROM t WHERE i > 123 AND i * FROM_UNIXTIME(1536999224);
1494 ERROR 22003: BIGINT value is out of range in '(`test`.`t`.`i` * <cache>(from_unixtime(1536999224)))'
1495 DROP TABLE t;
1496 # Bug#32824978: Generated columns inconsistency
1497 CREATE TABLE t1 (
1498 id INTEGER NOT NULL,
1499 mac BIGINT UNSIGNED DEFAULT NULL,
1500 str CHAR(12) AS (LPAD(CONVERT(HEX(mac) USING utf8mb4), 12,_utf8mb4'0')) VIRTUAL,
1501 separated CHAR(17) AS (INSERT(INSERT(INSERT(INSERT(INSERT(str, 11, 0, _utf8mb4':'), 9, 0, _utf8mb4':'), 7, 0, _utf8mb4':'), 5, 0, _utf8mb4':'), 3, 0, _utf8mb4':')) VIRTUAL,
1502 PRIMARY KEY (id)
1503 );
1504 INSERT INTO t1 (id, mac) VALUES
1505 (1, 10), (2, 564685488), (3, NULL), (4, 684756486);
1506 SELECT * FROM t1;
1507 id mac str separated
1508 1 10 00000000000A 00:00:00:00:00:0A
1509 2 564685488 000021A86AB0 00:00:21:A8:6A:B0
1510 3 NULL NULL NULL
1511 4 684756486 000028D08E06 00:00:28:D0:8E:06
1512 DROP TABLE t1;
1513 # Bug#32427727: Functional index ignored by SELECT query from
1514 # within stored function
1515 CREATE TABLE t1
1516 (pk BIGINT AUTO_INCREMENT PRIMARY KEY,
1517 n1 varchar(30),
1518 n2 varchar(30),
1519 n3 varchar(30),
1520 index n12((concat(n1, n2)))
1521 );
1522 ANALYZE TABLE t1;
1523 Table Op Msg_type Msg_text
1524 test.t1 analyze status OK
1525 CREATE PROCEDURE p1 (str VARCHAR(60))
1526 BEGIN
1527 EXPLAIN
1528 SELECT n3, COUNT(*) as 'count'
1529 FROM t1
1530 WHERE CONCAT(n1,n2) = str
1531 GROUP BY n3;
1532 SELECT n3, COUNT(*) as 'count'
1533 FROM t1
1534 WHERE CONCAT(n1,n2) = str
1535 GROUP BY n3;
1536 END |
1537 CALL p1('str');
1538 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1539 1 SIMPLE t1 NULL ref n12 n12 243 const 1 100.00 Using where; Using temporary
1540 n3 count
1541 DROP PROCEDURE p1;
1542 DROP TABLE t1;
1543
1544 # VARBINARY as primary key
1545
1546 CREATE TABLE t1 (
1547 doc JSON,
1548 id VARBINARY(32) GENERATED ALWAYS AS
1549 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id'))) STORED PRIMARY KEY);
1550 INSERT INTO t1 (doc) VALUES
1551 (JSON_OBJECT('id','1','name','foo')),(JSON_OBJECT('id','2','name','bar'));
1552 ANALYZE TABLE t1;
1553 Table Op Msg_type Msg_text
1554 test.t1 analyze status OK
1555 SET @a = '2';
1556 SET @num = 2;
1557 SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2';
1558 doc id
1559 {"id": "2", "name": "bar"} 2
1560 explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2';
1561 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1562 1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL
1563 Warnings:
1564 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1565 SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2';
1566 doc id
1567 {"id": "2", "name": "bar"} 2
1568 explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2';
1569 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1570 1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL
1571 Warnings:
1572 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1573 SELECT * FROM t1 WHERE id = '2';
1574 doc id
1575 {"id": "2", "name": "bar"} 2
1576 explain SELECT * FROM t1 WHERE id = '2';
1577 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1578 1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL
1579 Warnings:
1580 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1581 PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1582 # Not using index, resulting JSON comparison delivers empty result.
1583 # This deviates from the result when the GC is not indexed.
1584 EXECUTE s11 USING @a;
1585 doc id
1586 PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1587 EXECUTE s12 USING @a;
1588 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1589 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 2 50.00 Using where
1590 Warnings:
1591 Warning 1739 Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'id'
1592 Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id'
1593 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`id` = '2')
1594 PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1595 EXECUTE s21 USING @a;
1596 doc id
1597 {"id": "2", "name": "bar"} 2
1598 PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1599 EXECUTE s22 USING @a;
1600 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1601 1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL
1602 Warnings:
1603 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1604 PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?";
1605 EXECUTE s31 USING @a;
1606 doc id
1607 {"id": "2", "name": "bar"} 2
1608 PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?";
1609 EXECUTE s32 USING @a;
1610 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1611 1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL
1612 Warnings:
1613 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1614 DROP TABLE t1;
1615
1616 # VARCHAR as primary key
1617
1618 CREATE TABLE t1 (
1619 doc JSON,
1620 id VARCHAR(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS
1621 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id'))) STORED PRIMARY KEY);
1622 INSERT INTO t1 (doc) VALUES
1623 (JSON_OBJECT('id','1','name','foo')),(JSON_OBJECT('id','2','name','bar'));
1624 ANALYZE TABLE t1;
1625 Table Op Msg_type Msg_text
1626 test.t1 analyze status OK
1627 SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2';
1628 doc id
1629 {"id": "2", "name": "bar"} 2
1630 explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2';
1631 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1632 1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL
1633 Warnings:
1634 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1635 SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2';
1636 doc id
1637 {"id": "2", "name": "bar"} 2
1638 explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2';
1639 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1640 1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL
1641 Warnings:
1642 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1643 SELECT * FROM t1 WHERE id = '2';
1644 doc id
1645 {"id": "2", "name": "bar"} 2
1646 explain SELECT * FROM t1 WHERE id = '2';
1647 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1648 1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL
1649 Warnings:
1650 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1651 PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1652 # Not using index, resulting JSON comparison delivers expected row
1653 EXECUTE s11 USING @a;
1654 doc id
1655 {"id": "2", "name": "bar"} 2
1656 PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1657 EXECUTE s12 USING @a;
1658 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1659 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 2 50.00 Using where
1660 Warnings:
1661 Warning 1739 Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'id'
1662 Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id'
1663 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`id` = '2')
1664 PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1665 EXECUTE s21 USING @a;
1666 doc id
1667 {"id": "2", "name": "bar"} 2
1668 PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1669 EXECUTE s22 USING @a;
1670 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1671 1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL
1672 Warnings:
1673 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1674 PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?";
1675 EXECUTE s31 USING @a;
1676 doc id
1677 {"id": "2", "name": "bar"} 2
1678 PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?";
1679 EXECUTE s32 USING @a;
1680 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1681 1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL
1682 Warnings:
1683 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1684 DROP TABLE t1;
1685
1686 # BIGINT as primary key
1687
1688 CREATE TABLE t1 (
1689 doc JSON,
1690 id BIGINT GENERATED ALWAYS AS
1691 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id'))) STORED PRIMARY KEY);
1692 INSERT INTO t1 (doc) VALUES
1693 (JSON_OBJECT('id','1','name','foo')),(JSON_OBJECT('id','2','name','bar'));
1694 ANALYZE TABLE t1;
1695 Table Op Msg_type Msg_text
1696 test.t1 analyze status OK
1697 SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2';
1698 doc id
1699 {"id": "2", "name": "bar"} 2
1700 explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2';
1701 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1702 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1703 Warnings:
1704 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_extract(`test`.`t1`.`doc`,'$.id') = '2')
1705 SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2';
1706 doc id
1707 {"id": "2", "name": "bar"} 2
1708 explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2';
1709 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1710 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1711 Warnings:
1712 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_unquote(json_extract(`test`.`t1`.`doc`,'$.id')) = '2')
1713 SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = 2;
1714 doc id
1715 {"id": "2", "name": "bar"} 2
1716 explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = 2;
1717 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1718 1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL
1719 Warnings:
1720 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1721 SELECT * FROM t1 WHERE id = '2';
1722 doc id
1723 {"id": "2", "name": "bar"} 2
1724 explain SELECT * FROM t1 WHERE id = '2';
1725 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1726 1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL
1727 Warnings:
1728 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1729 PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1730 EXECUTE s11 USING @a;
1731 doc id
1732 {"id": "2", "name": "bar"} 2
1733 PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1734 EXECUTE s12 USING @a;
1735 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1736 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1737 Warnings:
1738 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_extract(`test`.`t1`.`doc`,'$.id') = '2')
1739 PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1740 EXECUTE s21 USING @a;
1741 doc id
1742 {"id": "2", "name": "bar"} 2
1743 PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1744 EXECUTE s22 USING @a;
1745 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1746 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1747 Warnings:
1748 Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_unquote(json_extract(`test`.`t1`.`doc`,'$.id')) = '2')
1749 PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?";
1750 EXECUTE s31 USING @a;
1751 doc id
1752 {"id": "2", "name": "bar"} 2
1753 PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?";
1754 EXECUTE s32 USING @a;
1755 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1756 1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL
1757 Warnings:
1758 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1759 PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1760 EXECUTE s11 USING @num;
1761 doc id
1762 {"id": "2", "name": "bar"} 2
1763 PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1764 EXECUTE s11 USING @num;
1765 doc id
1766 {"id": "2", "name": "bar"} 2
1767 PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?";
1768 EXECUTE s12 USING @num;
1769 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1770 1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL
1771 Warnings:
1772 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1773 PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1774 EXECUTE s21 USING @num;
1775 doc id
1776 {"id": "2", "name": "bar"} 2
1777 PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?";
1778 EXECUTE s22 USING @num;
1779 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1780 1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL
1781 Warnings:
1782 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1783 PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?";
1784 EXECUTE s31 USING @num;
1785 doc id
1786 {"id": "2", "name": "bar"} 2
1787 PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?";
1788 EXECUTE s32 USING @num;
1789 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1790 1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL
1791 Warnings:
1792 Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true
1793 DROP TABLE t1;
1794 # Bug#33142135: Assert for fields not marked as READ
1795 CREATE TABLE v1(v2 INT SERIAL DEFAULT VALUE, v3 INT AS (DEFAULT(v2)/1 IS NULL));
1796 ERROR HY000: Generated column 'v3' cannot refer to auto-increment column.
1797 CREATE TABLE v1(v2 INT DEFAULT 10, v3 INT AS (DEFAULT(v2)/1 IS NULL));
1798 SHOW CREATE TABLE v1;
1799 Table Create Table
1800 v1 CREATE TABLE `v1` (
1801 `v2` int DEFAULT '10',
1802 `v3` int GENERATED ALWAYS AS (((default(`v2`) / 1) is null)) VIRTUAL
1803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1804 DROP TABLE v1;