MDEV-8785 Wrong results for EXPLAIN EXTENDED...WHERE NULLIF(latin1_col, _utf8'a' COLLATE utf8_bin) IS NOT NULL

3 files changed, 75 insertions, 7 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 3bfee49560c..04afafd2915 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2677,6 +2677,70 @@ Item_func_nullif::fix_length_and_dec()
 }
 
 
+void Item_func_nullif::print(String *str, enum_query_type query_type)
+{
+  /*
+    NULLIF(a,b) is implemented according to the SQL standard as a short for
+    CASE WHEN a=b THEN NULL ELSE a END
+
+    The constructor of Item_func_nullif sets args[0] and m_args0_copy to the
+    same item "a", and sets args[1] to "b".
+
+    If "this" is a part of a WHERE or ON condition, then:
+    - the left "a" is a subject to equal field propagation with ANY_SUBST.
+    - the right "a" is a subject to equal field propagation with IDENTITY_SUBST.
+    Therefore, after equal field propagation args[0] and m_args0_copy can point
+    to different items.
+  */
+  if (!(query_type & QT_ITEM_FUNC_NULLIF_TO_CASE) || args[0] == m_args0_copy)
+  {
+    /*
+      If no QT_ITEM_FUNC_NULLIF_TO_CASE is requested,
+      that means we want the original NULLIF() representation,
+      e.g. when we are in:
+        SHOW CREATE {VIEW|FUNCTION|PROCEDURE}
+
+      The original representation is possible only if
+      args[0] and m_args0_copy still point to the same Item.
+
+      The caller must pass call print() with QT_ITEM_FUNC_NULLIF_TO_CASE
+      if an expression has undergone some optimization
+      (e.g. equal field propagation done in optimize_cond()) already and
+      NULLIF() potentially has two different representations of "a":
+      - one "a" for comparison
+      - another "a" for the returned value!
+
+      Note, the EXPLAIN EXTENDED and EXPLAIN FORMAT=JSON routines
+      do pass QT_ITEM_FUNC_NULLIF_TO_CASE to print().
+    */
+    DBUG_ASSERT(args[0] == m_args0_copy);
+    str->append(func_name());
+    str->append('(');
+    m_args0_copy->print(str, query_type);
+    str->append(',');
+    args[1]->print(str, query_type);
+    str->append(')');
+  }
+  else
+  {
+    /*
+      args[0] and m_args0_copy are different items.
+      This is possible after WHERE optimization (equal fields propagation etc),
+      e.g. in EXPLAIN EXTENDED or EXPLAIN FORMAT=JSON.
+      As it's not possible to print as a function with 2 arguments any more,
+      do it in the CASE style.
+    */
+    str->append(STRING_WITH_LEN("(case when "));
+    args[0]->print(str, query_type);
+    str->append(STRING_WITH_LEN(" = "));
+    args[1]->print(str, query_type);
+    str->append(STRING_WITH_LEN(" then NULL else "));
+    m_args0_copy->print(str, query_type);
+    str->append(STRING_WITH_LEN(" end)"));
+  }
+}
+
+
 /**
   @note
   Note that we have to evaluate the first argument twice as the compare
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index e5d20e6e33c..436bbcd259a 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -928,12 +928,7 @@ public:
   void fix_length_and_dec();
   uint decimal_precision() const { return m_args0_copy->decimal_precision(); }
   const char *func_name() const { return "nullif"; }
-
-  virtual inline void print(String *str, enum_query_type query_type)
-  {
-    Item_func::print(str, query_type);
-  }
-
+  void print(String *str, enum_query_type query_type);
   table_map not_null_tables() const { return 0; }
   bool is_null();
   Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
diff --git a/sql/mysqld.h b/sql/mysqld.h
index f19c322c96b..bf1af3afb94 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -654,9 +654,18 @@ enum enum_query_type
   /// If Item_subselect should print as just "(subquery#1)"
   /// rather than display the subquery body
   QT_ITEM_SUBSELECT_ID_ONLY= (1 << 5),
+  /// If NULLIF(a,b) should print itself as
+  /// CASE WHEN a_for_comparison=b THEN NULL ELSE a_for_return_value END
+  /// when "a" was replaced to two different items
+  /// (e.g. by equal fields propagation in optimize_cond()).
+  /// The default behaviour is to print as NULLIF(a_for_return, b)
+  /// which should be Ok for SHOW CREATE {VIEW|PROCEDURE|FUNCTION}
+  /// as they are not affected by WHERE optimization.
+  QT_ITEM_FUNC_NULLIF_TO_CASE= (1 <<6),
 
   /// This value means focus on readability, not on ability to parse back, etc.
   QT_EXPLAIN=           QT_TO_SYSTEM_CHARSET |
+                        QT_ITEM_FUNC_NULLIF_TO_CASE |
                         QT_ITEM_IDENT_SKIP_CURRENT_DATABASE |
                         QT_ITEM_CACHE_WRAPPER_SKIP_DETAILS |
                         QT_ITEM_SUBSELECT_ID_ONLY,
@@ -665,7 +674,7 @@ enum enum_query_type
   /// Be more detailed than QT_EXPLAIN.
   /// Perhaps we should eventually include QT_ITEM_IDENT_SKIP_CURRENT_DATABASE
   /// here, as it would give better readable results
-  QT_EXPLAIN_EXTENDED=  QT_TO_SYSTEM_CHARSET
+  QT_EXPLAIN_EXTENDED=  QT_TO_SYSTEM_CHARSET | QT_ITEM_FUNC_NULLIF_TO_CASE
 };