diff options
Diffstat (limited to 'mysql-test/suite/gcol/r/gcol_bugfixes.result')
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_bugfixes.result | 746 |
1 files changed, 746 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/r/gcol_bugfixes.result b/mysql-test/suite/gcol/r/gcol_bugfixes.result new file mode 100644 index 00000000..f124ebe6 --- /dev/null +++ b/mysql-test/suite/gcol/r/gcol_bugfixes.result @@ -0,0 +1,746 @@ +# Bug#21230709: Alter table statement fails with division by zero +CREATE TABLE t1 ( +col1 INTEGER NOT NULL, +col2 INTEGER NOT NULL, +col3 INTEGER NOT NULL, +gcol1 INTEGER GENERATED ALWAYS AS (col3 + col3) VIRTUAL, +col4 INTEGER DEFAULT NULL, +col5 INTEGER DEFAULT NULL, +col6 INTEGER DEFAULT NULL, +col7 INTEGER DEFAULT NULL, +col8 INTEGER DEFAULT NULL, +col9 INTEGER DEFAULT NULL, +col10 INTEGER DEFAULT NULL, +col11 INTEGER DEFAULT NULL, +col12 INTEGER DEFAULT NULL, +col13 INTEGER DEFAULT NULL, +col14 INTEGER DEFAULT NULL, +col15 INTEGER DEFAULT NULL, +col16 INTEGER DEFAULT NULL, +col17 INTEGER DEFAULT NULL, +col18 INTEGER DEFAULT NULL, +col19 INTEGER DEFAULT NULL, +col20 INTEGER DEFAULT NULL, +col21 INTEGER DEFAULT NULL, +col22 INTEGER DEFAULT NULL, +col23 INTEGER DEFAULT NULL, +col24 INTEGER DEFAULT NULL, +col25 INTEGER DEFAULT NULL, +col26 INTEGER DEFAULT NULL, +col27 INTEGER DEFAULT NULL, +col28 INTEGER DEFAULT NULL, +col29 INTEGER DEFAULT NULL, +col30 INTEGER DEFAULT NULL, +col31 INTEGER DEFAULT NULL, +col32 INTEGER DEFAULT NULL, +col33 INTEGER DEFAULT NULL, +gcol2 INTEGER GENERATED ALWAYS AS (col2 + col2) VIRTUAL, +gcol3 INTEGER GENERATED ALWAYS AS (gcol2 / gcol2) VIRTUAL, +PRIMARY KEY (col1), +KEY idx1 (gcol1) +) engine=innodb; +INSERT INTO t1 (col1, col2, col3) +VALUES (0,1,2), (1,2,3), (2,3,4), (3,4,5), (4,5,6); +FLUSH TABLE t1; +ALTER TABLE t1 ADD COLUMN extra INTEGER; +DROP TABLE t1; +# +# Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED +# +CREATE TABLE t1 ( +c_blob BLOB, +c_blob_key BLOB GENERATED ALWAYS AS (REPEAT(c_blob,15)) STORED, +KEY (c_blob_key(200)) +); +INSERT INTO t1 (c_blob) VALUES ('xceks'); +DROP TABLE t1; +# +# Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED. +# +CREATE TABLE c ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER, +gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL, +col_date_nokey DATE, +gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, +col_datetime_nokey DATETIME, +col_time_nokey TIME, +gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +col_varchar_nokey VARCHAR(1), +gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL, +PRIMARY KEY (pk), +UNIQUE KEY (gcol_int_key), +UNIQUE KEY (gcol_varchar_key), +UNIQUE KEY (gcol_date_key), +KEY (gcol_time_key), +KEY (gcol_datetime_key), +UNIQUE KEY (gcol_int_key, gcol_varchar_key), +KEY (gcol_int_key, col_int_nokey), +KEY(gcol_int_key,gcol_date_key), +KEY(gcol_int_key, gcol_time_key), +KEY(gcol_int_key, gcol_datetime_key), +UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key), +UNIQUE KEY (gcol_varchar_key, col_varchar_nokey), +UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key) +) ENGINE=INNODB; +INSERT IGNORE INTO c ( col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) +VALUES (7, '2004-04-09', '14:03:03.042673', '2001-11-28 00:50:27.051028', 'c'), +(1, '2006-05-13', '01:46:09.016386', '2007-10-09 19:53:04.008332', NULL); +Warnings: +Note 1265 Data truncated for column 'gcol_time_key' at row 1 +Note 1265 Data truncated for column 'gcol_time_key' at row 2 +CREATE TABLE bb ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER, +gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL, +col_date_nokey DATE, +gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, +col_datetime_nokey DATETIME, +col_time_nokey TIME, +gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +col_varchar_nokey VARCHAR(1), +gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL, +PRIMARY KEY (pk), +UNIQUE KEY (gcol_int_key), +UNIQUE KEY (gcol_varchar_key), +UNIQUE KEY (gcol_date_key), +KEY (gcol_time_key), +KEY (gcol_datetime_key), +UNIQUE KEY (gcol_int_key, gcol_varchar_key), +KEY (gcol_int_key, col_int_nokey), +KEY(gcol_int_key,gcol_date_key), +KEY(gcol_int_key, gcol_time_key), +KEY(gcol_int_key, gcol_datetime_key), +UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key), +UNIQUE KEY (gcol_varchar_key, col_varchar_nokey), +UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key) +) AUTO_INCREMENT=10 ENGINE=INNODB; +INSERT IGNORE INTO bb ( col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) +VALUES (0, '2003-08-04', '01:48:05.048577', '2006-11-03 00:00:00', 'p'), +(2, '2007-11-06', '00:00:00', '2009-11-26 19:28:11.005115', 'n'); +Warnings: +Note 1265 Data truncated for column 'gcol_time_key' at row 1 +Note 1265 Data truncated for column 'gcol_time_key' at row 2 +CREATE TABLE cc ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER, +gcol_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL, +col_date_nokey DATE, +gcol_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, +col_datetime_nokey DATETIME, +col_time_nokey TIME, +gcol_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +gcol_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +col_varchar_nokey VARCHAR(1), +gcol_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL, +PRIMARY KEY (pk), +UNIQUE KEY (gcol_int_key), +UNIQUE KEY (gcol_varchar_key), +UNIQUE KEY (gcol_date_key), +KEY (gcol_time_key), +KEY (gcol_datetime_key), +UNIQUE KEY (gcol_int_key, gcol_varchar_key), +KEY (gcol_int_key, col_int_nokey), +KEY(gcol_int_key,gcol_date_key), +KEY(gcol_int_key, gcol_time_key), +KEY(gcol_int_key, gcol_datetime_key), +UNIQUE KEY(gcol_date_key,gcol_time_key,gcol_datetime_key), +UNIQUE KEY (gcol_varchar_key, col_varchar_nokey), +UNIQUE KEY (gcol_int_key, gcol_varchar_key, gcol_date_key, gcol_time_key, gcol_datetime_key) +) AUTO_INCREMENT=10 ENGINE=INNODB; +INSERT IGNORE INTO cc (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) +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'); +Warnings: +Note 1265 Data truncated for column 'gcol_time_key' at row 1 +EXPLAIN SELECT +gp1 . gcol_datetime_key AS g1 +FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . gcol_datetime_key <> gp1 . +col_time_nokey ) +WHERE +gp1 . col_varchar_nokey IN +( +SELECT +DISTINCT p1 . gcol_varchar_key AS p1 +FROM bb AS p1 LEFT JOIN bb AS p2 +ON ( p1 . gcol_int_key = p2 . pk ) +) +AND gp1 . col_varchar_nokey = 'b' +HAVING g1 > 6; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT +gp1 . gcol_datetime_key AS g1 +FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . gcol_datetime_key <> gp1 . +col_time_nokey ) +WHERE +gp1 . col_varchar_nokey IN +( +SELECT +DISTINCT p1 . gcol_varchar_key AS p1 +FROM bb AS p1 LEFT JOIN bb AS p2 +ON ( p1 . gcol_int_key = p2 . pk ) +) +AND gp1 . col_varchar_nokey = 'b' +HAVING g1 > 6; +g1 +DROP TABLE bb, c, cc; +# Bug#21284646: Assertion !(table || table->read_set || bitmap_is_set()) +CREATE TABLE c ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER NOT NULL, +col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL, +col_date_nokey DATE NOT NULL, +col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, +col_datetime_nokey DATETIME NOT NULL, +col_time_nokey TIME NOT NULL, +col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL, +col_varchar_nokey VARCHAR(1) NOT NULL, +col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL, +PRIMARY KEY (pk,col_int_nokey), +UNIQUE KEY (col_int_key), +UNIQUE KEY (col_varchar_key), +UNIQUE KEY (col_date_key), +KEY (col_time_key), +KEY (col_datetime_key), +UNIQUE KEY (col_int_key, col_varchar_key), +KEY (col_int_key, col_int_nokey), +KEY(col_int_key,col_date_key), +KEY(col_int_key, col_time_key), +KEY(col_int_key, col_datetime_key), +UNIQUE KEY (col_date_key,col_time_key,col_datetime_key), +UNIQUE KEY (col_varchar_key, col_varchar_nokey), +UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key) +) ENGINE=INNODB; +INSERT INTO c (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) VALUES +(1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), +(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), +(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), +(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), +(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'), +(4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'); +Warnings: +Note 1265 Data truncated for column 'col_time_key' at row 1 +Note 1265 Data truncated for column 'col_time_key' at row 2 +Note 1265 Data truncated for column 'col_time_key' at row 3 +Note 1265 Data truncated for column 'col_time_key' at row 4 +Note 1265 Data truncated for column 'col_time_key' at row 5 +Note 1265 Data truncated for column 'col_time_key' at row 6 +ANALYZE TABLE c; +Table Op Msg_type Msg_text +test.c analyze status Engine-independent statistics collected +test.c analyze status OK +explain SELECT COUNT(DISTINCT col_varchar_key) AS x +FROM c +WHERE col_varchar_key IN ('rr', 'rr') OR +col_int_nokey <> 9 AND +pk >= 8 +HAVING x > '2000-02-06' +ORDER BY col_time_nokey, pk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE c index_merge PRIMARY,col_varchar_key,col_varchar_key_2 col_varchar_key,PRIMARY 5,4 NULL 2 Using union(col_varchar_key,PRIMARY); Using where +SELECT COUNT(DISTINCT col_varchar_key) AS x +FROM c +WHERE col_varchar_key IN ('rr', 'rr') OR +col_int_nokey <> 9 AND +pk >= 8 +HAVING x > '2000-02-06' +ORDER BY col_time_nokey, pk; +x +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '2000-02-06' +DROP TABLE c; +# Bug#21341044: Conditional jump at sort_param::make_sort_key +CREATE TABLE t1 ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER, +col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL, +col_blob_nokey BLOB, +col_blob_key BLOB GENERATED ALWAYS AS (REPEAT(col_blob_nokey,15)) VIRTUAL, +col_longblob_nokey LONGBLOB, +col_longtext_nokey LONGTEXT, +col_longblob_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 20)) VIRTUAL, +col_longtext_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 18)) VIRTUAL, +col_text_nokey TEXT, +col_text_key TEXT GENERATED ALWAYS AS (REPEAT(col_text_nokey, 30)) VIRTUAL, +PRIMARY KEY (pk), +KEY (col_int_key), +KEY (col_text_key(50)), +KEY (col_blob_key(200)), +KEY (col_longtext_key(200)), +KEY (col_longblob_key(200)), +KEY (col_int_key, col_text_key(100)), +KEY (col_int_key, col_longtext_key(100)), +KEY (col_int_key, col_blob_key(100)), +KEY (col_int_key, col_longblob_key(100)), +KEY (col_longtext_key(10), col_longblob_key(100)), +KEY (col_int_key, col_text_key(10), col_blob_key(100), col_longtext_key(50), col_longblob_key(50)) +) engine=innodb; +INSERT INTO t1 (col_int_nokey,col_blob_nokey,col_longtext_nokey,col_longblob_nokey,col_text_nokey) +VALUES +(0, 'ijcszxw', 'ijcszxw', 'ijcszxw', 'ijcszxw'), +(5, 'jcszxwb', 'jcszxwb', 'jcszxwb', 'jcszxwb'), +(4, 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv'), +(3, 'szxw', 'szxw', 'szxw', 'szxw'), +(7, 'zxwb', 'zxwb', 'zxwb', 'zxwb'), +(42, 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky'), +(142, 'wbjj', 'wbjj', 'wbjj', 'wbjj'), +(5, 'bjjv', 'bjjv', 'bjjv', 'bjjv'), +(0, 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu'), +(3, 'j', 'j', 'j', 'j'); +SELECT alias1.pk AS field1 +FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2 +ON alias1.col_int_key = alias2.col_int_key +WHERE alias2.col_int_key BETWEEN 8 AND (8 + 1 ) OR +alias2.col_int_key BETWEEN 8 AND (8 + 5 ) AND +alias2.col_int_key != 20 OR +alias2.col_int_key IN (8, 5, 8) AND +alias2.col_int_key >= 0 AND +alias2.col_int_key <= ( 8 + 75 ) AND +alias1.pk IS NOT NULL +ORDER BY field1; +field1 +2 +2 +3 +8 +8 +DROP TABLE t1; +# bug#21487651: gcols: memory leak after failed alter table +CREATE TABLE t(a int); +ALTER TABLE t ADD COLUMN b int GENERATED ALWAYS AS ( +date_sub(a,interval a month)) VIRTUAL; +ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a)); +ERROR HY000: Function or expression 'sum()' cannot be used in the GENERATED ALWAYS AS clause of `c` +DROP TABLE t; +# +# Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN +# (II) +# +CREATE TABLE t1( a INT ) ENGINE = INNODB; +INSERT INTO t1( a ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ); +ALTER TABLE t1 ADD COLUMN b INT GENERATED ALWAYS AS (a - 1) STORED; +ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS (b + 1) VIRTUAL; +# Used to cause valgrind warning. +ALTER TABLE t1 ADD INDEX( c ); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +# Make sure the index is correct. That's kinda important. +EXPLAIN +SELECT c FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c 5 NULL 5 Using index +SELECT c FROM t1; +c +1 +2 +3 +4 +5 +DROP TABLE t1; +# +# Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED. +# +CREATE TABLE C ( +col_int_1 INT, +col_int_2 INT GENERATED ALWAYS AS (col_int_1 + col_int_1) STORED, +col_int_3 INT GENERATED ALWAYS AS (col_int_2 + col_int_1) VIRTUAL +); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS +SELECT +col_int_2 AS field1, col_int_2 AS field2, +col_int_3 AS field3, col_int_3 AS field4 +FROM C; +SELECT * FROM v1; +field1 field2 field3 field4 +DROP TABLE C; +DROP VIEW v1; +# +# Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET +# +CREATE TABLE t (a INT); +CREATE TABLE v ( +a INT, +c INT, +b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, +KEY(c,b(1))); +INSERT INTO v (a,c) VALUES (1,1); +EXPLAIN SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v ); +1 +EXPLAIN SELECT (SELECT MAX(c) FROM v); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT (SELECT MAX(c) FROM v); +(SELECT MAX(c) FROM v) +1 +DROP TABLE t, v; +CREATE TABLE v ( +a INT, +c INT, +b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, KEY(c,b(1))); +INSERT INTO v (a,c) VALUES (1,1); +SELECT MAX(c), COUNT(b) FROM v; +MAX(c) COUNT(b) +1 1 +DROP TABLE v; +CREATE TABLE v ( +a INT PRIMARY KEY, +b INT, KEY(b)); +INSERT INTO v (a,b) VALUES (1,1); +SELECT MAX(a) FROM v WHERE b=1; +MAX(a) +1 +DROP TABLE v; +# +# Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS +# VIRTUAL GENERATED COLUMN +# +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL); +CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW BEGIN END; +INSERT INTO t (a) VALUES (1); +SELECT * FROM t; +a b +1 1 +DROP TRIGGER tr; +SELECT * FROM t; +a b +1 1 +CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t); +SELECT f(); +f() +1 +DROP FUNCTION f; +SELECT * FROM t; +a b +1 1 +DROP TABLE t; +# +# Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED. +# +CREATE TABLE C( +c1 INT AUTO_INCREMENT, +c8 DATETIME, +c9 TIME, +c11 TIME GENERATED ALWAYS AS(ADDTIME(c8,c9)) VIRTUAL, +c13 TIME GENERATED ALWAYS AS(ADDTIME(c8,c11)) VIRTUAL, +PRIMARY KEY(c1), +UNIQUE KEY(c13) +); +INSERT INTO C (c8,c9) VALUES('1970-01-01',0),('1970-01-01',1); +Warnings: +Note 1265 Data truncated for column 'c11' at row 1 +Note 1265 Data truncated for column 'c13' at row 1 +Note 1265 Data truncated for column 'c11' at row 2 +Note 1265 Data truncated for column 'c13' at row 2 +CREATE VIEW view_C AS SELECT * FROM C; +SELECT /*+ NO_BNL(t1) */ t1.c13 FROM C AS t2 STRAIGHT_JOIN C AS t1 FORCE INDEX(c13); +c13 +00:00:00 +00:00:00 +00:00:01 +00:00:01 +SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2; +c13 +00:00:00 +00:00:01 +DROP TABLE C; +DROP VIEW view_C; +# +# Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE +# LOCATION +# +CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL); +ALTER TABLE t CHANGE COLUMN q w INT; +ERROR 42S22: Unknown column 'q' in 't' +ALTER TABLE t CHANGE COLUMN q w INT; +ERROR 42S22: Unknown column 'q' in 't' +ALTER TABLE t CHANGE COLUMN q w INT; +ERROR 42S22: Unknown column 'q' in 't' +ALTER TABLE t CHANGE COLUMN q w INT; +ERROR 42S22: Unknown column 'q' in 't' +DROP TABLE t; +# +# Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED +# +CREATE TABLE t(b BLOB); +ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL; +ERROR 21000: Operand should contain 1 column(s) +DROP TABLE t; +CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL); +ERROR 21000: Operand should contain 1 column(s) +# +# Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE +# +CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) DEFAULT NULL, + `b` char(1) DEFAULT NULL, + `c` char(2) GENERATED ALWAYS AS (`a` <> 0 or `b` <> 0) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 (a,b) VALUES('1','1'); +SELECT * FROM t1; +a b c +1 1 1 +SET SQL_MODE=PIPES_AS_CONCAT; +SELECT * FROM t1; +a b c +1 1 1 +FLUSH TABLES; +SELECT * FROM t1; +a b c +1 1 1 +DROP TABLE t1; +CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(1) DEFAULT NULL, + `b` char(1) DEFAULT NULL, + `c` char(2) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 (a,b) VALUES('1','1'); +SELECT * FROM t1; +a b c +1 1 11 +SET SQL_MODE=DEFAULT; +SELECT * FROM t1; +a b c +1 1 11 +FLUSH TABLES; +SELECT * FROM t1; +a b c +1 1 11 +DROP TABLE t1; +# Bug#22018999: gcols: assertion failed: !error +SET @save_old_sql_mode= @@sql_mode; +SET sql_mode=""; +CREATE TABLE t (a INTEGER AS (SUBSTR('','a',1))) engine=innodb; +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' +DROP TABLE t; +CREATE TABLE t (a INTEGER) engine=innodb; +ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1)); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' +DROP TABLE t; +set sql_mode= @save_old_sql_mode; +# Bug#21875520 Problems with virtual column indexes +CREATE TABLE t( +a TIMESTAMP, +b BLOB, +c TIMESTAMP GENERATED ALWAYS AS (GREATEST(a, '2000-01-01 00:00:00')) VIRTUAL, +UNIQUE KEY(c) +); +INSERT INTO t(b) VALUES (''); +UPDATE t SET a='2001-01-01 00:00:00'; +SELECT c FROM t; +c +2001-01-01 00:00:00 +SELECT c, a FROM t; +c a +2001-01-01 00:00:00 2001-01-01 00:00:00 +UPDATE t SET b='xyz'; +DO (SELECT @c1:= c FROM t); +DO (SELECT (@c2:= c) - a FROM t); +SELECT @c2 - @c1; +@c2 - @c1 +0 +DROP TABLE t; +# +# Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA +# +CREATE TABLE t ( +a INT, +b INT GENERATED ALWAYS AS (1) VIRTUAL, +c INT GENERATED ALWAYS AS (1) VIRTUAL, +d INT GENERATED ALWAYS AS (1) VIRTUAL, +KEY (b,d) +) ENGINE=INNODB; +INSERT INTO t VALUES(); +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; +SELECT 1 FROM t WHERE c GROUP BY b; +1 +1 +COMMIT; +DROP TABLE t; +# +# Bug #25793677 INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN .... +# +CREATE TABLE v ( +a INT, +c INT, +b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, +KEY(c,b(1))) charset utf8mb4; +INSERT INTO v (a,c) VALUES (1,1); +SELECT (SELECT MAX(c) FROM v); +(SELECT MAX(c) FROM v) +1 +DROP TABLE v; +# +# MDEV-9255 Add generation_expression to information_schema.columns. +# +CREATE TABLE gcol_t1 ( +sidea DOUBLE, +sideb DOUBLE, +sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)) +); +SELECT table_schema,table_name,column_name,extra,is_generated,generation_expression +FROM information_schema.columns WHERE table_name='gcol_t1'; +table_schema table_name column_name extra is_generated generation_expression +test gcol_t1 sidea NEVER NULL +test gcol_t1 sideb NEVER NULL +test gcol_t1 sidec VIRTUAL GENERATED ALWAYS sqrt(`sidea` * `sidea` + `sideb` * `sideb`) +DROP TABLE gcol_t1; +# +# MDEV-16039 Crash when selecting virtual columns +# generated using functions with DAYNAME() +# +CREATE TABLE t1 ( +suppliersenttoday INT NOT NULL, +suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05'))) +) COLLATE utf8_bin; +INSERT INTO t1 (suppliersenttoday) VALUES (0); +INSERT INTO t1 (suppliersenttoday) VALUES (0); +SELECT * FROM t1; +suppliersenttoday suppliercaptoday +0 Wednesday +0 Wednesday +PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)'; +CREATE OR REPLACE TABLE t1 ( +suppliersenttoday INT NOT NULL, +suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05'))) +) COLLATE utf8_bin; +EXECUTE STMT; +EXECUTE STMT; +SELECT * FROM t1; +suppliersenttoday suppliercaptoday +1 Wednesday +1 Wednesday +DROP TABLE t1; +# (duplicate) MDEV-20380 Server crash during update +CREATE TABLE gafld ( +nuigafld INTEGER NOT NULL, +ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL +DEFAULT SUBSTRING_INDEX(USER(),'@',1) +); +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +DROP TABLE gafld; +# (duplicate) MDEV-17653 replace into generated columns is unstable +# Some columns are snipped from the MDEV test +CREATE TABLE t ( +c0 TIMESTAMP NOT NULL DEFAULT current_timestamp() +ON UPDATE current_timestamp(), +c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH('2020-02-05')), +c4 TIME NOT NULL, +c8 SMALLINT(6) GENERATED ALWAYS AS +(CONCAT_WS(CONVERT(C1 USING CP932), +'900') <> (c4 = 1)), +PRIMARY KEY (c4) +) DEFAULT CHARSET=latin1; +REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55'; +DROP TABLE t; +# (duplicate) MDEV-17986 crash when I insert on a table +CREATE OR REPLACE TABLE t2 ( +number BIGINT(20) NOT NULL, +lrn BIGINT(20) NOT NULL DEFAULT 0, +source VARCHAR(15) NOT NULL +DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))), +PRIMARY KEY (number) +); +REPLACE t2(number) VALUES('1'); +REPLACE t2(number) VALUES('1'); +DROP TABLE t2; +# MDEV-24583 SELECT aborts after failed REPLACE into table with vcol +CREATE TABLE t1 (pk INT, a VARCHAR(3), v VARCHAR(3) AS (CONCAT('x-',a)), +PRIMARY KEY(pk)) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 (pk, a) VALUES (1,'foo'); +SET sql_mode=CONCAT(@@sql_mode,',STRICT_ALL_TABLES'); +REPLACE INTO t1 (pk,a) VALUES (1,'qux'); +SELECT * FROM v1; +pk a v +1 foo x-f +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 ( +pk INT, +a VARCHAR(1), +v VARCHAR(1) AS (CONCAT('virt-',a)) VIRTUAL, +PRIMARY KEY (pk) +) ENGINE=InnoDB; +INSERT INTO t1 (pk,a) VALUES +(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'); +REPLACE INTO t1 (pk) VALUES (1); +ERROR 22001: Data too long for column 'v' at row 1 +SELECT * FROM t1 ORDER BY a; +pk a v +1 a v +2 b v +3 c v +4 d v +5 e v +6 f v +SET SQL_MODE=DEFAULT; +DROP TABLE t1; +# (duplicate) MDEV-24656 +# [FATAL] InnoDB: Data field type 0, len 0, ASAN heap-buffer-overflow +# upon LOAD DATA with virtual columns +CREATE TABLE t1 (id INT PRIMARY KEY, a VARCHAR(2333), +va VARCHAR(171) AS (a)) ENGINE=InnoDB; +INSERT INTO t1 (id,a) VALUES (1,REPEAT('x',200)); +SELECT id, va INTO OUTFILE 'load_t1' FROM t1; +LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va); +ERROR 22001: Data too long for column 'va' at row 1 +SELECT * FROM t1; +id a va +1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +DROP TABLE t1; +CREATE TABLE t1 (id BIGINT PRIMARY KEY, a VARCHAR(2333), +va VARCHAR(171) AS (a)) ENGINE=InnoDB; +INSERT INTO t1 (id,a) VALUES (1,REPEAT('x',200)); +SELECT id, va INTO OUTFILE 'load_t1' FROM t1; +LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va); +ERROR 22001: Data too long for column 'va' at row 1 +SELECT * FROM t1; +id a va +1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +DROP TABLE t1; +# (duplicate) MDEV-24665 +# ASAN errors, assertion failures, corrupt values after failed +# LOAD DATA into table with virtual/stored column +CREATE TABLE t1 (id INT PRIMARY KEY, +ts TIMESTAMP DEFAULT '1971-01-01 00:00:00', +c VARBINARY(8) DEFAULT '', vc VARCHAR(3) AS (c) STORED); +INSERT IGNORE INTO t1 (id,c) VALUES (1,'foobar'); +Warnings: +Warning 1265 Data truncated for column 'vc' at row 1 +SELECT id, ts, vc INTO OUTFILE 'load_t1' FROM t1; +LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id, ts, vc); +INSERT IGNORE INTO t1 (id) VALUES (2); +DROP TABLE t1; |