From ab58493db22d870cb6a470d6b0551cfc70dd09f3 Mon Sep 17 00:00:00 2001
From: Alexander Barkov <bar@mariadb.com>
Date: Thu, 19 Jul 2018 09:55:19 +0400
Subject: MDEV-13118 Wrong results with LOWER and UPPER and subquery

This problem is similar to MDEV-10306.

1. Fixing Item_str_conv::val_str(String *str) to return the result in "str",
   and to use tmp_value only as a temporary buffer for args[0]->val_str().
   The new code version now guarantees that the result is always returned in
   "str". The trick with copy_if_not_alloced() is not used any more.

2. The change #1 revealed the same problem in SUBSTRING_INDEX(),
   so some tests with combinations of UPPER()/LOWER() and SUBSTRING_INDEX()
   started to fail. Fixing Item_func_substr_index::val_str() the same way,
   to return the result in "str" and use tmp_value as a temporary buffer
   for args[0]->val_str().
---
 mysql-test/include/ctype_mdev13118.inc | 15 +++++++++++++++
 mysql-test/r/ctype_binary.result       | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_eucjpms.result      | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_euckr.result        | 29 +++++++++++++++++++++++++++++
 mysql-test/r/ctype_gbk.result          | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_latin1.result       | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_ucs.result          | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_ujis.result         | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_utf16.result        | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_utf16le.result      | 29 +++++++++++++++++++++++++++++
 mysql-test/r/ctype_utf32.result        | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_utf8.result         | 23 +++++++++++++++++++++++
 mysql-test/r/ctype_utf8mb4.result      | 23 +++++++++++++++++++++++
 mysql-test/t/ctype_binary.test         |  3 +++
 mysql-test/t/ctype_eucjpms.test        |  2 ++
 mysql-test/t/ctype_euckr.test          | 11 +++++++++++
 mysql-test/t/ctype_gbk.test            |  3 +++
 mysql-test/t/ctype_latin1.test         |  3 +++
 mysql-test/t/ctype_ucs.test            |  4 ++++
 mysql-test/t/ctype_ujis.test           |  4 ++++
 mysql-test/t/ctype_utf16.test          |  5 +++++
 mysql-test/t/ctype_utf16le.test        | 13 +++++++++++++
 mysql-test/t/ctype_utf32.test          |  8 ++++++++
 mysql-test/t/ctype_utf8.test           |  7 +++++++
 mysql-test/t/ctype_utf8mb4.test        |  8 ++++++++
 25 files changed, 374 insertions(+)
 create mode 100644 mysql-test/include/ctype_mdev13118.inc

(limited to 'mysql-test')

diff --git a/mysql-test/include/ctype_mdev13118.inc b/mysql-test/include/ctype_mdev13118.inc
new file mode 100644
index 00000000000..efcb57299df
--- /dev/null
+++ b/mysql-test/include/ctype_mdev13118.inc
@@ -0,0 +1,15 @@
+--echo #
+--echo # MDEV-13118 Wrong results with LOWER and UPPER and subquery
+--echo #
+
+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;
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+--sorted_result
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+--sorted_result
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 48b481883ca..b28d9040547 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -3022,6 +3022,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) NOT NULL DEFAULT ''
+) 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/r/ctype_eucjpms.result b/mysql-test/r/ctype_eucjpms.result
index a1232c115e9..1e2312638c9 100644
--- a/mysql-test/r/ctype_eucjpms.result
+++ b/mysql-test/r/ctype_eucjpms.result
@@ -33636,6 +33636,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 NOT NULL DEFAULT ''
+) 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/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result
index dcb68cfe60b..cea93b0c808 100644
--- a/mysql-test/r/ctype_euckr.result
+++ b/mysql-test/r/ctype_euckr.result
@@ -25274,3 +25274,32 @@ 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 NOT NULL DEFAULT ''
+) 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/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result
index 2d31a15bb62..15f9bc6bfea 100644
--- a/mysql-test/r/ctype_gbk.result
+++ b/mysql-test/r/ctype_gbk.result
@@ -4943,6 +4943,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 NOT NULL DEFAULT ''
+) 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/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index cf48aaab09b..8913c8082c8 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -7939,6 +7939,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) NOT NULL DEFAULT ''
+) 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/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 59d88414cab..58fd308c7e2 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -5629,6 +5629,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 NOT NULL DEFAULT ''
+) 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/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
index 413ab4efe31..d4589f62ae5 100644
--- a/mysql-test/r/ctype_ujis.result
+++ b/mysql-test/r/ctype_ujis.result
@@ -25942,6 +25942,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 NOT NULL DEFAULT ''
+) 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/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index 958ade902df..e182432faec 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -2134,6 +2134,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 NOT NULL DEFAULT ''
+) 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/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result
index 8098b0d1666..f6a4d351ad4 100644
--- a/mysql-test/r/ctype_utf16le.result
+++ b/mysql-test/r/ctype_utf16le.result
@@ -2319,3 +2319,32 @@ 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 NOT NULL DEFAULT ''
+) 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
+#
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index 1f3e519a525..91277cd5108 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -2231,6 +2231,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 NOT NULL DEFAULT ''
+) 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/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 7a3ff3732c3..6a4001597c4 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -10127,6 +10127,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 NOT NULL DEFAULT ''
+) 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/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index f31b7a6781e..4d91c42cf51 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -3427,6 +3427,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 NOT NULL DEFAULT ''
+) 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
 #
 # End of tests
