MDEV-8705 Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'

MDEV-8712 Wrong result for SELECT..WHERE latin1_bin_column=_latin1'a' AND latin1_bin_column='A'

4 files changed, 61 insertions, 0 deletions

diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index 759777762e0..2d2b3473d57 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -8149,5 +8149,24 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (length(`test`.`t1`.`a`) = 2))
 DROP TABLE t1;
 #
+# MDEV-8712 Wrong result for SELECT..WHERE latin1_bin_column=_latin1'a' AND latin1_bin_column='A'
+#
+SET NAMES latin1;
+CREATE TABLE t1 (a VARCHAR(20) COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a'),('b');
+SELECT * FROM t1 WHERE a='A';
+a
+SELECT * FROM t1 WHERE a='A' AND a=_latin1'a';
+a
+SELECT * FROM t1 WHERE a=_latin1'a' AND a='A';
+a
+SELECT * FROM t1 WHERE a=_latin1'A';
+a
+SELECT * FROM t1 WHERE a=_latin1'A' AND a=_latin1'a';
+a
+SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A';
+a
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result
index 51f37d13aa7..2f7cb7156a4 100644
--- a/mysql-test/r/ctype_uca.result
+++ b/mysql-test/r/ctype_uca.result
@@ -13709,5 +13709,22 @@ SELECT '10' COLLATE utf8_general_ci XOR '20' COLLATE utf8_unicode_ci;
 '10' COLLATE utf8_general_ci XOR '20' COLLATE utf8_unicode_ci
 0
 #
+# MDEV-8705 Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'
+#
+SET NAMES utf8 COLLATE utf8_german2_ci;
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a';
+a
+a
+SELECT * FROM t1 WHERE a=_utf8'a';
+a
+a
+SELECT * FROM t1 WHERE a='a' AND a=_utf8'a';
+a
+a
+DROP TABLE t1;
+SET NAMES utf8;
+#
 # End of MariaDB-10.1 tests
 #
diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test
index d4ea4877e83..830fa075a39 100644
--- a/mysql-test/t/ctype_latin1.test
+++ b/mysql-test/t/ctype_latin1.test
@@ -349,6 +349,19 @@ SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='a' AND LENGTH(a)=2;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-8712 Wrong result for SELECT..WHERE latin1_bin_column=_latin1'a' AND latin1_bin_column='A'
+--echo #
+SET NAMES latin1;
+CREATE TABLE t1 (a VARCHAR(20) COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a'),('b');
+SELECT * FROM t1 WHERE a='A';
+SELECT * FROM t1 WHERE a='A' AND a=_latin1'a';
+SELECT * FROM t1 WHERE a=_latin1'a' AND a='A';
+SELECT * FROM t1 WHERE a=_latin1'A';
+SELECT * FROM t1 WHERE a=_latin1'A' AND a=_latin1'a';
+SELECT * FROM t1 WHERE a=_latin1'a' AND a=_latin1'A';
+DROP TABLE t1;
 
 --echo #
 --echo # End of 10.1 tests
diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test
index f30eda37154..3e5fa873e9b 100644
--- a/mysql-test/t/ctype_uca.test
+++ b/mysql-test/t/ctype_uca.test
@@ -630,6 +630,18 @@ DROP TABLE t1;
 --echo #
 SELECT '10' COLLATE utf8_general_ci XOR '20' COLLATE utf8_unicode_ci;
 
+--echo #
+--echo # MDEV-8705 Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'
+--echo #
+SET NAMES utf8 COLLATE utf8_german2_ci;
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin);
+INSERT INTO t1 VALUES ('a'),('A');
+SELECT * FROM t1 WHERE a='a';
+SELECT * FROM t1 WHERE a=_utf8'a';
+# Make sure this does not return "Illegal mix of collations"
+SELECT * FROM t1 WHERE a='a' AND a=_utf8'a';
+DROP TABLE t1;
+SET NAMES utf8;
 
 --echo #
 --echo # End of MariaDB-10.1 tests