| 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: | |
| 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 | ||
[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.
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.