diff --git a/mysql-test/t/ctype_binary.test b/mysql-test/t/ctype_binary.test
index 3d3f90b444b..801e1328892 100644
--- a/mysql-test/t/ctype_binary.test
+++ b/mysql-test/t/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/t/ctype_eucjpms.test b/mysql-test/t/ctype_eucjpms.test
index 49ca81850ed..590f2ca9950 100644
--- a/mysql-test/t/ctype_eucjpms.test
+++ b/mysql-test/t/ctype_eucjpms.test
@@ -536,6 +536,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/t/ctype_euckr.test b/mysql-test/t/ctype_euckr.test
index 155b8ebed00..d81ba2a8bdf 100644
--- a/mysql-test/t/ctype_euckr.test
+++ b/mysql-test/t/ctype_euckr.test
@@ -197,3 +197,14 @@ 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 #
diff --git a/mysql-test/t/ctype_gbk.test b/mysql-test/t/ctype_gbk.test
index 454377d98a7..bcdcc09b473 100644
--- a/mysql-test/t/ctype_gbk.test
+++ b/mysql-test/t/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/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test
index 8a188b71e24..da4d76ee2cf 100644
--- a/mysql-test/t/ctype_latin1.test
+++ b/mysql-test/t/ctype_latin1.test
@@ -260,6 +260,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/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index d78977a3d1c..050e199e5b7 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -984,6 +984,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/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
index 48dc0e63058..8d55819acf7 100644
--- a/mysql-test/t/ctype_ujis.test
+++ b/mysql-test/t/ctype_ujis.test
@@ -1366,6 +1366,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/t/ctype_utf16.test b/mysql-test/t/ctype_utf16.test
index cda85239512..b562b15eeeb 100644
--- a/mysql-test/t/ctype_utf16.test
+++ b/mysql-test/t/ctype_utf16.test
@@ -866,6 +866,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/t/ctype_utf16le.test b/mysql-test/t/ctype_utf16le.test
index a8326900847..f5b89aa9dc4 100644
--- a/mysql-test/t/ctype_utf16le.test
+++ b/mysql-test/t/ctype_utf16le.test
@@ -744,3 +744,16 @@ SET NAMES utf8, collation_connection=utf16le_bin;
 --echo #
 --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 #
diff --git a/mysql-test/t/ctype_utf32.test b/mysql-test/t/ctype_utf32.test
index c747abb8b08..1a4c9fd28ee 100644
--- a/mysql-test/t/ctype_utf32.test
+++ b/mysql-test/t/ctype_utf32.test
@@ -979,6 +979,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/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index 2524daffa9e..11dfef67765 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -1880,6 +1880,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/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test
index f68cfd57e2a..89007546492 100644
--- a/mysql-test/t/ctype_utf8mb4.test
+++ b/mysql-test/t/ctype_utf8mb4.test
@@ -1913,6 +1913,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 # End of 10.0 tests
 
 --echo #
-- 
cgit v1.2.1


