MySQL Bugs: #57095: Different query output for 5.1 and 5.5 when coalesce is used

Bug #57095	Different query output for 5.1 and 5.5 when coalesce is used
Submitted:	29 Sep 2010 10:37	Modified:	16 Nov 2010 3:23
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.6rc	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	regression

Description:
5.1.49:

mysql> SELECT * FROM `a` WHERE b <= coalesce(NULL, now());
+---------------------+
| b                   |
+---------------------+
| 2010-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

5.5.6rc:

mysql> SELECT * FROM `a` WHERE b <= coalesce(NULL, now());
Empty set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `a`;
CREATE TEMPORARY TABLE `a` (`b` datetime NOT NULL DEFAULT '0000-00-00 00:00:00') ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `a` VALUES ('2010-01-01 00:00:00');
SELECT * FROM `a` WHERE b <= coalesce(NULL, now());

Interestingly, this gives the same output on both versions:

mysql> SELECT a FROM (SELECT '2010-01-01 00:00:00' AS a) AS b WHERE a <= COALESCE(NULL, NOW());
+---------------------+
| a                   |
+---------------------+
| 2010-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Confirmed only on InnoDB (MyISAM and MEMORY give identical results). Changing to InnoDB team.

this bug is caused when MySQL performs coalesce during query processing, if the new class Item_cache_datetime is involved, it uses my_strtoll10() (instead of TIME_to_ulonglong_datetime()) to convert a time string to long integer when coalesce a datetime data type, thus reduce the precision of datetime value, change something like "2010-09-30 01:10:10" to only "2010" instead of 20100930100000. 

This  my_strtoll10() is called when we just done 
Item_func_coalesce::str_op call, and fetched the time from coalesce, stack:

my_strtoll10_8bit
Item_func_numhybrid::val_int
Item::val_int_result 
Item_cache_datetime::cache_value_int 
Item_cache_datetime::val_int
get_datetime_value
Arg_comparator::compare_datetime

(gdb) p nptr
$56 = 0x9b65b40 "2010-09-30 01:10:10"

After the Item_func_numhybrid::val_int and my_strtoll10_8bit call:
(gdb)  n
(gdb)  n
(gdb) p int_value
$57 = 2010

so the comparison is between "2010-01-01 00:00:00 " (the value from table) and "2010" (instead of "2010-09-30 01:10:10") from  coalesce(NULL, now()) call:

Breakpoint 14, Arg_comparator::compare_datetime (this=0x9b65cb0)
    at /home/jy/work/mysql5.5_6/mysql-trunk-innodb/sql/item_cmpfunc.cc:1278
1278	    owner->null_value= 0;
4: b_value = 2010
3: a_value = 20100101000000

Thus the row is not returned for the query.

The reason that it does not happen in 5.1 is because we do not use(have) Item_cache_datetime class yet. So in get_datetime_value(), we call  item->val_str() instead of item->val_int() since item->result_as_longlong() returns false.

get_datetime_value()
{

  if (item->result_as_longlong())
  {
     value= item->val_int();  <=== In this case, item is Item_cache_datetime, we come in here
     ...
  }
  else
  {
    str= item->val_str(&buf);  <=== in 5.1, and 5.5 MyISAM case, "item" is not 
    *is_null= item->null_value;<=== "Item_cache_datetime" class, so they come here
  }

}

And the difference between InnoDB and MyISAM is due to different query plan, and in MyISAM case Item_cache_datetime is not used either, so it falls into 5.1 codepath.

The data stored in InnoDB layer is correct, and we fetched them out and translated to long correctly:

Breakpoint 13, get_datetime_value 
(gdb) 
1110	    *is_null= item->null_value;
2: value = 20100101000000

And as mentioned, in coalesce operation, we will lost the precision of the time due to using my_strtoll10() in Item_func_numhybrid::val_int. Stack:

Item_func_coalesce::str_op 
Item_func_numhybrid::val_int 
Item::val_int_result 
Item_cache_datetime::cache_value_int 
Item_cache_datetime::val_int
get_datetime_value 

If in this case in get_datetime_value, "item" is not of Item_cache_datetime class, and item->result_as_longlong() return false, we will fetch the date correctly as string with item->val_str(&buf), and then transfer to long for comparison. That has been the case for 5.1 and when we use MyISAM table.

There has been some comments on why result_as_longlong() return TRUE:

    "result_as_longlong() must return TRUE for Items representing DATE/TIME
    functions and DATE/TIME table fields.
    Those Items have result_type()==STRING_RESULT (and not INT_RESULT), but
    their values should be compared as integers (because the integer
    representation is more precise than the string one)."

apparently the coalesce operation did not properly processed the datetime data type and thus caused this problem. Someone works on this datetime processing should take a look at this problem.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119633

3213 Evgeny Potemkin	2010-10-01
      Bug#57095: Wrongly chosen expression cache type led to a wrong result.
      The coalesce function returned DATETIME type due to a DATETIME argument, but
      since it's not a date/time function it can't return correct int value for
      it. Nevertheless Item_datetime_cache was chosen to cache coalesce's result
      and that led to a wrong result.
      
      Now Item_datetime_cache is used only for those function that could return
      correct int representation of DATETIME values.
     @ mysql-test/r/type_datetime.result
        Added a test case for the bug#57095.
     @ mysql-test/t/type_datetime.test
        Added a test case for the bug#57095.
     @ sql/item.cc
        Bug#57095: Wrongly chosen expression cache type led to a wrong result.
        Now Item_datetime_cache is used only for those function that could return
        correct int representation of DATETIME values.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120280

3213 Evgeny Potemkin	2010-10-07
      Bug#57095: Wrongly chosen expression cache type led to a wrong result.
      The coalesce function returned DATETIME type due to a DATETIME argument, but
      since it's not a date/time function it can't return correct int value for
      it. Nevertheless Item_datetime_cache was chosen to cache coalesce's result
      and that led to a wrong result.
      
      Now Item_datetime_cache is used only for those function that could return
      correct int representation of DATETIME values.
     @ mysql-test/r/type_datetime.result
        Added a test case for the bug#57095.
     @ mysql-test/t/type_datetime.test
        Added a test case for the bug#57095.
     @ sql/item.cc
        Bug#57095: Wrongly chosen expression cache type led to a wrong result.
        Now Item_datetime_cache is used only for those function that could return
        correct int representation of DATETIME values.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Confirmed fixed in 5.5.7rc

=========
mysql> SELECT * FROM `a` WHERE b <= coalesce(NULL, now());
+---------------------+
| b                   |
+---------------------+
| 2010-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.5.7-rc
=========

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Noted in 5.5.7 changelog.

COALESCE() in MySQL 5.5 could return a result different from MySQL
5.1 for some arguments.