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