MDEV-6978 Bad results with join comparing case insensitive VARCHAR/ENUM/SET

expression to a _bin ENUM column

2 files changed, 25 insertions, 1 deletions

diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc
index 9c10790f9c0..6434c36aaf2 100644
--- a/sql/opt_table_elimination.cc
+++ b/sql/opt_table_elimination.cc
@@ -1501,7 +1501,9 @@ void check_equality(Dep_analysis_context *ctx, Dep_module_expr **eq_mod,
           We can't assume there's a functional dependency if the effective
           collation of the operation differ from the field collation.
         */
-        if (field->cmp_type() == STRING_RESULT &&
+        if ((field->cmp_type() == STRING_RESULT ||
+            field->real_type() == MYSQL_TYPE_ENUM ||
+            field->real_type() == MYSQL_TYPE_SET) &&
             field->charset() != cond->compare_collation())
           return;
       }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ce17adf44d4..180f9d8642b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4445,6 +4445,28 @@ add_key_field(JOIN *join,
         return;
 
       /*
+        Note, for ITEM/ENUM columns:
+        - field->cmp_type() returns INT_RESULT
+        - field->result_type() returns STRING_RESULT
+        - field->type() returns MYSQL_TYPE_STRING
+
+        Using field->real_type() to detect ENUM/SET,
+        as they need a special handling:
+        - Conditions between a ENUM/SET filter and a TIME expression
+          cannot be optimized. They were filtered out in the previous if block.
+        - It's Ok to use ref access for an ENUM/SET field compared to an
+          INT/REAL/DECIMAL expression.
+        - It's Ok to use ref for an ENUM/SET field compared to a STRING
+          expression if the collation of the field and the collation of
+          the condition match.
+      */
+      if ((field->real_type() == MYSQL_TYPE_ENUM ||
+           field->real_type() == MYSQL_TYPE_SET) &&
+          (*value)->cmp_type () == STRING_RESULT &&
+          field->charset() != cond->compare_collation())
+        return;
+
+      /*
 	We can't use indexes when comparing a string index to a
 	number or two strings if the effective collation
         of the operation differ from the field collation.