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 --source include/have_innodb.inc
10 --echo # Bug#21230709: Alter table statement fails with division by zero
11
12 CREATE TABLE t1 (
13 col1 INTEGER NOT NULL,
14 col2 INTEGER NOT NULL,
15 col3 INTEGER NOT NULL,
16 gcol1 INTEGER GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
17 col4 INTEGER DEFAULT NULL,
18 col5 INTEGER DEFAULT NULL,
19 col6 INTEGER DEFAULT NULL,
20 col7 INTEGER DEFAULT NULL,
21 col8 INTEGER DEFAULT NULL,
22 col9 INTEGER DEFAULT NULL,
23 col10 INTEGER DEFAULT NULL,
24 col11 INTEGER DEFAULT NULL,
25 col12 INTEGER DEFAULT NULL,
26 col13 INTEGER DEFAULT NULL,
27 col14 INTEGER DEFAULT NULL,
28 col15 INTEGER DEFAULT NULL,
29 col16 INTEGER DEFAULT NULL,
30 col17 INTEGER DEFAULT NULL,
31 col18 INTEGER DEFAULT NULL,
32 col19 INTEGER DEFAULT NULL,
33 col20 INTEGER DEFAULT NULL,
34 col21 INTEGER DEFAULT NULL,
35 col22 INTEGER DEFAULT NULL,
36 col23 INTEGER DEFAULT NULL,
37 col24 INTEGER DEFAULT NULL,
38 col25 INTEGER DEFAULT NULL,
39 col26 INTEGER DEFAULT NULL,
40 col27 INTEGER DEFAULT NULL,
41 col28 INTEGER DEFAULT NULL,
42 col29 INTEGER DEFAULT NULL,
43 col30 INTEGER DEFAULT NULL,
44 col31 INTEGER DEFAULT NULL,
45 col32 INTEGER DEFAULT NULL,
46 col33 INTEGER DEFAULT NULL,
47 gcol2 INTEGER GENERATED ALWAYS AS (col2 + col2) VIRTUAL,
48 gcol3 INTEGER GENERATED ALWAYS AS (gcol2 / gcol2) VIRTUAL,
49 PRIMARY KEY (col1),
50 KEY idx1 (gcol1)
51 ) engine=innodb;
52
53 INSERT INTO t1 (col1, col2, col3)
54 VALUES (0,1,2), (1,2,3), (2,3,4), (3,4,5), (4,5,6);
55
56 # This is likely needed to ensure we allocate a new record buffer that
57 # contains zero in the mis-used field
58 FLUSH TABLE t1;
59
60 ALTER TABLE t1 ADD COLUMN extra INTEGER;
61
62 DROP TABLE t1;
63
64 --echo #
65 --echo # Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED
66 --echo #
67
68 CREATE TABLE t1 (
69 c_blob BLOB,
70 c_blob_key BLOB GENERATED ALWAYS AS (REPEAT(c_blob,15)) STORED,
71 KEY (c_blob_key(200))
72 );
73
74 INSERT INTO t1 (c_blob) VALUES ('xceks');
75
76 DROP TABLE t1;
77
78 --echo #
79 --echo # Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED.
80 --echo #
81
82 CREATE TABLE c (
83 pk INTEGER AUTO_INCREMENT,
84 col_int_nokey INTEGER,
85 gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
86 col_date_nokey DATE,
87 gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
88 col_datetime_nokey DATETIME,
89 col_time_nokey TIME,
90 gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
91 gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
92 col_varchar_nokey VARCHAR(1),
93 gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
94 PRIMARY KEY (pk),
95 UNIQUE KEY (gcol_int_key),
96 UNIQUE KEY (gcol_varchar_key),
97 UNIQUE KEY (gcol_date_key),
98 KEY (gcol_time_key),
99 KEY (gcol_datetime_key),
100 UNIQUE KEY (gcol_int_key, gcol_varchar_key),
101 KEY (gcol_int_key, col_int_nokey),
102 KEY(gcol_int_key,gcol_date_key),
103 KEY(gcol_int_key, gcol_time_key),
104 KEY(gcol_int_key, gcol_datetime_key),
105 UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key),
106 UNIQUE KEY (gcol_varchar_key, col_varchar_nokey),
107 UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key)
108 ) ENGINE=INNODB;
109
110 INSERT IGNORE INTO c ( col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey)
111 VALUES (7, '2004-04-09', '14:03:03.042673', '2001-11-28 00:50:27.051028', 'c'),
112 (1, '2006-05-13', '01:46:09.016386', '2007-10-09 19:53:04.008332', NULL);
113
114 CREATE TABLE bb (
115 pk INTEGER AUTO_INCREMENT,
116 col_int_nokey INTEGER,
117 gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
118 col_date_nokey DATE,
119 gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
120 col_datetime_nokey DATETIME,
121 col_time_nokey TIME,
122 gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
123 gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
124 col_varchar_nokey VARCHAR(1),
125 gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
126 PRIMARY KEY (pk),
127 UNIQUE KEY (gcol_int_key),
128 UNIQUE KEY (gcol_varchar_key),
129 UNIQUE KEY (gcol_date_key),
130 KEY (gcol_time_key),
131 KEY (gcol_datetime_key),
132 UNIQUE KEY (gcol_int_key, gcol_varchar_key),
133 KEY (gcol_int_key, col_int_nokey),
134 KEY(gcol_int_key,gcol_date_key),
135 KEY(gcol_int_key, gcol_time_key),
136 KEY(gcol_int_key, gcol_datetime_key),
137 UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key),
138 UNIQUE KEY (gcol_varchar_key, col_varchar_nokey),
139 UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key)
140 ) AUTO_INCREMENT=10 ENGINE=INNODB;
141
142 INSERT IGNORE INTO bb ( col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey)
143 VALUES (0, '2003-08-04', '01:48:05.048577', '2006-11-03 00:00:00', 'p'),
144 (2, '2007-11-06', '00:00:00', '2009-11-26 19:28:11.005115', 'n');
145
146 CREATE TABLE cc (
147 pk INTEGER AUTO_INCREMENT,
148 col_int_nokey INTEGER,
149 gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
150 col_date_nokey DATE,
151 gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
152 col_datetime_nokey DATETIME,
153 col_time_nokey TIME,
154 gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
155 gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
156 col_varchar_nokey VARCHAR(1),
157 gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
158 PRIMARY KEY (pk),
159 UNIQUE KEY (gcol_int_key),
160 UNIQUE KEY (gcol_varchar_key),
161 UNIQUE KEY (gcol_date_key),
162 KEY (gcol_time_key),
163 KEY (gcol_datetime_key),
164 UNIQUE KEY (gcol_int_key, gcol_varchar_key),
165 KEY (gcol_int_key, col_int_nokey),
166 KEY(gcol_int_key,gcol_date_key),
167 KEY(gcol_int_key, gcol_time_key),
168 KEY(gcol_int_key, gcol_datetime_key),
169 UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key),
170 UNIQUE KEY (gcol_varchar_key, col_varchar_nokey),
171 UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key)
172 ) AUTO_INCREMENT=10 ENGINE=INNODB;
173
174 INSERT IGNORE INTO cc (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey)
175 VALUES (172, '2009-04-23', '00:00:00', '2000-12-07 10:17:40.013275', 'h'),
176 (NULL, '2002-10-06', '00:50:49.017545', NULL, 'm');
177
178 let $query=
179 SELECT
180 gp1 . gcol_datetime_key AS g1
181 FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . gcol_datetime_key <> gp1 .
182 col_time_nokey )
183 WHERE
184 gp1 . col_varchar_nokey IN
185 (
186 SELECT
187 DISTINCT p1 . gcol_varchar_key AS p1
188 FROM bb AS p1 LEFT JOIN bb AS p2
189 ON ( p1 . gcol_int_key = p2 . pk )
190 )
191 AND gp1 . col_varchar_nokey = 'b'
192 HAVING g1 > 6;
193
194 eval EXPLAIN $query;
195 eval $query;
196 DROP TABLE bb, c, cc;
197
198 --echo # Bug#21284646: Assertion !(table || table->read_set || bitmap_is_set())
199
200 CREATE TABLE c (
201 pk INTEGER AUTO_INCREMENT,
202 col_int_nokey INTEGER NOT NULL,
203 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
204 col_date_nokey DATE NOT NULL,
205 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL,
206 col_datetime_nokey DATETIME NOT NULL,
207 col_time_nokey TIME NOT NULL,
208 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
209 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL,
210 col_varchar_nokey VARCHAR(1) NOT NULL,
211 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
212 PRIMARY KEY (pk,col_int_nokey),
213 UNIQUE KEY (col_int_key),
214 UNIQUE KEY (col_varchar_key),
215 UNIQUE KEY (col_date_key),
216 KEY (col_time_key),
217 KEY (col_datetime_key),
218 UNIQUE KEY (col_int_key, col_varchar_key),
219 KEY (col_int_key, col_int_nokey),
220 KEY(col_int_key,col_date_key),
221 KEY(col_int_key, col_time_key),
222 KEY(col_int_key, col_datetime_key),
223 UNIQUE KEY (col_date_key,col_time_key,col_datetime_key),
224 UNIQUE KEY (col_varchar_key, col_varchar_nokey),
225 UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key)
226 ) ENGINE=INNODB;
227
228 INSERT INTO c (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) VALUES
229 (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'),
230 (8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'),
231 (9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'),
232 (6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'),
233 (2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'),
234 (4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r');
235
236 ANALYZE TABLE c;
237 let $query=
238 SELECT COUNT(DISTINCT col_varchar_key) AS x
239 FROM c
240 WHERE col_varchar_key IN ('rr', 'rr') OR
241 col_int_nokey <> 9 AND
242 pk >= 8
243 HAVING x > '2000-02-06'
244 ORDER BY col_time_nokey, pk;
245
246 eval explain $query;
247 eval $query;
248
249 DROP TABLE c;
250
251 --echo # Bug#21341044: Conditional jump at sort_param::make_sort_key
252
253 CREATE TABLE t1 (
254 pk INTEGER AUTO_INCREMENT,
255 col_int_nokey INTEGER,
256 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL,
257 col_blob_nokey BLOB,
258 col_blob_key BLOB GENERATED ALWAYS AS (REPEAT(col_blob_nokey,15)) VIRTUAL,
259 col_longblob_nokey LONGBLOB,
260 col_longtext_nokey LONGTEXT,
261 col_longblob_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 20)) VIRTUAL,
262 col_longtext_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 18)) VIRTUAL,
263 col_text_nokey TEXT,
264 col_text_key TEXT GENERATED ALWAYS AS (REPEAT(col_text_nokey, 30)) VIRTUAL,
265 PRIMARY KEY (pk),
266 KEY (col_int_key),
267 KEY (col_text_key(50)),
268 KEY (col_blob_key(200)),
269 KEY (col_longtext_key(200)),
270 KEY (col_longblob_key(200)),
271 KEY (col_int_key, col_text_key(100)),
272 KEY (col_int_key, col_longtext_key(100)),
273 KEY (col_int_key, col_blob_key(100)),
274 KEY (col_int_key, col_longblob_key(100)),
275 KEY (col_longtext_key(10), col_longblob_key(100)),
276 KEY (col_int_key, col_text_key(10), col_blob_key(100), col_longtext_key(50), col_longblob_key(50))
277 ) engine=innodb;
278
279 INSERT INTO t1 (col_int_nokey,col_blob_nokey,col_longtext_nokey,col_longblob_nokey,col_text_nokey)
280 VALUES
281 (0, 'ijcszxw', 'ijcszxw', 'ijcszxw', 'ijcszxw'),
282 (5, 'jcszxwb', 'jcszxwb', 'jcszxwb', 'jcszxwb'),
283 (4, 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv'),
284 (3, 'szxw', 'szxw', 'szxw', 'szxw'),
285 (7, 'zxwb', 'zxwb', 'zxwb', 'zxwb'),
286 (42, 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky'),
287 (142, 'wbjj', 'wbjj', 'wbjj', 'wbjj'),
288 (5, 'bjjv', 'bjjv', 'bjjv', 'bjjv'),
289 (0, 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu'),
290 (3, 'j', 'j', 'j', 'j');
291 SELECT alias1.pk AS field1
292 FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2
293 ON alias1.col_int_key = alias2.col_int_key
294 WHERE alias2.col_int_key BETWEEN 8 AND (8 + 1 ) OR
295 alias2.col_int_key BETWEEN 8 AND (8 + 5 ) AND
296 alias2.col_int_key != 20 OR
297 alias2.col_int_key IN (8, 5, 8) AND
298 alias2.col_int_key >= 0 AND
299 alias2.col_int_key <= ( 8 + 75 ) AND
300 alias1.pk IS NOT NULL
301 ORDER BY field1;
302
303 DROP TABLE t1;
304 --echo # bug#21487651: gcols: memory leak after failed alter table
305 CREATE TABLE t(a int);
306 ALTER TABLE t ADD COLUMN b int GENERATED ALWAYS AS (
307 date_sub(a,interval a month)) VIRTUAL;
308 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
309 ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a));
310 DROP TABLE t;
311
312 --echo #
313 --echo # Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN
314 --echo # (II)
315 --echo #
316 CREATE TABLE t1( a INT ) ENGINE = INNODB;
317 INSERT INTO t1( a ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );
318
319 ALTER TABLE t1 ADD COLUMN b INT GENERATED ALWAYS AS (a - 1) STORED;
320 ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS (b + 1) VIRTUAL;
321
322 --echo # Used to cause valgrind warning.
323 ALTER TABLE t1 ADD INDEX( c );
324
325 ANALYZE TABLE t1;
326
327 --echo # Make sure the index is correct. That's kinda important.
328 EXPLAIN
329 SELECT c FROM t1;
330 SELECT c FROM t1;
331
332 DROP TABLE t1;
333
334 --echo #
335 --echo # Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED.
336 --echo #
337
338 CREATE TABLE C (
339 col_int_1 INT,
340 col_int_2 INT GENERATED ALWAYS AS (col_int_1 + col_int_1) STORED,
341 col_int_3 INT GENERATED ALWAYS AS (col_int_2 + col_int_1) VIRTUAL
342 );
343
344 CREATE ALGORITHM=TEMPTABLE VIEW v1 AS
345 SELECT
346 col_int_2 AS field1, col_int_2 AS field2,
347 col_int_3 AS field3, col_int_3 AS field4
348 FROM C;
349
350 SELECT * FROM v1;
351
352 DROP TABLE C;
353 DROP VIEW v1;
354
355 --echo #
356 --echo # Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
357 --echo #
358
359 CREATE TABLE t (a INT);
360 CREATE TABLE v (
361 a INT,
362 c INT,
363 b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL,
364 KEY(c,b(1)));
365 INSERT INTO v (a,c) VALUES (1,1);
366
367 let $query=
368 SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v );
369 eval EXPLAIN $query;
370 eval $query;
371
372 # A similar one:
373 let $query=
374 SELECT (SELECT MAX(c) FROM v);
375 eval EXPLAIN $query;
376 eval $query;
377
378 DROP TABLE t, v;
379
380 CREATE TABLE v (
381 a INT,
382 c INT,
383 b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, KEY(c,b(1)));
384 INSERT INTO v (a,c) VALUES (1,1);
385 SELECT MAX(c), COUNT(b) FROM v;
386 DROP TABLE v;
387
388 # Using PK suffix of secondary index
389 CREATE TABLE v (
390 a INT PRIMARY KEY,
391 b INT, KEY(b));
392 INSERT INTO v (a,b) VALUES (1,1);
393 SELECT MAX(a) FROM v WHERE b=1;
394 DROP TABLE v;
395
396 --echo #
397 --echo # Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS
398 --echo # VIRTUAL GENERATED COLUMN
399 --echo #
400 CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL);
401 CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW BEGIN END;
402 INSERT INTO t (a) VALUES (1);
403 SELECT * FROM t;
404 # DROP TRIGGER used to hit a DBUG_ASSERT.
405 DROP TRIGGER tr;
406 SELECT * FROM t;
407 CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t);
408 # And this function call hit the same DBUG_ASSERT.
409 SELECT f();
410 DROP FUNCTION f;
411 SELECT * FROM t;
412 DROP TABLE t;
413
414 --echo #
415 --echo # Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED.
416 --echo #
417
418 CREATE TABLE C(
419 c1 INT AUTO_INCREMENT,
420 c8 DATETIME,
421 c9 TIME,
422 c11 TIME GENERATED ALWAYS AS(ADDTIME(c8,c9)) VIRTUAL,
423 c13 TIME GENERATED ALWAYS AS(ADDTIME(c8,c11)) VIRTUAL,
424 PRIMARY KEY(c1),
425 UNIQUE KEY(c13)
426 );
427
428 INSERT INTO C (c8,c9) VALUES('1970-01-01',0),('1970-01-01',1);
429
430 CREATE VIEW view_C AS SELECT * FROM C;
431
432 SELECT /*+ NO_BNL(t1) */ t1.c13 FROM C AS t2 STRAIGHT_JOIN C AS t1 FORCE INDEX(c13);
433 SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2;
434
435 DROP TABLE C;
436 DROP VIEW view_C;
437
438 --echo #
439 --echo # Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE
440 --echo # LOCATION
441 --echo #
442 CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL);
443 --error ER_BAD_FIELD_ERROR
444 ALTER TABLE t CHANGE COLUMN q w INT;
445 --error ER_BAD_FIELD_ERROR
446 ALTER TABLE t CHANGE COLUMN q w INT;
447 --error ER_BAD_FIELD_ERROR
448 ALTER TABLE t CHANGE COLUMN q w INT;
449 --error ER_BAD_FIELD_ERROR
450 ALTER TABLE t CHANGE COLUMN q w INT;
451 DROP TABLE t;
452
453 --echo #
454 --echo # Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED
455 --echo #
456
457 CREATE TABLE t(b BLOB);
458 --error ER_OPERAND_COLUMNS
459 ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL;
460 DROP TABLE t;
461 --error ER_OPERAND_COLUMNS
462 CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL);
463
464 --echo #
465 --echo # Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
466 --echo #
467
468 CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
469 SHOW CREATE TABLE t1;
470 INSERT INTO t1 (a,b) VALUES('1','1');
471 SELECT * FROM t1;
472 SET SQL_MODE=PIPES_AS_CONCAT;
473 SELECT * FROM t1;
474 FLUSH TABLES;
475 SELECT * FROM t1;
476 DROP TABLE t1;
477 # The other way around:
478 CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b));
479 SHOW CREATE TABLE t1;
480 INSERT INTO t1 (a,b) VALUES('1','1');
481 SELECT * FROM t1;
482 SET SQL_MODE=DEFAULT;
483 SELECT * FROM t1;
484 FLUSH TABLES;
485 SELECT * FROM t1;
486 DROP TABLE t1;
487
488 --echo # Bug#22018999: gcols: assertion failed: !error
489
490 SET @save_old_sql_mode= @@sql_mode;
491 SET sql_mode="";
492
493 CREATE TABLE t (a INTEGER AS (SUBSTR('','a',1))) engine=innodb;
494 DROP TABLE t;
495
496 CREATE TABLE t (a INTEGER) engine=innodb;
497
498 ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1));
499
500 DROP TABLE t;
501
502 set sql_mode= @save_old_sql_mode;
503
504 --echo # Bug#21875520 Problems with virtual column indexes
505
506 CREATE TABLE t(
507 a TIMESTAMP,
508 b BLOB,
509 c TIMESTAMP GENERATED ALWAYS AS (GREATEST(a, '2000-01-01 00:00:00')) VIRTUAL,
510 UNIQUE KEY(c)
511 );
512 INSERT INTO t(b) VALUES ('');
513 UPDATE t SET a='2001-01-01 00:00:00';
514 SELECT c FROM t;
515 SELECT c, a FROM t;
516 UPDATE t SET b='xyz';
517 DO (SELECT @c1:= c FROM t);
518 DO (SELECT (@c2:= c) - a FROM t);
519 SELECT @c2 - @c1;
520
521 DROP TABLE t;
522
523 --echo #
524 --echo # Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA
525 --echo #
526
527 CREATE TABLE t (
528 a INT,
529 b INT GENERATED ALWAYS AS (1) VIRTUAL,
530 c INT GENERATED ALWAYS AS (1) VIRTUAL,
531 d INT GENERATED ALWAYS AS (1) VIRTUAL,
532 KEY (b,d)
533 ) ENGINE=INNODB;
534 INSERT INTO t VALUES();
535 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
536 SELECT 1 FROM t WHERE c GROUP BY b;
537 COMMIT;
538 DROP TABLE t;
539
540 --echo #
541 --echo # Bug #25793677 INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN ....
542 --echo #
543
544 CREATE TABLE v (
545 a INT,
546 c INT,
547 b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL,
548 KEY(c,b(1))) charset utf8mb4;
549 INSERT INTO v (a,c) VALUES (1,1);
550 SELECT (SELECT MAX(c) FROM v);
551 DROP TABLE v;
552
553 --echo #
554 --echo # MDEV-9255 Add generation_expression to information_schema.columns.
555 --echo #
556
557 CREATE TABLE gcol_t1 (
558 sidea DOUBLE,
559 sideb DOUBLE,
560 sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
561 );
562
563 SELECT table_schema,table_name,column_name,extra,is_generated,generation_expression
564 FROM information_schema.columns WHERE table_name='gcol_t1';
565
566 DROP TABLE gcol_t1;
567
568 --echo #
569 --echo # MDEV-16039 Crash when selecting virtual columns
570 --echo # generated using functions with DAYNAME()
571 --echo #
572
573 CREATE TABLE t1 (
574 suppliersenttoday INT NOT NULL,
575 suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05')))
576 ) COLLATE utf8_bin;
577
578 INSERT INTO t1 (suppliersenttoday) VALUES (0);
579 INSERT INTO t1 (suppliersenttoday) VALUES (0);
580 SELECT * FROM t1;
581
582 PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)';
583
584 CREATE OR REPLACE TABLE t1 (
585 suppliersenttoday INT NOT NULL,
586 suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05')))
587 ) COLLATE utf8_bin;
588
589 EXECUTE STMT;
590 EXECUTE STMT;
591 SELECT * FROM t1;
592
593 DROP TABLE t1;
594
595 --echo # (duplicate) MDEV-20380 Server crash during update
596 CREATE TABLE gafld (
597 nuigafld INTEGER NOT NULL,
598 ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL
599 DEFAULT SUBSTRING_INDEX(USER(),'@',1)
600 );
601 EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10;
602 EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10;
603 DROP TABLE gafld;
604
605 --echo # (duplicate) MDEV-17653 replace into generated columns is unstable
606 --echo # Some columns are snipped from the MDEV test
607 CREATE TABLE t (
608 c0 TIMESTAMP NOT NULL DEFAULT current_timestamp()
609 ON UPDATE current_timestamp(),
610 c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH('2020-02-05')),
611 c4 TIME NOT NULL,
612 c8 SMALLINT(6) GENERATED ALWAYS AS
613 (CONCAT_WS(CONVERT(C1 USING CP932),
614 '900') <> (c4 = 1)),
615 PRIMARY KEY (c4)
616 ) DEFAULT CHARSET=latin1;
617
618 REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55';
619 REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55';
620 REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55';
621
622 DROP TABLE t;
623
624 --echo # (duplicate) MDEV-17986 crash when I insert on a table
625 CREATE OR REPLACE TABLE t2 (
626 number BIGINT(20) NOT NULL,
627 lrn BIGINT(20) NOT NULL DEFAULT 0,
628 source VARCHAR(15) NOT NULL
629 DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))),
630 PRIMARY KEY (number)
631 );
632
633 REPLACE t2(number) VALUES('1');
634 REPLACE t2(number) VALUES('1');
635
636 DROP TABLE t2;
637
638 --echo # MDEV-24583 SELECT aborts after failed REPLACE into table with vcol
639
640 CREATE TABLE t1 (pk INT, a VARCHAR(3), v VARCHAR(3) AS (CONCAT('x-',a)),
641 PRIMARY KEY(pk)) ENGINE=MyISAM;
642 CREATE VIEW v1 AS SELECT * FROM t1;
643 INSERT INTO t1 (pk, a) VALUES (1,'foo');
644 SET sql_mode=CONCAT(@@sql_mode,',STRICT_ALL_TABLES');
645 --error 0,ER_DATA_TOO_LONG
646 REPLACE INTO t1 (pk,a) VALUES (1,'qux');
647 SELECT * FROM v1;
648
649 # Cleanup
650 DROP VIEW v1;
651 DROP TABLE t1;
652
653 CREATE TABLE t1 (
654 pk INT,
655 a VARCHAR(1),
656 v VARCHAR(1) AS (CONCAT('virt-',a)) VIRTUAL,
657 PRIMARY KEY (pk)
658 ) ENGINE=InnoDB;
659
660 INSERT INTO t1 (pk,a) VALUES
661 (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f');
662
663 --error ER_DATA_TOO_LONG
664 REPLACE INTO t1 (pk) VALUES (1);
665 SELECT * FROM t1 ORDER BY a;
666
667 SET SQL_MODE=DEFAULT;
668 DROP TABLE t1;
669
670 --echo # (duplicate) MDEV-24656
671 --echo # [FATAL] InnoDB: Data field type 0, len 0, ASAN heap-buffer-overflow
672 --echo # upon LOAD DATA with virtual columns
673
674 CREATE TABLE t1 (id INT PRIMARY KEY, a VARCHAR(2333),
675 va VARCHAR(171) AS (a)) ENGINE=InnoDB;
676 INSERT INTO t1 (id,a) VALUES (1,REPEAT('x',200));
677 SELECT id, va INTO OUTFILE 'load_t1' FROM t1;
678 --error ER_DATA_TOO_LONG
679 LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va);
680 SELECT * FROM t1;
681 LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va);
682
683 DROP TABLE t1;
684 --let $datadir= `select @@datadir`
685 --remove_file $datadir/test/load_t1
686
687 CREATE TABLE t1 (id BIGINT PRIMARY KEY, a VARCHAR(2333),
688 va VARCHAR(171) AS (a)) ENGINE=InnoDB;
689 INSERT INTO t1 (id,a) VALUES (1,REPEAT('x',200));
690 SELECT id, va INTO OUTFILE 'load_t1' FROM t1;
691 --error ER_DATA_TOO_LONG
692 LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va);
693 SELECT * FROM t1;
694 LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va);
695
696 # Cleanup
697 DROP TABLE t1;
698 --let $datadir= `select @@datadir`
699 --remove_file $datadir/test/load_t1
700
701
702 --echo # (duplicate) MDEV-24665
703 --echo # ASAN errors, assertion failures, corrupt values after failed
704 --echo # LOAD DATA into table with virtual/stored column
705
706 CREATE TABLE t1 (id INT PRIMARY KEY,
707 ts TIMESTAMP DEFAULT '1971-01-01 00:00:00',
708 c VARBINARY(8) DEFAULT '', vc VARCHAR(3) AS (c) STORED);
709 INSERT IGNORE INTO t1 (id,c) VALUES (1,'foobar');
710 SELECT id, ts, vc INTO OUTFILE 'load_t1' FROM t1;
711 --error 0,ER_DATA_TOO_LONG
712 LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id, ts, vc);
713 INSERT IGNORE INTO t1 (id) VALUES (2);
714
715 # Cleanup
716 DROP TABLE t1;
717 --let $datadir= `select @@datadir`
718 --remove_file $datadir/test/load_t1
719
720
721 --echo #
722 --echo # MDEV-28566 Assertion `!expr->is_fixed()' failed in bool
723 --echo # Virtual_column_info::fix_session_expr(THD*)
724 --echo #
725
726 CREATE TABLE t1 (c1 CHAR(1));
727 FLUSH TABLES WITH READ LOCK;
728 --error ER_CANT_UPDATE_WITH_READLOCK
729 UPDATE t1 SET c1=1;
730 unlock tables;
731 SELECT * FROM t1;
732 DROP TABLE t1;
733
734 CREATE TABLE t1 (c1 CHAR AS (CONCAT (0,DAYNAME (0))));
735 FLUSH TABLES WITH READ LOCK;
736 --error ER_CANT_UPDATE_WITH_READLOCK
737 UPDATE t1 SET c1=1;
738 unlock tables;
739 UPDATE t1 SET c1=1;
740 SELECT * FROM t1;
741 DROP TABLE t1;
742
743 CREATE TABLE t1 (a int primary key, c1 CHAR AS (CONCAT (0,DAYNAME (0))));
744 insert into t1 (a) values (1);
745 FLUSH TABLES WITH READ LOCK;
746 --error ER_CANT_UPDATE_WITH_READLOCK
747 UPDATE t1 SET c1=1;
748 --error ER_CANT_UPDATE_WITH_READLOCK
749 UPDATE t1 SET a=2;
750 unlock tables;
751 UPDATE t1 SET a=2;
752 --error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
753 UPDATE t1 SET c1=1;
754 SELECT * FROM t1;
755 DROP TABLE t1;