From bd5cf02bbe7bce029b0275be1b15d2108806d5e9 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Fri, 13 Jul 2018 16:54:47 +0200
Subject: 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

MEMORY table could be renamed into a non-extistent database.

rename() is documented to return ENOENT when the source file does not
exist OR when the target directory not exist. Nonexistent source .frm
file is ok (table can still exist in the engine), nonexistent target
directory is not.

Make my_rename to use ENOTDIR for the latter case. Make RENAME TABLE
issue an appropriate error ("unknown database" instead of "unknown table")
---
 mysql-test/r/rename.result | 4 ++++
 mysql-test/t/rename.test   | 7 +++++++
 2 files changed, 11 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result
index c90aaf24e9b..f019527c951 100644
--- a/mysql-test/r/rename.result
+++ b/mysql-test/r/rename.result
@@ -133,3 +133,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/t/rename.test b/mysql-test/t/rename.test
index 67732d5b5b9..215ecbcbb18 100644
--- a/mysql-test/t/rename.test
+++ b/mysql-test/t/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;
-- 
cgit v1.2.1


From 40f29ecbf1c8bf36be443c40382fe8cdc8a7f413 Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Sat, 14 Jul 2018 00:51:23 +0200
Subject: MDEV-13397 MariaDB upgrade fail when using default_time_zone

don't try to set default time zone in --bootstrap,
this generally cannot be done, as timezone tables aren't loaded.
and bootstrap scripts don't need it anyway.
---
 mysql-test/t/bootstrap.test | 9 +++++++++
 1 file changed, 9 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test
index 5ab736cee15..3df3919220e 100644
--- a/mysql-test/t/bootstrap.test
+++ b/mysql-test/t/bootstrap.test
@@ -105,3 +105,12 @@ use test;
 EOF
 --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
-- 
cgit v1.2.1


From d57ddaa1904d3c2f08460c539c72652f0c78c09a Mon Sep 17 00:00:00 2001
From: Sergei Golubchik <serg@mariadb.org>
Date: Mon, 16 Jul 2018 15:12:38 +0200
Subject: MDEV-15551 Server hangs or assertion
 `strcmp(share->unique_file_name,filename) || share->last_version' fails in
 test_if_reopen or unexpected ER_LOCK_DEADLOCK

only use HA_EXTRA_PREPARE_FOR_DROP when the table is going to be dropped
---
 mysql-test/suite/parts/r/truncate_locked.result |  7 +++++++
 mysql-test/suite/parts/t/truncate_locked.test   | 10 ++++++++++
 2 files changed, 17 insertions(+)
 create mode 100644 mysql-test/suite/parts/r/truncate_locked.result
 create mode 100644 mysql-test/suite/parts/t/truncate_locked.test

(limited to 'mysql-test')

diff --git a/mysql-test/suite/parts/r/truncate_locked.result b/mysql-test/suite/parts/r/truncate_locked.result
new file mode 100644
index 00000000000..54a3722938e
--- /dev/null
+++ b/mysql-test/suite/parts/r/truncate_locked.result
@@ -0,0 +1,7 @@
+create table t1 (i int) engine=myisam partition by hash(i) partitions 2 ;
+lock table t1 write;
+truncate table t1;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+i	int(11)	YES		NULL	
+drop table t1;
diff --git a/mysql-test/suite/parts/t/truncate_locked.test b/mysql-test/suite/parts/t/truncate_locked.test
new file mode 100644
index 00000000000..4ff1016fb05
--- /dev/null
+++ b/mysql-test/suite/parts/t/truncate_locked.test
@@ -0,0 +1,10 @@
+#
+# MDEV-15551 Server hangs or assertion `strcmp(share->unique_file_name,filename) || share->last_version' fails in test_if_reopen or unexpected ER_LOCK_DEADLOCK
+#
+--source include/have_partition.inc
+create table t1 (i int) engine=myisam partition by hash(i) partitions 2 ;
+lock table t1 write;
+truncate table t1;
+desc t1;
+drop table t1;
+
-- 
cgit v1.2.1


