MDEV-13863 sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent

5 files changed, 184 insertions, 11 deletions

diff --git a/sql/item.h b/sql/item.h
index f691333525c..5fb21858a7a 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -56,8 +56,23 @@ struct st_value
 C_MODE_END
 
 
+class Value: public st_value
+{
+public:
+  bool is_null() const { return m_type == DYN_COL_NULL; }
+  bool is_longlong() const
+  {
+    return m_type == DYN_COL_UINT || m_type == DYN_COL_INT;
+  }
+  bool is_double() const { return m_type == DYN_COL_DOUBLE; }
+  bool is_temporal() const { return m_type == DYN_COL_DATETIME; }
+  bool is_string() const { return m_type == DYN_COL_STRING; }
+  bool is_decimal() const { return m_type == DYN_COL_DECIMAL; }
+};
+
+
 template<size_t buffer_size>
-class ValueBuffer: public st_value
+class ValueBuffer: public Value
 {
   char buffer[buffer_size];
   void reset_buffer()
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index f3dafec0020..47c9fd57ee9 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2859,6 +2859,16 @@ Item *Item_func_case_simple::find_item()
 }
 
 
+Item *Item_func_decode_oracle::find_item()
+{
+  uint idx;
+  if (!Predicant_to_list_comparator::cmp_nulls_equal(this, &idx))
+    return args[idx + 1];
+  Item **pos= Item_func_decode_oracle::else_expr_addr();
+  return pos ? pos[0] : 0;
+}
+
+
 String *Item_func_case::str_op(String *str)
 {
   DBUG_ASSERT(fixed == 1);
@@ -2988,7 +2998,8 @@ static void change_item_tree_if_needed(THD *thd,
 
 
 bool Item_func_case_simple::prepare_predicant_and_values(THD *thd,
-                                                         uint *found_types)
+                                                         uint *found_types,
+                                                         bool nulls_equal)
 {
   bool have_null= false;
   uint type_cnt;
@@ -2997,7 +3008,9 @@ bool Item_func_case_simple::prepare_predicant_and_values(THD *thd,
   add_predicant(this, 0);
   for (uint i= 0 ; i < ncases; i++)
   {
-    if (add_value_skip_null("case..when", this, i * 2 + 1, &have_null))
+    if (nulls_equal ?
+        add_value("case..when", this, i * 2 + 1) :
+        add_value_skip_null("case..when", this, i * 2 + 1, &have_null))
       return true;
   }
   all_values_added(&tmp, &type_cnt, &m_found_types);
@@ -3021,7 +3034,16 @@ void Item_func_case_simple::fix_length_and_dec()
   THD *thd= current_thd;
   Item **else_ptr= Item_func_case_simple::else_expr_addr();
   if (!aggregate_then_and_else_arguments(thd, &args[2], when_count(), else_ptr))
-    aggregate_switch_and_when_arguments(thd);
+    aggregate_switch_and_when_arguments(thd, false);
+}
+
+
+void Item_func_decode_oracle::fix_length_and_dec()
+{
+  THD *thd= current_thd;
+  Item **else_ptr= Item_func_decode_oracle::else_expr_addr();
+  if (!aggregate_then_and_else_arguments(thd, &args[2], when_count(), else_ptr))
+    aggregate_switch_and_when_arguments(thd, true);
 }
 
 
@@ -3072,13 +3094,14 @@ bool Item_func_case::aggregate_then_and_else_arguments(THD *thd,
   Aggregate the predicant expression and all WHEN expression types
   and collations when string comparison
 */
-bool Item_func_case_simple::aggregate_switch_and_when_arguments(THD *thd)
+bool Item_func_case_simple::aggregate_switch_and_when_arguments(THD *thd,
+                                                                bool nulls_eq)
 {
   Item **agg= arg_buffer;
   uint nagg;
   uint ncases= when_count();
   m_found_types= 0;
-  if (prepare_predicant_and_values(thd, &m_found_types))
+  if (prepare_predicant_and_values(thd, &m_found_types, nulls_eq))
   {
     /*
       If Predicant_to_list_comparator() fails to prepare components,
@@ -3962,6 +3985,14 @@ void cmp_item_decimal::store_value(Item *item)
 }
 
 
+int cmp_item_decimal::cmp_not_null(const Value *val)
+{
+  DBUG_ASSERT(!val->is_null());
+  DBUG_ASSERT(val->is_decimal());
+  return my_decimal_cmp(&value, &val->m_decimal);
+}
+
+
 int cmp_item_decimal::cmp(Item *arg)
 {
   my_decimal tmp_buf, *tmp= arg->val_decimal(&tmp_buf);
@@ -3993,6 +4024,14 @@ void cmp_item_temporal::store_value_internal(Item *item,
 }
 
 
+int cmp_item_datetime::cmp_not_null(const Value *val)
+{
+  DBUG_ASSERT(!val->is_null());
+  DBUG_ASSERT(val->is_temporal());
+  return value != pack_time(&val->value.m_time);
+}
+
+
 int cmp_item_datetime::cmp(Item *arg)
 {
   const bool rc= value != arg->val_datetime_packed();
@@ -4000,6 +4039,14 @@ int cmp_item_datetime::cmp(Item *arg)
 }
 
 
+int cmp_item_time::cmp_not_null(const Value *val)
+{
+  DBUG_ASSERT(!val->is_null());
+  DBUG_ASSERT(val->is_temporal());
+  return value != pack_time(&val->value.m_time);
+}
+
+
 int cmp_item_time::cmp(Item *arg)
 {
   const bool rc= value != arg->val_time_packed();
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index ac992ee03cc..0756899f47c 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1475,6 +1475,7 @@ public:
      "stored argument's value <> item's value"
   */
   virtual int cmp(Item *item)= 0;
+  virtual int cmp_not_null(const Value *value)= 0;
   // for optimized IN with row
   virtual int compare(cmp_item *item)= 0;
   virtual cmp_item *make_same()= 0;
@@ -1519,6 +1520,12 @@ public:
     value_res= item->val_str(&value);
     m_null_value= item->null_value;
   }
+  int cmp_not_null(const Value *val)
+  {
+    DBUG_ASSERT(!val->is_null());
+    DBUG_ASSERT(val->is_string());
+    return sortcmp(value_res, &val->m_string, cmp_charset) != 0;
+  }
   int cmp(Item *arg)
   {
     char buff[STRING_BUFFER_USUAL_SIZE];
@@ -1555,6 +1562,12 @@ public:
     value= item->val_int();
     m_null_value= item->null_value;
   }
+  int cmp_not_null(const Value *val)
+  {
+    DBUG_ASSERT(!val->is_null());
+    DBUG_ASSERT(val->is_longlong());
+    return value != val->value.m_longlong;
+  }
   int cmp(Item *arg)
   {
     const bool rc= value != arg->val_int();
@@ -1599,6 +1612,7 @@ public:
   {
     store_value_internal(item, MYSQL_TYPE_DATETIME);
   }
+  int cmp_not_null(const Value *val);
   int cmp(Item *arg);
   cmp_item *make_same();
 };
@@ -1614,6 +1628,7 @@ public:
   {
     store_value_internal(item, MYSQL_TYPE_TIME);
   }
+  int cmp_not_null(const Value *val);
   int cmp(Item *arg);
   cmp_item *make_same();
 };
@@ -1628,6 +1643,12 @@ public:
     value= item->val_real();
     m_null_value= item->null_value;
   }
+  int cmp_not_null(const Value *val)
+  {
+    DBUG_ASSERT(!val->is_null());
+    DBUG_ASSERT(val->is_double());
+    return value != val->value.m_double;
+  }
   int cmp(Item *arg)
   {
     const bool rc= value != arg->val_real();
@@ -1649,6 +1670,7 @@ public:
   cmp_item_decimal() {}                       /* Remove gcc warning */
   void store_value(Item *item);
   int cmp(Item *arg);
+  int cmp_not_null(const Value *val);
   int compare(cmp_item *c);
   cmp_item *make_same();
 };
@@ -1671,6 +1693,11 @@ public:
     value_res= item->val_str(&value);
     m_null_value= item->null_value;
   }
+  int cmp_not_null(const Value *val)
+  {
+    DBUG_ASSERT(false);
+    return TRUE;
+  }
   int cmp(Item *item)
   {
     // Should never be called
@@ -1837,7 +1864,24 @@ class Predicant_to_list_comparator
       return UNKNOWN;
     return in_item->cmp(args->arguments()[m_comparators[i].m_arg_index]);
   }
-
+  int cmp_args_nulls_equal(Item_args *args, uint i)
+  {
+    Predicant_to_value_comparator *cmp=
+      &m_comparators[m_comparators[i].m_handler_index];
+    cmp_item *in_item= cmp->m_cmp_item;
+    DBUG_ASSERT(in_item);
+    Item *predicant= args->arguments()[m_predicant_index];
+    Item *arg= args->arguments()[m_comparators[i].m_arg_index];
+    ValueBuffer<MAX_FIELD_WIDTH> val;
+    if (m_comparators[i].m_handler_index == i)
+      in_item->store_value(predicant);
+    m_comparators[i].m_handler->Item_save_in_value(arg, &val);
+    if (predicant->null_value && val.is_null())
+      return FALSE; // Two nulls are equal
+    if (predicant->null_value || val.is_null())
+      return UNKNOWN;
+    return in_item->cmp_not_null(&val);
+  }
   /**
     Predicant_to_value_comparator - a comparator for one pair (pred,valueN).
     See comments above.
@@ -2009,7 +2053,22 @@ public:
     }
     return true; // Not found
   }
-
+  /*
+    Same as above, but treats two NULLs as equal, e.g. as in DECODE_ORACLE().
+  */
+  bool cmp_nulls_equal(Item_args *args, uint *idx)
+  {
+    for (uint i= 0 ; i < m_comparator_count ; i++)
+    {
+      DBUG_ASSERT(m_comparators[i].m_handler != NULL);
+      if (cmp_args_nulls_equal(args, i) == FALSE)
+      {
+        *idx= m_comparators[i].m_arg_index;
+        return false; // Found a matching value
+      }
+    }
+    return true; // Not found
+  }
 };
 
 
