diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 12:24:36 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-13 12:24:36 +0000 |
| commit | 06eaf7232e9a920468c0f8d74dcf2fe8b555501c (patch) | |
| tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/gcol/t | |
| parent | Initial commit. (diff) | |
| download | mariadb-06eaf7232e9a920468c0f8d74dcf2fe8b555501c.tar.xz mariadb-06eaf7232e9a920468c0f8d74dcf2fe8b555501c.zip | |
Adding upstream version 1:10.11.6.upstream/1%10.11.6
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/gcol/t')
51 files changed, 6311 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/t/gcol_archive.test b/mysql-test/suite/gcol/t/gcol_archive.test new file mode 100644 index 00000000..6dce34e1 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_archive.test @@ -0,0 +1,44 @@ +################################################################################ +# t/gcol_archive.test # +# # +# Purpose: # +# ARCHIVE branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_archive.inc +SET @@session.default_storage_engine = 'archive'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests +--source suite/gcol/inc/gcol_unsupported_storage_engines.inc + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_blackhole.test b/mysql-test/suite/gcol/t/gcol_blackhole.test new file mode 100644 index 00000000..bf0d3fb3 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_blackhole.test @@ -0,0 +1,44 @@ +################################################################################ +# t/gcol_blackhole.test # +# # +# Purpose: # +# BLACKHOLE branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_blackhole.inc +SET @@session.default_storage_engine = 'blackhole'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests +--source suite/gcol/inc/gcol_unsupported_storage_engines.inc + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_blocked_sql_funcs_innodb.test b/mysql-test/suite/gcol/t/gcol_blocked_sql_funcs_innodb.test new file mode 100644 index 00000000..a1024b33 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_blocked_sql_funcs_innodb.test @@ -0,0 +1,47 @@ +################################################################################ +# t/gcol_supported_sql_funcs.test # +# # +# Purpose: # +# Test SQL functions not allowed for generated columns # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-08-31 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +let $skip_full_text_check = 1; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_blocked_sql_funcs_main.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_blocked_sql_funcs_myisam.test b/mysql-test/suite/gcol/t/gcol_blocked_sql_funcs_myisam.test new file mode 100644 index 00000000..0f967ee8 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_blocked_sql_funcs_myisam.test @@ -0,0 +1,44 @@ +################################################################################ +# t/gcol_supported_sql_funcs.test # +# # +# Purpose: # +# Test SQL functions not allowed for generated columns # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-08-31 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_blocked_sql_funcs_main.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_bug20746926.test b/mysql-test/suite/gcol/t/gcol_bug20746926.test new file mode 100644 index 00000000..8028823f --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_bug20746926.test @@ -0,0 +1,28 @@ +--echo #Bug #20746926: GENERATED COLUMNS: INVALID READ OF THD WHEN WARNINGS +--echo # +--echo # Testing cmp_item_datetime +connect(con1,localhost,root,,); +--disable_warnings +set sql_mode=''; +--enable_warnings +create table t1 ( +a date not null, +b mediumtext generated always as ((a not in (a,a))) virtual, +c timestamp generated always as ((a not in (b,b))) stored +); +insert t1(a) values(7777777777); +show warnings; +disconnect con1; +--source include/wait_until_disconnected.inc + +connect(con2,localhost,root,,); +--disable_warnings +set sql_mode=''; +--enable_warnings +insert t1(a) values(6666666666); +show warnings; + +drop table t1; +disconnect con2; +--source include/wait_until_disconnected.inc +connection default; 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 + + diff --git a/mysql-test/suite/gcol/t/gcol_column_def_options_innodb.test b/mysql-test/suite/gcol/t/gcol_column_def_options_innodb.test new file mode 100644 index 00000000..68931cd0 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_column_def_options_innodb.test @@ -0,0 +1,47 @@ +################################################################################ +# t/gcol_column_def_options_innodb.test # +# # +# Purpose: # +# Testing different optional parameters of generated columns. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 1; +--source suite/gcol/inc/gcol_column_def_options.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_column_def_options_myisam.test b/mysql-test/suite/gcol/t/gcol_column_def_options_myisam.test new file mode 100644 index 00000000..6f04ecf3 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_column_def_options_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/gcol_column_def_options_myisam.test # +# # +# Purpose: # +# Testing different optional parameters of generated columns. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 1; +--source suite/gcol/inc/gcol_column_def_options.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_handler_innodb.test b/mysql-test/suite/gcol/t/gcol_handler_innodb.test new file mode 100644 index 00000000..50f72135 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_handler_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/gcol_handler_innodb.test # +# # +# Purpose: # +# Testing HANDLER. +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_handler.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_handler_myisam.test b/mysql-test/suite/gcol/t/gcol_handler_myisam.test new file mode 100644 index 00000000..21923547 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_handler_myisam.test @@ -0,0 +1,45 @@ +################################################################################ +# t/gcol_handler_myisam.test # +# # +# Purpose: # +# Testing HANDLER. +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_handler.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_ins_upd_innodb.test b/mysql-test/suite/gcol/t/gcol_ins_upd_innodb.test new file mode 100644 index 00000000..15a0db29 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_ins_upd_innodb.test @@ -0,0 +1,47 @@ +################################################################################ +# t/gcol_ins_upd_innodb.test # +# # +# Purpose: # +# Testing DDL operations such as INSERT, UPDATE, REPLACE and DELETE. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 1; +--source suite/gcol/inc/gcol_ins_upd.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_ins_upd_myisam.test b/mysql-test/suite/gcol/t/gcol_ins_upd_myisam.test new file mode 100644 index 00000000..d54a6ae6 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_ins_upd_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/gcol_ins_upd_myisam.test # +# # +# Purpose: # +# Testing DDL operations such as INSERT, UPDATE, REPLACE and DELETE. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 1; +--source suite/gcol/inc/gcol_ins_upd.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_keys_innodb.test b/mysql-test/suite/gcol/t/gcol_keys_innodb.test new file mode 100644 index 00000000..7f7c4503 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_keys_innodb.test @@ -0,0 +1,89 @@ +################################################################################ +# t/gcol_keys_innodb.test # +# # +# Purpose: # +# Testing keys, indexes defined upon generated columns. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $skip_spatial_index_check = 1; +let $support_virtual_index= 1; +--source suite/gcol/inc/gcol_keys.inc + +if ($support_virtual_index) { +#------------------------------------------------------------------------------# +# Execute storage engine specific tests +--echo # +--echo # BUG#21365158 WL8149:ASSERTION `!TABLE || (!TABLE->WRITE_SET +--echo # +CREATE TABLE t1 ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER NOT NULL, + col_varchar_nokey VARCHAR(1), + col_varchar_key VARCHAR(2) GENERATED ALWAYS AS + (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL, + PRIMARY KEY (pk) +); + +INSERT INTO t1 ( col_int_nokey, col_varchar_nokey) +VALUES (4, 'b'),(9, 'o'),(4, 'k'),(5, 'a'),(5, 'f'), +(9, 't'),(3, 'c'),(8, 'c'),(0, 'r'),(98, 'k'); + +CREATE TABLE t2 ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER NOT NULL, + 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), + UNIQUE KEY (col_varchar_key) +); + +INSERT INTO t2 ( col_int_nokey, col_varchar_nokey) +VALUES (1, 'c'),(8, 'm'),(9, 'd'), (6, 'y'),(1, 't'), +(2, 's'),(4, 'r'); + +SELECT + CONCAT( t2.col_varchar_nokey , t2.col_varchar_nokey ) AS f2, + t1.col_varchar_key AS f5 +FROM + t2 LEFT JOIN t1 ON t2.col_int_nokey > t1.col_int_nokey +ORDER BY f2, f5; +DROP TABLE t1,t2; + +--echo # + +} +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_keys_myisam.test b/mysql-test/suite/gcol/t/gcol_keys_myisam.test new file mode 100644 index 00000000..9cd89107 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_keys_myisam.test @@ -0,0 +1,47 @@ +################################################################################ +# t/gcol_keys_myisam.test # +# # +# Purpose: # +# Testing keys, indexes defined upon generated columns. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 1; +let $skip_foreign_key_check=1; +--source suite/gcol/inc/gcol_keys.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_memory.test b/mysql-test/suite/gcol/t/gcol_memory.test new file mode 100644 index 00000000..a7d9235d --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_memory.test @@ -0,0 +1,43 @@ +################################################################################ +# t/gcol_memory.test # +# # +# Purpose: # +# MEMORY branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-02 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +SET @@session.default_storage_engine = 'memory'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests +--source suite/gcol/inc/gcol_unsupported_storage_engines.inc + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_merge.test b/mysql-test/suite/gcol/t/gcol_merge.test new file mode 100644 index 00000000..8e3ba476 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_merge.test @@ -0,0 +1,52 @@ +################################################################################ +# t/gcol_merge.test # +# # +# Purpose: # +# MERGE branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-03 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings + +create table t1 (a int, b int generated always as (a % 10) virtual); +create table t2 (a int, b int generated always as (a % 10) virtual); +insert into t1 values (1,default); +insert into t2 values (2,default); +--error ER_UNSUPPORTED_ENGINE_FOR_GENERATED_COLUMNS +create table t3 (a int, b int generated always as (a % 10) virtual) engine=MERGE UNION=(t1,t2); +drop table t1,t2; + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_non_stored_columns_innodb.test b/mysql-test/suite/gcol/t/gcol_non_stored_columns_innodb.test new file mode 100644 index 00000000..ed0d255b --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_non_stored_columns_innodb.test @@ -0,0 +1,48 @@ +################################################################################ +# t/gcol_non_stored_columns_innodb.test # +# # +# Purpose: # +# Ensure that MySQL behaviour is consistent irrelevant of # +# - the place of a non-stored column among other columns, # +# - the total number of non-stored fields. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_non_stored_columns.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_non_stored_columns_myisam.test b/mysql-test/suite/gcol/t/gcol_non_stored_columns_myisam.test new file mode 100644 index 00000000..9784071e --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_non_stored_columns_myisam.test @@ -0,0 +1,47 @@ +################################################################################ +# t/gcol_non_stored_columns_myisam.test # +# # +# Purpose: # +# Ensure that MySQL behaviour is consistent irrelevant of # +# - the place of a non-stored column among other columns, # +# - the total number of non-stored fields. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_non_stored_columns.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_partition_innodb.test b/mysql-test/suite/gcol/t/gcol_partition_innodb.test new file mode 100644 index 00000000..a8765970 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_partition_innodb.test @@ -0,0 +1,63 @@ +################################################################################ +# t/gcol_partition_innodb.test # +# # +# Purpose: # +# Testing partitioning tables with generated columns. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_partition.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +--echo # +--echo # MDEV-16980 Wrongly set tablename len while opening the +--echo # table for purge thread +--echo # + +CREATE TABLE t1(pk SERIAL, d DATE, vd DATE AS (d) VIRTUAL, + PRIMARY KEY(pk), KEY (vd))ENGINE=InnoDB + PARTITION BY HASH(pk) PARTITIONS 2; + +INSERT IGNORE INTO t1 (d) VALUES ('2015-04-14'); +SET sql_mode= ''; +REPLACE INTO t1 SELECT * FROM t1; + +# Cleanup +DROP TABLE t1; + +--source suite/innodb/include/wait_all_purged.inc +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_partition_myisam.test b/mysql-test/suite/gcol/t/gcol_partition_myisam.test new file mode 100644 index 00000000..0a0cd9f9 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_partition_myisam.test @@ -0,0 +1,45 @@ +################################################################################ +# t/gcol_partition_myisam.test # +# # +# Purpose: # +# Testing partitioning tables with generated columns. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_partition.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_purge.test b/mysql-test/suite/gcol/t/gcol_purge.test new file mode 100644 index 00000000..cfe20c4a --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_purge.test @@ -0,0 +1,33 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +SET @save_dbug=@@GLOBAL.debug_dbug; +CREATE TABLE t1(f1 INT NOT NULL, f2 int not null, + f3 int generated always as (f2 * 2) VIRTUAL, + primary key(f1), INDEX (f3))ENGINE=InnoDB; +connect(con1,localhost,root,,,); +--source ../innodb/include/wait_all_purged.inc +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +INSERT INTO t1(f1, f2) VALUES(1,2); +DELETE from t1 where f1 = 1; + +connect(con2,localhost,root,,,); +begin; +INSERT INTO t1 (f1, f2) VALUES(1,2); + +set global debug_dbug="+d,ib_purge_virtual_index_callback"; +connection con1; +COMMIT; + +SET GLOBAL innodb_max_purge_lag_wait=1; + +connection con2; +commit; + +disconnect con1; +disconnect con2; +connection default; +SET GLOBAL debug_dbug=@save_dbug; +DROP TABLE t1; diff --git a/mysql-test/suite/gcol/t/gcol_rejected_innodb.test b/mysql-test/suite/gcol/t/gcol_rejected_innodb.test new file mode 100644 index 00000000..1780fdf8 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_rejected_innodb.test @@ -0,0 +1,41 @@ +################################################################################ +# t/gcol_handler_innodb.test # +# # +# Purpose: # +# Testing rejected generated column additions. +# # +# InnoDB branch # +# # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +# @todo This test is broken for all VIRTUAL columns +#--source suite/gcol/inc/gcol_rejected.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_rollback.test b/mysql-test/suite/gcol/t/gcol_rollback.test new file mode 100644 index 00000000..888e6be8 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_rollback.test @@ -0,0 +1,136 @@ +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc + +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + +# Make sure there are no unexpected open tables from previous tests +--disable_query_log +FLUSH TABLES; +--enable_query_log + +CREATE TABLE t ( + a INTEGER, + b BLOB GENERATED ALWAYS AS (a) VIRTUAL, + INDEX (b(57)) +)ENGINE=INNODB; + +INSERT INTO t (a) VALUES (9); +BEGIN; +SAVEPOINT a; +UPDATE t set a = 12; +DELETE FROM t where a = 12; +ROLLBACK TO SAVEPOINT a; +COMMIT; + +CHECK TABLE t; + +SELECT * FROM t; + +BEGIN; +INSERT INTO t (a) VALUES (10); +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc +SELECT * FROM t; +DROP TABLE t; + +CREATE TABLE t ( + a INTEGER, + b BLOB GENERATED ALWAYS AS (a) VIRTUAL, + c INTEGER +)ENGINE=INNODB; + +INSERT INTO t (a,c) VALUES (9, 10); +SELECT * FROM t; + +connect (con1,localhost,root,,); +connection con1; + +# This DEBUG_SYNC should not kick in yet, because the duplicate key will be +# detected before we get a chance to apply the online log. + +SET DEBUG_SYNC = 'row_log_apply_after SIGNAL created WAIT_FOR dml_done'; +--send +ALTER TABLE t ADD KEY(b(57)), ALGORITHM=INPLACE; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR created'; +BEGIN; +INSERT INTO t (a,c) VALUES (10, 12); +SELECT * FROM t; +ROLLBACK; +SET DEBUG_SYNC = 'now SIGNAL dml_done'; + +connection con1; +reap; +disconnect con1; +connection default; + +SELECT * FROM t; +DROP TABLE t; + +# drop virtual column and alter index +CREATE TABLE t ( + a INT, + b INT, + c INT GENERATED ALWAYS AS(a+b), + d INT GENERATED ALWAYS AS(a+b+b), + KEY(c, d) +)ENGINE=INNODB; + +INSERT INTO t (a,b) VALUES (9, 10); +SELECT * FROM t; + +connect (con1,localhost,root,,); +connection con1; + +# This DEBUG_SYNC should not kick in yet, because the duplicate key will be +# detected before we get a chance to apply the online log. + +SET DEBUG_SYNC = 'row_log_apply_after SIGNAL created WAIT_FOR dml_done'; +--send +ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR created'; +BEGIN; +INSERT INTO t (a,b) VALUES (10, 12); +SELECT * FROM t; +ROLLBACK; +SET DEBUG_SYNC = 'now SIGNAL dml_done'; + +connection con1; +reap; +connection default; + +SELECT * FROM t; + +DROP TABLE t; +SET DEBUG_SYNC = 'RESET'; + +--echo # +--echo # MDEV-30597 Assertion `flag == 1' failed in +--echo # row_build_index_entry_low +--echo # +CREATE TABLE t1 ( +col1 INT PRIMARY KEY, col_text TEXT, +col_text_g TEXT GENERATED ALWAYS AS (SUBSTR(col_text,1,499)) +) ENGINE = InnoDB ROW_FORMAT = Compact; +connection con1; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; +INSERT INTO t1 (col1) VALUES (1) ; +DELETE FROM t1 WHERE col1 = 1; +ALTER TABLE t1 ADD UNIQUE INDEX (col_text_g(9)); +BEGIN; +INSERT INTO t1 (col1) VALUES (1); +ROLLBACK; +disconnect con1; +DROP TABLE t1; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + +--echo # End of 10.4 tests diff --git a/mysql-test/suite/gcol/t/gcol_select_innodb.test b/mysql-test/suite/gcol/t/gcol_select_innodb.test new file mode 100644 index 00000000..18c17041 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_select_innodb.test @@ -0,0 +1,53 @@ +################################################################################ +# t/gcol_select_innodb.test # +# # +# Purpose: # +# Testing different SELECTs. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-18 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; +eval SET optimizer_switch='derived_merge=off'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 0; +--source suite/gcol/inc/gcol_select.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +eval SET optimizer_switch='derived_merge=default'; +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_select_myisam.test b/mysql-test/suite/gcol/t/gcol_select_myisam.test new file mode 100644 index 00000000..e078efba --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_select_myisam.test @@ -0,0 +1,52 @@ +################################################################################ +# t/gcol_select.test # +# # +# Purpose: # +# Testing different SELECTs. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-18 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; +eval SET optimizer_switch='derived_merge=off'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +let $support_virtual_index= 1; +--source suite/gcol/inc/gcol_select.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +eval SET optimizer_switch='derived_merge=default'; +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_supported_sql_funcs_innodb.test b/mysql-test/suite/gcol/t/gcol_supported_sql_funcs_innodb.test new file mode 100644 index 00000000..2e910936 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_supported_sql_funcs_innodb.test @@ -0,0 +1,45 @@ +################################################################################ +# t/gcol_supported_sql_funcs.test # +# # +# Purpose: # +# Test SQL functions allowed for generated columns # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-08-31 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_supported_sql_funcs_main.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_supported_sql_funcs_myisam.test b/mysql-test/suite/gcol/t/gcol_supported_sql_funcs_myisam.test new file mode 100644 index 00000000..a9dee4c7 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_supported_sql_funcs_myisam.test @@ -0,0 +1,44 @@ +################################################################################ +# t/gcol_supported_sql_funcs.test # +# # +# Purpose: # +# Test SQL functions allowed for generated columns # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-08-31 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_supported_sql_funcs_main.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_trigger_sp_innodb.test b/mysql-test/suite/gcol/t/gcol_trigger_sp_innodb.test new file mode 100644 index 00000000..4478cbe2 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_trigger_sp_innodb.test @@ -0,0 +1,47 @@ +################################################################################ +# t/gcol_trigger_sp_innodb.test # +# # +# Purpose: # +# Testing triggers, stored procedures and functions # +# defined on tables with generated columns. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_trigger_sp.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_trigger_sp_myisam.test b/mysql-test/suite/gcol/t/gcol_trigger_sp_myisam.test new file mode 100644 index 00000000..1a39d921 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_trigger_sp_myisam.test @@ -0,0 +1,46 @@ +################################################################################ +# t/gcol_trigger_sp_myisam.test # +# # +# Purpose: # +# Testing triggers, stored procedures and functions # +# defined on tables with generated columns. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_trigger_sp.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_update.test b/mysql-test/suite/gcol/t/gcol_update.test new file mode 100644 index 00000000..2076632f --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_update.test @@ -0,0 +1,62 @@ +--source include/have_innodb.inc + +connect (purge_control,localhost,root); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; + +# Index on virtual column + +create table t1(f1 int not null, f2 blob not null, f3 blob not null, + vchar char(2) as (substr(f3,2,2)) virtual, + primary key(f1, f3(5)), index(vchar))engine=innodb; + +insert into t1(f1,f2,f3) values(1, repeat('a',8000), repeat('b', 9000)); + +update t1 set f1=5 where f1=1; +delete from t1 where f1=5; + +connection purge_control; +COMMIT; +--source ../../innodb/include/wait_all_purged.inc +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; + +drop table t1; + +# Index on virtual column and blob + +create table t1(f1 int not null, f2 blob not null, f3 blob not null, + vchar char(2) as (substr(f3,2,2)) virtual, + primary key(f1, f3(5)), index(vchar, f3(2)))engine=innodb; + +insert into t1(f1,f2,f3) values(1, repeat('a',8000), repeat('b', 9000)); + +update t1 set f1=5 where f1=1; +delete from t1 where f1=5; + +connection purge_control; +COMMIT; +--source ../../innodb/include/wait_all_purged.inc +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; +drop table t1; + +# Index on virtual column of blob type + +create table t1(f1 int not null, f2 blob not null, f3 blob not null, + vchar blob as (f3) virtual, + primary key(f1, f3(5)), index(vchar(3)))engine=innodb; + +insert into t1(f1,f2,f3) values(1, repeat('a',8000), repeat('b', 9000)); + +update t1 set f1=5 where f1=1; +delete from t1 where f1=5; + +connection purge_control; +COMMIT; +--source ../../innodb/include/wait_all_purged.inc +disconnect purge_control; + +connection default; +drop table t1; diff --git a/mysql-test/suite/gcol/t/gcol_view_innodb.test b/mysql-test/suite/gcol/t/gcol_view_innodb.test new file mode 100644 index 00000000..06ded123 --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_view_innodb.test @@ -0,0 +1,46 @@ +################################################################################ +# t/gcol_view_innodb.test # +# # +# Purpose: # +# Testing views defined on tables with generated columns. # +# # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +eval SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_view.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/gcol_view_myisam.test b/mysql-test/suite/gcol/t/gcol_view_myisam.test new file mode 100644 index 00000000..3779f0bb --- /dev/null +++ b/mysql-test/suite/gcol/t/gcol_view_myisam.test @@ -0,0 +1,45 @@ +################################################################################ +# t/gcol_view_myisam.test # +# # +# Purpose: # +# Testing views defined on tables with generated columns. # +# # +# MyISAM branch # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +eval SET @@session.default_storage_engine = 'MyISAM'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/gcol/inc/gcol_view.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc diff --git a/mysql-test/suite/gcol/t/innodb_partition.test b/mysql-test/suite/gcol/t/innodb_partition.test new file mode 100644 index 00000000..268a8c7c --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_partition.test @@ -0,0 +1,30 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +--echo # +--echo # Bug#22444530 - GCOLS + PARTITIONED TABLE, CRASH IN +--echo # +set sql_mode=''; +create table t ( + a int not null, + b int generated always as (1) virtual, + c int generated always as (1) virtual, + key (c) +) engine=innodb partition by key (a) partitions 2; +insert into t(a) values(1); +select b from t group by c; + +drop table t; + +# Make column b a BLOB +create table t ( + a int not null, + b blob generated always as ("a") virtual, + c int generated always as (1) virtual, + key (c) +) engine=innodb partition by key (a) partitions 2; +insert into t(a) values(1); +select b from t group by c; + +drop table t; + diff --git a/mysql-test/suite/gcol/t/innodb_prefix_index_check.test b/mysql-test/suite/gcol/t/innodb_prefix_index_check.test new file mode 100644 index 00000000..4923ead9 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_prefix_index_check.test @@ -0,0 +1,22 @@ +--source include/have_innodb.inc + +--echo #Bug #22445211 GCOLS: SIMPLE DML, FAILING ASSERTION: +--echo #!CURSOR->INDEX->IS_COMMITTED() + +--echo #Create and alter table examples for virtual column for full +--echo #column index followed by prefix index. + +CREATE TABLE t1( +f1 INT DEFAULT NULL, +f2 CHAR(2) GENERATED ALWAYS AS ('11') VIRTUAL, +f3 INT, +UNIQUE KEY(f1), +UNIQUE KEY(f3,f1), +KEY(f2,f1), +key(f1,f2(1)) +)ENGINE=INNODB; + +REPLACE INTO t1(f3) VALUES (1),(1); + +DROP TABLE t1; + diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test new file mode 100644 index 00000000..32598758 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test @@ -0,0 +1,1446 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/big_test.inc + +call mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual"); + +set default_storage_engine=innodb; + +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d CHAR(20), e CHAR(10) GENERATED ALWAYS AS (c), g INT); +INSERT INTO t VALUES(10, DEFAULT, "aa", "bb", DEFAULT, 20); +INSERT INTO t VALUES(11, DEFAULT, "jj", "kk", DEFAULT, 21); + +CREATE INDEX idx ON t(e) algorithm=inplace; +INSERT INTO t VALUES(12, DEFAULT, 'mm', "nn", DEFAULT, 22); + +SELECT e FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +CREATE INDEX idx ON t(c); +SELECT c FROM t; + +UPDATE t SET a = 10 WHERE a = 11; +SELECT c FROM t; + +SELECT * FROM t; + +DELETE FROM t WHERE a = 18; + +SELECT c FROM t; + +START TRANSACTION; + +INSERT INTO t VALUES (128, 22, DEFAULT, "xx"); +INSERT INTO t VALUES (1290, 212, DEFAULT, "xmx"); +ROLLBACK; + +SELECT c FROM t; +SELECT * FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); +INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); +INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); + +SELECT c FROM t; +SELECT m FROM t; +SELECT p FROM t; +SELECT * FROM t; + +update t set a = 13 where a =11; + +delete from t where a =13; + +DROP INDEX idx1 ON t; +DROP INDEX idx2 ON t; +DROP TABLE t; + +/* Test large BLOB data */ +CREATE TABLE `t` ( + `a` BLOB, + `b` BLOB, + `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `h` VARCHAR(10) DEFAULT NULL +) ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk"); + +CREATE INDEX idx ON t(c(100)); + +SELECT length(c) FROM t; + +START TRANSACTION; + +INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); + +ROLLBACK; + +INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); + +START TRANSACTION; + +UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%"; + +ROLLBACK; + +# This SELECT did not give correct answer, even though InnoDB return +# all qualified rows +SELECT COUNT(*) FROM t WHERE c like "aaa%"; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +CREATE INDEX idx ON t(c); + +START TRANSACTION; + +UPDATE t SET a = 100 WHERE a = 11; + +UPDATE t SET a =22 WHERE a = 18; + +UPDATE t SET a = 33 WHERE a = 22; + +SELECT c FROM t; + +ROLLBACK; + +SELECT c FROM t; +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +CREATE INDEX idx ON t(c); +SELECT c FROM t; + +connect(con1,localhost,root,,test); +START TRANSACTION; +SELECT c FROM t; + +connection default; +UPDATE t SET a = 19 WHERE a = 11; + +# this should report the same value as previous one (14, 19, 29). +connection con1; +SELECT c FROM t; + +ROLLBACK; + +SELECT c FROM t; + +connection default; +disconnect con1; + +DROP TABLE t; + +# CREATE a more complex TABLE +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + x), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, RTRIM(y))), x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(1, 2, DEFAULT, "hhh", 3, DEFAULT, "nnn", DEFAULT, 4, "yyy", 5); + +INSERT INTO t VALUES(2, 3, DEFAULT, "hhha", 4, DEFAULT, "nnna", DEFAULT, 5, "yyya", 6); + +INSERT INTO t VALUES(12, 13, DEFAULT, "hhhb", 14, DEFAULT, "nnnb", DEFAULT, 15, "yyyb", 16); + +# CREATE an INDEX ON multiple virtual COLUMN +CREATE INDEX idx6 ON t(p, c); + +SELECT p, c FROM t; + +START TRANSACTION; +INSERT INTO t VALUES(32, 33, DEFAULT, "hhhb", 34, DEFAULT, "nnnb", DEFAULT, 35, "yyyb", 36); +ROLLBACK; + +UPDATE t SET a = 100 WHERE a = 1; + +START TRANSACTION; +UPDATE t SET a = 1 WHERE a = 100; +ROLLBACK; + +DROP TABLE t; + +CREATE TABLE t1(a INT); +ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual); +ALTER TABLE t1 add COLUMN (d INT generated always as (a+1) virtual, e INT as(5) virtual); + +SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual; + +#--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT as(5) virtual), DROP COLUMN e; + +SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual; + +DROP TABLE t1; + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1); + +ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT ); + +# Inplace ADD/DROP virtual COLUMNs can only go with their own for +# inplace algorithm, not to combine with other operations, except create index +ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace; + +--enable_info +ALTER TABLE t1 add COLUMN (h1 INT generated always as (a+1) virtual), add INDEX idx1 (h1); +--disable_info + +ALTER TABLE t1 DROP COLUMN h1, DROP INDEX idx; + +DROP TABLE t1; + +# Virtual COLUMN cannot be INDEXed +CREATE TABLE t1(a INT); +CREATE INDEX idx ON t1(a); +CREATE TABLE t3(a INT, b INT , INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a)); +--error ER_CANT_CREATE_TABLE +CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a)); +CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b)); +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; + +CREATE TABLE t1(a INT); +ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual); + +ALTER TABLE t1 change b x INT generated always as (a+1) virtual; + +SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual; + +DROP TABLE t1; + +# We do not support Fulltext or Spatial INDEX ON Virtual Columns +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a), fulltext INDEX idx (b)); +CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a)); +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +ALTER TABLE t ADD FULLTEXT INDEX (b); +DROP TABLE t; + +--error ER_SPATIAL_CANT_HAVE_NULL +CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a), spatial INDEX idx (b)); +CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a)); +--error ER_SPATIAL_CANT_HAVE_NULL +ALTER TABLE t ADD SPATIAL INDEX (b); +DROP TABLE t; + +#test DEFAULT value +CREATE TABLE t (a INT DEFAULT 1, b INT DEFAULT 2, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); +CREATE INDEX idx ON t(c); +INSERT INTO t(h)VALUES ('mm'); +SELECT c FROM t; + +CREATE unique INDEX idx1 ON t(c); + +--error ER_DUP_ENTRY +INSERT INTO t(h)VALUES ('mm'); + +DROP TABLE t; + +CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, `x` INT(11) NOT NULL, `h` VARCHAR(10) DEFAULT NULL, PRIMARY KEY (`x`), KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm'); +INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm'); + +connect(con1,localhost,root,,test); +UPDATE t1 SET x = 4 WHERE x =3; +DROP TABLE t1; + +CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, `x` INT(11) NOT NULL, `h` VARCHAR(10) DEFAULT NULL, KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm'); +INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm'); + +START TRANSACTION; +SELECT * FROM t1; + +connection con1; +START TRANSACTION; +UPDATE t1 SET x = 15 WHERE x = 3; + +UPDATE t1 SET b = 10 WHERE b=2; +ROLLBACK; + +connection default; +SELECT c FROM t1; + +disconnect con1; + +DROP TABLE t1; + +CREATE TABLE `t` ( + `a` INT(11) DEFAULT NULL, + `b` INT(11) DEFAULT NULL, + `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, + `d` INT(11) GENERATED ALWAYS AS (a) VIRTUAL, + `h` INT(11) NOT NULL, + PRIMARY KEY (`h`), + KEY `idx` (`c`) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, 1); +INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, 2); +INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, 3); +INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, 4); + +delimiter |; +CREATE PROCEDURE UPDATE_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 2000) DO + UPDATE t SET a = 100 + i WHERE h = 1; + SET i = i + 1; + END WHILE; +END| + +CREATE PROCEDURE DELETE_insert_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 2000) DO + UPDATE t SET a = 100 + i WHERE h = 1; + SET i = i + 1; + END WHILE; +END| +delimiter ;| + +CALL UPDATE_t(); +SELECT c FROM t; + +CALL DELETE_insert_t(); +SELECT c FROM t; + +DROP INDEX idx ON t; +CALL UPDATE_t(); +SELECT c FROM t; + +DROP PROCEDURE DELETE_insert_t; +DROP PROCEDURE UPDATE_t; + +DROP TABLE t; + +--echo # Bug#20767937: WL8149:ASSERTION FAILED IN ROW_UPD_SEC_INDEX_ENTRY +CREATE TABLE b ( +col_INT_nokey INTEGER NOT NULL, +col_INT_key INTEGER GENERATED ALWAYS AS (col_INT_nokey) VIRTUAL, +col_date_nokey DATE, +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)), +col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, + col_time_nokey)), + +col_VARCHAR_nokey VARCHAR(1) NOT NULL, +col_VARCHAR_key VARCHAR(2) GENERATED ALWAYS AS(CONCAT(col_VARCHAR_nokey, + col_VARCHAR_nokey)), + +KEY (col_INT_key), +KEY (col_VARCHAR_key), +KEY (col_date_key), +KEY (col_time_key), +KEY (col_datetime_key), +KEY (col_INT_key, col_VARCHAR_key), +KEY (col_INT_key, col_VARCHAR_key, col_date_key, +col_time_key, col_datetime_key) +); +INSERT INTO b ( + col_INT_nokey, + col_date_nokey, + col_time_nokey, + col_datetime_nokey, + col_VARCHAR_nokey +) VALUES +(0, NULL, '21:22:34.025509', '2002-02-13 17:30:06.013935', 'j'), +(8, '2004-09-18', '10:50:38.059966', '2008-09-27 +00:34:58.026613', 'v'); + +EXPLAIN SELECT col_INT_key FROM b; +SELECT col_INT_key FROM b; +SELECT col_INT_nokey, col_INT_key FROM b; +DELETE FROM b; + +DROP TABLE b; + +let $row_format=COMPACT; +--source inc/innodb_v_large_col.inc + +CREATE TABLE `t` ( + `a` BLOB, + `b` BLOB, + `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `d` BLOB GENERATED ALWAYS AS (b) VIRTUAL, + `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL, + `h` INT(11) NOT NULL, + PRIMARY KEY (`h`) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, DEFAULT, DEFAULT, 1); +INSERT INTO t VALUES (REPEAT('a', 32000), REPEAT('b', 11000), DEFAULT, DEFAULT, DEFAULT, 2); +INSERT INTO t VALUES (REPEAT('m', 18000), REPEAT('n', 46000), DEFAULT, DEFAULT, DEFAULT, 3); + +CREATE INDEX idx ON t(c(100), d(20)); + +UPDATE t SET a = NULL WHERE h=1; + +UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1; + +UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 1000) WHERE h = 1; + +delimiter |; + +CREATE PROCEDURE UPDATE_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 200) DO + UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1; + SET i = i + 1; + END WHILE; +END| + +CREATE PROCEDURE DELETE_insert_t() +begin + DECLARE i INT DEFAULT 1; + WHILE (i <= 200) DO + DELETE FROM t WHERE h = 1; + INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1); + SET i = i + 1; + END WHILE; +END| +delimiter ;| + +CALL UPDATE_t(); +CALL DELETE_insert_t(); + +UPDATE t SET a = NULL WHERE h=1; + +DROP PROCEDURE DELETE_insert_t; +DROP PROCEDURE UPDATE_t; +DROP TABLE t; + +CREATE TABLE `t` ( + `m1` INT(11) DEFAULT NULL, + `m2` INT(11) DEFAULT NULL, + `m3` INT(11) GENERATED ALWAYS AS (m1 + m2) VIRTUAL, + `m4` INT(11) DEFAULT NULL, + `m5` CHAR(10) DEFAULT NULL, + `m6` CHAR(12) GENERATED ALWAYS AS (m5) VIRTUAL, + `a` VARCHAR(10000) DEFAULT NULL, + `b` VARCHAR(3000) DEFAULT NULL, + `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL, + `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL, + `h` INT(11) NOT NULL, + PRIMARY KEY (`h`), + KEY `m3` (`m3`), + KEY `c` (`c`(100)), + KEY `e` (`e`,`d`(20)) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (1, 2, DEFAULT, 3, "aaa", DEFAULT, REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1); + +INSERT INTO t VALUES (11, 21, DEFAULT, 31, "bbb", DEFAULT, REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2); + +INSERT INTO t VALUES (21, 31, DEFAULT, 41, "zzz", DEFAULT, REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3); + +ALTER TABLE t DROP COLUMN c; + +DELETE FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +CREATE INDEX idx ON t(a, c); +SELECT a, c FROM t; + +START TRANSACTION; + +UPDATE t SET a = 13 where a = 11; + +ROLLBACK; +DELETE FROM t; + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), m int); + +INSERT INTO t VALUES (11, 3, DEFAULT, "a", 1); +INSERT INTO t VALUES (18, 1, DEFAULT, "b", 2); +INSERT INTO t VALUES (28, 1, DEFAULT, "c", 3 ); +INSERT INTO t VALUES (null, null, DEFAULT, "d", 4); + +CREATE INDEX idx ON t(a, c, h); +SELECT a, c FROM t; + +START TRANSACTION; +UPDATE t SET m =10 WHERE m = 1; +UPDATE t SET h = "e" WHERE h="a"; +ROLLBACK; +SELECT a, c, h FROM t; + +DROP TABLE t; + +# bug#21065137 - WL8149:FAILING ASSERTION: NAME_OFS < FULL_LEN +CREATE TABLE `t1` ( + `col1` int(11) NOT NULL, + `col2` int(11) NOT NULL, + `col3` int(11) NOT NULL, + `col4` int(11) DEFAULT NULL, + `col5` int(11) GENERATED ALWAYS AS (col2 % col3) VIRTUAL, + `col7` int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL, + `col8` int(11) GENERATED ALWAYS AS (col5 % col5) VIRTUAL, + `col9` text, + `extra` int(11) DEFAULT NULL, + UNIQUE KEY `uidx` (`col5`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE t1 CHANGE COLUMN extra col6 INT; + +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# No spatial and FTS index on virtual columns +--error ER_SPATIAL_CANT_HAVE_NULL +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c point, d point GENERATED ALWAYS AS (c), spatial index idx (d)); + +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d char(20) GENERATED ALWAYS AS (c), fulltext index idx (d)); + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); +INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); +INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); + +alter table t add x int, add xx int generated ALWAYS AS(x); + +DROP TABLE t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); + +INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT); +INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT); +INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT); + +ALTER TABLE t DROP COLUMN c, algorithm=inplace; +ALTER TABLE t DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace; + +# This should fail +#ALTER TABLE t ADD x INT, DROP COLUMN m, algorithm=inplace; +SELECT s FROM t; + +ALTER TABLE t ADD x VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), ADD INDEX idx (x), algorithm=inplace; +DROP TABLE t; + +CREATE TABLE `t1` ( + `col1` int(11) DEFAULT NULL, + `col2` int(11) DEFAULT NULL, + `col3` int(11) DEFAULT NULL, + `col4` int(11) DEFAULT NULL, + `col5` int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL, + `col6` int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, + `col7` int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL, + `col8` int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, + `col9` text, + `extra` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE t1 DROP COLUMN col7; + +DROP TABLE t1; + +CREATE TABLE t1 ( + col1 INTEGER NOT NULL, + gv_col INTEGER GENERATED ALWAYS AS (col1) VIRTUAL, + txt1 TEXT, + FULLTEXT INDEX fi(txt1) +); + +select * from t1; + +DROP TABLE t1; + +CREATE TABLE t1 ( + col1 INTEGER NOT NULL, + col2 INTEGER NOT NULL, + col3 INTEGER DEFAULT NULL, + 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, + gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL, + KEY idx1 (gcol1) +); + +INSERT INTO t1 (col1, col2) + VALUES (0,1), (1,2), (2,3), (3,4), (4,5); + +SELECT gcol1 FROM t1 FORCE INDEX(idx1); + +ALTER TABLE t1 ADD COLUMN extra INTEGER; + +SELECT gcol1 FROM t1 FORCE INDEX(idx1); + +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT NOT NULL, + store_id INT NOT NULL, + x INT GENERATED ALWAYS AS (id + store_id) +) +PARTITION BY RANGE (store_id) ( + PARTITION p0 VALUES LESS THAN (6), + PARTITION p1 VALUES LESS THAN (11), + PARTITION p2 VALUES LESS THAN (16), + PARTITION p3 VALUES LESS THAN (21) +); + +INSERT INTO t1 VALUES(1, 2, default); +INSERT INTO t1 VALUES(3, 4, default); + +INSERT INTO t1 VALUES(3, 12, default); +INSERT INTO t1 VALUES(4, 18, default); + +CREATE INDEX idx ON t1(x); + +SELECT x FROM t1; + +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT NOT NULL, + store_id INT NOT NULL, + x INT GENERATED ALWAYS AS (id + store_id) +) +PARTITION BY RANGE (x) ( + PARTITION p0 VALUES LESS THAN (6), + PARTITION p1 VALUES LESS THAN (11), + PARTITION p2 VALUES LESS THAN (16), + PARTITION p3 VALUES LESS THAN (21) +); + +insert into t1 values(1, 2, default); +insert into t1 values(3, 4, default); + +insert into t1 values(3, 12, default); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert into t1 values(4, 18, default); + +CREATE INDEX idx ON t1(x); +SELECT x FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a+1) ,c int) PARTITION BY RANGE (b) ( +PARTITION p0 VALUES LESS THAN (6), +PARTITION p1 VALUES LESS THAN (11), +PARTITION p2 VALUES LESS THAN (16), +PARTITION p3 VALUES LESS THAN (21) ); + +INSERT INTO t1 VALUES (10,DEFAULT,2); +INSERT INTO t1 VALUES (19,DEFAULT,8); + +CREATE INDEX idx ON t1 (b); + +INSERT INTO t1 VALUES (5,DEFAULT,9); + +SELECT * FROM t1; + +ALTER TABLE t1 REMOVE PARTITIONING; + +DROP TABLE t1; + +CREATE TABLE `t#P#1` (a INT, bt INT GENERATED ALWAYS AS (a+1) ,c int) +PARTITION BY RANGE (bt) +subpartition by hash (bt) + ( + PARTITION p0 VALUES LESS THAN (6) ( + SUBPARTITION s0, + SUBPARTITION s1), + PARTITION p1 VALUES LESS THAN (11) ( + SUBPARTITION s2, + SUBPARTITION s3), + PARTITION p2 VALUES LESS THAN (16) ( + SUBPARTITION s4, + SUBPARTITION s5), + PARTITION p3 VALUES LESS THAN (21) ( + SUBPARTITION s6, + SUBPARTITION s7) + ); +insert into `t#P#1` values (10,DEFAULT,2); +insert into `t#P#1` values (19,DEFAULT,8); +create index idx on `t#P#1` (bt); +insert into `t#P#1` values (5,DEFAULT,9); +select * from `t#P#1`; +alter table `t#P#1` remove partitioning; +drop table `t#P#1`; + +let $row_format=DYNAMIC; +--source inc/innodb_v_large_col.inc + +let $row_format=REDUNDANT; +--source inc/innodb_v_large_col.inc + +let $row_format=COMPRESSED; +--disable_query_log +SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; +SET GLOBAL innodb_read_only_compressed=OFF; +--enable_query_log +--source inc/innodb_v_large_col.inc +--disable_query_log +SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; +--enable_query_log + +# Make sure FTS_DOC_ID for FULLTEXT index set with correct column id with +# virtual columns +CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB; +ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ; +ALTER TABLE t ADD FULLTEXT INDEX (a) ; +ALTER TABLE t ADD INDEX (b(1)) ; + +DROP TABLE t; + +CREATE TABLE t(a TEXT CHARSET UTF8, FULLTEXT INDEX(a))ENGINE=INNODB; +ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ; +ALTER TABLE t ADD INDEX (b(1)) ; +DROP TABLE t; + +# Virtual column cannot be used as DOC ID column for FULLTEXT index +CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB; +ALTER TABLE t ADD COLUMN FTS_DOC_ID BLOB GENERATED ALWAYS AS (a) VIRTUAL ; +--error ER_INNODB_FT_WRONG_DOCID_COLUMN +ALTER TABLE t ADD FULLTEXT INDEX (a) ; +DROP TABLE t; + +# Test uses ICP on column h and d +create table t (a int,b int,c int,d int,e int, +f int generated always as (a+b) virtual, +g int,h blob,i int,unique key (d,h(25))) engine=innodb; + +select h from t where d is null; +drop table t; + +# Test Add virtual column of MySQL long true type +create table t(a blob not null) engine=innodb; +alter table t add column b int; +alter table t add column c varbinary (1000) generated always as (a) virtual; +alter table t add unique index (c(39)); +replace into t set a = 'a',b =1; +replace into t set a = 'a',b =1; +drop table t; + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t ADD COLUMN xs INT GENERATED ALWAYS AS(a+b), ADD COLUMN mm INT +GENERATED ALWAYS AS(a+b) STORED, ALGORITHM = INPLACE; + +ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ALGORITHM = INPLACE; + +ALTER TABLE t DROP COLUMN x, ALGORITHM = INPLACE; + +ALTER TABLE t ADD COLUMN x1 INT GENERATED ALWAYS AS(a+b), DROP COLUMN c, +ALGORITHM = INPLACE; + +DROP TABLE t; + +if (0) { +# Some test on virtual/stored column numbering for spatial indexing +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` POINT GENERATED ALWAYS AS (1) STORED +) ENGINE=INNODB; +ALTER TABLE t ADD SPATIAL INDEX (`e`); +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` POINT GENERATED ALWAYS AS (1) STORED, + SPATIAL INDEX (`e`) +) ENGINE=INNODB; +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e2` POINT GENERATED ALWAYS AS (1) STORED, + `e` POINT GENERATED ALWAYS AS (1) STORED +) ENGINE=INNODB; +ALTER TABLE t ADD SPATIAL INDEX (`e`); +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e2` POINT GENERATED ALWAYS AS (1) STORED, + `d2` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` POINT GENERATED ALWAYS AS (1) STORED +) ENGINE=INNODB; +ALTER TABLE t ADD SPATIAL INDEX (`e`); +DROP TABLE t; +CREATE TABLE `t` ( + `a` INT GENERATED ALWAYS AS (1) VIRTUAL, + `b` INT GENERATED ALWAYS AS (1) VIRTUAL, + `c` INT GENERATED ALWAYS AS (1) VIRTUAL, + `d` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e2` POINT GENERATED ALWAYS AS (1) STORED, + `d2` INT GENERATED ALWAYS AS (1) VIRTUAL, + `e` int +) ENGINE=INNODB; +ALTER TABLE t ADD INDEX (`e`); +DROP TABLE t; +} + +CREATE TABLE t (a INT GENERATED ALWAYS AS(1) VIRTUAL,KEY(a)) ENGINE=INNODB; +INSERT INTO t VALUES(default); +SELECT a FROM t FOR UPDATE; +DROP TABLE t; + +# Test add virtual column and add index at the same time +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); + +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); + +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); + +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +--enable_info +ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x); +--disable_info + +SELECT x FROM t; + +DROP TABLE t; + +CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t1 VALUES (11, 3, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 ADD INDEX idx12 (c), LOCK=NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), LOCK=NONE; + +DROP TABLE t1 ; + +# Check ALTER TABLE CHANGE VIRTUAL COLUMN TYPE and ORDER +CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t1 VALUES (11, 3, DEFAULT, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (18, 1, DEFAULT, DEFAULT, 'mm'); + +INSERT INTO t1 VALUES (28, 1, DEFAULT, DEFAULT, 'mm'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 CHANGE d d INT GENERATED ALWAYS AS(a+b) FIRST, ALGORITHM = INPLACE; + +# Change column type is not allow for inplace also +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 CHANGE d d VARCHAR(10) GENERATED ALWAYS AS(h), ALGORITHM = INPLACE; + +ALTER TABLE t1 CHANGE d d INT GENERATED ALWAYS AS(a+b) FIRST; + +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +# Test foreign key which could be a base column of indexed virtual column +CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=INNODB; + +CREATE TABLE child ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + FOREIGN KEY (parent_id) + REFERENCES parent(id) + ON DELETE CASCADE +) ENGINE=INNODB; + +ALTER TABLE child ADD INDEX `i1` (x); + +# If foreign constrain does not have cascade clause, or with "no action" clause +# the index can still be created +CREATE TABLE child_1 ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + FOREIGN KEY (parent_id) + REFERENCES parent(id) +) ENGINE=INNODB; + +# This should be successful +ALTER TABLE child_1 ADD INDEX `i1` (x); + +DROP TABLE child_1; + +DROP TABLE child; + +CREATE TABLE child ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + INDEX i1 (x), + + FOREIGN KEY (parent_id) + REFERENCES parent(id) + ON DELETE CASCADE +) ENGINE=INNODB; + +DROP TABLE child; + +CREATE TABLE child ( + id INT, + parent_id INT, + x int(11) GENERATED ALWAYS AS (parent_id+1), + INDEX par_ind (parent_id), + INDEX `i1` (x) +) ENGINE=INNODB; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON DELETE CASCADE; + +# Check inplace option +SET foreign_key_checks = 0; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON DELETE CASCADE; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON DELETE SET NULL; + +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id) +ON UPDATE CASCADE; + +# this should be successful +ALTER TABLE child ADD FOREIGN KEY (parent_id) +REFERENCES parent(id); + +SET foreign_key_checks = 1; + +DROP TABLE child; + +DROP TABLE parent; + +# Test for Bug 21890816 - ASSERT UPDATE->OLD_VROW, VIRTUAL COLUMNS +CREATE TABLE `ibstd_16` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`) +) ENGINE=INNODB; + +# Block when FK constraint on base column of stored column. +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE `ibstd_16_fk` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`), + CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL +) ENGINE=InnoDB; + +# Take out "KEY `a_2` (`a`,`vbidxcol`)", this should then be successful +CREATE TABLE `ibstd_16_fk` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`), + CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL +) ENGINE=InnoDB; + +ALTER TABLE ibstd_16_fk ADD INDEX `a_2` (`a`,`vbidxcol`); + +# Now try to add a table with virtual index, and then add constraint +DROP TABLE ibstd_16_fk; + +# Create a table without constraint +CREATE TABLE `ibstd_16_fk` ( + `a` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + `b` varchar(198) DEFAULT NULL, + `c` char(179) DEFAULT NULL, + `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, + UNIQUE KEY `b` (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c` (`c`(99),`b`(33)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`) +) ENGINE=InnoDB; + +ALTER TABLE `ibstd_16_fk` ADD CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL; + +# DROP the index +DROP INDEX a_2 ON ibstd_16_fk; + +INSERT INTO ibstd_16 VALUES (1, 2, "aaa", "bbb", default, default, default); +INSERT INTO ibstd_16_fk VALUES(1, 3, "mmm", "SSS", default, default); + +# Cascading delete/update on column non-related to virtual column or virtual +# index will be fine +DELETE FROM ibstd_16 WHERE a = 1; + +DROP TABLE ibstd_16_fk; +DROP TABLE ibstd_16; + +# Bug 21941320 - GCOLS: FAILING ASSERTION: N_IDX > 0 +create table t(a int) engine=innodb; +insert into t set a=1; +alter table t add column c int generated always as (1) virtual; +insert into t set a=2; + +# Following will cause create index fail, we need to make sure the column +# ord_part is reset +--error ER_DUP_ENTRY +alter table t add unique index(c); +insert into t set a=1; +drop table t; + +# Bug 21875974 - VCOL : READ OF FREED MEMORY IN DTUPLE_GET_N_FIELDS +# CAUSE CRASH + +create table t ( + x int, + a int generated always as (x) virtual, + b int generated always as (1) stored, + c int not null, + unique (b), + unique (a,b) +) engine=innodb; + +insert into t(x, c) values(1, 3); + +# This will exercise row_vers_impl_x_locked_low() for virtual columns +replace into t(x, c) values(1, 0); + +drop table t; + +# Bug22123674 VCOL:INNODB: FAILING ASSERTION: !UT_STRCMP(NAME, +# FIELD->FIELD_NAME) + +CREATE TABLE t( +c7c CHAR(1)GENERATED ALWAYS AS (c3) VIRTUAL, +c1 int(1), +c2 int(1), +c3 int(1), +c4 int(1), +c5 int(1)GENERATED ALWAYS AS ((c2 - c4)) VIRTUAL, +UNIQUE KEY c5_9(c5) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t CHANGE COLUMN c5 c5 INT(1) GENERATED ALWAYS AS(c2 - +c4)VIRTUAL AFTER c3,ALGORITHM=INPLACE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t CHANGE COLUMN c7c c7c INT(1) GENERATED ALWAYS AS(c3) +VIRTUAL AFTER c5,ALGORITHM=INPLACE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t DROP COLUMN c7c,ADD COLUMN c5c INT GENERATED ALWAYS AS(c4/ +c3)VIRTUAL AFTER c3,ALGORITHM=INPLACE; + +DROP TABLE t; + +# Bug22111464 VCOL:INNODB: FAILING ASSERTION: I < TABLE->N_DEF + +CREATE TABLE `t` ( + `col1` int(11) DEFAULT NULL, + `col2` int(11) DEFAULT NULL, + `col4` int(11) DEFAULT NULL, + `col5` int(11) GENERATED ALWAYS AS ((`col2` % `col4`)) VIRTUAL, + `col6` int(11) GENERATED ALWAYS AS ((`col2` - `col2`)) VIRTUAL, + `col5x` int(11) GENERATED ALWAYS AS ((`col1` / `col1`)) VIRTUAL, + `col6x` int(11) GENERATED ALWAYS AS ((`col2` / `col4`)) VIRTUAL, + `col7x` int(11) GENERATED ALWAYS AS ((`col6` % `col6x`)) VIRTUAL, + `col8x` int(11) GENERATED ALWAYS AS ((`col6` / `col6`)) VIRTUAL, + `col9` text, + `col7c` int(11) GENERATED ALWAYS AS ((`col6x` % `col6x`)) VIRTUAL, + `col1b` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL, + `col3` int(11) DEFAULT NULL, + `col7` int(11) DEFAULT NULL, + `col5c` int(11) GENERATED ALWAYS AS ((`col5x` * `col6`)) VIRTUAL, + `col6c` varchar(20) GENERATED ALWAYS AS (`col5x`) VIRTUAL, + `col3b` bigint(20) GENERATED ALWAYS AS ((`col6x` * `col6`)) VIRTUAL, + `col1a` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL, + `col8` int(11) DEFAULT NULL, + UNIQUE KEY `col5` (`col5`), + UNIQUE KEY `col6x` (`col6x`), + UNIQUE KEY `col5_2` (`col5`), + KEY `idx2` (`col9`(10)), + KEY `idx4` (`col2`), + KEY `idx8` (`col9`(10),`col5`), + KEY `idx9` (`col6`), + KEY `idx6` (`col6`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +ALTER TABLE t CHANGE COLUMN col3b col8a BIGINT GENERATED ALWAYS AS +(col6x * col6) VIRTUAL, ADD UNIQUE KEY uidx ( col8a ); + +DROP TABLE t; + +--echo # +--echo # Bug 22141031 - GCOLS: PURGED THREAD DIES: TRIED TO PURGE +--echo # NON-DELETE-MARKED RECORD IN INDEX +--echo # +create table t ( + a int,b int,c text,d int,e int,f int,g int, + h text generated always as ('1') virtual, + i int,j int,k int,l int,m int, + primary key (c(1)),unique key (c(1)), + key (i),key (h(1)) +) engine=innodb default charset latin1; + +replace into t(c) values (''); +replace into t(c) values (''); +alter table t drop column d ; + +drop table t; + +--echo # +--echo # Bug 22139917 - ASSERTION: DICT_TABLE_GET_NTH_COL(USER_TABLE, NTH_COL) +--echo # ->LEN < NEW_LEN +--echo # + +create table t ( + a int generated always as (1) virtual, + b varbinary(1), + c varbinary(1) generated always as (b) virtual +) engine=innodb; +alter table t change column b b varbinary(2), algorithm=inplace; +alter table t change column c c varbinary(2) generated always as (b) virtual, algorithm=inplace; + +drop table t; + +# Bug22202788 GCOL:ASSERTION:0 IN ROW_SEL_GET_CLUST_REC_FOR_MYSQL AT +# ROW0SEL.CC +SET @@SESSION.sql_mode=0; + +CREATE TABLE t( + c1 INT AUTO_INCREMENT, + c2 INT, + c3 INT GENERATED ALWAYS AS(c2 + c2)VIRTUAL, + c3k INT GENERATED ALWAYS AS(c2 + c3)VIRTUAL, + c4 DATE, + c5 DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL, + c5k DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL, + c5time_gckey DATE, + c6 TIME, + c5time DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL, + c7 TIME GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL, + c5timek DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c7)) VIRTUAL, + c7k TIME GENERATED ALWAYS AS(ADDTIME(c5time,c6)) VIRTUAL, + c8 CHAR(10), + c9 CHAR(20)GENERATED ALWAYS AS (CONCAT(RTRIM(c8),RTRIM(c8))) VIRTUAL, + c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(RTRIM(c8),0)) VIRTUAL, + PRIMARY KEY(c1), + KEY(c3), + KEY(c9(10)), + UNIQUE KEY(c9k), + UNIQUE KEY(c3k,c9k(5),c5k,c7k,c5timek,c3,c9(5),c5,c7,c5time) +)ENGINE=INNODB; + +--error ER_DUP_ENTRY +INSERT INTO +t(c2,c4,c6,c5time_gckey,c8)VALUES(1,0,0,0,0),(0,0,0,0,'ityzg'),(0,0,1,0,'tyzgk +t'),(0,1,0,1,'yzgktb'),(0,0,0,0,'zgktb'),(0,0,0,0,'gktbkj'),(0,0,0,0,0),(0,0,1 +,0,1),(0,0,0,0,1),(0,0,0,0,'tbkjrkm'),(0,0,0,0,'bkjr'),(0,0,0,0,0),(0,0,0,0,0) +,(0,0,0,0,'rk'),(0,0,0,0,'kmqmknbtoe'),(1,0,0,0,'mqmknbt'),(0,1,0,0,'qmknb'),( +0,0,0,0,'mkn'),(0,0,0,0,'knbtoervql'),(0,0,1,0,1),(0,0,0,0,'nbtoerv'),(0,0,0,0 +,'btoerv'),(0,0,1,0,'toer'),(1,0,0,0,0),(0,0,0,0,'ervq'),(0,0,0,0,'rvqlzsvasu' +),(0,0,0,0,'vqlzs'),(0,0,0,0,0),(0,1,0,0,'lzsvasu'),(0,0,0,0,'zsvasurq'); + +SELECT +DISTINCT * FROM t +FORCE KEY(PRIMARY,c3k,c3,c9k,c9) +WHERE +(c9 IS NULL AND (c9=0)) +OR( +(c9k NOT IN ('ixfq','xfq','New Mexico','fq')OR c9 IS NULL) +) +OR(c9 BETWEEN 'hwstqua' AND 'wstquadcji' OR (c9k=0)) +AND(c3 IS NULL OR c3 IN (0,0,0)); + +drop table t; + +# +# BUG#22082762 RE-ENABLE SUPPORT FOR ADDING VIRTUAL INDEX WHILE DROPPING VIRTUAL COLUMN +# + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT +GENERATED ALWAYS AS(a+b+b), e INT GENERATED ALWAYS AS(a), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, DEFAULT, 'mm'); +CREATE INDEX idx ON t(c, d); +CREATE INDEX idx1 ON t(c); +CREATE INDEX idx2 ON t(e, c, d); + +# This will drop column c, drop index idx1 on column c, and build index +# idx and idx2, so they become idx(d) and idx2(e, d) respectively. +ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE; + +SELECT d FROM t; + +SHOW CREATE TABLE t; + +# Drop a column, adding a new column and also adding a index on this new column +# is not allowed for INPLACE algorithm +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE; + +# Add an index on existing column along with dropping a column is allowed +ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (e), ALGORITHM=INPLACE, LOCK=NONE; +SHOW CREATE TABLE t; + +# Add an index on existing column along with adding a virtual column and droping a virtual index +ALTER TABLE t ADD INDEX idx4(c, e), ADD COLUMN x VARCHAR(10) GENERATED ALWAYS AS(h), DROP INDEX idx, ALGORITHM=INPLACE, LOCK=NONE; +SHOW CREATE TABLE t; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD COLUMN j INT, ALGORITHM=INPLACE; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t ADD INDEX (x), ADD COLUMN j INT, ALGORITHM=INPLACE, LOCK=NONE; +ALTER TABLE t ADD INDEX (x), ADD COLUMN j INT, ALGORITHM=INPLACE; +SHOW CREATE TABLE t; + +# Online add an index on newly added virtual column is not allowed. +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (i), ALGORITHM=INPLACE, LOCK=NONE; + +ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (i), ALGORITHM=INPLACE, LOCK=SHARED; +SHOW CREATE TABLE t; + +SELECT i FROM t; + +SELECT * FROM t; + +DROP TABLE t; + +# +# BUG#22469459 WRONG VALUES IN ADDED INDEX WHILE DROPPING VIRTUAL COLUMN +# + +# Drop column with existing index on it. +CREATE TABLE t ( + a INT, + b INT, + c INT GENERATED ALWAYS AS(a+b), + d INT GENERATED ALWAYS AS(a+b+b), + KEY vidx (c, d) +)ENGINE=INNODB; + +INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL); + +SELECT c, d FROM t; + +SELECT * FROM t; + +ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE; + +SELECT d FROM t; + +SELECT * FROM t; + +DROP TABLE t; + +# Drop column with a new index. +CREATE TABLE t ( + a INT, + b INT, + c INT GENERATED ALWAYS AS(a+b), + d INT GENERATED ALWAYS AS(a+b+b) +)ENGINE=INNODB; + +INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL); + +SELECT * FROM t; + +ALTER TABLE t DROP COLUMN c, ADD INDEX vidx(d), ALGORITHM=INPLACE; + +SELECT d FROM t; + +SELECT * FROM t WHERE d > 0; + +SELECT * FROM t; + +DROP TABLE t; + +--echo # +--echo # Bug #22162200 MEMORY LEAK IN HA_INNOPART_SHARE +--echo # ::SET_V_TEMPL PARTITIONED ON VIRTUAL COLUMN +--echo # +create table t ( + c tinyint, + d longblob generated always as (c) virtual +) engine=innodb partition by key (c) partitions 2; + +select d in(select d from t)from t group by d; +drop table t; + +--echo # +--echo # BUG#23052231 - ASSERTION FAILURE: ROW0MERGE.CC:2100:ADD_AUTOINC +--echo # < DICT_TABLE_GET_N_USER_COLS +--echo # +CREATE TABLE `t` ( + `a` int(11) NOT NULL, + `d` int(11) NOT NULL, + `b` varchar(198) NOT NULL, + `c` char(177) DEFAULT NULL, + `vadcol` int(11) GENERATED ALWAYS AS ((`a` + length(`d`))) STORED, + `vbcol` char(2) GENERATED ALWAYS AS (substr(`b`,2,2)) VIRTUAL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL, + PRIMARY KEY (`b`(10),`a`,`d`), + KEY `d` (`d`), + KEY `a` (`a`), + KEY `c_renamed` (`c`(99),`b`(35)), + KEY `b` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`,`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO t values (11, 1, "11", "aa", default, default, default); + +ALTER TABLE t ADD COLUMN nc01128 BIGINT AUTO_INCREMENT NOT NULL, ADD KEY auto_nc01128(nc01128); + +DROP TABLE t; + +--echo # +--echo #Bug #22965271 NEEDS REBUILD FOR COLUMN LENGTH CHANGE THAT IS +--echo #PART OF VIRTUAL INDEX. +--echo # + +CREATE TABLE t1( +a VARCHAR(45) CHARACTER SET LATIN1, +b VARCHAR(115) CHARACTER SET UTF8 GENERATED ALWAYS AS ('f1') VIRTUAL, +UNIQUE KEY (b,a))ENGINE=INNODB; +INSERT INTO t1(a) VALUES (''); +ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(85); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a INT GENERATED ALWAYS AS(1) VIRTUAL) ENGINE=InnoDB; +ALTER TABLE t1 ADD b INT GENERATED ALWAYS AS (2) VIRTUAL; +ALTER TABLE t1 ADD c INT; +SELECT * FROM t1; +INSERT INTO t1 SET c=3; +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_blob.test b/mysql-test/suite/gcol/t/innodb_virtual_blob.test new file mode 100644 index 00000000..e94d2c77 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_blob.test @@ -0,0 +1,25 @@ +--source include/have_innodb.inc + +--echo # +--echo # Bug#22046353 ALTER: ASSERT PAGE_SIZE.EQUALS_TO(SPACE_PAGE_SIZE), +--echo # BTR_COPY_BLOB_PREFIX +--echo # + +--disable_query_log +SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; +SET GLOBAL innodb_read_only_compressed=OFF; +--enable_query_log + +CREATE TABLE t1 +( f1 int primary key, f2 blob, + f3 blob generated always as (f2)) + row_format=compressed, engine=innodb; +insert into t1 (f1, f2) values (1, repeat('&', 50000)); +alter table t1 add index i1 (f3(200)) ; +alter table t1 row_format=compact; + +--disable_query_log +SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; +--enable_query_log + +drop table t1; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug.test b/mysql-test/suite/gcol/t/innodb_virtual_debug.test new file mode 100644 index 00000000..cd2b8604 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug.test @@ -0,0 +1,341 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/have_debug.inc +--source include/count_sessions.inc + +set default_storage_engine=innodb; +CREATE TABLE `t` ( + `a` VARCHAR(100), + `b` VARCHAR(100), + `c` VARCHAR(200) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `h` VARCHAR(10) DEFAULT NULL, + `i` int +) ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 10), DEFAULT, "kk", 1); +INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2); + +CREATE INDEX idx ON t(c(100)); + +SET session debug_dbug="+d,ib_alter_add_virtual_fail"; +--error ER_WRONG_KEY_COLUMN +ALTER TABLE t ADD COLUMN x VARCHAR(200) GENERATED ALWAYS AS (a) VIRTUAL, +ALGORITHM = INPLACE; +--error ER_WRONG_KEY_COLUMN +ALTER TABLE t DROP COLUMN c, ALGORITHM = INPLACE; +SET session debug_dbug=""; +DROP TABLE t; + +#online test +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, "mx"); + +SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; +--send CREATE INDEX idx ON t(c) + +connect (con1,localhost,root,,); + +SET DEBUG_SYNC = 'now WAIT_FOR start_create'; +update t set a=0 where a = 11; +start transaction; +update t set a=1 where a = 0; +ROLLBACK; +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; +reap; + +SELECT c FROM t; +SHOW CREATE TABLE t; +SELECT * FROM t; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t FORCE, LOCK=NONE; +if (0) {# MDEV-14341 TODO: re-enable this +SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; +--send ALTER TABLE t FORCE + +connection con1; +SET DEBUG_SYNC = 'now WAIT_FOR start_create'; +start transaction; +update t set a=1 where a = 0; +rollback; +start transaction; +delete from t; +insert into t values(1,null,default,null); +rollback; +start transaction; +update t set b=b+1; +rollback; +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; +reap; + +check table t; +SELECT c FROM t; + +SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; +--send ALTER TABLE t FORCE + +connection con1; + +SET DEBUG_SYNC = 'now WAIT_FOR start_create'; +start transaction; +DELETE FROM t WHERE a = 0; +ROLLBACK; +DELETE FROM t WHERE a = 0; +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; +reap; + +SELECT c FROM t; +} + +disconnect con1; +DROP TABLE t; + +SET DEBUG_SYNC = 'RESET'; + + +# Test add virtual column and add index at the same time +# introduce some error + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); + +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); + +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); + +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +CREATE INDEX idx_1 on t(c); + +SET @saved_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,create_index_fail'; + +--enable_info +--error ER_DUP_ENTRY +ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x), +ADD INDEX idcx (c,x); + +UPDATE t SET a=a+1; + +--error ER_DUP_ENTRY +ALTER TABLE t ADD INDEX idc(c); +SET debug_dbug = @saved_dbug; +--disable_info + +UPDATE t SET b=b-1; + +SHOW CREATE TABLE t; + +SELECT c FROM t; + +DROP TABLE t; + +if (0) {# MDEV-14341 TODO: re-enable LOCK=NONE and these tests +--echo # +--echo # Bug#22018532 ASSERTION WHEN ONLINE REAPPLY REBUILD LOG ON +--echo # MULTIPLE INDEXED VIRTUAL COLUMNS +--echo # + +create table t ( + a int as (1) virtual, + b int, + c int as (1) virtual, + unique(b), + unique(c), + key(a) +) engine=innodb; + +insert ignore into t values(); + +SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; +--send optimize table t + +connect (con1,localhost,root,,); + +SET DEBUG_SYNC = 'now WAIT_FOR start_create'; +insert ignore into t values(); +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; +--echo /* connection default */ optimize table t; +reap; +SELECT c FROM t; +SHOW CREATE TABLE t; +SELECT * FROM t; +DROP TABLE t; + +# Do another test without duplicate error + +CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); + +INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); +INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); +INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); + +CREATE INDEX idx ON t(c); + +SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_rebuild WAIT_FOR go_ahead'; +--send optimize table t + +connection con1; +SET DEBUG_SYNC = 'now WAIT_FOR start_rebuild'; +INSERT INTO t VALUES (48, 2, DEFAULT, 'xx'); +INSERT INTO t VALUES (68, 3, DEFAULT, 'sx'); +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; +--echo /* connection default */ optimize table t; +reap; + +SELECT c FROM t; + +disconnect con1; + +DROP TABLE t; + +--echo # +--echo # Bug#22951879 - ASSERTS RELATED TO ONLINE DDL AND GCOL +--echo # + +# Create a table with 2 virtual column, one (vbidxcol) is indexed and +# the other one (vbcol) is not +create table ibstd_14 (a int not null, d int not null, b varchar(198) not null, c char(181), vadcol int as (a+length(d)) stored, vbcol char(2) as (substr(b,2,2)) virtual, vbidxcol char(3) as (substr(b,1,3)) virtual , index(d), index(a), index(vbidxcol), index(a,vbidxcol), index(vbidxcol,d), unique key (b(10), a, d), index(c(99), b(31)), index(b(5), c(10), a) , index(a,d)) engine=InnoDB stats_persistent=1 row_format=dynamic; + +# Do an alter table rebuild table and also create a new index on this +# non-indexed virtual column +SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; +--send alter table ibstd_14 row_format=compressed key_block_size=4,add key kn3 (d,c,vbcol,b) + +# Do a concurrent insert, and make sure this newly indexed virtual column +# is also logged +connect (con1,localhost,root); +SET DEBUG_SYNC = 'now WAIT_FOR start_create'; +insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6',repeat('oacolaarlruoacuroauurloraarucoooarcooauoolacalllaulrruarrrucruuooclacuoouccarrcoocloccorrrrarourcooalloocooccouruolaorlcaocualolc','1'),repeat('lolrrlalcocroraaulauclaaucolcorcuooaolruaooooluooooouaoorlarucorullalcrrloccououaooaorluorraclrcooouuolocoaolcocaaculruoocucoocoooauuolarcoraraocaoolulolarru','1'),default,default); + +insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6', 'aaaa', 'lll', default, default); + +# Also do an concurrent update, make sure this is performed +update ibstd_14 set b='11111' where b='aaaa'; + +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; +reap; + +select * from ibstd_14; + +# This will use the newly added "kn3" index, to check materialized vbcol +# after log reapply +select d,c,vbcol,b from ibstd_14; + +# check the value is inserted into the index +select vbcol from ibstd_14; + +drop table ibstd_14; + +--echo # +--echo # Bug#22018745 CORRUPTION IN ONLINE TABLE REBUILD +--echo # (ROW_FORMAT=REDUNDANT, INDEXED VIRTUAL COLUMN) +--echo # + +CREATE TABLE t ( + b char(5) PRIMARY KEY, + v char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, KEY(v) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT; + +connection con1; +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL prepared WAIT_FOR apply'; +--send OPTIMIZE TABLE t +connection default; + +SET DEBUG_SYNC='now WAIT_FOR prepared'; +INSERT INTO t SET b='fubar'; +BEGIN; +DELETE FROM t; +ROLLBACK; +SET DEBUG_SYNC='now SIGNAL apply'; + +connection con1; +reap; + +connection default; +CHECK TABLE t; +SELECT * FROM t; +DROP TABLE t; + +disconnect con1; +} +SET DEBUG_SYNC = 'RESET'; + + +--echo # +--echo # Bug#28825718 - ASSERTION FAILURE: TRX0REC.CC:NNN:N_IDX > 0 WHILE DOING REPLACE/INSERT +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT GENERATED ALWAYS AS(b+1) VIRTUAL) ENGINE=InnoDB; + +INSERT INTO t1(a, b) VALUES(1, 1); + +connect (con1,localhost,root,,); +SET DEBUG_SYNC = 'row_log_apply_after SIGNAL s1 WAIT_FOR s2'; +SET lock_wait_timeout = 1; +--send ALTER TABLE t1 ADD UNIQUE INDEX(c, b) + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR s1'; +SET DEBUG_SYNC = 'row_log_insert_handle SIGNAL s2 WAIT_FOR s3'; +--send INSERT INTO t1(a, b) VALUES(2, 2) + +connection con1; +--error ER_LOCK_WAIT_TIMEOUT +reap; +SET DEBUG_SYNC = 'now SIGNAL s3'; +disconnect con1; +connection default; +reap; +SET DEBUG_SYNC = 'RESET'; +ALTER TABLE t1 ADD KEY(b); +INSERT INTO t1(a, b) VALUES(3, 3); +SELECT * FROM t1; +CHECK TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-28806 Assertion `flag == 1' failure in +--echo # row_build_index_entry_low upon concurrent ALTER and UPDATE +--echo # + +CREATE TABLE t1(a CHAR(8), b INT, c INT AS (b), KEY(a)) ENGINE=InnoDB; +INSERT INTO t1(b) VALUES (1),(2); + +--connect (con1,localhost,root,,test) +SET DEBUG_SYNC="alter_table_inplace_before_lock_upgrade SIGNAL dml_start WAIT_FOR dml_commit"; +send ALTER TABLE t1 ADD KEY ind (c); + +--connection default +SET DEBUG_SYNC="now WAIT_FOR dml_start"; +UPDATE t1 SET a ='foo'; +SET DEBUG_SYNC="now SIGNAL dml_commit"; + +# Cleanup +--connection con1 +--reap +CHECK TABLE t1; +DROP TABLE t1; +--disconnect con1 +connection default; +SET DEBUG_SYNC=RESET; + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt new file mode 100644 index 00000000..a1207721 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.opt @@ -0,0 +1 @@ +--innodb-purge-threads=1 diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test new file mode 100644 index 00000000..09fba028 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test @@ -0,0 +1,268 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/have_debug.inc +--source include/have_partition.inc + +set default_storage_engine=innodb; +set @old_dbug=@@global.debug_dbug; +# Ensure that the history list length will actually be decremented by purge. +SET @saved_stats_persistent = @@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent = OFF; + +CREATE TABLE `t` ( + `a` BLOB, + `b` BLOB, + `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + `h` VARCHAR(10) DEFAULT NULL, + `i` int +) ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk", 1); +INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, "mm", 2); + +CREATE INDEX idx ON t(c(100)); + +SET global debug_dbug="d,ib_purge_virtual_index_callback"; +UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%"; +--source ../../innodb/include/wait_all_purged.inc +SET global debug_dbug=@old_dbug; +DROP TABLE t; + +CREATE TABLE t ( + a TINYBLOB, + b TINYBLOB, + c TINYBLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + h VARCHAR(10) DEFAULT NULL, + i INT +) ROW_FORMAT=COMPACT ENGINE=InnoDB; + +INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 100), DEFAULT, "kk", 1); +INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2); + +CREATE INDEX idx ON t(c(100)); + +SET global debug_dbug="d,ib_purge_virtual_index_callback"; +UPDATE t SET a = REPEAT('m', 100) WHERE a like "aaa%"; +--source ../../innodb/include/wait_all_purged.inc +SET global debug_dbug=@old_dbug; +DROP TABLE t; + + +CREATE TABLE t1 ( + id INT NOT NULL, + store_id INT NOT NULL, + x INT GENERATED ALWAYS AS (id + store_id) +) +PARTITION BY RANGE (store_id) ( + PARTITION p0 VALUES LESS THAN (6), + PARTITION p1 VALUES LESS THAN (11), + PARTITION p2 VALUES LESS THAN (16), + PARTITION p3 VALUES LESS THAN (21) +); + +insert into t1 values(1, 2, default); +insert into t1 values(3, 4, default); + +insert into t1 values(3, 12, default); +insert into t1 values(4, 18, default); + +CREATE INDEX idx ON t1(x); + +SET global debug_dbug="d,ib_purge_virtual_index_callback"; +UPDATE t1 SET id = 10 WHERE id = 1; +--source ../../innodb/include/wait_all_purged.inc +SET global debug_dbug=@old_dbug; +DROP TABLE t1; + +# +# BUG#22082762 RE-ENABLE SUPPORT FOR ADDING VIRTUAL INDEX WHILE DROPPING VIRTUAL COLUMN +# +--source include/count_sessions.inc + +connect (con1,localhost,root,,); +connection default; + +# Test adding virtual index on newly added virtual column +CREATE TABLE t1 (a INT, b INT); + +INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3); + +connection con1; +--echo # disable purge +CREATE TABLE t0 (a INT) ENGINE=InnoDB; +BEGIN; SELECT * FROM t0; + +connection default; +DELETE FROM t1 WHERE a = 1; + +UPDATE t1 SET a = 4, b = 4 WHERE a = 3; + +INSERT INTO t1(a, b) VALUES (5, 5); + +SET DEBUG_SYNC= 'inplace_after_index_build SIGNAL uncommitted WAIT_FOR purged'; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE; +send ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=SHARED; + +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR uncommitted'; + +--echo # enable purge +COMMIT; + +--echo # wait for purge to process the deleted records. +let $wait_all_purged = 1; +--source ../../innodb/include/wait_all_purged.inc +let $wait_all_purged = 0; + +SET DEBUG_SYNC= 'now SIGNAL purged'; + +connection default; +--echo /* connection default */ ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=SHARED; +--reap +SHOW CREATE TABLE t1; + +SELECT * FROM t1; + +DROP TABLE t1; + +# Test adding index on existing virtual column +CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b)); + +INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3), (4, 4); + +connection con1; +--echo # disable purge +BEGIN; SELECT * FROM t0; + +connection default; +DELETE FROM t1 WHERE a = 1; + +UPDATE t1 SET a = 2, b = 2 WHERE a = 5; + +INSERT INTO t1(a, b) VALUES (6, 6); + +SET DEBUG_SYNC= 'inplace_after_index_build SIGNAL uncommitted WAIT_FOR purged'; +send ALTER TABLE t1 ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE; + +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR uncommitted'; + +DELETE FROM t1 WHERE a = 3; + +UPDATE t1 SET a = 7, b = 7 WHERE a = 4; + +INSERT INTO t1(a, b) VALUES (8, 8); + +--echo # enable purge +COMMIT; + +--echo # wait for purge to process the deleted/updated records. +let $wait_all_purged=2; +--source ../../innodb/include/wait_all_purged.inc +let $wait_all_purged=0; + +SET DEBUG_SYNC= 'now SIGNAL purged'; + +disconnect con1; + +connection default; +--echo /* connection default */ ALTER TABLE t1 ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE; +--reap +SHOW CREATE TABLE t1; + +SELECT * FROM t1; + +DROP TABLE t0, t1; + +# +# test MDLs with purge +# +create table t (a blob, b blob, c blob as (concat(a,b)), h varchar(10), index (c(100))); +insert t(a,b,h) values (repeat('g', 16000), repeat('x', 16000), "kk"); +insert t(a,b,h) values (repeat('a', 16000), repeat('b', 16000), "mm"); +set global debug_dbug="d,ib_purge_virtual_index_callback"; +connect(prevent_purge, localhost, root); +start transaction with consistent snapshot; +connection default; +update t set a = repeat('m', 16000) where a like "aaa%"; +connect(lock_table, localhost, root); +lock table t write; +connection prevent_purge; +commit; +connection default; +disconnect lock_table; +--source ../../innodb/include/wait_all_purged.inc +set global debug_dbug=@old_dbug; +drop table t; + +--echo # +--echo # MDEV-15165 InnoDB purge for index on virtual column +--echo # is trying to access an incomplete record +--echo # +CREATE TABLE t1( + u INT PRIMARY KEY, b BLOB, ug INT GENERATED ALWAYS AS (u) VIRTUAL, + INDEX bug(b(100),ug) +) ENGINE=InnoDB; +INSERT INTO t1 (u,b) VALUES(1,REPEAT('a',16384)); +connection prevent_purge; +start transaction with consistent snapshot; +connection default; +DELETE FROM t1; +SET DEBUG_SYNC='blob_write_middle SIGNAL halfway WAIT_FOR purged'; +send INSERT INTO t1 (u,b) VALUES(1,REPEAT('a',16384)); +connection prevent_purge; +SET DEBUG_SYNC='now WAIT_FOR halfway'; +COMMIT; +--source ../../innodb/include/wait_all_purged.inc +SET DEBUG_SYNC='now SIGNAL purged'; + +connection default; +reap; +DROP TABLE t1; + +CREATE TABLE t1 (y YEAR, vy YEAR AS (y) VIRTUAL UNIQUE, pk INT PRIMARY KEY) +ENGINE=InnoDB; + +INSERT INTO t1 (pk,y) VALUES (1,2022); +CREATE TABLE t2(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB; + +SET GLOBAL debug_dbug = 'd,ib_purge_virtual_index_callback'; + +BEGIN; +INSERT INTO t2(f1) VALUES(1); +connection prevent_purge; +SET DEBUG_SYNC=RESET; +start transaction with consistent snapshot; +connection default; +COMMIT; + +connect(truncate,localhost,root,,); +REPLACE INTO t1(pk, y) SELECT pk,y FROM t1; +send TRUNCATE TABLE t1; + +connection prevent_purge; +COMMIT; +SET DEBUG_SYNC='now SIGNAL purge_start'; +disconnect prevent_purge; + +connection default; +SET DEBUG_SYNC='now WAIT_FOR purge_start'; +--source ../../innodb/include/wait_all_purged.inc +SET GLOBAL debug_dbug=@old_dbug; + +connection truncate; +reap; +disconnect truncate; + +connection default; +DROP TABLE t1, t2; + +--disable_ps2_protocol +--enable_ps2_protocol +--disable_ps2_protocol +--enable_ps2_protocol +--source include/wait_until_count_sessions.inc +set debug_sync=reset; + +SET GLOBAL innodb_stats_persistent = @saved_stats_persistent; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk.test b/mysql-test/suite/gcol/t/innodb_virtual_fk.test new file mode 100644 index 00000000..0f0406b5 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test @@ -0,0 +1,879 @@ +-- source include/have_innodb.inc + +set default_storage_engine=innodb; + +--echo # +--echo # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED +--echo # WHEN A VIRTUAL INDEX EXISTS. + + +--echo # UPDATE CASCADE +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # UPDATE SET NULL +CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY, + KEY(fld1)); +CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE SET NULL); +INSERT INTO t1 VALUES(1, 2); +INSERT INTO t2 VALUES(1, DEFAULT); +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DELETE CASCADE +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t1 VALUES(2); +INSERT INTO t2 VALUES(1, DEFAULT); +INSERT INTO t2 VALUES(2, DEFAULT); +DELETE FROM t1 WHERE fld1= 1; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DELETE SET NULL +CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY, KEY(fld1)); +CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE SET NULL); +INSERT INTO t1 VALUES(1, 1); +INSERT INTO t1 VALUES(2, 2); +INSERT INTO t2 VALUES(1, DEFAULT); +INSERT INTO t2 VALUES(2, DEFAULT); +DELETE FROM t1 WHERE fld1= 1; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # VIRTUAL INDEX CONTAINS FK CONSTRAINT COLUMN +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT, fld3 INT AS (fld2) VIRTUAL, + KEY(fld3, fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1, fld2) VALUES(1, 3); +UPDATE t1 SET fld1= 2; +SELECT fld3, fld1 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # Multiple level of VIRTUAL columns. + +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + fld3 INT AS (fld2) VIRTUAL, KEY(fld3), KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1) VALUES(1); +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT fld3 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # Drop the VIRTUAL INDEX using alter copy ALGORITHM +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY vk(fld2), + KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1) VALUES(1); +UPDATE t1 SET fld1= 2; +SELECT fld2, fld1 FROM t2; +ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY; +UPDATE t1 SET fld1= 3; +SELECT fld2, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Drop the VIRTUAL INDEX using INPLACE alter ALGORITHM +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + KEY vk(fld2), KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1) VALUES(1); +UPDATE t1 SET fld1= 2; +SELECT fld2, fld1 FROM t2; +ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY; +UPDATE t1 SET fld1= 3; +SELECT fld2, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Add the VIRTUAL INDEX using COPY alter ALGORITHM +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1) VALUES(1); +UPDATE t1 SET fld1= 2; +SELECT fld2, fld1 FROM t2; +ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY; +UPDATE t1 SET fld1= 3; +SELECT fld2, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Add the VIRTUAL INDEX using INPLACE alter ALGORITHM +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL,fld2 INT AS (fld1) VIRTUAL, + KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1) VALUES(1); +UPDATE t1 SET fld1= 2; +SELECT fld2, fld1 FROM t2; +ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE; +UPDATE t1 SET fld1= 3; +SELECT fld2, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Drop the VIRTUAL INDEX contains fk constraint column +--echo # using alter copy ALGORITHM +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1), + KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1, fld2) VALUES(1, 2); +UPDATE t1 SET fld1= 2; +SELECT fld3, fld1 FROM t2; +ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY; +UPDATE t1 SET fld1= 3; +SELECT fld3, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Drop the VIRTUAL INDEX which contains fk constraint column +--echo # using INPLACE alter operation +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1), + KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1, fld2) VALUES(1, 2); +UPDATE t1 SET fld1= 2; +SELECT fld3, fld1 FROM t2; +alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE; +UPDATE t1 SET fld1= 3; +SELECT fld3, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Add the VIRTUAL INDEX contains fk constraint column +--echo # using copy alter operatiON +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY(fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1, fld2) VALUES(1, 2); +UPDATE t1 SET fld1= 2; +SELECT fld3, fld1 FROM t2; +alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY; +UPDATE t1 SET fld1= 3; +SELECT fld3, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # Cascading UPDATEs and DELETEs for the multiple +--echo # fk dependent TABLEs + +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + KEY(fld1), KEY(fld2, fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); +CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + KEY(fld2, fld1), + FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2(fld1) VALUES(1), (2); +INSERT INTO t3(fld1) VALUES(1), (2); +UPDATE t1 SET fld1= 4 WHERE fld1= 1; +SELECT fld2, fld1 FROM t2; +SELECT fld2, fld1 FROM t3; +DROP TABLE t3, t2, t1; + +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), KEY(fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE); +CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), + FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default); +INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default); +UPDATE t1 SET fld1= 4 WHERE fld1= 1; +SELECT fld3, fld1 FROM t2; +SELECT fld3, fld1 FROM t3; +DROP TABLE t3, t2, t1; + +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + KEY(fld1), KEY(fld2, fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE); +CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, + KEY(fld2, fld1), FOREIGN KEY(fld1) REFERENCES t2(fld1) + ON DELETE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2(fld1) VALUES(1), (2); +INSERT INTO t3(fld1) VALUES(1), (2); +DELETE FROM t1 WHERE fld1= 1; +SELECT fld2, fld1 FROM t2; +SELECT fld2, fld1 FROM t3; +DROP TABLE t3, t2, t1; + +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, + KEY(fld3, fld1), KEY(fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON DELETE CASCADE); +CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), + FOREIGN KEY(fld1) REFERENCES t2(fld1) + ON DELETE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default); +INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default); +DELETE FROM t1 WHERE fld1= 1; +SELECT fld3, fld1 FROM t2; +SELECT fld3, fld1 FROM t3; +DROP TABLE t3, t2, t1; + +--echo # RENAME TABLE +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + KEY(fld2, fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON DELETE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, DEFAULT), (2, default); +RENAME TABLE t2 to t3; +DELETE FROM t1 WHERE fld1= 1; +SELECT fld2, fld1 FROM t3; +DROP TABLE t3, t1; + +--echo # FOREIGN_KEY_CHECKS disabled DURING INPLACE ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, DEFAULT), (2, default); +SET foreign_key_checks = 0; +ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM=INPLACE; +SET foreign_key_checks = 1; +UPDATE t1 SET fld1= 3 WHERE fld1= 2; +SELECT fld2 FROM t2; +DROP TABLE t2, t1; + +--echo # GENERATED COLUMN COMPUTATION FAILS when SQL_MODE +--echo # is set to ERROR_FOR_DIVISION_BY_ZERO +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (100/fld1) VIRTUAL, + KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, DEFAULT), (2, default); +#--error ER_DIVISION_BY_ZERO +UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2; +SELECT fld2 FROM t2; +DROP TABLE t2, t1; + +--echo # CHANGE SQL_MODE and try the ERROR_FOR_DIVISION_BY_ZERO +SET sql_mode = STRICT_ALL_TABLES; +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (100/fld1) VIRTUAL, + KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, DEFAULT), (2, default); +UPDATE t1 SET fld1= 0 WHERE fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; +SET sql_mode = default; + +--echo # ADD FOREIGN CONSTRAINT USING COPY +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, KEY(fld2)); +ALTER TABLE t2 ADD FOREIGN KEY (fld1) + REFERENCES t1(fld1) ON UPDATE CASCADE, + ALGORITHM=copy; +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # ADD FOREIGN CONSTRAINT USING INPLACE +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, KEY(fld2)); +SET foreign_key_checks = 0; +ALTER TABLE t2 ADD FOREIGN KEY (fld1) + REFERENCES t1(fld1) ON UPDATE CASCADE, + ALGORITHM=inplace; +SET foreign_key_checks = 1; +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DROP FOREIGN CONSTRAINT USING COPY +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, KEY(fld2), + CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=COPY; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DROP FOREIGN CONSTRAINT USING INPLACE +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, KEY(fld2), + CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +SET foreign_key_checks = 0; +ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=INPLACE; +SET foreign_key_checks = 1; +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # ADD VC INDEX and ADD FK IN SAME COPY ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE, ALGORITHM=copy; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # ADD VC INDEX and ADD FK IN SAME INPLACE ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +SET foreign_key_checks = 0; +ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE, ALGORITHM=inplace; +SET foreign_key_checks = 1; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # ADD VC INDEX and DROP FK IN SAME COPY ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=copy; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # ADD VC INDEX and DROP FK IN SAME INPLACE ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +SET foreign_key_checks = 0; +ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=inplace; +SET foreign_key_checks = 1; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DROP VC INDEX and ADD FK IN SAME COPY ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + KEY idx(fld2)); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE, ALGORITHM=COPY; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DROP VC INDEX and ADD FK IN SAME INPLACE ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + KEY idx(fld2)); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +SET foreign_key_checks = 0; +ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE, ALGORITHM=INPLACE; +SET foreign_key_checks = 1; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DROP VC INDEX and DROP FK IN SAME COPY ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + KEY idx(fld2), + CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=COPY; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # DROP VC INDEX and DROP FK IN SAME INPLACE ALTER +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY); +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + KEY idx(fld2), + CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE); +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(1, DEFAULT); +SET foreign_key_checks = 0; +ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=INPLACE; +SET foreign_key_checks = 1; +UPDATE t1 SET fld1= 2; +SELECT fld2 FROM t2; +SELECT * FROM t2; +DROP TABLE t2, t1; + +# Foreign key constraint references to virtual index +CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB; +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL, + KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +INSERT INTO t2(f1) VALUES(1); +EXPLAIN SELECT f1, f2 FROM t2; +SELECT f1, f2 FROM t2; +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO t2(f1) VALUES(2); +DROP TABLE t2, t1; + +# Update foreign key constraint references to virtual index +CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB; +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL, + KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1) + ON UPDATE CASCADE)ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +INSERT INTO t2(f1) VALUES(1); +EXPLAIN SELECT f1, f2 FROM t2; +SELECT f1, f2 FROM t2; +UPDATE t1 SET f1 = 2 WHERE f1 = 1; +EXPLAIN SELECT f1, f2 FROM t2; +SELECT f1, f2 FROM t2; +DROP TABLE t2, t1; + +# Add foreign key constraint via inplace alter references to virtual index + +CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB; +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL, + KEY (f1, f2))ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +INSERT INTO t2(f1) VALUES(1); +SET FOREIGN_KEY_CHECKS = 0; +ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1) + ON UPDATE CASCADE, ALGORITHM=INPLACE; +SET FOREIGN_KEY_CHECKS = 1; +UPDATE t1 SET f1 = 3; +EXPLAIN SELECT f1, f2 FROM t2; +SELECT f1, f2 FROM t2; +DROP TABLE t2, t1; + +# Add foreign key constraint via copy alter references to virtual index + +CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB; +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL, + KEY (f1, f2))ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +INSERT INTO t2(f1) VALUES(1); +ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1) + ON UPDATE CASCADE, ALGORITHM=COPY; +UPDATE t1 SET f1 = 3; +EXPLAIN SELECT f1, f2 FROM t2; +SELECT f1, f2 FROM t2; +DROP TABLE t2, t1; + +# Drop column via inplace alter which triggers to remove the FK index idx + +CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB; +CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL, + f3 INT AS (2) VIRTUAL, + FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE, + KEY idx1 (f2, f1, f3))ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +INSERT INTO t2(f1) VALUES(1); +ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=INPLACE; +UPDATE t1 SET f1 = 3; +EXPLAIN SELECT f1, f3 FROM t2; +SELECT f1, f3 FROM t2; +DROP TABLE t2, t1; + +# Drop column via copy alter which triggers to remove the FK index idx + +CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB; +CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL, + f3 INT AS (2) VIRTUAL, + FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE, + KEY idx1 (f2, f1, f3))ENGINE=INNODB; +INSERT INTO t1 VALUES(1); +INSERT INTO t2(f1) VALUES(1); +ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=COPY; +UPDATE t1 SET f1 = 3; +EXPLAIN SELECT f1, f3 FROM t2; +SELECT f1, f3 FROM t2; +DROP TABLE t2, t1; + +--echo # +--echo # MDEV-15553 Assertion failed in dict_table_get_col_name +--echo # +CREATE TABLE t1 ( + c1 TIMESTAMP, + c2 YEAR, + c3 TIME, + c4 CHAR(10), + v1 TIMESTAMP AS (c1) VIRTUAL, + v2 YEAR AS (c2) VIRTUAL, + v3 TIME AS (c3) VIRTUAL, + v4 CHAR(10) AS (c4) VIRTUAL +) ENGINE=InnoDB; +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); +SET foreign_key_checks=0; +--error ER_FK_NO_INDEX_CHILD +ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); +ALTER TABLE t1 ADD INDEX(v4); +ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); +SET foreign_key_checks=1; +SHOW CREATE TABLE t1; +ALTER TABLE t1 DROP FOREIGN KEY fk; +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); +SHOW CREATE TABLE t1; +# Cleanup +DROP TABLE t1; + +--echo # +--echo # MDEV-20396 Server crashes after DELETE with SEL NULL Foreign key and a +--echo # virtual column in index +--echo # +CREATE TABLE parent +( + ID int unsigned NOT NULL, + PRIMARY KEY (ID) +); + +CREATE TABLE child +( + ID int unsigned NOT NULL, + ParentID int unsigned NULL, + Value int unsigned NOT NULL DEFAULT 0, + Flag int unsigned AS (Value) VIRTUAL, + PRIMARY KEY (ID), + KEY (ParentID, Flag), + FOREIGN KEY (ParentID) REFERENCES parent (ID) ON DELETE SET NULL + ON UPDATE CASCADE +); + +INSERT INTO parent (ID) VALUES (100); +INSERT INTO child (ID,ParentID,Value) VALUES (123123,100,1); +DELETE FROM parent WHERE ID=100; +select * from child; +INSERT INTO parent (ID) VALUES (100); +UPDATE child SET ParentID=100 WHERE ID=123123; + +# Cleanup +DROP TABLE child, parent; +--echo # +--echo # MDEV-23387 dict_load_foreign() fails to load the table during alter +--echo # +SET FOREIGN_KEY_CHECKS=0; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, + f3 INT AS (f1) VIRTUAL, + INDEX(f1), INDEX(f2))ENGINE=InnoDB; +ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f2) REFERENCES t1(f1), LOCK=NONE; +SHOW CREATE TABLE t1; +ALTER TABLE t1 DROP INDEX f1; +ALTER TABLE t1 DROP f3; +DROP TABLE t1; + +--echo # +--echo # MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB +--echo # +SET FOREIGN_KEY_CHECKS=1; +CREATE DATABASE `a-b`; +USE `a-b`; +CREATE TABLE emails ( + id int, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +CREATE TABLE email_stats ( + id int, + email_id int, + date_sent char(4), + generated_email_id int as (email_id), + #generated_sent_date DATE GENERATED ALWAYS AS (date_sent), + PRIMARY KEY (id), + KEY mautic_generated_sent_date_email_id (generated_email_id), + FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL + ON UPDATE CASCADE +) ENGINE=InnoDB; + + +CREATE TABLE emails_metadata ( + email_id int, + PRIMARY KEY (email_id), + CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB; + + +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) VALUES (1,1,'Jan'); +INSERT INTO emails_metadata VALUES (1); + +UPDATE emails SET id=2; +DELETE FROM emails; + +DROP TABLE email_stats; +DROP TABLE emails_metadata; +DROP TABLE emails; +DROP DATABASE `a-b`; +USE test; + +--echo # +--echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE +--echo # + +--echo # Test-Case 1 +CREATE TABLE emails ( + id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +CREATE TABLE email_stats ( + id bigint unsigned NOT NULL AUTO_INCREMENT, + email_id int unsigned DEFAULT NULL, + date_sent datetime NOT NULL, + generated_sent_date date GENERATED ALWAYS AS + (concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'), + '-', lpad(dayofmonth(date_sent), 2, '0'))), + PRIMARY KEY (id), + KEY IDX_ES1 (email_id), + KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id), + FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL +) ENGINE = InnoDB; + +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) + VALUES (1, 1, '2020-10-22 13:32:41'); +SELECT * FROM email_stats; + +DELETE FROM emails; +DELETE FROM email_stats; + +--echo # Clean up. +DROP TABLE email_stats; +DROP TABLE emails; + +--echo # Test-Case 2 +CREATE TABLE emails ( + id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE = InnoDB + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_unicode_ci + ROW_FORMAT = DYNAMIC; + +CREATE TABLE email_stats ( + id bigint unsigned NOT NULL AUTO_INCREMENT, + email_id int unsigned DEFAULT NULL, + date_sent datetime NOT NULL, + generated_sent_date date GENERATED ALWAYS AS + (concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'), + '-', lpad(dayofmonth(date_sent), 2, '0'))), + PRIMARY KEY (id), + KEY IDX_ES1 (email_id), + KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id), + FOREIGN KEY (email_id) REFERENCES emails (id) + ON DELETE SET NULL + ON UPDATE SET NULL +) ENGINE = InnoDB; + +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) + VALUES (1, 1, '2020-10-22 13:32:41'); + +UPDATE emails SET id = 2 where id = 1; + +SELECT id FROM email_stats WHERE generated_sent_date IS NULL; +SELECT * FROM email_stats; +UPDATE email_stats + SET email_id=2 + WHERE DATE(generated_sent_date) = '2020-10-22'; +SELECT * FROM email_stats; + +--echo # Clean up. +DROP TABLE email_stats; +DROP TABLE emails; + +--echo # Test-case 3 +CREATE TABLE emails ( + id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE = INNODB + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_unicode_ci + ROW_FORMAT = DYNAMIC; +CREATE TABLE email_stats ( + id bigint unsigned NOT NULL AUTO_INCREMENT, + email_id int unsigned DEFAULT NULL, + date_sent datetime NOT NULL, + generated_sent_email varchar(20) GENERATED ALWAYS AS + (CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))), + PRIMARY KEY (id), + KEY idx_es1 (email_id), + KEY mautic_generated_sent_date_email(generated_sent_email, email_id), + FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL +) ENGINE = INNODB; + +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) + VALUES (1, 1, '2020-10-22 13:32:41'); +SELECT * FROM email_stats; +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1'; + +DELETE FROM emails; + +SELECT * FROM email_stats; +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$'; + +--echo # Clean up. +DROP TABLE email_stats; +DROP TABLE emails; + +--echo # Test-case 4 +CREATE TABLE emails ( + id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE = INNODB; + +CREATE TABLE email_stats ( + id bigint unsigned NOT NULL AUTO_INCREMENT, + email_id int unsigned DEFAULT NULL, + date_sent datetime NOT NULL, + generated_sent_email varchar(20) GENERATED ALWAYS AS + (CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))), + PRIMARY KEY (id), + KEY idx_es1 (email_id), + KEY mautic_generated_sent_date_email(generated_sent_email, email_id), + FOREIGN KEY (email_id) REFERENCES emails (id) ON UPDATE SET NULL +) ENGINE = INNODB; + +INSERT INTO emails VALUES (1); +INSERT INTO email_stats (id, email_id, date_sent) + VALUES (1, 1, '2020-10-22 13:32:41'); +SELECT * FROM email_stats; +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1'; + +UPDATE emails SET id = 2 WHERE id = 1; + +SELECT * FROM email_stats; +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$'; + +#clean up. +DROP TABLE email_stats; +DROP TABLE emails; + +CREATE TABLE emails (breaker int unsigned, + KEY (breaker), + id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE email_stats ( + id bigint unsigned NOT NULL AUTO_INCREMENT, + email_id int unsigned DEFAULT NULL, + date_sent datetime NOT NULL, + generated_sent_email varchar(20) GENERATED ALWAYS AS + (CONCAT(YEAR(date_sent), + '-', + COALESCE(email_id, '$'))), + PRIMARY KEY (id), + KEY idx_es1 (email_id), + KEY mautic_generated_sent_date_email (generated_sent_email, email_id), + FOREIGN KEY fk_ea1 (email_id) REFERENCES emails (breaker) + ON DELETE SET NULL +) ENGINE=INNODB; + +show create table email_stats; +INSERT INTO emails VALUES (1,1); +INSERT INTO email_stats(id, email_id, date_sent) + VALUES (1, 1, '2020-10-22 13:32:41'); +SELECT * FROM email_stats; +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1'; +DELETE FROM emails; +SELECT * FROM email_stats; +SELECT date_sent + FROM email_stats force index (mautic_generated_sent_date_email) + WHERE generated_sent_email = '2020-$'; +SELECT date_sent + FROM email_stats force index (idx_es1) + WHERE generated_sent_email = '2020-$'; + +DROP TABLE email_stats; +DROP TABLE emails; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk_restart.test b/mysql-test/suite/gcol/t/innodb_virtual_fk_restart.test new file mode 100644 index 00000000..45d1c129 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_fk_restart.test @@ -0,0 +1,51 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc + +--echo # +--echo # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED +--echo # WHEN A VIRTUAL INDEX EXISTS. + +--echo # Add the VIRTUAL INDEX contains fk constraINT column +--echo # using INPLACE alter operatiON +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY) engine=innodb; +CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL, + fld3 INT AS (fld2) VIRTUAL, KEY(fld1), + FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE) engine=innodb; +CREATE TABLE u1(a INT, KEY(a)) ENGINE=InnoDB; +CREATE TABLE u2(b INT, vb INT GENERATED ALWAYS AS(b) VIRTUAL, KEY(vb), + FOREIGN KEY(b) REFERENCES u1(a)ON DELETE CASCADE)ENGINE=InnoDB; + +INSERT INTO u1 SET a=1; +INSERT INTO u2 SET b=1; +INSERT INTO t1(fld1) VALUES(1); +INSERT INTO t2(fld1, fld2) VALUES(1, 2); +--source include/restart_mysqld.inc +UPDATE t1 SET fld1= 2; +DELETE FROM u1; +SELECT * FROM u2; +DROP TABLE u2,u1; +SELECT fld3, fld1 FROM t2; +alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM=INPLACE; +UPDATE t1 SET fld1=3; +SELECT fld3, fld1 FROM t2; +DROP TABLE t2, t1; + +--echo # TEMPORARY TABLE NAME and CHILD TABLE NAME are same +CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY) engine=innodb; +CREATE TABLE t2(fld1 INT NOT NULL, + fld2 INT AS (fld1) VIRTUAL, + KEY(fld2), + FOREIGN KEY(fld1) REFERENCES t1(fld1) + ON UPDATE CASCADE) engine=innodb; +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1, DEFAULT), (2, default); +--source include/restart_mysqld.inc +CREATE TEMPORARY TABLE t2 (fld1 INT NOT NULL)ENGINE=INNODB; +UPDATE t1 SET fld1= 3 WHERE fld1= 2; +--connect(con1,localhost,root,,test) +SELECT fld2 FROM t2; +CHECK TABLE t2; +connection default; +disconnect con1; +DROP TABLE t2; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.opt b/mysql-test/suite/gcol/t/innodb_virtual_index.opt new file mode 100644 index 00000000..c3f4a891 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_index.opt @@ -0,0 +1 @@ +--innodb_sort_buffer_size=64k diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.test b/mysql-test/suite/gcol/t/innodb_virtual_index.test new file mode 100644 index 00000000..83d9a6bd --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_index.test @@ -0,0 +1,386 @@ +--source include/have_innodb.inc +--source include/have_sequence.inc + +SET default_storage_engine= innodb; + +--echo # +--echo # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING +--echo # THE NUMBER OF VIRTUAL COLUMNS +--echo # + +CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT +GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4 +INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL, +a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a) +VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS +(a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ; + +INSERT INTO t1(a) VALUES(10); + +SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND +a6=10 AND a7=10 AND a8=10 AND a9=10; + +DROP TABLE t1; + +--echo # +--echo # Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS( +--echo # OFFSETS) +--echo # +SET @@SESSION.sql_mode=0; + +CREATE TABLE t1( +c1 int(1)AUTO_INCREMENT, + c2 int(1), + c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL, + c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL, + c5 date, + c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL, + c7 DATE, + c8 time, + c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL, + c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL, + c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL, + c12 CHAR(1), + c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL, + c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL, + PRIMARY KEY(c1), + KEY c4_6(c4,c11) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE t2( + c1 int(1)AUTO_INCREMENT, + c2 int(1), + c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL, + c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL, + c5 date, + c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL, + c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL, + c7 DATE, + c8 time, + c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL, + c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL, + c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL, + c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL, + c12 CHAR(1), + c13 CHAR(2)GENERATED ALWAYS AS (concat(RTRIM(c12),RTRIM(c12))) VIRTUAL, + c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL, + PRIMARY KEY(c1), + KEY c13(c13), + KEY c4_6(c4,c11) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v'); + +CREATE TABLE t3( + c1 int(1)AUTO_INCREMENT, + c2 int(1), + c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL, + c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL, + c5 date, + c7 DATE, + c8 time, + c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL, + c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL, + c12 CHAR(1), + PRIMARY KEY(c1), + KEY c4_6(c4,c11) +)ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES +(0,0,0,0,0,'q'),(0,0,0,0,0,'g'),(0,0,0,0,0,'l'),(0,0,0,0,0,1),(0,0,0,0,0,'v'), +(0,1,0,0,0,'c'),(0,0,0,0,0,'x'); + +UPDATE +t2 AS O1,t3 AS O2 +SET O1.c12=1 +WHERE O1.c14 NOT IN +( +SELECT +DISTINCT I1.c14 AS y +FROM t1 AS I1 +ORDER BY I1.c14); + +SET @@SESSION.sql_mode=default; +--source ../../innodb/include/wait_all_purged.inc + +DROP TABLE t1, t2, t3; + +--echo # +--echo # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER +--echo # +CREATE TABLE `ibstd_08` ( + `nc00577` tinyint(4) DEFAULT NULL, + `nc07844` varchar(41) DEFAULT NULL, + `gc01908` point NOT NULL, + `nc04156` char(17) DEFAULT NULL, + `nc09536` longblob NOT NULL, + `nc09231` decimal(10,0) NOT NULL, + `a` int(11) NOT NULL, + `b` varchar(198) NOT NULL, + `nc04560` mediumtext, + `c` char(187) DEFAULT NULL, + `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL, + `gc00881` polygon NOT NULL, + `nc05121` int(11) NOT NULL DEFAULT '85941481', + KEY `a` (`a`), + KEY `b` (`b`(3),`a`), + KEY `c` (`c`(99),`b`(25)), + KEY `b_2` (`b`(5),`c`(10),`a`), + KEY `vbidxcol` (`vbidxcol`), + KEY `a_2` (`a`,`vbidxcol`), + KEY `vbidxcol_2` (`vbidxcol`), + FULLTEXT KEY `ftsic` (`c`,`b`) +) ENGINE=InnoDB; + + +ALTER TABLE ibstd_08 ADD COLUMN nc07006 BIGINT AUTO_INCREMENT NOT NULL , ADD KEY auto_nc07006(nc07006); + +DROP TABLE ibstd_08; + +--echo # +--echo # Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256) +--echo # AND SEGFAULT +--echo # +set sql_mode=""; +create table t (a int) engine=innodb; +create table s ( +b int generated always as (1) virtual, +c int, +d int generated always as (1) virtual, +key (d) +) engine=innodb; + +insert into t(a) values ((select d from s for update)); +insert into s(c) values (''); + +SET sql_mode = default; +drop table if exists t,s; + +--echo # +--echo # Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V +--echo # +CREATE TABLE t1 ( + col1 int(11) NOT NULL, + col2 int(11) DEFAULT NULL, + col3 int(11) NOT NULL, + col4 int(11) DEFAULT NULL, + col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL, + col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL, + col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL, + col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL, + col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL, + col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL, + col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL, + col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL, + col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL, + col9 text, + col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL, + col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL, + col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL, + col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL, + extra int(11) DEFAULT NULL, + col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL, + col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL, + col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL, + col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL, + col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL, + col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL, + col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL, + col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL, + UNIQUE KEY idx7 (col1,col3,col2), + UNIQUE KEY uidx (col9(10)), + KEY idx15 (col9(10) DESC,col2 DESC), + KEY idx10 (col9(10) DESC,col1 DESC), + KEY idx11 (col6x DESC), + KEY idx6 (col9(10) DESC,col7 DESC), + KEY idx14 (col6 DESC) + ) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +# Cannot add virtual column along with create FULLTEXT index with +# adding a hidden FTS_DOC_ID column (which require a table rebuild) +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x) +VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace; + +# This will add a hidden FTS_DOC_ID column +CREATE FULLTEXT INDEX idx ON t1(col9); + +# Since there is no table rebuild needed, now the alter would be sucessful +ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x) +VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace; + +DROP TABLE t1; + +CREATE TABLE t1 ( + col1 int(11) NOT NULL, + col2 int(11) DEFAULT NULL, + col3 int(11) NOT NULL, + col4 int(11) DEFAULT NULL) engine=innodb; + +# This secondary key idx will be coverted to a new Primary Key, thus a table +# rebuild. It is blocked since there is an adding of virtual columns +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2) +VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace; + +DROP TABLE t1; + +--echo # +--echo # Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7 +--echo # +CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Bug #27968952 INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES +--echo # + +CREATE TABLE t1( + a INT NOT NULL UNIQUE, + b INT NOT NULL, + c TEXT GENERATED ALWAYS AS (a <> b) VIRTUAL, + d TEXT NOT NULL, + UNIQUE KEY (c(1)), KEY(d(1)) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SET @t = REPEAT('t',@@innodb_page_size); +INSERT INTO t1 (a,b,d) VALUES (1,0,@t), (0,0,@t); +--error ER_DUP_ENTRY +UPDATE t1 SET b = a; +REPLACE INTO t1 SET a = 0, b = 1, d = 'd'; +SELECT * FROM t1; +CHECK TABLE t1; +DROP TABLE t1; + +--disable_query_log +SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; +SET GLOBAL innodb_read_only_compressed=OFF; +--enable_query_log +CREATE TABLE t1( + a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL, + b VARCHAR(1000) NOT NULL, + c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED, + KEY (b(1)), + KEY (a(1)) +) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; +INSERT INTO t1(b) VALUES(REPEAT('b',1000)); +DELETE FROM t1; +--disable_query_log +SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; +--enable_query_log +DROP TABLE t1; + +--echo # +--echo # Bug #22990029 GCOLS: INCORRECT BEHAVIOR +--echo # AFTER DATA INSERTED WITH IGNORE KEYWORD +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT, vb DATE AS(b) VIRTUAL, KEY(vb)) +ENGINE=InnoDB; +INSERT IGNORE INTO t1 (a,b) VALUES(1,20190132); +SELECT * FROM t1; +BEGIN; +DELETE FROM t1; +INSERT INTO t1 (a,b) VALUES(1,20190123); +SELECT * FROM t1; +ROLLBACK; +SELECT * FROM t1; +CHECK TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-25872 InnoDB: Assertion failure in row_merge_read_clustered_index +--echo # upon ALTER on table with indexed virtual columns +--echo # + +CREATE TABLE t1 ( + id BIGINT AUTO_INCREMENT PRIMARY KEY, + a INT, + va INT ZEROFILL AS (a) VIRTUAL, + b TIMESTAMP, + c CHAR(204), + vc CHAR(8), + KEY(vc,c(64),b,va) +) ENGINE=InnoDB CHARACTER SET utf32; +INSERT INTO t1 (id) SELECT NULL FROM seq_1_to_75; +INSERT IGNORE INTO t1 (id, a) VALUES (NULL, -1); +--error ER_WARN_DATA_OUT_OF_RANGE +ALTER TABLE t1 FORCE; +DROP TABLE t1; + +--echo # +--echo # MDEV-24713 Assertion `dict_table_is_comp(index->table)' failed +--echo # in row_merge_buf_add() +--echo # +CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(3), + b CHAR(8) AS (a) VIRTUAL, KEY(b)) + ROW_FORMAT=REDUNDANT ENGINE=InnoDB + CHARACTER SET utf8; +INSERT INTO t1 (id,a) VALUES (1,'foo'); +OPTIMIZE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20154 Assertion `len <= col->len || ((col->mtype) == 5 +--echo # || (col->mtype) == 14)' failed in row_merge_buf_add +--echo # + +CREATE TABLE t1 ( + a VARCHAR(2500), + b VARCHAR(2499) AS (a) VIRTUAL +) ENGINE=InnoDB; +INSERT INTO t1 (a) VALUES ('foo'); + +ALTER TABLE t1 MODIFY a VARCHAR(2600), ALGORITHM=INPLACE; +ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE; + +--echo # Cleanup +DROP TABLE t1; + +--echo # End of 10.2 tests + +--echo # +--echo # MDEV-29299 SELECT from table with vcol index reports warning +--echo # + +CREATE TABLE t(fld1 INT NOT NULL, + fld2 INT AS (100/fld1) VIRTUAL, + KEY(fld1), KEY(fld2)); +CREATE TABLE t_odd(id int); +INSERT INTO t(fld1) VALUES(1), (2); + +--connect stop_purge,localhost,root +# This prevents purge for records in t +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO t_odd VALUES(10000); + +--connection default +UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2; +UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3; +UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4; +SELECT fld2 FROM t FORCE INDEX(fld2); +SELECT fld2 FROM t FORCE INDEX(fld1); + +--disconnect stop_purge +DROP TABLE t, t_odd; + +--echo # +--echo # MDEV-29753 An error is wrongly reported during INSERT with vcol index +--echo # See also Bug #22990029 +--echo # + +CREATE TABLE t(pk INT PRIMARY KEY, + fld1 INT NOT NULL, + fld2 INT AS (100/fld1) VIRTUAL, + KEY(fld1), KEY(fld2)); +INSERT IGNORE t(pk, fld1) VALUES(1, 0); +SELECT * FROM t; +BEGIN; +DELETE FROM t; +INSERT INTO t (pk, fld1) VALUES(1,1); +SELECT * FROM t; + +--echo # Cleanup +ROLLBACK; +DROP TABLE t; + +--echo # End of 10.3 tests diff --git a/mysql-test/suite/gcol/t/innodb_virtual_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_purge.test new file mode 100644 index 00000000..99c7267c --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_purge.test @@ -0,0 +1,185 @@ +--source include/have_innodb.inc +--source include/count_sessions.inc + +--echo # +--echo # Bug#21869656 UNDO LOG DOES NOT CONTAIN ENOUGH INFORMATION +--echo # ON INDEXED VIRTUAL COLUMNS +--echo # + +CREATE TABLE t1 (a INT, b INT, + a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1) +) ENGINE=InnoDB; + +INSERT INTO t1 (a,b) VALUES(1,1); + +connect (con1,localhost,root,,); +# disable purge +CREATE TABLE t0 (a INT) ENGINE=InnoDB; +BEGIN; SELECT * FROM t0; + +connection default; +# write the problematic update_undo log record +UPDATE t1 SET a=0; + +ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE; +ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD +INDEX(b1), +ALGORITHM=INPLACE; + +connection con1; +# enable purge +COMMIT; +UPDATE t1 SET a=1; + +connection default; +--source ../../innodb/include/wait_all_purged.inc + +CHECK TABLE t1 EXTENDED; +SELECT b1 FROM t1; + + +# Create multi-virtual column, more ADD/DROP to test it +ALTER TABLE t1 +ADD COLUMN a1 INT GENERATED ALWAYS AS (a) VIRTUAL, +ADD COLUMN a2 INT GENERATED ALWAYS AS (a + b) VIRTUAL, +ADD COLUMN a3 INT GENERATED ALWAYS AS (a - b) VIRTUAL, +ADD COLUMN a4 INT GENERATED ALWAYS AS (a - b) VIRTUAL, +ADD INDEX(a1), ADD INDEX(a2), ADD INDEX(a3), ALGORITHM=INPLACE; + +CREATE TABLE t2 ( + a BLOB, + b BLOB, + c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, + h VARCHAR(10) DEFAULT NULL +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, 'kk'); + +INSERT INTO t2 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm'); + +CREATE INDEX idx ON t2(c(100)); + +INSERT INTO t1 (a, b) VALUES(1,1); + +connection con1; +# disable purge +BEGIN; SELECT * FROM t0; + +connection default; +--enable_info + +# write the problematic update_undo log record +UPDATE t1 SET a=0; +UPDATE t1 SET b=0; + +ALTER TABLE t1 DROP COLUMN a3, ALGORITHM=INPLACE; + +UPDATE t1 SET a=2; +ALTER TABLE t1 DROP COLUMN a2, ALGORITHM=INPLACE; +UPDATE t1 SET b=3; + +ALTER TABLE t1 ADD COLUMN b2 INT GENERATED ALWAYS AS (b) VIRTUAL, +ADD INDEX(b2), ALGORITHM=INPLACE; +UPDATE t1 SET b=9; + +ALTER TABLE t1 ADD COLUMN b3 INT GENERATED ALWAYS AS (a) VIRTUAL, +ADD INDEX(b3), ALGORITHM=INPLACE; + +UPDATE t1 SET b=10; + +ALTER TABLE t2 DROP COLUMN c; + +UPDATE t2 SET a = REPEAT('s', 6000) WHERE a like 'aaa%'; + +ALTER TABLE t2 ADD COLUMN x1 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, +ADD COLUMN x2 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, +ADD INDEX(x1(100), x2(120)), ADD INDEX (x1(20)); + +UPDATE t1 SET a=5; + +UPDATE t2 SET a = REPEAT('m', 16000) WHERE a like 'sss%'; + +ALTER TABLE t1 DROP COLUMN b2, ALGORITHM=INPLACE; + +UPDATE t1 SET a=6; + +ALTER TABLE t2 DROP COLUMN x1, DROP COLUMN x2, ALGORITHM=INPLACE; + +UPDATE t2 SET a = REPEAT('x', 1000) WHERE a like 'mmm%'; + +ALTER TABLE t1 DROP INDEX b3; +UPDATE t1 SET a=100; +--disable_info + +connection con1; +# enable purge +COMMIT; +disconnect con1; + +connection default; +--source ../../innodb/include/wait_all_purged.inc + +CHECK TABLE t1 EXTENDED; +SELECT b1 FROM t1; + +SELECT * FROM t1; +CHECK TABLE t2 EXTENDED; +DROP TABLE t2, t1, t0; + +CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VIRTUAL); + +--error ER_DUP_FIELDNAME +CREATE INDEX idx ON t1(a2(10), b, a2(20)); + +DROP TABLE t1; + +--echo # +--echo # MDEV-17540 Server crashes in row_purge after TRUNCATE TABLE +--echo # + +# Note: this test case is nondeterministic and should depend on +# MDEV-12288 to trigger the needed purge activity. +# The test does not seem to repeat the bug on MariaDB 10.2. + +CREATE TABLE t1 (a BIT(14)) ENGINE=InnoDB; +INSERT INTO t1 VALUES + (b'01110110101011'),(b'01100111111000'),(b'00001011110100'), + (b'01110110111010'),(b'10001010101011'),(b'01100111001111'); + +CREATE TABLE t2 ( + pk INT DEFAULT 1, + b YEAR, + c BIT(14), + d YEAR AS (b), + e BIT(14) AS (c), + UNIQUE(pk), + KEY(e) +) ENGINE=InnoDB; + +# Run a few times in order to improve the chances of triggering the bug. +--disable_query_log +let $n=10; +while ($n) { +REPLACE INTO t2 (c) SELECT a FROM t1; +TRUNCATE TABLE t2; +dec $n; +} +--enable_query_log + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-30024 InnoDB: tried to purge non-delete-marked record +--echo # of an index on a virtual column prefix +--echo # + +CREATE TABLE t(a BINARY(8), b CHAR(8) AS (a) VIRTUAL, KEY(b(4))) +CHARACTER SET utf8 ENGINE=InnoDB; +INSERT INTO t (a) VALUES (''),(''); +UPDATE t SET a = 'x'; +UPDATE t SET a = ''; +SET GLOBAL innodb_max_purge_lag_wait=0; +CHECK TABLE t EXTENDED; +DROP TABLE t; + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test new file mode 100644 index 00000000..fe4f5e30 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test @@ -0,0 +1,51 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1 (j SERIAL, i INT, v INT AS (i) VIRTUAL) ENGINE=InnoDB; +CREATE TABLE t2 (j SERIAL, i INT, v INT AS (i) VIRTUAL) ENGINE=InnoDB +ROW_FORMAT=REDUNDANT; +CREATE TABLE t3 (i INT, v INT AS (i) VIRTUAL) ENGINE=InnoDB; +CREATE TABLE t4 (i INT, v INT AS (i) VIRTUAL) ENGINE=InnoDB +ROW_FORMAT=REDUNDANT; + +let $n=4; +while ($n) +{ +eval INSERT INTO t$n SET i=1; +eval ALTER TABLE t$n ADD INDEX(v), LOCK=NONE; +# MDEV-17468 FIXME: Fix this, and remove the 2 --error below. +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +eval ALTER TABLE t$n ADD COLUMN k INT, LOCK=NONE; +--error ER_CANT_DROP_FIELD_OR_KEY +eval ALTER TABLE t$n DROP k, LOCK=NONE; +eval ALTER TABLE t$n DROP INDEX v, LOCK=NONE; +dec $n; +} + +connect (ddl,localhost,root,,test); +connection default; + +let $n=4; +while ($n) +{ +connection ddl; +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL copied WAIT_FOR dml'; +send_eval ALTER TABLE t$n FORCE; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR copied'; +BEGIN; +eval UPDATE t$n SET i = 0; +ROLLBACK; +SET DEBUG_SYNC = 'now SIGNAL dml'; + +connection ddl; +reap; +connection default; +eval SELECT * FROM t$n; +eval DROP TABLE t$n; +dec $n; +} +disconnect ddl; +SET DEBUG_SYNC = 'RESET'; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_stats.test b/mysql-test/suite/gcol/t/innodb_virtual_stats.test new file mode 100644 index 00000000..69c67af8 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_virtual_stats.test @@ -0,0 +1,56 @@ +--source include/have_innodb.inc + +# +# BUG#22469660 INNODB DOESN'T UPDATE INDEX STATS WHEN ADDING OR DROPPING VIRTUAL COLUMN +# + +CREATE TABLE t ( + a INT, + b INT, + c INT GENERATED ALWAYS AS(a+b), + d INT GENERATED ALWAYS AS(a+b+b), + KEY idxa (a), + KEY vidxcd (c, d) +) ENGINE=INNODB STATS_PERSISTENT=1 STATS_AUTO_RECALC=1; + +INSERT INTO t (a,b) VALUES (1, 2); + +SELECT index_name, stat_name, stat_description +FROM mysql.innodb_index_stats +WHERE database_name = 'test' AND table_name = 't'; + +ALTER TABLE t ADD COLUMN e INT GENERATED ALWAYS AS(a+a+b), ADD INDEX idxb (b), ALGORITHM=INPLACE; +select count(*) from t; + +SELECT index_name, stat_name, stat_description +FROM mysql.innodb_index_stats +WHERE database_name = 'test' AND table_name = 't'; + +ALTER TABLE t DROP COLUMN c, DROP INDEX idxa, ALGORITHM=INPLACE; +select count(*) from t; + +SELECT index_name, stat_name, stat_description +FROM mysql.innodb_index_stats +WHERE database_name = 'test' AND table_name = 't'; + +ALTER TABLE t ADD INDEX vidxe (e), ALGORITHM=INPLACE; +select count(*) from t; + +SELECT index_name, stat_name, stat_description +FROM mysql.innodb_index_stats +WHERE database_name = 'test' AND table_name = 't'; + +ALTER TABLE t ADD COLUMN f INT GENERATED ALWAYS AS(a + a), ADD INDEX vidxf (f), ALGORITHM=INPLACE; +select count(*) from t; + +SELECT index_name, stat_name, stat_description +FROM mysql.innodb_index_stats +WHERE database_name = 'test' AND table_name = 't'; + +ALTER TABLE t DROP INDEX vidxcd; + +SELECT index_name, stat_name, stat_description +FROM mysql.innodb_index_stats +WHERE database_name = 'test' AND table_name = 't'; + +DROP TABLE t; diff --git a/mysql-test/suite/gcol/t/innodb_wl8114.test b/mysql-test/suite/gcol/t/innodb_wl8114.test new file mode 100644 index 00000000..bed83753 --- /dev/null +++ b/mysql-test/suite/gcol/t/innodb_wl8114.test @@ -0,0 +1,42 @@ +--source include/have_innodb.inc + +# Test alter table add column +CREATE TABLE t_8114 (a int) ENGINE = INNODB; + +ALTER TABLE t_8114 ADD b INT GENERATED ALWAYS AS (a) VIRTUAL; + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%t_8114"; + +SELECT NAME, POS, MTYPE, PRTYPE, LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%t_8114"); + +INSERT INTO t_8114 VALUES (9, default); +INSERT INTO t_8114 VALUES (3, default); +INSERT INTO t_8114 VALUES (1, default); +INSERT INTO t_8114 VALUES (5, default); + +SELECT * FROM t_8114; + +DROP TABLE t_8114; + +CREATE TABLE t_8114 (Column_1 CHAR(5) GENERATED ALWAYS AS (PI()+5), Column_2 CHAR(5)) engine=innodb; + +SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%t_8114"; + +SELECT NAME, POS, MTYPE, PRTYPE, LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%t_8114"); + +INSERT INTO t_8114 VALUES (default, "aa"); +INSERT INTO t_8114 VALUES (default, "bb"); +INSERT INTO t_8114 VALUES (default, "ee"); +INSERT INTO t_8114 VALUES (default, "pp"); + +SELECT * FROM t_8114; + +ALTER TABLE t_8114 DROP Column_1; + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%t_8114"; + +SELECT NAME, POS, MTYPE, PRTYPE, LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE "%t_8114"); + +SELECT * FROM t_8114; + +DROP TABLE t_8114; diff --git a/mysql-test/suite/gcol/t/main_alter_table.test b/mysql-test/suite/gcol/t/main_alter_table.test new file mode 100644 index 00000000..2ce768a9 --- /dev/null +++ b/mysql-test/suite/gcol/t/main_alter_table.test @@ -0,0 +1,50 @@ +--source include/have_innodb.inc + +--echo # +--echo # Bug#22017616: ASSERTION FAILED: TABLE_SHARE->IS_MISSING_PRIMARY_KEY() +--echo # == M_PREBUILT->CLUST_IND +--echo # +--echo # Ensure that adding indexes with virtual columns are not promoted to +--echo # primary keys +--echo # +--echo # Base line with normal column - should be promoted +CREATE TABLE t0(a INT NOT NULL) ENGINE=INNODB; +ALTER TABLE t0 ADD UNIQUE INDEX (a); + +--echo # Case a: Create table with virtual unique not null column +CREATE TABLE t1(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL UNIQUE) ENGINE=INNODB; +SELECT * FROM t1; + +--echo # Case b: Create table with index on virtual point column +CREATE TABLE t2(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL, UNIQUE INDEX no_pk(a(1))) ENGINE=INNODB; +SELECT * FROM t2; + +--echo # Case c: Add unique index on virtual point column +CREATE TABLE t3(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL) +ENGINE=INNODB; +ALTER TABLE t3 ADD UNIQUE INDEX (a(1)); +SELECT * FROM t3; + +--echo # Case d: Add unique index on virtual blob column +CREATE TABLE t4 (a BLOB, b BLOB GENERATED ALWAYS AS (a) VIRTUAL) ENGINE=INNODB; +ALTER TABLE t4 ADD UNIQUE INDEX (b(1)); +SELECT * FROM t4; + +--echo # Query I_S to verify that 'a' is promoted to pk only when it +--echo # isn't virtual +SELECT T.NAME AS TABLE_NAME, I.NAME AS INDEX_NAME, + CASE (I.TYPE & 3) + WHEN 3 THEN "yes" + ELSE "no" END AS IS_PRIMARY_KEY, + F.NAME AS FIELD_NAME, F.POS AS FIELD_POS FROM + INFORMATION_SCHEMA.INNODB_SYS_TABLES AS T JOIN + INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS I JOIN + INFORMATION_SCHEMA.INNODB_SYS_FIELDS AS F + ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID + WHERE T.NAME LIKE 'test/t%'; + +DROP TABLE t0; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; diff --git a/mysql-test/suite/gcol/t/main_mysqldump.test b/mysql-test/suite/gcol/t/main_mysqldump.test new file mode 100644 index 00000000..c2b4efd0 --- /dev/null +++ b/mysql-test/suite/gcol/t/main_mysqldump.test @@ -0,0 +1,44 @@ +--source include/have_innodb.inc +--source include/not_embedded.inc + +CREATE DATABASE dump_generated; +USE dump_generated; +CREATE TABLE t1 (pk INTEGER, a INTEGER, b INTEGER, c VARCHAR(16), + sum INTEGER GENERATED ALWAYS AS (a+b), + sub VARCHAR(4) GENERATED ALWAYS AS (SUBSTRING(c, 1, 4)), + key k1(sum), + key k2(sub) +) engine=innodb; +INSERT INTO t1(pk, a, b, c) VALUES (1, 11, 12, 'oneone'), (2, 21, 22, 'twotwo'); +SELECT * FROM t1; +--exec $MYSQL_DUMP dump_generated t1 > $MYSQLTEST_VARDIR/tmp/t1.sql +DELETE FROM t1; +--exec $MYSQL dump_generated < $MYSQLTEST_VARDIR/tmp/t1.sql +SELECT * FROM t1; +--exec $MYSQL_DUMP dump_generated t1 --tab=$MYSQLTEST_VARDIR/tmp/ +DELETE FROM t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t1 +SELECT * FROM t1; +DROP TABLE t1; + +--echo # A table with regular columns after generated +CREATE TABLE t2 (pk INTEGER, a INTEGER, b INTEGER, + sum INTEGER GENERATED ALWAYS AS (a+b), + c VARCHAR(16), + key k1(sum) +) engine=innodb; +INSERT INTO t2(pk, a, b, c) VALUES (1, 11, 12, 'oneone'), (2, 21, 22, 'twotwo'); +SELECT * FROM t2; +--exec $MYSQL_DUMP dump_generated t2 > $MYSQLTEST_VARDIR/tmp/t2.sql +DELETE FROM t2; +--exec $MYSQL dump_generated < $MYSQLTEST_VARDIR/tmp/t2.sql +SELECT * FROM t2; +--exec $MYSQL_DUMP dump_generated t2 --tab=$MYSQLTEST_VARDIR/tmp/ +DELETE FROM t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t2.txt' INTO TABLE t2 +SELECT * FROM t2; +DROP TABLE t2; + +DROP DATABASE dump_generated; diff --git a/mysql-test/suite/gcol/t/rpl_gcol.test b/mysql-test/suite/gcol/t/rpl_gcol.test new file mode 100644 index 00000000..6193d944 --- /dev/null +++ b/mysql-test/suite/gcol/t/rpl_gcol.test @@ -0,0 +1,65 @@ +################################################################################ +# t/gcol_rpl.test # +# # +# Purpose: # +# Test replication of tables with generated columns. # +# # +#------------------------------------------------------------------------------# +# Original Author: Andrey Zhakov # +# Original Date: 2008-09-04 # +# Change Author: # +# Change Date: # +# Change: # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +# Set the session storage engine +--source include/have_innodb.inc +SET @@session.default_storage_engine = 'InnoDB'; + +##### Workarounds for known open engine specific bugs +# none + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source include/master-slave.inc + +connection master; +create table t1 (a int, b int generated always as (a+1) virtual); +show create table t1; +insert into t1 values (1,default); +insert into t1 values (2,default); +select * from t1; +save_master_pos; + +connection slave; +sync_with_master; +select * from t1; + +connection master; +drop table t1; +save_master_pos; + +connection slave; +sync_with_master; + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + + +#------------------------------------------------------------------------------# +# Cleanup +--source suite/gcol/inc/gcol_cleanup.inc +--source include/rpl_end.inc diff --git a/mysql-test/suite/gcol/t/virtual_index_drop.test b/mysql-test/suite/gcol/t/virtual_index_drop.test new file mode 100644 index 00000000..0fe4986f --- /dev/null +++ b/mysql-test/suite/gcol/t/virtual_index_drop.test @@ -0,0 +1,74 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--echo # +--echo # MDEV-24971 InnoDB access freed virtual column +--echo # after rollback of secondary index +--echo # + +# Exclusive lock must not defer the index removal + +CREATE TABLE t1(f1 INT, f2 INT AS (f1 + 2) VIRTUAL)ENGINE=InnoDB; +INSERT INTO t1(f1) VALUES(1), (1); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD UNIQUE INDEX(f2), ALGORITHM=INPLACE, LOCK=EXCLUSIVE; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# If Shared lock and table doesn't have any other open handle +# then InnoDB must not defer the index removal + +CREATE TABLE t1(f1 INT, f2 INT AS (f1 + 2) VIRTUAL)ENGINE=InnoDB; +INSERT INTO t1(f1) VALUES(1), (1); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD UNIQUE INDEX(f2), ALGORITHM=INPLACE, LOCK=SHARED; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# InnoDB should store the newly dropped virtual column into +# new_vcol_info in index when rollback of alter happens + +CREATE TABLE t1(f1 INT, f2 INT AS (f1) VIRTUAL)ENGINE=InnoDB; +INSERT INTO t1(f1) VALUES(1); +SET DEBUG_DBUG="+d,create_index_fail"; +SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal"; +SEND ALTER TABLE t1 ADD COLUMN f3 INT AS (f1) VIRTUAL, ADD INDEX(f2, f3); +connect(con1,localhost,root,,,); +SET DEBUG_SYNC="now WAIT_FOR con1_go"; +BEGIN; +SELECT * FROM t1; +SET DEBUG_SYNC="now SIGNAL alter_signal"; +connection default; +--error ER_DUP_ENTRY +reap; +connection con1; +rollback; +connection default; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT, f2 INT AS (f1) VIRTUAL)ENGINE=InnoDB; +INSERT INTO t1(f1) VALUES(1); +SET DEBUG_DBUG="+d,create_index_fail"; +SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal"; +send ALTER TABLE t1 ADD INDEX(f2); +connection con1; +SET DEBUG_SYNC="now WAIT_FOR con1_go"; +BEGIN; +INSERT INTO t1(f1) VALUES(1); +SET DEBUG_SYNC="now SIGNAL alter_signal"; +connection default; +--error ER_DUP_ENTRY +reap; +connection con1; +rollback; +connection default; +disconnect con1; +DROP TABLE t1; + +CREATE TABLE t1(f1 CHAR(100), f2 CHAR(100) as (f1) VIRTUAL)ENGINE=InnoDB; +--error ER_DUP_FIELDNAME +ALTER TABLE t1 ADD COLUMN f3 CHAR(100) AS (f2) VIRTUAL, ADD INDEX(f3(10), f1, f3(12)); +DROP TABLE t1; +SET DEBUG_SYNC=RESET; |