Debian Codegraph @

mysql-test/suite/gcol/t/gcol_bugfixes.test

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