Bug #49771 SELECT MIN (date) returns wrong results when minimum value is 0000-00-00
Submitted: 17 Dec 2009 13:48 Modified: 4 Aug 2010 20:04
Reporter: John Embretsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:next-mr_bzr 5.5-M3 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: pushbuild, rqg_pb2, test failure
Triage: Triaged: D2 (Serious)

[17 Dec 2009 13:48] John Embretsen
Description:
Since the push (merge) into mysql-next-mr branch with revision alik@sun.com-20091216083231-rp8ecpnvkkbhtb27 on 2009-12-16 the Random Query Generator test rqg_partn_pruning_compare_50 has failed in Pushbuild.

This test runs queries against the pushed code using partition pruning, and compares results with same queries against server version 5.0.87, which does not have the partition pruning feature. The symptom is result mismatches between the servers in columns of type date or datetime:

# 11:45:03 Query:   
SELECT MIN( table1 . `col_date` ) AS field1 
FROM `PP_V` AS table1 
  RIGHT  JOIN `PP_M` AS table2 
   ON ( table2 . `col_varchar_256_cp932_key` = table1 . `col_varchar_256_utf8_key` ) 
WHERE NOT ( table1 . `col_int_signed` >= 8 )    
failed: result content mismatch between servers.
# 11:45:03 --- /export/home/pb2/test/sb_1-1086689-1260959838.88/tmp//randgen5775-1260960303-server0.dump	2009-12-16 11:45:03.000000000 +0100
# 11:45:03 +++ /export/home/pb2/test/sb_1-1086689-1260959838.88/tmp//randgen5775-1260960303-server1.dump	2009-12-16 11:45:03.000000000 +0100
# 11:45:03 @@ -1 +1 @@
# 11:45:03 -2001-12-28
# 11:45:03 +0000-00-00

# 11:45:45 Query:   
  SELECT MAX( table1 . `col_datetime` ) AS field1 
  FROM `PP_R` AS table1 
  WHERE  ( 
    NOT ( table1 . `col_int_signed`  BETWEEN 9 AND ( 9 + 3 ) ) 
     OR table1 . `col_varchar_512_utf8_key` = table1 . `col_varchar_512_utf8` )    
failed: result content mismatch between servers.
# 11:45:46 --- /export/home/pb2/test/sb_1-1086689-1260959838.88/tmp//randgen5775-1260960345-server0.dump	2009-12-16 11:45:45.000000000 +0100
# 11:45:46 +++ /export/home/pb2/test/sb_1-1086689-1260959838.88/tmp//randgen5775-1260960345-server1.dump	2009-12-16 11:45:45.000000000 +0100
# 11:45:46 @@ -1 +1 @@
# 11:45:46 -2009-11-23 23:15:03
# 11:45:46 +2023-12-17 00:00:00

In the above diff, server0 is the code from bzr (mysql-next-mr), while server1 is 5.0.87.

The same results are seen when comparing mysql-next-mr before the above mentioned push (e.g. 2009-12-11) to current mysql-next-mr (2009-12-17). This means that behavior changed as part of the merge on 2009-12-16.

In PB2 the test failed on linux (Red Hat 5.2) x86 and Windows (2003) x86, but not on Solaris (10, SPARC).

Further investigation may show exactly which code change caused this change in behavior.

How to repeat:
To repeat the same test as in PB, you need the Random Query Generator test framework and configuration, as well as a copy of the mysql-next-mr server code before and after the above mentioned push at 2009-12-16.

We may be able to provide a simplified test case later.

Steps to reproduce:

1.bzr branch lp:randgen

You will end up with a "randgen" directory.

Tested revision is john.embretsen@sun.com-20091215135321-337oovy6dd2ybnhk (revno 238). You may need to revert to this revision if the RQG or grammar changes in a way which affects this test.

2. Obtain (by building or downloading) a copy of an "old" version of MySQL, e.g. 5.0.87, 5.5.0-m2, or mysql-next-mr pre-2009-12-16. Refer to this as environment variable BASEDIR_OLD.

3. Obtain (by building or downloading) a copy of current bzr branch mysql-next-mr. Refer to this as environment variable BASEDIR_BZR.

4. cd to randgen directory.

5. Execute the test (assuming Linux here):

