Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function

is involved.

The Arg_comparator::compare_datetime() comparator caches its arguments if
they are constants i.e. const_item() returns true. The
Item_func_get_user_var::const_item() returns true or false based on
the current query_id and the query_id where the variable was created.
Thus even if a query can change its value its const_item() still will return
true. All this leads to a wrong comparison result when an object of the
Item_func_get_user_var class is involved.

Now the Arg_comparator::can_compare_as_dates() and the
get_datetime_value() functions never cache result of the GET_USER_VAR()
function (the Item_func_get_user_var class).


mysql-test/t/type_datetime.test:
  A test case is added for the bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
  is involved.
mysql-test/r/type_datetime.result:
  A test case is added for the bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
  is involved.
sql/item_cmpfunc.cc:
  Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
  is involved.
  Now the Arg_comparator::can_compare_as_dates() and the
  get_datetime_value() functions never cache result of the GET_USER_VAR()
  function (the Item_func_get_user_var class).

3 files changed, 49 insertions, 2 deletions

diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 573139bed48..48cc54fb3ef 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -352,3 +352,27 @@ select left(f1,10) = curdate() from t1;
 left(f1,10) = curdate()
 1
 drop table t1;
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+Warnings:
+Warning	1292	Incorrect date value: '' for column 'f1' at row 1
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+drop table t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 4c2416000be..cf512aa3649 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -234,3 +234,15 @@ create table t1 (f1 date);
 insert into t1 values (curdate());
 select left(f1,10) = curdate() from t1;
 drop table t1;
+
+#
+# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
+#            is involved.
+#
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+drop table t1;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 9a251312aca..3a79940b571 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -633,7 +633,13 @@ Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
 
   if (cmp_type != CMP_DATE_DFLT)
   {
-    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+    /*
+      Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+      for the current thread but it still may change during the execution.
+    */
+    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() &&
+        (str_arg->type() != Item::FUNC_ITEM ||
+        ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC))
     {
       THD *thd= current_thd;
       ulonglong value;
@@ -780,7 +786,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
       MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
     value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
   }
-  if (item->const_item() && cache_arg)
+  /*
+    Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+    for the current thread but it still may change during the execution.
+  */
+  if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
+      ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
   {
     Item_cache_int *cache= new Item_cache_int();
     /* Mark the cache as non-const to prevent re-caching. */