| Bug #31800 | Date comparison fails with timezone and slashes for greater than comparison | ||
|---|---|---|---|
| Submitted: | 23 Oct 2007 23:50 | Modified: | 29 Nov 2007 0:37 |
| Reporter: | Wesley Dyk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0.45 | OS: | Windows |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
| Tags: | date comparison, greater than operator, time zone | ||
[24 Oct 2007 0:17]
Jim Winstead
MySQL does not support time zones in strings that are converted to datetimes. See http://dev.mysql.com/doc/refman/5.0/en/datetime.html
[24 Oct 2007 14:40]
Wesley Dyk
It is not about the server supporting time zone in the datetime conversion. The SQL mode suggests that the default behavior of truncating values should be used. The CAST AS DATETIME function works as expected and returns a datetime value without the time zone. This one comparison, using the greater than operator, behaves differently. That is the bug.
[28 Oct 2007 12:26]
Valeriy Kravchuk
I think, this is a bug, as we have inconsistent behaviour:
mysql> select str_to_date('2007-10-01','%Y-%m-%d');
Field 1: `str_to_date('2007-10-01','%Y-%m-%d')`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: DATE
Collation: binary (63)
Length: 10
Max_length: 10
Decimals: 0
Flags: BINARY
+--------------------------------------+
| str_to_date('2007-10-01','%Y-%m-%d') |
+--------------------------------------+
| 2007-10-01 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select cast('2007/10/01 00:00:00 GMT-6' as date);
Field 1: `cast('2007/10/01 00:00:00 GMT-6' as date)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: DATE
Collation: binary (63)
Length: 10
Max_length: 10
Decimals: 31
Flags: BINARY
+-------------------------------------------+
| cast('2007/10/01 00:00:00 GMT-6' as date) |
+-------------------------------------------+
| 2007-10-01 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select str_to_date('2007-10-01','%Y-%m-%d') = cast('2007/10/01 00:00:00 G
MT-6' as date);
Field 1: `str_to_date('2007-10-01','%Y-%m-%d') = cast('2007/10/01 00:00:00 GM
T-6' as date)`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 1
Max_length: 1
Decimals: 0
Flags: BINARY NUM
+-------------------------------------------------------------------------------
---+
| str_to_date('2007-10-01','%Y-%m-%d') = cast('2007/10/01 00:00:00 GMT-6' as dat
e) |
+-------------------------------------------------------------------------------
---+
|
1 |
+-------------------------------------------------------------------------------
---+
1 row in set, 1 warning (0.00 sec)
So, we got warning, but the above is true, while:
mysql> select str_to_date('2007-10-01','%Y-%m-%d') = '2007/10/01 00:00:00 GMT-6'
;
Field 1: `str_to_date('2007-10-01','%Y-%m-%d') = '2007/10/01 00:00:00 GMT-6'`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 1
Max_length: 1
Decimals: 0
Flags: BINARY NUM
+--------------------------------------------------------------------+
| str_to_date('2007-10-01','%Y-%m-%d') = '2007/10/01 00:00:00 GMT-6' |
+--------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
with implicit data type conversion we got warning and result is false. This is inconsistent and, thus, a bug.
[29 Oct 2007 14:05]
Timour Katchaounov
Wrong result, so P2.
[8 Nov 2007 4:50]
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/37304 ChangeSet@1.2557, 2007-11-08 05:50:02+01:00, tnurnberg@mysql.com +4 -0 Bug#31800: Date comparison fails with timezone and slashes for greater than comparison BETWEEN was more lenient with regard to what it accepted as a DATE/DATETIME in comparisons than greater-than and less-than were. ChangeSet makes < > comparisons similarly robust with regard to trailing garbage (" GMT-1") and "missing" leading zeros. Now all three comparators behave similarly in that they throw a warning for "junk" at the end of the data, but then proceed anyway if possible. Before < > fell back on a string- (rather than date-) comparison when a warning-condition was raised in the string-to-date conversion. Now the fallback only happens on actual errors, while warning- conditions still result in a warning being to delivered to the client.
[10 Nov 2007 16:19]
Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23 in opt
[14 Nov 2007 21:55]
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/37792 ChangeSet@1.2687, 2007-11-14 22:55:06+01:00, tnurnberg@mysql.com +1 -0 Bug#31800: Date comparison fails with timezone and slashes for greater than comparison post-merge fixes 5.1->6.0
[15 Nov 2007 8: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/37826 ChangeSet@1.2689, 2007-11-15 09:07:56+01:00, tnurnberg@mysql.com +2 -0 Bug#31800: Date comparison fails with timezone and slashes for greater than comparison fix test and results for falcon
[16 Nov 2007 9:31]
Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33]
Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:36]
Bugs System
Pushed into 6.0.4-alpha
[29 Nov 2007 0:37]
Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs. Comparison results for BETWEEN were different from those for operators like < and > for DATETIME-like values with trailing extra characters such as '2007-10-01 00:00:00 GMT-6'. BETWEEN treated the values as DATETIME, whereas the other operators performed a binary-string comparison. Now they all uniformly use a DATETIME comparison, but generate warnings for values with trailing garbage.
Description: When attempting a date and time comparison using the greater than operator, a true statement returns false rather than true when the date string contains slashes and a time zone. See the following script execution for an example. mysql> select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT-6' and '2007/10/20 00:00:00 GMT-6' `between_check` , str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' ge_check , str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6' le_check; +---------------+----------+----------+ | between_check | ge_check | le_check | +---------------+----------+----------+ | 1 | 0 | 1 | +---------------+----------+----------+ 1 row in set, 4 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' | | Warning | 1292 | Truncated incorrect date value: '2007/10/20 00:00:00 GMT-6' | | Warning | 1292 | Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT-6' | | Warning | 1292 | Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT-6' | +---------+------+-----------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00' and '2007/10/20 00:00:00' `between_check` , str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00' ge_check , str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00' le_check; +---------------+----------+----------+ | between_check | ge_check | le_check | +---------------+----------+----------+ | 1 | 1 | 1 | +---------------+----------+----------+ 1 row in set (0.00 sec) mysql> select str_to_date('2007-10-09','%Y-%m-%d') between '2007-10-01 00:00:00 GMT-6' and '2007-10-20 00:00:00 GMT-6' `between_check` , str_to_date('2007-10-09','%Y-%m-%d') > '2007-10-01 00:00:00 GMT-6' ge_check , str_to_date('2007-10-09','%Y-%m-%d') <= '2007-10-20 00:00:00 GMT-6' le_check; +---------------+----------+----------+ | between_check | ge_check | le_check | +---------------+----------+----------+ | 1 | 1 | 1 | +---------------+----------+----------+ 1 row in set, 4 warnings (0.00 sec) mysql> select str_to_date('2007-10-09','%Y-%m-%d') between '2007-10-01 00:00:00' and '2007-10-20 00:00:00' `between_check` , str_to_date('2007-10-09','%Y-%m-%d') > '2007-10-01 00:00:00' ge_check , str_to_date('2007-10-09','%Y-%m-%d') <= '2007-10-20 00:00:00' le_check; +---------------+----------+----------+ | between_check | ge_check | le_check | +---------------+----------+----------+ | 1 | 1 | 1 | +---------------+----------+----------+ 1 row in set (0.00 sec) mysql> select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) The CAST('2007/10/09 00:00:00 GMT-6' AS DATETIME) function works as expected. I should note that testing it out on a 5.0.38 version failed for the between comparison as well. How to repeat: Execute the following query to see the failure. Only one case fails. Each should be 1. select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT-6' and '2007/10/20 00:00:00 GMT-6' `between_check` , str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' ge_check , str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/20 00:00:00 GMT-6' le_check;