perl runall.pl \ 
--mysqld=--loose-innodb-lock-wait-timeout=5 \ 
--mysqld=--table-lock-wait-timeout=5 \ 
--mysqld=--skip-safemalloc \ 
--gendata=conf/partition_pruning.zz \ 
--grammar=conf/partition_pruning.yy \ 
--basedir1=$BASEDIR_BZR \ 
--basedir2=$BASEDIR_OLD \ 
--vardir1=$BASEDIR_BZR/vardirs/vardir-bzr \ 
--vardir2=$BASEDIR_BZR/vardirs/vardir-5.0 \ 
--mysqld=--innodb \ 
--validators=ResultsetComparator \ 
--reporters=ErrorLog,Backtrace \ 
--threads=1 \ 
--queries=10000 \ 
--duration=300 \ 
--mysqld=--log-output=file

Suggested fix:
Revert to old behavior unless this is a valid change.
[17 Dec 2009 15:26] John Embretsen
Compressed MTR test file which shows the issues

Attachment: repro49771_test.tgz (application/x-gzip, text), 186.07 KiB.

[11 Jan 2010 15:03] John Embretsen
Notes on reproducing this issue:

You can use the attached MTR test case to reproduce this issue (I did this today with up-to-date mysql-next-mr-bugfixing). 

To avoid producing warnings about using MyISAM instead of InnoDB, add the following to the top of the test file:

--source include/have_innodb.inc

Copy the test file repro49771.test to mysql-test/t in an installation of MySQL 5.0.x (a mysql-next-mr branch prior to 2009-12-16 may do the same as well).

Run the test and create a result file:

perl ./mysql-test-run.pl --record repro49771

Copy the test file to mysql-test/t in an installation of a recent (newer than 2009-12-16) mysql-next-mr or mysql-next-mr-bugfixing branch. Do the same with the result file generated with 5.0 (or the old next-mr branch), i.e. copy the mysql-test/r/repro49771.result file to mysql-test/r/ in the most recent next-mr branch.

Run the test case in the most recent next-mr branch:

perl ./mysql-test-run.pl repro49771

You should see a result file diff due to different dates (2001-12-28 instead of 0000-00-00) produced by the SELECT queries.

Although the test and result files are quire large due to a large amount of INSERTs, you can easily move to the appropriate sections with the results of the relevant queries by searching for the word "Diff" in the .result or .test files.

I will see if I can provide a simplified test case later this week.
[11 Jan 2010 15:54] Mattias Jonsson
Not related to partitioning, the result is still the same when removing partitioning. Does not exists in mysql-trunk as of today.
[12 Jan 2010 8:46] Mattias Jonsson
Smaller test case, which also shows not dependent of partitioning or engine

Attachment: b49771.test.tgz (application/x-gzip, text), 8.83 KiB.

[15 Jan 2010 16:12] John Embretsen
I did some more test simplification, and it turns out that this issue can be reduced to the following (I changed the Synopsis accordingly):

CREATE TABLE t1 (col_date DATE);

INSERT INTO t1 VALUES ('2004-04-19');
INSERT INTO t1 VALUES ('0000-00-00');
INSERT INTO t1 VALUES ('2004-04-18');

SELECT  MIN(col_date) FROM t1;

-----------------------

Results (from mysql-next-mr-bugfixing 2010-01-14):

--Result should be 0000-00-00
mysql> SELECT MIN(col_date) FROM t1;
+---------------+
| MIN(col_date) |
+---------------+
| 2004-04-18    |
+---------------+
1 row in set (0.00 sec)

--Correct
mysql> SELECT MAX(col_date) FROM t1;
+---------------+
| MAX(col_date) |
+---------------+
| 2004-04-19    |
+---------------+
1 row in set (0.00 sec)

--Correct
mysql> SELECT * FROM t1 ORDER BY col_date;
+------------+
| col_date   |
+------------+
| 0000-00-00 |
| 2004-04-18 |
| 2004-04-19 |
+------------+
3 rows in set (0.01 sec)

-----------------------

It seems that the order of the INSERT statements is significant. If '0000-00-00' is inserted first or last (instead of in the middle among three) 0000-00-00 is returned by the SELECT MIN() query.

From a quick look at the bzr log from the rather large merge/push which changed results for the previously mentioned test, the fix for Bug#43668 could be related, however this is just a shot in the dark.

