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:
None 
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

[23 Oct 2007 23:50] Wesley Dyk
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;
[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.