From 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af Mon Sep 17 00:00:00 2001
From: Igor Babaev <igor@askmonty.org>
Date: Thu, 19 Jul 2018 15:31:30 -0700
Subject: MDEV-16726 Assertion `tab->type == JT_REF || tab->type == JT_EQ_REF'
 failed

Due to a legacy bug in the code of make_join_statistics() detecting
so-called constant tables could miss some of them in rare queries
that used RIGHT JOIN. As a result these queries had execution plans
different from the execution plans of the equivalent queries with
LEFT JOIN.
Besides starting from 10.2 this could trigger an assertion failure.
---
 mysql-test/r/join_outer.result              | 50 +++++++++++++++++++++++++++++
 mysql-test/r/join_outer_jcl6.result         | 50 +++++++++++++++++++++++++++++
 mysql-test/r/subselect_mat_cost_bugs.result |  2 +-
 mysql-test/t/join_outer.test                | 48 +++++++++++++++++++++++++++
 4 files changed, 149 insertions(+), 1 deletion(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 67b22ca86b2..d55f11cc2f2 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2368,5 +2368,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(`test`.`tb2`.`v1`, 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(`test`.`tb2`.`v1`, 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/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index c019da6197b..8a9b395edff 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2379,6 +2379,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(`test`.`tb2`.`v1`, 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(`test`.`tb2`.`v1`, 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/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index d33f1488e4d..03f4b3e1903 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/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/t/join_outer.test b/mysql-test/t/join_outer.test
index 2769aea9969..305421c10d5 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1911,6 +1911,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;
-- 
cgit v1.2.1


From e0139c2b92ba1f8819a3ed75d020d9665545e2bf Mon Sep 17 00:00:00 2001
From: Oleksandr Byelkin <sanja@mariadb.com>
Date: Tue, 24 Jul 2018 18:16:41 +0200
Subject: fix plugins.processlist

make it not to fail when `show engine innodb status` output
contains a double quote
---
 mysql-test/suite/plugins/t/processlist.test | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test
index 5aacef317b9..e8f03aacb10 100644
--- a/mysql-test/suite/plugins/t/processlist.test
+++ b/mysql-test/suite/plugins/t/processlist.test
@@ -8,7 +8,8 @@ start transaction;
 insert t1 values (1);
 let id=`select connection_id()`;
 connect con2,localhost,root;
-let s=query_get_value(show engine innodb status,Status,1);
+replace_regex /\"/-/; #"
+let s=`show engine innodb status`;
 disable_query_log;
 eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status`;
 eval select state as `state from show processlist` from information_schema.processlist where id = $id;
-- 
cgit v1.2.1


From 9fbe360e9f7d41d169eb5abed22dc15f26f6c749 Mon Sep 17 00:00:00 2001
From: Oleksandr Byelkin <sanja@mariadb.com>
Date: Tue, 24 Jul 2018 18:24:21 +0200
Subject: make plugins.processlist more robust

---
 mysql-test/suite/plugins/r/processlist.result | 4 +---
 mysql-test/suite/plugins/t/processlist.test   | 5 +++--
 2 files changed, 4 insertions(+), 5 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/suite/plugins/r/processlist.result b/mysql-test/suite/plugins/r/processlist.result
index d08ea9d3523..9200c865533 100644
--- a/mysql-test/suite/plugins/r/processlist.result
+++ b/mysql-test/suite/plugins/r/processlist.result
@@ -1,8 +1,6 @@
 create table t1 (a int) engine=innodb;
 start transaction;
 insert t1 values (1);
-state from show engine innodb status
+state from show engine innodb status, must be empty
  
-state from show processlist
-
 drop table t1;
diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test
index e8f03aacb10..39b715b867b 100644
--- a/mysql-test/suite/plugins/t/processlist.test
+++ b/mysql-test/suite/plugins/t/processlist.test
@@ -8,11 +8,12 @@ start transaction;
 insert t1 values (1);
 let id=`select connection_id()`;
 connect con2,localhost,root;
+let $wait_condition=select state='' from information_schema.processlist where id = $id;
+--source include/wait_condition.inc
 replace_regex /\"/-/; #"
 let s=`show engine innodb status`;
 disable_query_log;
-eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status`;
-eval select state as `state from show processlist` from information_schema.processlist where id = $id;
+eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status, must be empty`;
 enable_query_log;
 disconnect con2;
 connection default;
-- 
cgit v1.2.1