This issue has also been verified against the mysql-6.0-codebase-bugfixing branch as of 2010-01-15.
[27 Jan 2010 9:41] Manyi Lu
John, does this bug occur in 5.1 branch? Thanks.
[27 Jan 2010 10:44] John Embretsen
Issue not repeatable against the 5.1.42 release, nor against current mysql-5.1-bugteam branch (revid dao-gang.qu@sun.com-20100127025213-9jgrxqm73ueqnrvk). Tested on Solaris 10 x86_64.
[1 Feb 2010 11:12] 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/98806

2977 Evgeny Potemkin	2010-02-01
      Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Arg_comparator::set_cmp_func now sets the correct comparison context
      for items being compared as DATETIME values.
      The Item::is_datetime_comparable helper function is added. It checks whether
      this and given items can be compared as DATETIME values by Arg_comparator.
      The equality propagation optimization is adjusted to take into account that
      items which being compared as DATETIME can have different comparison
      contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of get_date & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/type_date.result
        A test case is added for the bug#49771.
     @ mysql-test/t/type_date.test
        A test case is added for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME value by means of get_date & my_TIME_to_str functions.
     @ sql/item.h
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Item::is_datetime_comparable helper function is added. It checks whether
        this and given items can be compared as DATETIME values by Arg_comparator.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[1 Feb 2010 11:16] 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/98808

2977 Evgeny Potemkin	2010-02-01
      Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Arg_comparator::set_cmp_func now sets the correct comparison context
      for items being compared as DATETIME values.
      The Item::is_datetime_comparable helper function is added. It checks whether
      this and given items can be compared as DATETIME values by Arg_comparator.
      The equality propagation optimization is adjusted to take into account that
      items which being compared as DATETIME can have different comparison
      contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of get_date & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/type_date.result
        A test case is added for the bug#49771.
     @ mysql-test/t/type_date.test
        A test case is added for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME value by means of get_date & my_TIME_to_str functions.
     @ sql/item.h
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Item::is_datetime_comparable helper function is added. It checks whether
        this and given items can be compared as DATETIME values by Arg_comparator.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[8 Feb 2010 11:31] 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/99580

2977 Evgeny Potemkin	2010-02-08
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::is_datetime_comparable helper function is added. It checks whether
      this and given items can be compared as DATETIME values by Arg_comparator.
      The equality propagation optimization is adjusted to take into account that
      items which being compared as DATETIME can have different comparison
      contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME/TIME value.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::is_datetime_comparable helper function is added. It checks whether
        this and given items can be compared as DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[25 May 2010 11:35] 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/109152

3200 Evgeny Potemkin	2010-05-25
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::is_datetime_comparable helper function is added. It checks whether
      this and given items can be compared as DATETIME values by Arg_comparator.
      The equality propagation optimization is adjusted to take into account that
      items which being compared as DATETIME can have different comparison
      contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of get_date & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::is_datetime_comparable helper function is added. It checks whether
        this and given items can be compared as DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[25 May 2010 12:08] 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/109154

3200 Evgeny Potemkin	2010-05-25
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::is_datetime_comparable helper function is added. It checks whether
      this and given items can be compared as DATETIME values by Arg_comparator.
      The equality propagation optimization is adjusted to take into account that
      items which being compared as DATETIME can have different comparison
      contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of get_date & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::is_datetime_comparable helper function is added. It checks whether
        this and given items can be compared as DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[2 Jul 2010 12:15] 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/112752

3200 Evgeny Potemkin	2010-07-02
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::has_compatible_context helper function is added. It checks whether
      this and given items has the same comparison context or can be compared as
      DATETIME values by Arg_comparator. The equality propagation optimization is
      adjusted to take into account that items which being compared as DATETIME
      can have different comparison contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of number_to_datetime & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::has_compatible_context helper function is added. It checks whether
        this and given items has the same comparison context or can be compared as
        DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[13 Jul 2010 10:58] 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/113440

3200 Evgeny Potemkin	2010-07-13
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::has_compatible_context helper function is added. It checks whether
      this and given items has the same comparison context or can be compared as
      DATETIME values by Arg_comparator. The equality propagation optimization is
      adjusted to take into account that items which being compared as DATETIME
      can have different comparison contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of number_to_datetime & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::has_compatible_context helper function is added. It checks whether
        this and given items has the same comparison context or can be compared as
        DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[19 Jul 2010 12:59] 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/113858

