diff options
| author | Alexander Barkov <bar@mariadb.com> | 2023-04-06 14:50:26 +0400 |
|---|---|---|
| committer | Alexander Barkov <bar@mariadb.com> | 2023-04-06 16:17:50 +0400 |
| commit | ed2adc8c6f986f7e9c81d7a99f85cad0e2d46d80 (patch) | |
| tree | 350cfe4eb13e0d44e61c876a0c46c34a00ebc195 | |
| parent | 54715a1074a0c9fc12a5d52152df73826a484df7 (diff) | |
| download | mariadb-git-ed2adc8c6f986f7e9c81d7a99f85cad0e2d46d80.tar.gz | |
MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic
This problem was fixed earlier by MDEV-27653.
Adding MTR tests only.
5 files changed, 213 insertions, 0 deletions
diff --git a/mysql-test/include/sql_mode_pad_char_to_full_length.inc b/mysql-test/include/sql_mode_pad_char_to_full_length.inc new file mode 100644 index 00000000000..df03c4dbc28 --- /dev/null +++ b/mysql-test/include/sql_mode_pad_char_to_full_length.inc @@ -0,0 +1,31 @@ +--echo # +--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +--echo # + +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; + + +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; + + +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; diff --git a/mysql-test/main/sql_mode_pad_char_to_full_length.result b/mysql-test/main/sql_mode_pad_char_to_full_length.result new file mode 100644 index 00000000000..6f68aade613 --- /dev/null +++ b/mysql-test/main/sql_mode_pad_char_to_full_length.result @@ -0,0 +1,94 @@ +# +# Start of 10.4 tests +# +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +SET default_storage_engine=MyISAM; +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(20) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET default_storage_engine=MEMORY; +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(20) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET default_storage_engine=DEFAULT; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/sql_mode_pad_char_to_full_length.test b/mysql-test/main/sql_mode_pad_char_to_full_length.test new file mode 100644 index 00000000000..4d492bc1b70 --- /dev/null +++ b/mysql-test/main/sql_mode_pad_char_to_full_length.test @@ -0,0 +1,19 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +--echo # + +SET default_storage_engine=MyISAM; +--source include/sql_mode_pad_char_to_full_length.inc + +SET default_storage_engine=MEMORY; +--source include/sql_mode_pad_char_to_full_length.inc + +SET default_storage_engine=DEFAULT; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/innodb/r/sql_mode_pad_char_to_full_length.result b/mysql-test/suite/innodb/r/sql_mode_pad_char_to_full_length.result new file mode 100644 index 00000000000..09c1cf57497 --- /dev/null +++ b/mysql-test/suite/innodb/r/sql_mode_pad_char_to_full_length.result @@ -0,0 +1,51 @@ +SET default_storage_engine=InnoDB; +# +# Start of 10.4 tests +# +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(20) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/innodb/t/sql_mode_pad_char_to_full_length.test b/mysql-test/suite/innodb/t/sql_mode_pad_char_to_full_length.test new file mode 100644 index 00000000000..ba286c744d9 --- /dev/null +++ b/mysql-test/suite/innodb/t/sql_mode_pad_char_to_full_length.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc + +SET default_storage_engine=InnoDB; + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +--echo # + +--source include/sql_mode_pad_char_to_full_length.inc + + +--echo # +--echo # End of 10.4 tests +--echo # |