1 files changed, 36 insertions, 0 deletions
diff --git a/mysql-test/t/type_varchar.test b/mysql-test/t/type_varchar.test
index 528d26d6f86..cc09069508f 100644
--- a/mysql-test/t/type_varchar.test
+++ b/mysql-test/t/type_varchar.test
@@ -240,6 +240,42 @@ SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
 EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1;
 DROP TABLE IF EXISTS t1,t2;
 
+
+--echo #
+--echo # MDEV-6989 BINARY and COLLATE xxx_bin comparisions are not used for optimization in some cases
+--echo #
+CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');
+SELECT * FROM t1 WHERE c1=BINARY 'a';
+EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a';
+SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
+EXPLAIN SELECT * FROM t1 WHERE c1=_latin1'a' COLLATE latin1_bin;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+ALTER TABLE t1 MODIFY c1 VARBINARY(10);
+SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t1 VALUES ('a'),('c');
+CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
+INSERT INTO t2 VALUES ('a'),('b');
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # t2 should be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+ALTER TABLE t1 MODIFY c1 VARBINARY(10);
+SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+--echo # t2 should be eliminated
+EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
+DROP TABLE t1,t2;
+
 --echo #
 --echo # End of 10.0 tests
 --echo #