@@ -2103,12 +2162,14 @@ public:
 class Item_func_case_simple: public Item_func_case,
                              public Predicant_to_list_comparator
 {
+protected:
   uint m_found_types;
   uint when_count() const { return (arg_count - 1) / 2; }
   bool with_else() const { return arg_count % 2 == 0; }
   Item **else_expr_addr() const { return with_else() ? &args[arg_count - 1] : 0; }
-  bool aggregate_switch_and_when_arguments(THD *thd);
-  bool prepare_predicant_and_values(THD *thd, uint *found_types);
+  bool aggregate_switch_and_when_arguments(THD *thd, bool nulls_equal);
+  bool prepare_predicant_and_values(THD *thd, uint *found_types,
+                                              bool nulls_equal);
 public:
   Item_func_case_simple(THD *thd, List<Item> &list)
    :Item_func_case(thd, list),
@@ -2142,6 +2203,22 @@ public:
 };
 
 
+class Item_func_decode_oracle: public Item_func_case_simple
+{
+public:
+  Item_func_decode_oracle(THD *thd, List<Item> &list)
+   :Item_func_case_simple(thd, list)
+  { }
+  const char *func_name() const { return "decode_oracle"; }
+  void print(String *str, enum_query_type query_type)
+  { Item_func::print(str, query_type); }
+  void fix_length_and_dec();
+  Item *find_item();
+  Item *get_copy(THD *thd, MEM_ROOT *mem_root)
+  { return get_item_copy<Item_func_decode_oracle>(thd, mem_root, this); }
+};
+
+
 /*
   The Item_func_in class implements
   in_expr IN (<in value list>)
@@ -2321,6 +2398,11 @@ public:
   bool alloc_comparators(THD *thd, uint n);
   bool prepare_comparators(THD *, Item **args, uint arg_count);
   int cmp(Item *arg);
+  int cmp_not_null(const Value *val)
+  {
+    DBUG_ASSERT(false);
+    return TRUE;
+  }
   int compare(cmp_item *arg);
   cmp_item *make_same();
   void store_value_by_template(THD *thd, cmp_item *tmpl, Item *);
diff --git a/sql/item_create.cc b/sql/item_create.cc
index e0bfe7a4402..b0b33041383 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -623,6 +623,19 @@ protected:
 };
 
 
+class Create_func_decode_oracle : public Create_native_func
+{
+public:
+  virtual Item *create_native(THD *thd, LEX_CSTRING *name, List<Item> *item_list);
+
+  static Create_func_decode_oracle s_singleton;
+
+protected:
+  Create_func_decode_oracle() {}
+  virtual ~Create_func_decode_oracle() {}
+};
+
+
 class Create_func_concat_ws : public Create_native_func
 {
 public:
@@ -3894,6 +3907,21 @@ Create_func_decode_histogram::create_2_arg(THD *thd, Item *arg1, Item *arg2)
   return new (thd->mem_root) Item_func_decode_histogram(thd, arg1, arg2);
 }
 
+Create_func_decode_oracle Create_func_decode_oracle::s_singleton;
+
+Item*
+Create_func_decode_oracle::create_native(THD *thd, LEX_CSTRING *name,
+                                         List<Item> *item_list)
+{
+  uint arg_count= item_list ? item_list->elements : 0;
+  if (arg_count < 3)
+  {
+    my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0), name->str);
+    return NULL;
+  }
+  return new (thd->mem_root) Item_func_decode_oracle(thd, *item_list);
+}
+
 Create_func_concat_ws Create_func_concat_ws::s_singleton;
 
 Item*
@@ -6851,6 +6879,7 @@ static Native_func_registry func_array[] =
   { { C_STRING_WITH_LEN("DAYOFYEAR") }, BUILDER(Create_func_dayofyear)},
   { { C_STRING_WITH_LEN("DEGREES") }, BUILDER(Create_func_degrees)},
   { { C_STRING_WITH_LEN("DECODE_HISTOGRAM") }, BUILDER(Create_func_decode_histogram)},
+  { { C_STRING_WITH_LEN("DECODE_ORACLE") }, BUILDER(Create_func_decode_oracle)},
   { { C_STRING_WITH_LEN("DES_DECRYPT") }, BUILDER(Create_func_des_decrypt)},
   { { C_STRING_WITH_LEN("DES_ENCRYPT") }, BUILDER(Create_func_des_encrypt)},
   { { C_STRING_WITH_LEN("DIMENSION") }, GEOM_BUILDER(Create_func_dimension)},
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index a475914d369..8d250abd994 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -9465,7 +9465,7 @@ column_default_non_parenthesized_expr:
         | DECODE_SYM '(' expr ',' decode_when_list ')'
           {
             $5->push_front($3, thd->mem_root);
-            if (!($$= new (thd->mem_root) Item_func_case_simple(thd, *$5)))
+            if (!($$= new (thd->mem_root) Item_func_decode_oracle(thd, *$5)))
               MYSQL_YYABORT;
           }
         | DEFAULT '(' simple_ident ')'