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 (OCA) 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
Triage: Triaged: D2 (Serious) / E2 (Low)

[29 Sep 2010 10:37] Roel Van de Paar
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());
[29 Sep 2010 10:42] Roel Van de Paar
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)
[29 Sep 2010 10:44] Roel Van de Paar
Confirmed only on InnoDB (MyISAM and MEMORY give identical results). Changing to InnoDB team.
[30 Sep 2010 8:53] Xinjun Yang
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.
[1 Oct 2010 11:39] Bugs System
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.
[7 Oct 2010 16:17] Bugs System
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.
[9 Nov 2010 19:48] Bugs System
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)
[10 Nov 2010 1:07] Roel Van de Paar
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
=========
[13 Nov 2010 16:20] Bugs System
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)
[13 Nov 2010 16:38] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 3:23] Paul Dubois
Noted in 5.5.7 changelog.

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