diff options
Diffstat (limited to 'mysql-test/suite/gcol/t/gcol_bugfixes.test')
| -rw-r--r-- | mysql-test/suite/gcol/t/gcol_bugfixes.test | 726 |
1 files changed, 726 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/t/gcol_bugfixes.test b/mysql-test/suite/gcol/t/gcol_bugfixes.test new file mode 100644 index 00000000..4c1b00a8 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_bugfixes.test @@ -0,0 +1,726 @@ +################################################################################ +# t/gcol_bugfixes.test # +# # +# Purpose: # +# Bug fixes that only need one storage engine # +# # +################################################################################ + +--source include/have_innodb.inc +--echo # 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); + +# This is likely needed to ensure we allocate a new record buffer that +# contains zero in the mis-used field +FLUSH TABLE t1; + +ALTER TABLE t1 ADD COLUMN extra INTEGER; + +DROP TABLE t1; + +--echo # +--echo # Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED +--echo # + +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; + +--echo # +--echo # Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED. +--echo # + +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); + +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'); + +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'); + +let $query= +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; + +eval EXPLAIN $query; +eval $query; +DROP TABLE bb, c, cc; + +--echo # 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'); + +ANALYZE TABLE c; +let $query= +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; + +eval explain $query; +eval $query; + +DROP TABLE c; + +--echo # 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; + +DROP TABLE t1; +--echo # 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; +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a)); +DROP TABLE t; + +--echo # +--echo # Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN +--echo # (II) +--echo # +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; + +--echo # Used to cause valgrind warning. +ALTER TABLE t1 ADD INDEX( c ); + +ANALYZE TABLE t1; + +--echo # Make sure the index is correct. That's kinda important. +EXPLAIN +SELECT c FROM t1; +SELECT c FROM t1; + +DROP TABLE t1; + +--echo # +--echo # Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED. +--echo # + +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; + +DROP TABLE C; +DROP VIEW v1; + +--echo # +--echo # Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET +--echo # + +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); + +let $query= +SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v ); +eval EXPLAIN $query; +eval $query; + +# A similar one: +let $query= +SELECT (SELECT MAX(c) FROM v); +eval EXPLAIN $query; +eval $query; + +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; +DROP TABLE v; + +# Using PK suffix of secondary index +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; +DROP TABLE v; + +--echo # +--echo # Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS +--echo # VIRTUAL GENERATED COLUMN +--echo # +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; +# DROP TRIGGER used to hit a DBUG_ASSERT. +DROP TRIGGER tr; +SELECT * FROM t; +CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t); +# And this function call hit the same DBUG_ASSERT. +SELECT f(); +DROP FUNCTION f; +SELECT * FROM t; +DROP TABLE t; + +--echo # +--echo # Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED. +--echo # + +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); + +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); +SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2; + +DROP TABLE C; +DROP VIEW view_C; + +--echo # +--echo # Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE +--echo # LOCATION +--echo # +CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL); +--error ER_BAD_FIELD_ERROR +ALTER TABLE t CHANGE COLUMN q w INT; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t CHANGE COLUMN q w INT; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t CHANGE COLUMN q w INT; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t CHANGE COLUMN q w INT; +DROP TABLE t; + +--echo # +--echo # Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED +--echo # + +CREATE TABLE t(b BLOB); +--error ER_OPERAND_COLUMNS +ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL; +DROP TABLE t; +--error ER_OPERAND_COLUMNS +CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL); + +--echo # +--echo # Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE +--echo # + +CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b)); +SHOW CREATE TABLE t1; +INSERT INTO t1 (a,b) VALUES('1','1'); +SELECT * FROM t1; +SET SQL_MODE=PIPES_AS_CONCAT; +SELECT * FROM t1; +FLUSH TABLES; +SELECT * FROM t1; +DROP TABLE t1; +# The other way around: +CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b)); +SHOW CREATE TABLE t1; +INSERT INTO t1 (a,b) VALUES('1','1'); +SELECT * FROM t1; +SET SQL_MODE=DEFAULT; +SELECT * FROM t1; +FLUSH TABLES; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # 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; +DROP TABLE t; + +CREATE TABLE t (a INTEGER) engine=innodb; + +ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1)); + +DROP TABLE t; + +set sql_mode= @save_old_sql_mode; + +--echo # 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; +SELECT c, a FROM t; +UPDATE t SET b='xyz'; +DO (SELECT @c1:= c FROM t); +DO (SELECT (@c2:= c) - a FROM t); +SELECT @c2 - @c1; + +DROP TABLE t; + +--echo # +--echo # Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA +--echo # + +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; +COMMIT; +DROP TABLE t; + +--echo # +--echo # Bug #25793677 INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN .... +--echo # + +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); +DROP TABLE v; + +--echo # +--echo # MDEV-9255 Add generation_expression to information_schema.columns. +--echo # + +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'; + +DROP TABLE gcol_t1; + +--echo # +--echo # MDEV-16039 Crash when selecting virtual columns +--echo # generated using functions with DAYNAME() +--echo # + +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; + +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; + +DROP TABLE t1; + +--echo # (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; +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +DROP TABLE gafld; + +--echo # (duplicate) MDEV-17653 replace into generated columns is unstable +--echo # 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; + +--echo # (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; + +--echo # 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'); +--error 0,ER_DATA_TOO_LONG +REPLACE INTO t1 (pk,a) VALUES (1,'qux'); +SELECT * FROM v1; + +# Cleanup +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'); + + --error ER_DATA_TOO_LONG +REPLACE INTO t1 (pk) VALUES (1); +SELECT * FROM t1 ORDER BY a; + +SET SQL_MODE=DEFAULT; +DROP TABLE t1; + +--echo # (duplicate) MDEV-24656 +--echo # [FATAL] InnoDB: Data field type 0, len 0, ASAN heap-buffer-overflow +--echo # 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)); +--disable_ps2_protocol +SELECT id, va INTO OUTFILE 'load_t1' FROM t1; +--enable_ps2_protocol +--error ER_DATA_TOO_LONG +LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va); +SELECT * FROM t1; +LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va); + +DROP TABLE t1; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load_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)); +--disable_ps2_protocol +SELECT id, va INTO OUTFILE 'load_t1' FROM t1; +--enable_ps2_protocol +--error ER_DATA_TOO_LONG +LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id,va); +SELECT * FROM t1; +LOAD DATA INFILE 'load_t1' IGNORE INTO TABLE t1 (id,va); + +# Cleanup +DROP TABLE t1; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load_t1 + + +--echo # (duplicate) MDEV-24665 +--echo # ASAN errors, assertion failures, corrupt values after failed +--echo # 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'); +--disable_ps2_protocol +SELECT id, ts, vc INTO OUTFILE 'load_t1' FROM t1; +--enable_ps2_protocol +--error 0,ER_DATA_TOO_LONG +LOAD DATA INFILE 'load_t1' REPLACE INTO TABLE t1 (id, ts, vc); +INSERT IGNORE INTO t1 (id) VALUES (2); + +# Cleanup +DROP TABLE t1; +--let $datadir= `select @@datadir` +--remove_file $datadir/test/load_t1 + + |