diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-08-16 10:08:30 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-08-16 10:08:30 +0300 |
| commit | 734db318ac69f60959bcd0d6c9116a3681449a38 (patch) | |
| tree | cb8ccd4ecfebe752ec559834d3bf083efb9c8cd4 /mysql-test/main | |
| parent | b151cc2b0b1fc938de91035fbafc46667d9019e3 (diff) | |
| parent | 8716bb3b72b7f5fed69b6dde413c2138f6d175b2 (diff) | |
| download | mariadb-git-734db318ac69f60959bcd0d6c9116a3681449a38.tar.gz | |
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test/main')
66 files changed, 1982 insertions, 6 deletions
diff --git a/mysql-test/main/bootstrap.test b/mysql-test/main/bootstrap.test index 7c5586a8784..d75be403f13 100644 --- a/mysql-test/main/bootstrap.test +++ b/mysql-test/main/bootstrap.test @@ -114,4 +114,14 @@ EOF --source include/kill_mysqld.inc --exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 --remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql + +# +# MDEV-13397 MariaDB upgrade fail when using default_time_zone +# +--write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql +use test; +EOF +--exec $MYSQLD_BOOTSTRAP_CMD --default-time-zone=Europe/Moscow < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 +--remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql + --source include/start_mysqld.inc diff --git a/mysql-test/main/ctype_binary.result b/mysql-test/main/ctype_binary.result index 0afe5298411..8059e02611e 100644 --- a/mysql-test/main/ctype_binary.result +++ b/mysql-test/main/ctype_binary.result @@ -3048,6 +3048,29 @@ DROP TABLE t1; SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; _binary 0x7E _binary X'7E' _binary B'01111110' ~ ~ ~ +SET NAMES utf8, character_set_connection=binary; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varbinary(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +abcdefghi-abcdefghi +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_binary.test b/mysql-test/main/ctype_binary.test index 8da4eaff572..155d8548f77 100644 --- a/mysql-test/main/ctype_binary.test +++ b/mysql-test/main/ctype_binary.test @@ -24,6 +24,9 @@ SET NAMES binary; --echo # SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; +SET NAMES utf8, character_set_connection=binary; +--source include/ctype_mdev13118.inc + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/ctype_eucjpms.result b/mysql-test/main/ctype_eucjpms.result index bc7b4b9ef9d..507d0021a1f 100644 --- a/mysql-test/main/ctype_eucjpms.result +++ b/mysql-test/main/ctype_eucjpms.result @@ -33844,6 +33844,29 @@ HEX(a) CHAR_LENGTH(a) DROP TABLE t1; SELECT _eucjpms 0x8EA0; ERROR HY000: Invalid eucjpms character string: '8EA0' +SET NAMES eucjpms; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET eucjpms DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_eucjpms.test b/mysql-test/main/ctype_eucjpms.test index c69abc32a03..867ca809dbf 100644 --- a/mysql-test/main/ctype_eucjpms.test +++ b/mysql-test/main/ctype_eucjpms.test @@ -537,6 +537,8 @@ DROP TABLE t1; --error ER_INVALID_CHARACTER_STRING SELECT _eucjpms 0x8EA0; +SET NAMES eucjpms; +--source include/ctype_mdev13118.inc --echo # --echo # End of 10.0 tests diff --git a/mysql-test/main/ctype_euckr.result b/mysql-test/main/ctype_euckr.result index 3fb6f98b2eb..cc50ddd20be 100644 --- a/mysql-test/main/ctype_euckr.result +++ b/mysql-test/main/ctype_euckr.result @@ -25405,6 +25405,35 @@ A1A1A1A1A1A120202020202020202020202020202020202020 # End of 5.6 tests # # +# Start of 10.0 tests +# +SET NAMES utf8, character_set_connection=euckr; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET euckr DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# +# End of 10.0 tests +# +# # Start of 10.2 tests # # diff --git a/mysql-test/main/ctype_euckr.test b/mysql-test/main/ctype_euckr.test index 950c60c47e5..46955360217 100644 --- a/mysql-test/main/ctype_euckr.test +++ b/mysql-test/main/ctype_euckr.test @@ -197,6 +197,16 @@ set collation_connection=euckr_bin; --echo # End of 5.6 tests --echo # +--echo # +--echo # Start of 10.0 tests +--echo # + +SET NAMES utf8, character_set_connection=euckr; +--source include/ctype_mdev13118.inc + +--echo # +--echo # End of 10.0 tests +--echo # --echo # --echo # Start of 10.2 tests diff --git a/mysql-test/main/ctype_gbk.result b/mysql-test/main/ctype_gbk.result index 6bbb9bb3094..4515b9fbdbf 100644 --- a/mysql-test/main/ctype_gbk.result +++ b/mysql-test/main/ctype_gbk.result @@ -5081,6 +5081,29 @@ E05C5B E05B DROP TABLE t1; # Start of ctype_E05C.inc +SET NAMES utf8, character_set_connection=gbk; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET gbk DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant # diff --git a/mysql-test/main/ctype_gbk.test b/mysql-test/main/ctype_gbk.test index a3dc7f4af44..d9d0a4d0ee4 100644 --- a/mysql-test/main/ctype_gbk.test +++ b/mysql-test/main/ctype_gbk.test @@ -199,6 +199,9 @@ let $ctype_unescape_combinations=selected; SET NAMES gbk; --source include/ctype_E05C.inc +SET NAMES utf8, character_set_connection=gbk; +--source include/ctype_mdev13118.inc + --echo # --echo # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant --echo # diff --git a/mysql-test/main/ctype_latin1.result b/mysql-test/main/ctype_latin1.result index d5906cb74dd..43a84275629 100644 --- a/mysql-test/main/ctype_latin1.result +++ b/mysql-test/main/ctype_latin1.result @@ -8022,6 +8022,29 @@ a 0 DROP VIEW v1; DROP TABLE t1; +SET NAMES latin1; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_latin1.test b/mysql-test/main/ctype_latin1.test index 38f147708b8..61df80e2186 100644 --- a/mysql-test/main/ctype_latin1.test +++ b/mysql-test/main/ctype_latin1.test @@ -262,6 +262,9 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; +SET NAMES latin1; +--source include/ctype_mdev13118.inc + --echo # --echo # End of 10.0 tests diff --git a/mysql-test/main/ctype_ucs.result b/mysql-test/main/ctype_ucs.result index a68309831c6..5641726a831 100644 --- a/mysql-test/main/ctype_ucs.result +++ b/mysql-test/main/ctype_ucs.result @@ -5690,6 +5690,29 @@ c2 YWJjZGVmZ2hp-YWJjZGVmZ2hp DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; +SET NAMES utf8, character_set_connection=ucs2; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET ucs2 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_ucs.test b/mysql-test/main/ctype_ucs.test index 9592e27f8e1..d0d463c0340 100644 --- a/mysql-test/main/ctype_ucs.test +++ b/mysql-test/main/ctype_ucs.test @@ -992,6 +992,10 @@ DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; +SET NAMES utf8, character_set_connection=ucs2; +--source include/ctype_mdev13118.inc + + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/ctype_ujis.result b/mysql-test/main/ctype_ujis.result index cb9de83fedc..0b1cf606f0b 100644 --- a/mysql-test/main/ctype_ujis.result +++ b/mysql-test/main/ctype_ujis.result @@ -26149,6 +26149,29 @@ HEX(a) CHAR_LENGTH(a) DROP TABLE t1; SELECT _ujis 0x8EA0; ERROR HY000: Invalid ujis character string: '8EA0' +SET NAMES ujis; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET ujis DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_ujis.test b/mysql-test/main/ctype_ujis.test index ff9c61c05ba..cf247763b46 100644 --- a/mysql-test/main/ctype_ujis.test +++ b/mysql-test/main/ctype_ujis.test @@ -1368,6 +1368,10 @@ DROP TABLE t1; SELECT _ujis 0x8EA0; +SET NAMES ujis; +--source include/ctype_mdev13118.inc + + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/ctype_utf16.result b/mysql-test/main/ctype_utf16.result index 8de21a14bd9..a105017b726 100644 --- a/mysql-test/main/ctype_utf16.result +++ b/mysql-test/main/ctype_utf16.result @@ -2142,6 +2142,29 @@ EXECUTE stmt USING @arg00; CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; +SET NAMES utf8, character_set_connection=utf16; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf16 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_utf16.test b/mysql-test/main/ctype_utf16.test index 526f5251055..504e62e3e89 100644 --- a/mysql-test/main/ctype_utf16.test +++ b/mysql-test/main/ctype_utf16.test @@ -870,6 +870,11 @@ SET @arg00=_binary 0x00FF; EXECUTE stmt USING @arg00; DEALLOCATE PREPARE stmt; + +SET NAMES utf8, character_set_connection=utf16; +--source include/ctype_mdev13118.inc + + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/ctype_utf16le.result b/mysql-test/main/ctype_utf16le.result index 0fedff7d3bf..6d8d7ddca2a 100644 --- a/mysql-test/main/ctype_utf16le.result +++ b/mysql-test/main/ctype_utf16le.result @@ -2326,6 +2326,35 @@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF # End of 5.6 tests # # +# Start of 10.0 tests +# +SET NAMES utf8, character_set_connection=utf16le; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf16le DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# +# Start of 10.0 tests +# +# # Start of 10.1 tests # # diff --git a/mysql-test/main/ctype_utf16le.test b/mysql-test/main/ctype_utf16le.test index 79cf875852a..b8728b52db2 100644 --- a/mysql-test/main/ctype_utf16le.test +++ b/mysql-test/main/ctype_utf16le.test @@ -747,6 +747,19 @@ SET NAMES utf8, collation_connection=utf16le_bin; --echo # End of 5.6 tests --echo # + +--echo # +--echo # Start of 10.0 tests +--echo # + + +SET NAMES utf8, character_set_connection=utf16le; +--source include/ctype_mdev13118.inc + +--echo # +--echo # Start of 10.0 tests +--echo # + --echo # --echo # Start of 10.1 tests --echo # diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result index 59ce4c2f445..28b9caf6ffe 100644 --- a/mysql-test/main/ctype_utf32.result +++ b/mysql-test/main/ctype_utf32.result @@ -2241,6 +2241,29 @@ EXECUTE stmt USING @arg00; CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; +SET NAMEs utf8, character_set_connection=utf32; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf32 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_utf32.test b/mysql-test/main/ctype_utf32.test index 6f036898d15..2e739ebfdbc 100644 --- a/mysql-test/main/ctype_utf32.test +++ b/mysql-test/main/ctype_utf32.test @@ -983,6 +983,14 @@ SET @arg00=_binary 0x00FF; EXECUTE stmt USING @arg00; DEALLOCATE PREPARE stmt; + +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET NAMEs utf8, character_set_connection=utf32; +--source include/ctype_mdev13118.inc + + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result index 10d0efeff3c..d696ff78831 100644 --- a/mysql-test/main/ctype_utf8.result +++ b/mysql-test/main/ctype_utf8.result @@ -10286,6 +10286,29 @@ SELECT * FROM v1; c ß DROP VIEW v1; +SET NAMES utf8; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_utf8.test b/mysql-test/main/ctype_utf8.test index 20a60d370a0..6f8657dacb5 100644 --- a/mysql-test/main/ctype_utf8.test +++ b/mysql-test/main/ctype_utf8.test @@ -1890,6 +1890,13 @@ SELECT * FROM v1; DROP VIEW v1; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET NAMES utf8; +--source include/ctype_mdev13118.inc + + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/ctype_utf8mb4.result b/mysql-test/main/ctype_utf8mb4.result index 7e5e99354ea..27ffd5552fa 100644 --- a/mysql-test/main/ctype_utf8mb4.result +++ b/mysql-test/main/ctype_utf8mb4.result @@ -3478,6 +3478,29 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; SET NAMES default; +SET NAMES utf8mb4; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/main/ctype_utf8mb4.test b/mysql-test/main/ctype_utf8mb4.test index 545347fcd26..9cf87d12340 100644 --- a/mysql-test/main/ctype_utf8mb4.test +++ b/mysql-test/main/ctype_utf8mb4.test @@ -1959,6 +1959,14 @@ DROP TABLE t1; SET NAMES default; + +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET NAMES utf8mb4; +--source include/ctype_mdev13118.inc + + --echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index a1dd1a77d3b..9874f16aa8a 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1053,6 +1053,7 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -1082,6 +1083,38 @@ i drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +# +# MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 # # Start of 10.1 tests diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 466d79af409..6c51f23c51e 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -913,6 +913,7 @@ CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -935,7 +936,27 @@ call pr(2); drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +--echo # +--echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +--echo # + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; --echo # end of 5.5 --echo # diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index f882dbba466..2c983e3de3e 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15960,3 +15960,123 @@ f c 9 1 DROP VIEW v1; DROP TABLE t1; +# +# MDEV-15087: error from inexpensive subquery before check +# for condition pushdown into derived +# +CREATE TABLE t1 (i1 int, v1 varchar(1)); +INSERT INTO t1 VALUES (7,'x'); +CREATE TABLE t2 (i1 int); +INSERT INTO t2 VALUES (8); +CREATE TABLE t3 (i1 int ,v1 varchar(1), v2 varchar(1)); +INSERT INTO t3 VALUES (4, 'v','v'),(62,'v','k'),(7, 'n', NULL); +SELECT 1 +FROM (t1 AS a1 +JOIN (((SELECT DISTINCT t3.* +FROM t3) AS a2 +JOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1)) +WHERE (SELECT BIT_COUNT(t2.i1) +FROM (t2 JOIN t3)) IS NULL; +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1, t2, t3; +# +# MDEV-16614 signal 7 after calling stored procedure, that uses regexp +# +CREATE PROCEDURE p1(m1 varchar(5), m2 varchar(5)) +BEGIN +SELECT a FROM +(SELECT "aa" a) t +JOIN (SELECT "aa" b) t1 on t.a=t1.b +WHERE t.a regexp m1 and t1.b regexp m2 +GROUP BY a; +END$$ +CALL p1('a','a'); +a +aa +DROP PROCEDURE p1; +CREATE PROCEDURE p1(m1 varchar(5)) +BEGIN +SELECT a FROM (SELECT "aa" a) t WHERE t.a regexp m1; +END$$ +CALL p1('a'); +a +aa +DROP PROCEDURE p1; +SELECT a FROM (SELECT "aa" a) t WHERE REGEXP_INSTR(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); +a +aa +CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT +BEGIN +RETURN 1; +END;$$ +CREATE OR REPLACE PROCEDURE p1(m1 varchar(5)) +BEGIN +SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, m1); +END$$ +CALL p1('a'); +a +aa +DROP PROCEDURE p1; +DROP FUNCTION f1; +CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT +BEGIN +RETURN 1; +END;$$ +SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); +a +aa +DROP FUNCTION f1; +# +# MDEV-16801: splittable materialized derived/views with +# one grouping field from table without keys +# +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES +(77,7), (11,1), (33,3), (44,4), (8,88), +(78,7), (98,9), (38,3), (28,2), (79,7), +(58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES +(100), (700), (200), (100), (200); +INSERT INTO t3 VALUES +(3), (4), (1), (8), (3); +ANALYZE tables t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +d b c +3 3 700 +3 3 200 +3 3 100 +4 4 700 +4 4 200 +4 4 100 +1 1 700 +1 1 200 +1 1 100 +3 3 700 +3 3 200 +3 3 100 +EXPLAIN EXTENDED SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.d 2 100.00 +2 LATERAL DERIVED t1 ref idx_b idx_b 5 test.t3.d 2 100.00 Using index; Using temporary; Using filesort +2 LATERAL DERIVED t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t3`.`d` AS `d`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from `test`.`t3` join (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t3`.`d` group by `test`.`t1`.`b`,`test`.`t2`.`c`) `dt` where `dt`.`b` = `test`.`t3`.`d` +DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index e51646a9764..b75c56e9ff2 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2906,3 +2906,116 @@ SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-15087: error from inexpensive subquery before check +--echo # for condition pushdown into derived +--echo # + +CREATE TABLE t1 (i1 int, v1 varchar(1)); +INSERT INTO t1 VALUES (7,'x'); + +CREATE TABLE t2 (i1 int); +INSERT INTO t2 VALUES (8); + +CREATE TABLE t3 (i1 int ,v1 varchar(1), v2 varchar(1)); +INSERT INTO t3 VALUES (4, 'v','v'),(62,'v','k'),(7, 'n', NULL); + +--error ER_SUBQUERY_NO_1_ROW +SELECT 1 +FROM (t1 AS a1 + JOIN (((SELECT DISTINCT t3.* + FROM t3) AS a2 + JOIN t1 ON (t1.v1 = a2.v2))) ON (t1.v1 = a2.v1)) +WHERE (SELECT BIT_COUNT(t2.i1) + FROM (t2 JOIN t3)) IS NULL; + +DROP TABLE t1, t2, t3; + +--echo # +--echo # MDEV-16614 signal 7 after calling stored procedure, that uses regexp +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1(m1 varchar(5), m2 varchar(5)) +BEGIN +SELECT a FROM + (SELECT "aa" a) t + JOIN (SELECT "aa" b) t1 on t.a=t1.b +WHERE t.a regexp m1 and t1.b regexp m2 +GROUP BY a; +END$$ +DELIMITER ;$$ +CALL p1('a','a'); +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1(m1 varchar(5)) +BEGIN + SELECT a FROM (SELECT "aa" a) t WHERE t.a regexp m1; +END$$ +DELIMITER ;$$ +CALL p1('a'); +DROP PROCEDURE p1; + + +SELECT a FROM (SELECT "aa" a) t WHERE REGEXP_INSTR(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); + + +DELIMITER $$; +CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT +BEGIN + RETURN 1; +END;$$ +CREATE OR REPLACE PROCEDURE p1(m1 varchar(5)) +BEGIN + SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, m1); +END$$ +DELIMITER ;$$ +CALL p1('a'); +DROP PROCEDURE p1; +DROP FUNCTION f1; + + +DELIMITER $$; +CREATE OR REPLACE FUNCTION f1(a VARCHAR(10), b VARCHAR(10)) RETURNS INT +BEGIN + RETURN 1; +END;$$ +DELIMITER ;$$ +SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); +DROP FUNCTION f1; + +--echo # +--echo # MDEV-16801: splittable materialized derived/views with +--echo # one grouping field from table without keys +--echo # + +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES + (77,7), (11,1), (33,3), (44,4), (8,88), + (78,7), (98,9), (38,3), (28,2), (79,7), + (58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES + (100), (700), (200), (100), (200); +INSERT INTO t3 VALUES + (3), (4), (1), (8), (3); + +ANALYZE tables t1,t2,t3; + +let $q= +SELECT * + FROM t3, + (SELECT t1.b, t2.c + FROM t1, t2 + GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index a7e8b5c9e59..cc3764c9ed2 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -761,6 +761,36 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # +# MDEV-16054 simple json functions flatline cpu on garbage input. +# +select json_array(1,uuid(),compress(5.140264e+307)); +json_array(1,uuid(),compress(5.140264e+307)) +NULL +# +# MDEV-16869 String functions don't respect character set of JSON_VALUE. +# +create table t1(json_col TEXT) DEFAULT CHARSET=latin1; +insert into t1 values (_latin1 X'7B226B657931223A2253EC227D'); +select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; +JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' +1 +select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1; +REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' +1 +drop table t1; +# +# MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. +# +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6); +JSON_SET('{}', '$.a', _utf8 0xC3B6) +{"a": "ö"} +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6); +JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) +{"a": "ö", "b": "ö"} +SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); +JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') +{"a": "ö", "x": 1, "b": "ö"} +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 8d72e5afa59..4ef752e3d32 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -423,6 +423,30 @@ SHOW CREATE TABLE t1; DROP TABLE t1; --echo # +--echo # MDEV-16054 simple json functions flatline cpu on garbage input. +--echo # + +select json_array(1,uuid(),compress(5.140264e+307)); + +--echo # +--echo # MDEV-16869 String functions don't respect character set of JSON_VALUE. +--echo # + +create table t1(json_col TEXT) DEFAULT CHARSET=latin1; +insert into t1 values (_latin1 X'7B226B657931223A2253EC227D'); +select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; +select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1; +drop table t1; + +--echo # +--echo # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. +--echo # + +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6); +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6); +SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index 075f5b9ec51..b6f9ca8e628 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -3554,6 +3554,38 @@ DROP FUNCTION fn_sleep_before_now; DROP TRIGGER trg_insert_t_ts; DROP TABLE t_ts, t_trig; # +# MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer +# +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:10'),('00:00:20'); +SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1; +a SUBTIME(a,TIME'00:00:01') ADDTIME(a,TIME'00:00:01') +00:00:10 00:00:09 00:00:11 +00:00:20 00:00:19 00:00:21 +SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'; +a +00:00:10 +SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +a +SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +a +EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' and addtime(`test`.`t1`.`a`,TIME'00:00:01') <= TIME'00:00:09' +DROP TABLE t1; +# +# MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) +# +SELECT +LAST_DAY(TIME'00:00:00') AS c1, +CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, +CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; +c1 c2 c3 +2018-08-31 00:00:00 00:00:00 +# End of 10.3 tests +# # MDEV-14032 SEC_TO_TIME executes side effect two times # SET @a=10000000; diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index f6f39d967f2..b03c14fb8ba 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -2141,6 +2141,31 @@ DROP TABLE t_ts, t_trig; --echo # +--echo # MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer +--echo # + +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:10'),('00:00:20'); +SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1; +SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'; +SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) +--echo # + +SELECT + LAST_DAY(TIME'00:00:00') AS c1, + CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, + CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; + +--echo # End of 10.3 tests + +--echo # --echo # MDEV-14032 SEC_TO_TIME executes side effect two times --echo # diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result index ff7ff2b3611..4b639bf969c 100644 --- a/mysql-test/main/grant.result +++ b/mysql-test/main/grant.result @@ -1759,6 +1759,7 @@ drop user mysqltest@localhost; disconnect user1; drop database mysqltest; use test; +call mtr.add_suppression("Can't open and lock privilege tables"); FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; FLUSH PRIVILEGES; @@ -1852,8 +1853,6 @@ BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END ;|| -Warnings: -Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges connection default; SHOW GRANTS FOR 'user1'@'localhost'; Grants for user1@localhost @@ -1864,6 +1863,7 @@ SHOW GRANTS FOR 'user2'; Grants for user2@% GRANT USAGE ON *.* TO 'user2'@'%' GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` TO 'user2'@'%' disconnect con1; disconnect con2; DROP PROCEDURE db1.proc1; diff --git a/mysql-test/main/grant.test b/mysql-test/main/grant.test index cb4254fe8c6..c3bb987acc8 100644 --- a/mysql-test/main/grant.test +++ b/mysql-test/main/grant.test @@ -1598,6 +1598,9 @@ use test; # # Bug#16470 crash on grant if old grant tables # + +call mtr.add_suppression("Can't open and lock privilege tables"); + --echo FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; FLUSH PRIVILEGES; diff --git a/mysql-test/main/invisible_field_debug.result b/mysql-test/main/invisible_field_debug.result index 4c4ebffbdfc..b3c84d18333 100644 --- a/mysql-test/main/invisible_field_debug.result +++ b/mysql-test/main/invisible_field_debug.result @@ -369,3 +369,8 @@ t1 1 invisible 1 c A NULL NULL NULL YES BTREE t1 1 invisible_2 1 invisible A NULL NULL NULL YES BTREE drop table t1; set @old_debug= @@debug_dbug; +CREATE TABLE t1 (i INT ); +SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; +CREATE TABLE t2 LIKE t1; +SET debug_dbug= DEFAULT; +DROP TABLE t1, t2; diff --git a/mysql-test/main/invisible_field_debug.test b/mysql-test/main/invisible_field_debug.test index 8674620e055..c1d6899d16e 100644 --- a/mysql-test/main/invisible_field_debug.test +++ b/mysql-test/main/invisible_field_debug.test @@ -270,3 +270,9 @@ explain select * from t1 where invisible =9; show indexes in t1; drop table t1; set @old_debug= @@debug_dbug; +## MDEV 15127 +CREATE TABLE t1 (i INT ); +SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; +CREATE TABLE t2 LIKE t1; +SET debug_dbug= DEFAULT; +DROP TABLE t1, t2; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index 5978b261b3a..bd015ae351c 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1506,6 +1506,46 @@ DROP VIEW v2; DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; # +# MDEV-16512 +# Server crashes in find_field_in_table_ref on 2nd execution of SP referring to +# non-existing field +# +CREATE TABLE t (i INT); +CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +FLUSH TABLES; +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +DROP TABLE t; +CREATE TABLE t (f INT); +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +DROP TABLE t; +CREATE TABLE t (i INT); +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +DROP PROCEDURE p; +DROP TABLE t; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); +CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +CALL p1; +ERROR 23000: Column 'c' in field list is ambiguous +CALL p1; +ERROR 23000: Column 'c' in field list is ambiguous +DROP PROCEDURE p1; +DROP TABLE t1,t2,t3,t4,t5; +# +# End of MariaDB 5.5 tests +# +# # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING # CREATE TABLE t1 (a int); diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index e07a3665920..8a088de91cc 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1161,6 +1161,59 @@ DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; --echo # +--echo # MDEV-16512 +--echo # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to +--echo # non-existing field +--echo # + +CREATE TABLE t (i INT); +CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); +--error ER_BAD_FIELD_ERROR +CALL p; +--error ER_BAD_FIELD_ERROR +CALL p; +FLUSH TABLES; +--error ER_BAD_FIELD_ERROR +CALL p; +DROP TABLE t; + +# +# Fix the table definition to match the using +# + +CREATE TABLE t (f INT); +# +# The following shouldn't fail as the table is now matching the using +# +--error ER_BAD_FIELD_ERROR +CALL p; +DROP TABLE t; +CREATE TABLE t (i INT); +--error ER_BAD_FIELD_ERROR +CALL p; +DROP PROCEDURE p; +DROP TABLE t; + +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); +CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +--error ER_NON_UNIQ_ERROR +CALL p1; +--error ER_NON_UNIQ_ERROR +CALL p1; +DROP PROCEDURE p1; +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # End of MariaDB 5.5 tests +--echo # + + +--echo # --echo # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING --echo # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index eea397402ad..fde6e0fec6b 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -5925,6 +5925,39 @@ SET join_buffer_space_limit= default; set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; # +# MDEV-16603: BNLH for query with materialized semi-join +# +set join_cache_level=4; +CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (7,'x'); +CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), +(228,'x'),(3,'y'),(1,'z'),(9,'z'); +CREATE TABLE temp +SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); +SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); +f1 f2 +7 x +7 x +7 x +7 x +7 x +7 x +7 x +EXPLAIN EXTENDED SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 +1 PRIMARY temp hash_ALL NULL #hash#$hj 9 test.t1.i1,test.t1.v1 7 100.00 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 1 100.00 Using where +2 MATERIALIZED t2 hash_index v1 #hash#v1:v1 4:9 test.t1.v1 10 10.00 Using index; Using join buffer (flat, BNLH join) +Warnings: +Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where `test`.`temp`.`f1` = `test`.`t1`.`i1` and `test`.`t2`.`v1` = `test`.`t1`.`v1` and `test`.`temp`.`f2` = `test`.`t1`.`v1` +DROP TABLE t1,t2,temp; +SET join_cache_level = default; +# # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. # set join_cache_level=default; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index b775725903c..df89fc30dee 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -3870,6 +3870,37 @@ set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; --echo # +--echo # MDEV-16603: BNLH for query with materialized semi-join +--echo # + +--source include/have_innodb.inc + +set join_cache_level=4; + +CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (7,'x'); + +CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + +INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + +CREATE TABLE temp +SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + +let $q = +SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,temp; + +SET join_cache_level = default; + +--echo # --echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. --echo # @@ -3958,5 +3989,4 @@ drop table t1, t2; set join_buffer_size = default; # The following command must be the last one the file -# this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 6753349dece..9245111369f 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2460,5 +2460,55 @@ id sid id 1 NULL NULL 2 NULL NULL drop table t1, t2; +# +# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN +# converted to INNER JOIN with first constant inner table +# +CREATE TABLE t1 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) +) engine=MyISAM; +INSERT INTO t1 VALUES +(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), +(14,226,'m','m'),(15,133,'p','p'); +CREATE TABLE t2 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) +) engine=MyISAM; +INSERT INTO t2 VALUES (10,6,'p','p'); +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t2,t1) +LEFT JOIN +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL +SELECT STRAIGHT_JOIN DISTINCT t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +v2 +DROP TABLE t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index 34b0551a32e..6d20c089bd9 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -1992,6 +1992,54 @@ select * from t1 t on t.id=r.id ; drop table t1, t2; +--echo # +--echo # MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN +--echo # converted to INNER JOIN with first constant inner table +--echo # + +CREATE TABLE t1 ( + pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) +) engine=MyISAM; +INSERT INTO t1 VALUES + (8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), + (14,226,'m','m'),(15,133,'p','p'); + +CREATE TABLE t2 ( + pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) +) engine=MyISAM; +INSERT INTO t2 VALUES (10,6,'p','p'); + +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM + (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) + RIGHT JOIN + (t2,t1) + ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; + +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM + (t2,t1) + LEFT JOIN + (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) + ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; + +SELECT STRAIGHT_JOIN DISTINCT t2.v2 +FROM + (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) + RIGHT JOIN + (t2,t1) + ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; + +DROP TABLE t1,t2; + --echo # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 9ff501baf6a..cb582b1399f 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2471,6 +2471,56 @@ id sid id 1 NULL NULL 2 NULL NULL drop table t1, t2; +# +# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN +# converted to INNER JOIN with first constant inner table +# +CREATE TABLE t1 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) +) engine=MyISAM; +INSERT INTO t1 VALUES +(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), +(14,226,'m','m'),(15,133,'p','p'); +CREATE TABLE t2 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) +) engine=MyISAM; +INSERT INTO t2 VALUES (10,6,'p','p'); +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t2,t1) +LEFT JOIN +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL +SELECT STRAIGHT_JOIN DISTINCT t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +v2 +DROP TABLE t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; diff --git a/mysql-test/main/rename.result b/mysql-test/main/rename.result index 3ee9dd593d4..da9e4b62ecd 100644 --- a/mysql-test/main/rename.result +++ b/mysql-test/main/rename.result @@ -144,3 +144,7 @@ select * from t2; a 1 drop table tmp,t2; +create table t1 (a int) engine=memory; +rename table t1 to non_existent.t2; +ERROR 42000: Unknown database 'non_existent' +drop table t1; diff --git a/mysql-test/main/rename.test b/mysql-test/main/rename.test index 67732d5b5b9..215ecbcbb18 100644 --- a/mysql-test/main/rename.test +++ b/mysql-test/main/rename.test @@ -141,3 +141,10 @@ select * from tmp; select * from t2; drop table tmp,t2; +# +# MDEV-11741 handler::ha_reset(): Assertion `bitmap_is_set_all(&table->s->all_set)' failed or server crash in mi_reset or buffer overrun or unexpected ER_CANT_REMOVE_ALL_FIELDS +# +create table t1 (a int) engine=memory; +--error ER_BAD_DB_ERROR +rename table t1 to non_existent.t2; +drop table t1; diff --git a/mysql-test/main/session_tracker_last_gtid.result b/mysql-test/main/session_tracker_last_gtid.result index 795d0aaa2a2..deffeca1333 100644 --- a/mysql-test/main/session_tracker_last_gtid.result +++ b/mysql-test/main/session_tracker_last_gtid.result @@ -2,7 +2,13 @@ # MDEV-15477: SESSION_SYSVARS_TRACKER does not track last_gtid # SET gtid_seq_no=1000; +-- Tracker : SESSION_TRACK_SCHEMA +-- test + SET @@session.session_track_system_variables='last_gtid'; +-- Tracker : SESSION_TRACK_SCHEMA +-- test + create table t1 (a int) engine=innodb; -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES -- last_gtid diff --git a/mysql-test/main/sp_notembedded.result b/mysql-test/main/sp_notembedded.result index 190f8f78d23..8edb9e79b70 100644 --- a/mysql-test/main/sp_notembedded.result +++ b/mysql-test/main/sp_notembedded.result @@ -228,8 +228,6 @@ FLUSH PRIVILEGES; connect con1, localhost, mysqltest_1,,; connection con1; CREATE PROCEDURE p1(i INT) BEGIN END; -Warnings: -Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges disconnect con1; connection default; DROP PROCEDURE p1; diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index fcced761283..ceadb61feea 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -516,4 +516,133 @@ use test; drop database db1; drop database db2; drop table t1; +# +# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +# +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) +NULL +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +SELECT MAX(pk) FROM t1; +MAX(pk) +NULL +DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index bd77dec2b77..2cb6b55f665 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -305,4 +305,116 @@ drop database db1; drop database db2; drop table t1; +--echo # +--echo # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +--echo # + +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16757: manual addition of min/max statistics for BLOB +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +--sorted_result +SELECT * FROM mysql.column_stats; +DELETE FROM mysql.column_stats + WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES + ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +--sorted_result +SELECT * FROM mysql.column_stats; + +SELECT pk FROM t1; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16757: manual addition of min/max statistics for BLOB +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +--sorted_result +SELECT * FROM mysql.column_stats; +DELETE FROM mysql.column_stats + WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES + ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +--sorted_result +SELECT * FROM mysql.column_stats; + +SELECT pk FROM t1; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +SELECT * FROM mysql.column_stats; + +DROP TABLE t1; + +set use_stat_tables=@save_use_stat_tables; + + +--echo # +--echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +--echo # + +SET use_stat_tables= PREFERABLY; + +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); + +SELECT MAX(pk) FROM t1; + +DROP TABLE t1; + set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 42443bfcb72..7b98ca4259f 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -543,6 +543,135 @@ use test; drop database db1; drop database db2; drop table t1; +# +# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +# +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) +NULL +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +SELECT MAX(pk) FROM t1; +MAX(pk) +NULL +DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index d5034bc49b0..fb651fe6e2d 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7237,6 +7237,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index 37d57e51f1b..86ac9649d16 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -6098,6 +6098,21 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests --echo # End of 10.0 tests diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 39c0d1dd22b..e208dfe74c1 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2391,6 +2391,99 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-16751: Server crashes in st_join_table::cleanup or +# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +# +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +drop table t1,t2; +# +# MDEV-15454: Nested SELECT IN returns wrong results +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); +CREATE PROCEDURE prepare_data() +BEGIN +DECLARE i INT DEFAULT 1; +WHILE i < 1000 DO +INSERT INTO t1 (a) VALUES (i); +INSERT INTO t2 (a,b) VALUES (i,56); +INSERT INTO t3 (c,b) VALUES (i,i); +SET i = i + 1; +END WHILE; +END$$ +CALL prepare_data(); +SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); +a +7878 +3465 +1403 +4189 +8732 +5 +set @save_optimizer_switch= @@optimizer_switch; +SET optimizer_switch='materialization=off'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +SET optimizer_switch='materialization=on'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +drop procedure prepare_data; +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +1 +1 +1 +1 +drop table t1,t2; +drop view v1; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index 658722112d2..6377ae556d2 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -442,7 +442,7 @@ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 -2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index +2 DEPENDENT SUBQUERY t2 index i2 i2 11 NULL 2 Using where; Using index DROP TABLE t1,t2,t3; # # MDEV-7599: in-to-exists chosen after min/max optimization diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index afbd82fb359..655f3f4e9ce 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7237,6 +7237,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 2e9caf22ca0..f766f5b6d6a 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7230,6 +7230,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 2e82f98e01e..59dc3fd8328 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7228,6 +7228,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 0dc83d85a74..6a8939aef3c 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7243,6 +7243,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 18333df11cb..b088bbc310d 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7228,6 +7228,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 62248536e34..884451d7dff 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1728,6 +1728,57 @@ id 13 drop table t1; # +# MDEV-15982: Incorrect results when subquery is materialized +# +CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); +INSERT INTO `t1` VALUES +(45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), +(63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), +(81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), +(99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), +(114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), +(129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), +(147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), +(162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), +(174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), +(7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); +CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); +INSERT INTO `t2` VALUES +(2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), +(1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), +(2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); +CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); +INSERT INTO `t3` VALUES +(1,'incident',31),(2,'faux pas',32), +(5,'oopsies',33),(3,'deniable',34), +(11,'wasntme',35),(10,'wasntme',36), +(17,'faux pas',37),(13,'unlikely',38), +(13,'improbable',39),(14,'incident',40), +(26,'problem',41),(14,'problem',42), +(26,'incident',43),(27,'incident',44); +explain +SELECT t2.id FROM t2,t1 +WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 +2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index +SELECT t2.id FROM t2,t1 +WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +id +10 +11 +set optimizer_switch='materialization=off'; +SELECT t2.id FROM t2,t1 +WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +id +11 +10 +set optimizer_switch='materialization=on'; +DROP TABLE t1,t2,t3; +# # MDEV-15247: Crash when SET NAMES 'utf8' is set # CREATE TABLE t1 ( diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test index bba436078e3..6ae687aa99a 100644 --- a/mysql-test/main/subselect_sj2_mat.test +++ b/mysql-test/main/subselect_sj2_mat.test @@ -347,6 +347,55 @@ WHERE ( drop table t1; --echo # +--echo # MDEV-15982: Incorrect results when subquery is materialized +--echo # + +CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); +INSERT INTO `t1` VALUES +(45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), +(63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), +(81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), +(99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), +(114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), +(129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), +(147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), +(162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), +(174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), +(7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); + +CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); +INSERT INTO `t2` VALUES +(2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), +(1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), +(2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); + +CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); +INSERT INTO `t3` VALUES +(1,'incident',31),(2,'faux pas',32), +(5,'oopsies',33),(3,'deniable',34), +(11,'wasntme',35),(10,'wasntme',36), +(17,'faux pas',37),(13,'unlikely',38), +(13,'improbable',39),(14,'incident',40), +(26,'problem',41),(14,'problem',42), +(26,'incident',43),(27,'incident',44); + +explain +SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + +SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + +set optimizer_switch='materialization=off'; + +SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +set optimizer_switch='materialization=on'; + +DROP TABLE t1,t2,t3; + + +--echo # --echo # MDEV-15247: Crash when SET NAMES 'utf8' is set --echo # diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index b241be77df7..7630b0b9c0f 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -2431,6 +2431,99 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-16751: Server crashes in st_join_table::cleanup or +# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +# +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +drop table t1,t2; +# +# MDEV-15454: Nested SELECT IN returns wrong results +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); +CREATE PROCEDURE prepare_data() +BEGIN +DECLARE i INT DEFAULT 1; +WHILE i < 1000 DO +INSERT INTO t1 (a) VALUES (i); +INSERT INTO t2 (a,b) VALUES (i,56); +INSERT INTO t3 (c,b) VALUES (i,i); +SET i = i + 1; +END WHILE; +END$$ +CALL prepare_data(); +SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); +a +7878 +3465 +1403 +4189 +8732 +5 +set @save_optimizer_switch= @@optimizer_switch; +SET optimizer_switch='materialization=off'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +SET optimizer_switch='materialization=on'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +drop procedure prepare_data; +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +1 +1 +1 +1 +drop table t1,t2; +drop view v1; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test index 66c11b61435..7b8179bba38 100644 --- a/mysql-test/main/subselect_sj_mat.test +++ b/mysql-test/main/subselect_sj_mat.test @@ -2157,6 +2157,85 @@ eval $q; DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-16751: Server crashes in st_join_table::cleanup or +--echo # TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +--echo # + +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); + +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); + +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); + +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); + +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); + +drop table t1,t2; + +--echo # +--echo # MDEV-15454: Nested SELECT IN returns wrong results +--echo # + +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); + +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); + +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); + +DELIMITER $$; +CREATE PROCEDURE prepare_data() +BEGIN + DECLARE i INT DEFAULT 1; + WHILE i < 1000 DO + INSERT INTO t1 (a) VALUES (i); + INSERT INTO t2 (a,b) VALUES (i,56); + INSERT INTO t3 (c,b) VALUES (i,i); + SET i = i + 1; + END WHILE; +END$$ +DELIMITER ;$$ + +CALL prepare_data(); + +SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); + +set @save_optimizer_switch= @@optimizer_switch; +SET optimizer_switch='materialization=off'; + +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SET optimizer_switch='materialization=on'; + +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +drop procedure prepare_data; +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2,t3; + +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +drop table t1,t2; +drop view v1; --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index fe80f67045c..0c6ddd3675a 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2149,6 +2149,22 @@ DROP VIEW v1; UNION (SELECT 2, 2); ERROR 42S02: Table 'test.v1' doesn't exist +# +# Bug#27197235 USER VARIABLE + UINON + DECIMAL COLUMN RETURNS +# WRONG VALUES +# +SET NAMES utf8; +SET @advertAcctId = 1000003; +select @advertAcctId as a from dual union all select 1.0 from dual; +a +1000003.0 +1.0 +SET NAMES latin1; +SET @advertAcctId = 1000003; +select @advertAcctId as a from dual union all select 1.0 from dual; +a +1000003.0 +1.0 End of 5.5 tests # # WL#1763 Avoid creating temporary table in UNION ALL diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index d0a8e335d88..ca14731600e 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -1480,6 +1480,21 @@ DROP VIEW v1; UNION (SELECT 2, 2); +--echo # +--echo # Bug#27197235 USER VARIABLE + UINON + DECIMAL COLUMN RETURNS +--echo # WRONG VALUES +--echo # + +let $old_charset= `SELECT @@character_set_client`; + +SET NAMES utf8; +SET @advertAcctId = 1000003; +select @advertAcctId as a from dual union all select 1.0 from dual; + +eval SET NAMES $old_charset; +SET @advertAcctId = 1000003; +select @advertAcctId as a from dual union all select 1.0 from dual; + --echo End of 5.5 tests --echo # |