MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM columnbb-10.2-MDEV-26129

Range optimizer incorrectly was used for ENUM columns
when the operation collation did not match the column collation.

Adding a virtual implementation of Field_enum::can_optimize_range()
which tests if the column and the operation collation match.

1 files changed, 20 insertions, 0 deletions

diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index 105da427219..2576b87ee51 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -454,3 +454,23 @@ SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 MODIFY a ENUM('2001','2002');
 SELECT * FROM t1;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column
+--echo #
+
+CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
+INSERT INTO t1 VALUES ('a');
+CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin);
+INSERT INTO t2 VALUES ('a'),('A');
+# without the following insert the bug doesn't show, was fixed in MDEV-6978
+INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E');
+ALTER TABLE t2 ADD PRIMARY KEY(a);
+SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
+SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
+DROP TABLE IF EXISTS t1,t2;
+
+
+--echo #
+--echo # End of 10.2. tests
+--echo #