3200 Evgeny Potemkin	2010-07-19
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::has_compatible_context helper function is added. It checks whether
      this and given items has the same comparison context or can be compared as
      DATETIME values by Arg_comparator. The equality propagation optimization is
      adjusted to take into account that items which being compared as DATETIME
      can have different comparison contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of number_to_datetime & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::has_compatible_context helper function is added. It checks whether
        this and given items has the same comparison context or can be compared as
        DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[19 Jul 2010 13:53] 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/113862

3200 Evgeny Potemkin	2010-07-19
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::has_compatible_context helper function is added. It checks whether
      this and given items has the same comparison context or can be compared as
      DATETIME values by Arg_comparator. The equality propagation optimization is
      adjusted to take into account that items which being compared as DATETIME
      can have different comparison contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of number_to_datetime & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ mysql-test/t/group_by.test
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::has_compatible_context helper function is added. It checks whether
        this and given items has the same comparison context or can be compared as
        DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[19 Jul 2010 17:12] 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/113888

3130 Evgeny Potemkin	2010-07-19
      Bug#49771: Incorrect MIN/MAX for date/time values.
      
      This bug is a design flaw of the fix for the bug#33546. It assumed that an
      item can be used only in one comparison context, but actually it isn't the
      case. Item_cache_datetime is used to store result for MIX/MAX aggregate
      functions. Because Arg_comparator always compares datetime values as INTs when
      possible the Item_cache_datetime most time caches only INT value. But
      since all datetime values has STRING result type MIN/MAX functions are asked
      for a STRING value when the result is being sent to a client. The
      Item_cache_datetime was designed to avoid conversions and get INT/STRING
      values from an underlying item, but at the moment the values is asked
      underlying item doesn't hold it anymore thus wrong result is returned.
      Beside that MIN/MAX aggregate functions was wrongly initializing cached result
      and this led to a wrong result.
      
      The Item::has_compatible_context helper function is added. It checks whether
      this and given items has the same comparison context or can be compared as
      DATETIME values by Arg_comparator. The equality propagation optimization is
      adjusted to take into account that items which being compared as DATETIME
      can have different comparison contexts.
      The Item_cache_datetime now converts cached INT value to a correct STRING
      DATETIME value by means of number_to_datetime & my_TIME_to_str functions.
      The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
      It sets comparison context of items being compared as DATETIMEs to INT if
      items will be compared as longlong.
      The Item_sum_hybrid::setup function now correctly initializes its result
      value.
      In order to avoid unnecessary conversions Item_sum_hybrid now states that it
      can provide correct longlong value if the item being aggregated can do it
      too.
     @ mysql-test/r/group_by.result
        Added a test case for the bug#49771.
     @ sql/item.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The equality propagation mechanism is adjusted to take into account that
        items which being compared as DATETIME can have different comparison
        contexts.
        The Item_cache_datetime now converts cached INT value to a correct STRING
        DATETIME/TIME value.
     @ sql/item.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item::has_compatible_context helper function is added. It checks whether
        this and given items has the same comparison context or can be compared as
        DATETIME values by Arg_comparator.
        Added Item_cache::clear helper function.
     @ sql/item_cmpfunc.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_func now sets the correct comparison context
        for items being compared as DATETIME values.
     @ sql/item_cmpfunc.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
        It sets comparison context of items being compared as DATETIMEs to INT if
        items will be compared as longlong.
     @ sql/item_sum.cc
        Bug#49771: Incorrect MIN/MAX for date/time values.
        The Item_sum_hybrid::setup function now correctly initializes its result
        value.
     @ sql/item_sum.h
        Bug#49771: Incorrect MIN/MAX for date/time values.
        In order to avoid unnecessary conversions Item_sum_hybrid now states that it
        can provide correct longlong value if the item being aggregated can do it
        too.
[20 Jul 2010 4:23] 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/113921

3316 Evgeny Potemkin	2010-07-20 [merge]
      Auto-merged fix for the bug#49771.
[23 Jul 2010 12:26] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:32] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[29 Jul 2010 18:39] Paul Dubois
Noted in 5.5.6 changelog.

MIN() and MAX() returned incorrect results for DATE columns if the 
set of values included '0000-00-00'.
[4 Aug 2010 8:07] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 8:23] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 20:04] Paul Dubois
Bug is not present in any released 5.6.x version.
[24 Jan 2011 12:57] Øystein Grøvlen
The fix for this issue has introduced the regression reported in Bug#59686.