diff options
30 files changed, 1238 insertions, 22 deletions
diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt index 2236d306318..6ed51eff2f4 100644 --- a/libmysqld/CMakeLists.txt +++ b/libmysqld/CMakeLists.txt @@ -107,6 +107,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc ../sql/sql_analyze_stmt.cc ../sql/sql_analyze_stmt.h ../sql/compat56.cc ../sql/sql_type.cc ../sql/sql_type.h + ../sql/sql_mode.cc ../sql/table_cache.cc ../sql/mf_iocache_encr.cc ../sql/item_inetfunc.cc ../sql/wsrep_dummy.cc ../sql/encryption.cc diff --git a/mysql-test/suite/gcol/r/innodb_virtual_basic.result b/mysql-test/suite/gcol/r/innodb_virtual_basic.result index 8ac46c34265..523f89fc197 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_basic.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_basic.result @@ -164,7 +164,7 @@ c connection default; disconnect con1; 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 + x), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, y)), x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); +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); @@ -1237,8 +1237,8 @@ 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(c8,c8)) VIRTUAL, -c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(c8,0)) VIRTUAL, +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)), diff --git a/mysql-test/suite/gcol/r/innodb_virtual_index.result b/mysql-test/suite/gcol/r/innodb_virtual_index.result index fb223f3f152..cd079544ed0 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_index.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_index.result @@ -54,7 +54,7 @@ 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(c12,c12)) VIRTUAL, +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), diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test index 27172dce020..83bb5d067f3 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_basic.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test @@ -150,7 +150,7 @@ 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, y)), x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)); +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); @@ -1238,8 +1238,8 @@ CREATE TABLE t( 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(c8,c8)) VIRTUAL, - c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(c8,0)) VIRTUAL, + 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)), diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.test b/mysql-test/suite/gcol/t/innodb_virtual_index.test index a89ae813b05..c2f9cd78fe2 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_index.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_index.test @@ -63,7 +63,7 @@ CREATE TABLE t2( 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(c12,c12)) VIRTUAL, + 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), diff --git a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc index f19c0f6d63f..8be139471f1 100644 --- a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc +++ b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc @@ -235,7 +235,7 @@ CREATE TABLE t1 ( b CHAR(10) NULL DEFAULT NULL, c blob NULL DEFAULT NULL, blob_a blob GENERATED ALWAYS AS (a) VIRTUAL, - blob_b blob GENERATED ALWAYS AS (b) VIRTUAL, + blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL, blob_c blob GENERATED ALWAYS AS (c) VIRTUAL, key (a), key (blob_a(10)), diff --git a/mysql-test/suite/vcol/r/update.result b/mysql-test/suite/vcol/r/update.result index 2576859b00b..3e8bbc43246 100644 --- a/mysql-test/suite/vcol/r/update.result +++ b/mysql-test/suite/vcol/r/update.result @@ -8,13 +8,13 @@ select * from t1; a b c 2 3 4 drop table t1; -create table t1 (a int, c int as(a), p varchar(20) as(y), y char(20), index (p,c)); +create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL, - `p` varchar(20) GENERATED ALWAYS AS (`y`) VIRTUAL, + `p` varchar(20) GENERATED ALWAYS AS (rtrim(`y`)) VIRTUAL, `y` char(20) DEFAULT NULL, KEY `p` (`p`,`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/suite/vcol/r/update_binlog.result b/mysql-test/suite/vcol/r/update_binlog.result index 9a22005f062..977037535b4 100644 --- a/mysql-test/suite/vcol/r/update_binlog.result +++ b/mysql-test/suite/vcol/r/update_binlog.result @@ -10,13 +10,13 @@ select * from t1; a b c 2 3 4 drop table t1; -create table t1 (a int, c int as(a), p varchar(20) as(y), y char(20), index (p,c)); +create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL, - `p` varchar(20) GENERATED ALWAYS AS (`y`) VIRTUAL, + `p` varchar(20) GENERATED ALWAYS AS (rtrim(`y`)) VIRTUAL, `y` char(20) DEFAULT NULL, KEY `p` (`p`,`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -190,13 +190,13 @@ select * from t1; a b c 2 3 4 drop table t1; -create table t1 (a int, c int as(a), p varchar(20) as(y), y char(20), index (p,c)); +create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL, - `p` varchar(20) GENERATED ALWAYS AS (`y`) VIRTUAL, + `p` varchar(20) GENERATED ALWAYS AS (rtrim(`y`)) VIRTUAL, `y` char(20) DEFAULT NULL, KEY `p` (`p`,`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode.result b/mysql-test/suite/vcol/r/vcol_sql_mode.result new file mode 100644 index 00000000000..50aec76cb8d --- /dev/null +++ b/mysql-test/suite/vcol/r/vcol_sql_mode.result @@ -0,0 +1,428 @@ +# +# Start of 10.2 tests +# +# +# MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH +# +# +# PAD_CHAR_TO_FULL_LENGTH + various virtual column data types +# +CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` char(5) GENERATED ALWAYS AS (`a`) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); +DROP TABLE t1; +CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); +ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100))); +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` text GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL, + KEY `v` (`v`(100)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (trim(trailing ' ' from `a`)) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` text GENERATED ALWAYS AS (trim(trailing ' ' from `a`)) VIRTUAL, + KEY `v` (`v`(100)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (trim(both ' ' from `a`)) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` text GENERATED ALWAYS AS (trim(both ' ' from `a`)) VIRTUAL, + KEY `v` (`v`(100)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (trim(trailing NULL from `a`)) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (trim(both NULL from `a`)) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100))); +ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 ( +a CHAR(5), +v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 ( +a CHAR(5), +v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a) +CREATE TABLE t1 ( +a CHAR(5), +b CHAR(5), +v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100))); +ERROR HY000: Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,5,' ')) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,6,' ')) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,6,NULL)) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,NULL,' ')) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 ( +a CHAR(5), +b CHAR(5), +v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL, +KEY(v) +); +ERROR HY000: Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# PAD_CHAR_TO_FULL_LENGTH + comparison +CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (`a` = 'a') VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 ( +a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin, +v INT AS (a='a') VIRTUAL, KEY(v) +); +ERROR HY000: Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# PAD_CHAR_TO_FULL_LENGTH + LIKE +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (`a` like 'a%') VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (`a` like NULL) VIRTUAL, + KEY `v` (`v`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v)); +ERROR HY000: Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency +CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v)); +ERROR HY000: Function or expression 'length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# +# Testing NO_UNSIGNED_SUBTRACTION +# +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c INT GENERATED ALWAYS AS (a-b) VIRTUAL, +KEY (c) +); +ERROR HY000: Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` +Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL, +KEY (c) +); +ERROR HY000: Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` +Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL, +KEY (c) +); +ERROR HY000: Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` +Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-CAST(b AS SIGNED)) VIRTUAL, +KEY (c) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(10) unsigned DEFAULT NULL, + `b` int(10) unsigned DEFAULT NULL, + `c` int(11) GENERATED ALWAYS AS (cast(`a` as signed) - cast(`b` as signed)) VIRTUAL, + KEY `c` (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c INT GENERATED ALWAYS AS (CAST(a AS DECIMAL(20,0))-CAST(b AS DECIMAL(20,0))) VIRTUAL, +KEY (c) +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(10) unsigned DEFAULT NULL, + `b` int(10) unsigned DEFAULT NULL, + `c` int(11) GENERATED ALWAYS AS (cast(`a` as decimal(20,0)) - cast(`b` as decimal(20,0))) VIRTUAL, + KEY `c` (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION +# +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c CHAR(5), +v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL, +KEY (v) +); +ERROR HY000: Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c CHAR(5), +v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL, +KEY (v) +); +ERROR HY000: Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error +CREATE TABLE t1 ( +a INT UNSIGNED, +b INT UNSIGNED, +c CHAR(5), +v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL +); +ALTER TABLE t1 ADD KEY(v); +ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +DROP TABLE t1; +# A virtual column on the second position in an index - cannot depend on sql_mode +CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100))); +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +# A persisten virtual column cannot depend on sql_mode +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT); +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(5) DEFAULT NULL, + `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) STORED +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result index de5447e49eb..403515482f0 100644 --- a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result @@ -232,7 +232,7 @@ a INTEGER UNSIGNED NULL DEFAULT NULL, b CHAR(10) NULL DEFAULT NULL, c blob NULL DEFAULT NULL, blob_a blob GENERATED ALWAYS AS (a) VIRTUAL, -blob_b blob GENERATED ALWAYS AS (b) VIRTUAL, +blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL, blob_c blob GENERATED ALWAYS AS (c) VIRTUAL, key (a), key (blob_a(10)), diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result index f5c87838ec9..de605bf8722 100644 --- a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result @@ -232,7 +232,7 @@ a INTEGER UNSIGNED NULL DEFAULT NULL, b CHAR(10) NULL DEFAULT NULL, c blob NULL DEFAULT NULL, blob_a blob GENERATED ALWAYS AS (a) VIRTUAL, -blob_b blob GENERATED ALWAYS AS (b) VIRTUAL, +blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL, blob_c blob GENERATED ALWAYS AS (c) VIRTUAL, key (a), key (blob_a(10)), diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test index 53189ee3219..4218f0e3efb 100644 --- a/mysql-test/suite/vcol/t/update.test +++ b/mysql-test/suite/vcol/t/update.test @@ -14,7 +14,7 @@ drop table t1; # one keypart is virtual, the other keypart is updated # this tests TABLE::mark_columns_needed_for_update() # -create table t1 (a int, c int as(a), p varchar(20) as(y), y char(20), index (p,c)); +create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)); show create table t1; insert into t1 (a,y) values(1, "yyy"); update t1 set a = 100 where a = 1; diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode.test b/mysql-test/suite/vcol/t/vcol_sql_mode.test new file mode 100644 index 00000000000..5fb7ec8331d --- /dev/null +++ b/mysql-test/suite/vcol/t/vcol_sql_mode.test @@ -0,0 +1,319 @@ +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH +--echo # + +--echo # +--echo # PAD_CHAR_TO_FULL_LENGTH + various virtual column data types +--echo # + +CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); +DROP TABLE t1; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100))); +SHOW WARNINGS; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100))); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100))); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100))); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100))); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +# more than one space +CREATE TABLE t1 ( + a CHAR(5), + v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +# more than one space +CREATE TABLE t1 ( + a CHAR(5), + v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a) +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a CHAR(5), + b CHAR(5), + v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100))); +SHOW WARNINGS; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v)); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a CHAR(5), + b CHAR(5), + v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL, + KEY(v) +); +SHOW WARNINGS; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + comparison + +CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin, + v INT AS (a='a') VIRTUAL, KEY(v) +); +SHOW WARNINGS; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + LIKE + +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v)); +SHOW WARNINGS; + + +--echo # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v)); +SHOW WARNINGS; + + +--echo # +--echo # Testing NO_UNSIGNED_SUBTRACTION +--echo # + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c INT GENERATED ALWAYS AS (a-b) VIRTUAL, + KEY (c) +); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL, + KEY (c) +); +SHOW WARNINGS; + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL, + KEY (c) +); +SHOW WARNINGS; + +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-CAST(b AS SIGNED)) VIRTUAL, + KEY (c) +); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# 'CAST(signed AS DECIMAL)' does not copy 'unsigned_flag' from the argument. +# So the below is safe. +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c INT GENERATED ALWAYS AS (CAST(a AS DECIMAL(20,0))-CAST(b AS DECIMAL(20,0))) VIRTUAL, + KEY (c) +); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION +--echo # + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c CHAR(5), + v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL, + KEY (v) +); +SHOW WARNINGS; + + +# The below solves the dependency on NO_UNSIGNED_SUBTRACTION +# but does not solve the dependency on PAD_CHAR_TO_FULL_LENGTH, +# because the 'length' argument to RPAD() is not a constant. + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c CHAR(5), + v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL, + KEY (v) +); +SHOW WARNINGS; + + + +--echo # ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error + +# This makes sure that QT_ITEM_IDENT_SKIP_DB_NAMES and +# QT_ITEM_IDENT_SKIP_TABLE_NAMES are passed to print() +# to avoid temporary table names like `test`.`#sql-50a6_4`.`c` +# in the error message. +# + +CREATE TABLE t1 ( + a INT UNSIGNED, + b INT UNSIGNED, + c CHAR(5), + v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL +); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +ALTER TABLE t1 ADD KEY(v); +SHOW WARNINGS; +DROP TABLE t1; + + +--echo # A virtual column on the second position in an index - cannot depend on sql_mode + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100))); +SHOW WARNINGS; + + +--echo # A persisten virtual column cannot depend on sql_mode + +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT); +SHOW WARNINGS; + +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index 769a1b53331..963243c9767 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -135,7 +135,7 @@ SET (SQL_SOURCE my_apc.cc mf_iocache_encr.cc item_jsonfunc.cc my_json_writer.cc rpl_gtid.cc rpl_parallel.cc - sql_type.cc + sql_type.cc sql_mode.cc item_windowfunc.cc sql_window.cc sql_cte.cc ${WSREP_SOURCES} diff --git a/sql/field.cc b/sql/field.cc index d7ac03328a0..e5359a7520e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1428,6 +1428,43 @@ void Field::load_data_set_value(const char *pos, uint length, } +void Field::error_generated_column_function_is_not_allowed(THD *thd) const +{ + StringBuffer<64> tmp; + vcol_info->expr->print(&tmp, (enum_query_type) + (QT_TO_SYSTEM_CHARSET | + QT_ITEM_IDENT_SKIP_DB_NAMES | + QT_ITEM_IDENT_SKIP_TABLE_NAMES)); + my_error(ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(0), + tmp.c_ptr(), vcol_info->get_vcol_type_name(), + const_cast<const char*>(field_name)); +} + + +/* + Check if an indexed or a persistent virtual column depends on sql_mode flags + that it cannot handle. + See sql_mode.h for details. +*/ +bool Field::check_vcol_sql_mode_dependency(THD *thd) const +{ + DBUG_ASSERT(vcol_info); + if ((flags & PART_KEY_FLAG) != 0 || stored_in_db()) + { + Sql_mode_dependency dep= + vcol_info->expr->value_depends_on_sql_mode() & + Sql_mode_dependency(~0, ~can_handle_sql_mode_dependency_on_store()); + if (dep) + { + error_generated_column_function_is_not_allowed(thd); + dep.push_dependency_warnings(thd); + return true; + } + } + return false; +} + + /** Numeric fields base class constructor. */ @@ -1464,6 +1501,12 @@ void Field_num::prepend_zeros(String *value) const } +sql_mode_t Field_num::can_handle_sql_mode_dependency_on_store() const +{ + return MODE_PAD_CHAR_TO_FULL_LENGTH; +} + + Item *Field_num::get_equal_zerofill_const_item(THD *thd, const Context &ctx, Item *const_item) { @@ -5654,6 +5697,12 @@ my_time_t Field_timestampf::get_timestamp(const uchar *pos, /*************************************************************/ +sql_mode_t Field_temporal::can_handle_sql_mode_dependency_on_store() const +{ + return MODE_PAD_CHAR_TO_FULL_LENGTH; +} + + uint Field_temporal::is_equal(Create_field *new_field) { return new_field->sql_type == real_type() && @@ -7271,6 +7320,18 @@ longlong Field_string::val_int(void) } +sql_mode_t Field_string::value_depends_on_sql_mode() const +{ + return has_charset() ? MODE_PAD_CHAR_TO_FULL_LENGTH : sql_mode_t(0); +}; + + +sql_mode_t Field_string::can_handle_sql_mode_dependency_on_store() const +{ + return has_charset() ? MODE_PAD_CHAR_TO_FULL_LENGTH : sql_mode_t(0); +} + + String *Field_string::val_str(String *val_buffer __attribute__((unused)), String *val_ptr) { @@ -8881,6 +8942,12 @@ bool Field_geom::load_data_set_null(THD *thd) ** If one uses this string in a number context one gets the type number. ****************************************************************************/ +sql_mode_t Field_enum::can_handle_sql_mode_dependency_on_store() const +{ + return MODE_PAD_CHAR_TO_FULL_LENGTH; +} + + enum ha_base_keytype Field_enum::key_type() const { switch (packlength) { diff --git a/sql/field.h b/sql/field.h index ac7794081bc..9333d1492fd 100644 --- a/sql/field.h +++ b/sql/field.h @@ -697,6 +697,7 @@ protected: val_str(&result); return to->store(result.ptr(), result.length(), charset()); } + void error_generated_column_function_is_not_allowed(THD *thd) const; static void do_field_int(Copy_field *copy); static void do_field_real(Copy_field *copy); static void do_field_string(Copy_field *copy); @@ -1174,6 +1175,16 @@ public: } bool stored_in_db() const { return !vcol_info || vcol_info->stored_in_db; } + bool check_vcol_sql_mode_dependency(THD *) const; + + virtual sql_mode_t value_depends_on_sql_mode() const + { + return 0; + } + virtual sql_mode_t can_handle_sql_mode_dependency_on_store() const + { + return 0; + } inline THD *get_thd() const { return likely(table) ? table->in_use : current_thd; } @@ -1653,6 +1664,7 @@ public: enum Derivation derivation(void) const { return DERIVATION_NUMERIC; } uint repertoire(void) const { return MY_REPERTOIRE_NUMERIC; } CHARSET_INFO *charset(void) const { return &my_charset_numeric; } + sql_mode_t can_handle_sql_mode_dependency_on_store() const; Item *get_equal_const_item(THD *thd, const Context &ctx, Item *const_item) { return (flags & ZEROFILL_FLAG) ? @@ -2389,6 +2401,7 @@ public: field_name_arg) { flags|= BINARY_FLAG; } Item_result result_type () const { return STRING_RESULT; } + sql_mode_t can_handle_sql_mode_dependency_on_store() const; int store_hex_hybrid(const char *str, uint length) { return store(str, length, &my_charset_bin); @@ -3190,6 +3203,8 @@ public: { return charset() == &my_charset_bin ? FALSE : TRUE; } Field *make_new_field(MEM_ROOT *root, TABLE *new_table, bool keep_type); virtual uint get_key_image(uchar *buff,uint length, imagetype type); + sql_mode_t value_depends_on_sql_mode() const; + sql_mode_t can_handle_sql_mode_dependency_on_store() const; private: int do_save_field_metadata(uchar *first_byte); }; @@ -3599,6 +3614,7 @@ public: enum_field_types type() const { return MYSQL_TYPE_STRING; } enum Item_result cmp_type () const { return INT_RESULT; } enum ha_base_keytype key_type() const; + sql_mode_t can_handle_sql_mode_dependency_on_store() const; Copy_func *get_copy_func(const Field *from) const { if (eq_def(from)) diff --git a/sql/item.h b/sql/item.h index 2239544dc88..ff4cfd6c1b8 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1216,6 +1216,32 @@ public: double val_real_from_decimal(); double val_real_from_date(); + /* + Returns true if this item can be calculated during + value_depends_on_sql_mode() + */ + bool value_depends_on_sql_mode_const_item() + { + /* + Currently we use value_depends_on_sql_mode() only for virtual + column expressions. They should not contain any expensive items. + If we ever get a crash on the assert below, it means + check_vcol_func_processor() is badly implemented for this item. + */ + DBUG_ASSERT(!is_expensive()); + /* + It should return const_item() actually. + But for some reasons Item_field::const_item() returns true + at value_depends_on_sql_mode() call time. + This should be checked and fixed. + */ + return basic_const_item(); + } + virtual Sql_mode_dependency value_depends_on_sql_mode() const + { + return Sql_mode_dependency(); + } + // Get TIME, DATE or DATETIME using proper sql_mode flags for the field type bool get_temporal_with_sql_mode(MYSQL_TIME *ltime); // Check NULL value for a TIME, DATE or DATETIME expression @@ -2694,6 +2720,10 @@ public: { return MONOTONIC_STRICT_INCREASING; } + Sql_mode_dependency value_depends_on_sql_mode() const + { + return Sql_mode_dependency(0, field->value_depends_on_sql_mode()); + } longlong val_int_endpoint(bool left_endp, bool *incl_endp); bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); bool get_date_result(MYSQL_TIME *ltime,ulonglong fuzzydate); @@ -4102,6 +4132,7 @@ public: inline Item **arguments() const { return args; } inline uint argument_count() const { return arg_count; } inline void remove_arguments() { arg_count=0; } + Sql_mode_dependency value_depends_on_sql_mode_bit_or() const; }; @@ -4334,6 +4365,10 @@ public: bool const_item() const { return const_item_cache; } table_map used_tables() const { return used_tables_cache; } Item* build_clone(THD *thd, MEM_ROOT *mem_root); + Sql_mode_dependency value_depends_on_sql_mode() const + { + return Item_args::value_depends_on_sql_mode_bit_or().soft_to_hard(); + } }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 8bf130b297f..2c3a96f3f47 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -531,6 +531,23 @@ bool Item_func::setup_args_and_comparator(THD *thd, Arg_comparator *cmp) } +/* + Comparison operators remove arguments' dependency on PAD_CHAR_TO_FULL_LENGTH + in case of PAD SPACE comparison collations: trailing spaces do not affect + the comparison result for such collations. +*/ +Sql_mode_dependency +Item_bool_rowready_func2::value_depends_on_sql_mode() const +{ + if (compare_collation()->state & MY_CS_NOPAD) + return Item_func::value_depends_on_sql_mode(); + return ((args[0]->value_depends_on_sql_mode() | + args[1]->value_depends_on_sql_mode()) & + Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)). + soft_to_hard(); +} + + bool Item_bool_rowready_func2::fix_length_and_dec() { max_length= 1; // Function returns 0 or 1 @@ -5284,6 +5301,29 @@ bool Item_func_like::with_sargable_pattern() const } +/* + subject LIKE pattern + removes subject's dependency on PAD_CHAR_TO_FULL_LENGTH + if pattern ends with the '%' wildcard. +*/ +Sql_mode_dependency Item_func_like::value_depends_on_sql_mode() const +{ + if (!args[1]->value_depends_on_sql_mode_const_item()) + return Item_func::value_depends_on_sql_mode(); + StringBuffer<64> patternbuf; + String *pattern= args[1]->val_str_ascii(&patternbuf); + if (!pattern || !pattern->length()) + return Sql_mode_dependency(); // Will return NULL or 0 + DBUG_ASSERT(pattern->charset()->mbminlen == 1); + if (pattern->ptr()[pattern->length() - 1] != '%') + return Item_func::value_depends_on_sql_mode(); + return ((args[0]->value_depends_on_sql_mode() | + args[1]->value_depends_on_sql_mode()) & + Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)). + soft_to_hard(); +} + + SEL_TREE *Item_func_like::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr) { MEM_ROOT *tmp_root= param->mem_root; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 5045e39fc5a..0a5abfe6273 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -489,6 +489,7 @@ public: { allowed_arg_cols= 0; // Fetch this value from first argument } + Sql_mode_dependency value_depends_on_sql_mode() const; void print(String *str, enum_query_type query_type) { Item_func::print_op(str, query_type); @@ -1920,6 +1921,7 @@ public: Item_bool_func2(thd, a, b), canDoTurboBM(FALSE), pattern(0), pattern_len(0), bmGs(0), bmBc(0), escape_item(escape_arg), escape_used_in_parsing(escape_used), use_sampling(0), negated(0) {} + Sql_mode_dependency value_depends_on_sql_mode() const; longlong val_int(); enum Functype functype() const { return LIKE_FUNC; } void print(String *str, enum_query_type query_type); diff --git a/sql/item_func.cc b/sql/item_func.cc index 5ff1b26d734..d11b56f2aad 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -422,6 +422,15 @@ void Item_args::propagate_equal_fields(THD *thd, } +Sql_mode_dependency Item_args::value_depends_on_sql_mode_bit_or() const +{ + Sql_mode_dependency res; + for (uint i= 0; i < arg_count; i++) + res|= args[i]->value_depends_on_sql_mode(); + return res; +} + + /** See comments in Item_cond::split_sum_func() */ @@ -1414,9 +1423,13 @@ bool Item_func_minus::fix_length_and_dec() { if (Item_num_op::fix_length_and_dec()) return TRUE; - if (unsigned_flag && - (current_thd->variables.sql_mode & MODE_NO_UNSIGNED_SUBTRACTION)) - unsigned_flag=0; + m_sql_mode_dependency= Item_func::value_depends_on_sql_mode(); + if (unsigned_flag) + { + m_sql_mode_dependency|= Sql_mode_dependency(0,MODE_NO_UNSIGNED_SUBTRACTION); + if (current_thd->variables.sql_mode & MODE_NO_UNSIGNED_SUBTRACTION) + unsigned_flag= false; + } return FALSE; } diff --git a/sql/item_func.h b/sql/item_func.h index 81a7e948085..5be62427852 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -773,11 +773,16 @@ public: class Item_func_minus :public Item_func_additive_op { + Sql_mode_dependency m_sql_mode_dependency; public: Item_func_minus(THD *thd, Item *a, Item *b): Item_func_additive_op(thd, a, b) {} const char *func_name() const { return "-"; } enum precedence precedence() const { return ADD_PRECEDENCE; } + Sql_mode_dependency value_depends_on_sql_mode() const + { + return m_sql_mode_dependency; + } longlong int_op(); double real_op(); my_decimal *decimal_op(my_decimal *); diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index 753b6134419..dcfbd272809 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -2103,6 +2103,41 @@ void Item_func_trim::print(String *str, enum_query_type query_type) } +/* + RTRIM(expr) + TRIM(TRAILING ' ' FROM expr) + remove argument's soft dependency on PAD_CHAR_TO_FULL_LENGTH: +*/ +Sql_mode_dependency Item_func_trim::value_depends_on_sql_mode() const +{ + DBUG_ASSERT(fixed); + if (arg_count == 1) // RTRIM(expr) + return (args[0]->value_depends_on_sql_mode() & + Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)). + soft_to_hard(); + // TRIM(... FROM expr) + DBUG_ASSERT(arg_count == 2); + if (!args[1]->value_depends_on_sql_mode_const_item()) + return Item_func::value_depends_on_sql_mode(); + StringBuffer<64> trimstrbuf; + String *trimstr= args[1]->val_str(&trimstrbuf); + if (!trimstr) + return Sql_mode_dependency(); // will return NULL + if (trimstr->length() == 0) + return Item_func::value_depends_on_sql_mode(); // will trim nothing + if (trimstr->lengthsp() != 0) + return Item_func::value_depends_on_sql_mode(); // will trim not only spaces + if (trimstr->length() > trimstr->charset()->mbminlen || + trimstr->numchars() > 1) + return Item_func::value_depends_on_sql_mode(); // more than one space + // TRIM(TRAILING ' ' FROM expr) + return ((args[0]->value_depends_on_sql_mode() | + args[1]->value_depends_on_sql_mode()) & + Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)). + soft_to_hard(); +} + + /* Item_func_password */ bool Item_func_password::fix_fields(THD *thd, Item **ref) @@ -3116,6 +3151,38 @@ bool Item_func_pad::fix_length_and_dec() } +/* + PAD(expr,length,' ') + removes argument's soft dependency on PAD_CHAR_TO_FULL_LENGTH if the result + is longer than the argument's maximim possible length. +*/ +Sql_mode_dependency Item_func_rpad::value_depends_on_sql_mode() const +{ + DBUG_ASSERT(fixed); + DBUG_ASSERT(arg_count == 3); + if (!args[1]->value_depends_on_sql_mode_const_item() || + !args[2]->value_depends_on_sql_mode_const_item()) + return Item_func::value_depends_on_sql_mode(); + Longlong_hybrid len= args[1]->to_longlong_hybrid(); + if (args[1]->null_value || len.neg()) + return Sql_mode_dependency(); // will return NULL + if (len.abs() > 0 && len.abs() < args[0]->max_char_length()) + return Item_func::value_depends_on_sql_mode(); + StringBuffer<64> padstrbuf; + String *padstr= args[2]->val_str(&padstrbuf); + if (!padstr || !padstr->length()) + return Sql_mode_dependency(); // will return NULL + if (padstr->lengthsp() != 0) + return Item_func::value_depends_on_sql_mode(); // will pad not only spaces + // RPAD(expr, length, ' ') -- with a long enough length + return ((args[0]->value_depends_on_sql_mode() | + args[1]->value_depends_on_sql_mode()) & + Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)). + soft_to_hard(); +} + + + String *Item_func_rpad::val_str(String *str) { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index a398b4972cb..df8761534a1 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -489,6 +489,7 @@ protected: public: Item_func_trim(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) {} Item_func_trim(THD *thd, Item *a): Item_str_func(thd, a) {} + Sql_mode_dependency value_depends_on_sql_mode() const; String *val_str(String *); bool fix_length_and_dec(); const char *func_name() const { return "trim"; } @@ -504,6 +505,10 @@ class Item_func_ltrim :public Item_func_trim public: Item_func_ltrim(THD *thd, Item *a, Item *b): Item_func_trim(thd, a, b) {} Item_func_ltrim(THD *thd, Item *a): Item_func_trim(thd, a) {} + Sql_mode_dependency value_depends_on_sql_mode() const + { + return Item_func::value_depends_on_sql_mode(); + } String *val_str(String *); const char *func_name() const { return "ltrim"; } const char *mode_name() const { return "leading"; } @@ -950,6 +955,7 @@ public: Item_func_pad(thd, arg1, arg2, arg3) {} String *val_str(String *); const char *func_name() const { return "rpad"; } + Sql_mode_dependency value_depends_on_sql_mode() const; Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return get_item_copy<Item_func_rpad>(thd, mem_root, this); } }; diff --git a/sql/mysqld.h b/sql/mysqld.h index 26229c8cc5d..2947734901c 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -19,6 +19,7 @@ #include <my_global.h> /* MYSQL_PLUGIN_IMPORT, FN_REFLEN, FN_EXTLEN */ #include "sql_basic_types.h" /* query_id_t */ +#include "sql_mode.h" /* Sql_mode_dependency */ #include "sql_bitmap.h" /* Bitmap */ #include "my_decimal.h" /* my_decimal */ #include "mysql_com.h" /* SERVER_VERSION_LENGTH */ diff --git a/sql/set_var.h b/sql/set_var.h index 8a563f3c089..a5439a27137 100644 --- a/sql/set_var.h +++ b/sql/set_var.h @@ -427,6 +427,7 @@ inline bool IS_SYSVAR_AUTOSIZE(void *ptr) bool fix_delay_key_write(sys_var *self, THD *thd, enum_var_type type); sql_mode_t expand_sql_mode(sql_mode_t sql_mode); +const char *sql_mode_string_representation(uint bit_number); bool sql_mode_string_representation(THD *thd, sql_mode_t sql_mode, LEX_STRING *ls); int default_regex_flags_pcre(const THD *thd); diff --git a/sql/sql_mode.cc b/sql/sql_mode.cc new file mode 100644 index 00000000000..9eea6ccdb52 --- /dev/null +++ b/sql/sql_mode.cc @@ -0,0 +1,34 @@ +/* + Copyright (c) 2019, MariaDB. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + +#include "mysqld.h" +#include "set_var.h" + +void Sql_mode_dependency::push_dependency_warnings(THD *thd) +{ + sql_mode_t all= m_hard | m_soft; + for (uint i= 0; all ; i++, all >>= 1) + { + if (all & 1) + { + // TODO-10.5: add a new error code + push_warning_printf(thd, + Sql_condition::WARN_LEVEL_WARN, ER_UNKNOWN_ERROR, + "Expression depends on the @@%s value %s", + "sql_mode", sql_mode_string_representation(i)); + } + } +} diff --git a/sql/sql_mode.h b/sql/sql_mode.h new file mode 100644 index 00000000000..e92848fb6d1 --- /dev/null +++ b/sql/sql_mode.h @@ -0,0 +1,162 @@ +#ifndef SQL_MODE_H_INCLUDED +#define SQL_MODE_H_INCLUDED +/* + Copyright (c) 2019, MariaDB. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ + +#ifdef USE_PRAGMA_INTERFACE +#pragma interface /* gcc class implementation */ +#endif + +#include "sql_basic_types.h" + +/* + class Sql_mode_dependency + + A combination of hard and soft dependency on sql_mode. + Used to watch if a GENERATED ALWAYS AS expression guarantees consitent + data written to its virtual column. + + A virtual column can appear in an index if: + - the generation expression does not depend on any sql_mode flags, or + - the generation expression has a soft dependency on an sql_mode flag, + and the column knows how to handle this dependeny. + + A virtual column cannot appear in an index if: + - its generation expression has a hard dependency + - its generation expression has a soft dependency, but the column + cannot handle it on store. + An error is reported in such cases. + + How dependencies appear: + - When a column return value depends on some sql_mode flag, + its Item_field adds a corresponding bit to m_soft. For example, + Item_field for a CHAR(N) column adds the PAD_CHAR_TO_FULL_LENGTH flag. + - When an SQL function/operator return value depends on some sql_mode flag, + it adds a corresponding bit to m_soft. For example, Item_func_minus + adds the MODE_NO_UNSIGNED_SUBTRACTION in case of unsigned arguments. + + How dependency are processed (see examples below): + - All SQL functions/operators bit-OR all hard dependencies from all arguments. + - Some soft dependencies can be handled by the underlying Field on store, + e.g. CHAR(N) can handle PAD_CHAR_TO_FULL_LENGTH. + - Some soft dependencies can be handled by SQL functions and operators, + e.g. RTRIM(expr) removes expr's soft dependency on PAD_CHAR_TO_FULL_LENGTH. + If a function or operator handles a soft dependency on a certain sql_mode + flag, it removes the corresponding bit from m_soft (see below). + Note, m_hard is not touched in such cases. + - When an expression with a soft dependency on a certain sql_mode flag + goes as an argument to an SQL function/operator which cannot handle + this flag, the dependency escalates from soft to hard + (by moving the corresponding bit from m_soft to m_hard) and cannot be + handled any more on the upper level, neither by a Field on store, + nor by another SQL function/operator. + + There are four kinds of Items: + 1. Items that generate a soft or hard dependency, e.g. + - Item_field for CHAR(N) - generates soft/PAD_CHAR_TO_FULL_LENGTH + - Item_func_minus - generates soft/NO_UNSIGNED_SUBTRACTION + 2. Items that convert a soft dependency to a hard dependency. + This happens e.g. when an Item_func instance gets a soft dependency + from its arguments, and it does not know how to handle this dependency. + Most Item_func descendants do this. + 3. Items that remove soft dependencies, e.g.: + - Item_func_rtrim - removes soft/PAD_CHAR_TO_FULL_LENGTH + that came from args[0] (under certain conditions) + - Item_func_rpad - removes soft/PAD_CJAR_TO_FULL_LENGTH + that came from args[0] (under certain conditions) + 4. Items that repeat soft dependency from its arguments to the caller. + They are not implemented yet. But functions like Item_func_coalesce, + Item_func_case, Item_func_case_abbreviation2 could do this. + + Examples: + + 1. CREATE OR REPLACE TABLE t1 (a CHAR(5), v CHAR(20) AS(a), KEY(v)); + + Here `v` has a soft dependency on `a`. + The value of `a` depends on PAD_CHAR_TO_FULL_LENGTH, it can return: + - 'a' - if PAD_CHAR_TO_FULL_LENGTH is disabled + - 'a' followed by four spaces - if PAD_CHAR_TO_FULL_LENGTH is enabled + But `v` will pad trailing spaces to the full length on store anyway. + So Field_string handles this soft dependency on store. + This combination of the virtial column data type and its generation + expression is safe and provides consistent data in `v`, which is + 'a' followed by four spaces, no matter what PAD_CHAR_TO_FULL_LENGTH is. + + 2. CREATE OR REPLACE TABLE t1 (a CHAR(5), v VARCHAR(20) AS(a), KEY(v)); + + Here `v` has a soft dependency on `a`. But Field_varstring does + not pad spaces on store, so it cannot handle this dependency. + This combination of the virtual column data type and its generation + expression is not safe. An error is returned. + + 3. CREATE OR REPLACE TABLE t1 (a CHAR(5), v INT AS(LENGTH(a)), KEY(v)); + + Here `v` has a hard dependency on `a`, because the value of `a` + is wrapped to the function LENGTH(). + The value of `LENGTH(a)` depends on PAD_CHAR_TO_FULL_LENGTH, it can return: + - 1 - if PAD_CHAR_TO_FULL_LENGTH is disabled + - 4 - if PAD_CHAR_TO_FULL_LENGTH is enabled + This combination cannot provide consistent data stored to `v`, + therefore it's disallowed. +*/ +class Sql_mode_dependency +{ + sql_mode_t m_hard; + sql_mode_t m_soft; +public: + Sql_mode_dependency() + :m_hard(0), m_soft(0) + { } + Sql_mode_dependency(sql_mode_t hard, sql_mode_t soft) + :m_hard(hard), m_soft(soft) + { } + sql_mode_t hard() const { return m_hard; } + sql_mode_t soft() const { return m_soft; } + operator bool () const + { + return m_hard > 0 || m_soft > 0; + } + Sql_mode_dependency operator|(const Sql_mode_dependency &other) const + { + return Sql_mode_dependency(m_hard | other.m_hard, m_soft | other.m_soft); + } + Sql_mode_dependency operator&(const Sql_mode_dependency &other) const + { + return Sql_mode_dependency(m_hard & other.m_hard, m_soft & other.m_soft); + } + Sql_mode_dependency &operator|=(const Sql_mode_dependency &other) + { + m_hard|= other.m_hard; + m_soft|= other.m_soft; + return *this; + } + Sql_mode_dependency &operator&=(const Sql_mode_dependency &other) + { + m_hard&= other.m_hard; + m_soft&= other.m_soft; + return *this; + } + Sql_mode_dependency &soft_to_hard() + { + m_hard|= m_soft; + m_soft= 0; + return *this; + } + void push_dependency_warnings(THD *thd); +}; + + +#endif // SQL_MODE_H_INCLUDED diff --git a/sql/sql_string.h b/sql/sql_string.h index a7652376bbc..e1d64722898 100644 --- a/sql/sql_string.h +++ b/sql/sql_string.h @@ -235,6 +235,11 @@ public: return skr; } + size_t lengthsp() const + { + return str_charset->cset->lengthsp(str_charset, Ptr, str_length); + } + void set(String &str,uint32 offset,uint32 arg_length) { DBUG_ASSERT(&str != this); diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 04ecbb29b12..8eb75e49d8e 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3160,6 +3160,14 @@ static const char *sql_mode_names[]= 0 }; + +const char *sql_mode_string_representation(uint bit_number) +{ + DBUG_ASSERT(bit_number < array_elements(sql_mode_names)); + return sql_mode_names[bit_number]; +} + + export bool sql_mode_string_representation(THD *thd, sql_mode_t sql_mode, LEX_STRING *ls) { diff --git a/sql/table.cc b/sql/table.cc index d3b4cf3a3c0..62449ebcc06 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1073,6 +1073,12 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table, vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str, &((*field_ptr)->vcol_info), error_reported); *(vfield_ptr++)= *field_ptr; + if (vcol && field_ptr[0]->check_vcol_sql_mode_dependency(thd)) + { + DBUG_ASSERT(thd->is_error()); + *error_reported= true; + goto end; + } break; case VCOL_DEFAULT: vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str, |