| Bug #23093 | Implicit conversion of 9912101 to date does not match cast(9912101 as date) | ||
|---|---|---|---|
| Submitted: | 8 Oct 2006 18:45 | Modified: | 12 Jun 2007 14:17 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1.12-bk (2006/10/6) | OS: | Linux (Linux, ubuntu dapper drake) |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
| Tags: | cast, conversion, date, implicit | ||
[8 Oct 2006 19:10]
Roland Bouman
Here's another example: mysql> select @d:=11111, year(@d), month(@d), day(@d), cast(@d as date); +-----------+----------+-----------+---------+------------------+ | @d:=11111 | year(@d) | month(@d) | day(@d) | cast(@d as date) | +-----------+----------+-----------+---------+------------------+ | 11111 | 2011 | 11 | 1 | 2011-11-01 | +-----------+----------+-----------+---------+------------------+ 1 row in set (0.00 sec) insert into dts values (11111,11111,11111); select * from dts; +------------+---------------------+---------------------+ | d | dt | ts | +------------+---------------------+---------------------+ | 2001-11-11 | 2001-11-11 00:00:00 | 2001-11-11 00:00:00 | +------------+---------------------+---------------------+
[9 Oct 2006 8:41]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described:
openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.12-beta-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select @d:=9912101, year(@d), month(@d), day(@d), cast(@d as date);
+-------------+----------+-----------+---------+------------------+
| @d:=9912101 | year(@d) | month(@d) | day(@d) | cast(@d as date) |
+-------------+----------+-----------+---------+------------------+
| 9912101 | 1999 | 12 | 10 | 1999-12-10 |
+-------------+----------+-----------+---------+------------------+
1 row in set (0.00 sec)
mysql> create table dts (
-> d date
-> , dt datetime
-> , ts timestamp
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into dts values (9912101,9912101,9912101) ;
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1264
Message: Out of range value for column 'd' at row 1
*************************** 2. row ***************************
Level: Warning
Code: 1264
Message: Out of range value for column 'dt' at row 1
*************************** 3. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column 'ts' at row 1
3 rows in set (0.00 sec)
mysql> select @d:=11111, year(@d), month(@d), day(@d), cast(@d as date);
+-----------+----------+-----------+---------+------------------+
| @d:=11111 | year(@d) | month(@d) | day(@d) | cast(@d as date) |
+-----------+----------+-----------+---------+------------------+
| 11111 | 2011 | 11 | 1 | 2011-11-01 |
+-----------+----------+-----------+---------+------------------+
1 row in set (0.00 sec)
mysql> insert into dts values (11111,11111,11111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from dts;
+------------+---------------------+---------------------+
| d | dt | ts |
+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 2001-11-11 | 2001-11-11 00:00:00 | 2001-11-11 00:00:00 |
+------------+---------------------+---------------------+
2 rows in set (0.00 sec)
[16 Jan 2007 17:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/18201 ChangeSet@1.2390, 2007-01-16 18:21:29+01:00, tnurnberg@mysql.com +3 -0 Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date) For dates given as integers, INSERT and friends parse as integers (meaning, dates with an odd number of digits are parsed right to left, zero-padding from the left), while CAST(), YEAR() etc. parse as strings (left-to-right). This can lead to counter-intuitive results for invalid dates (dates with no separators and an odd number of digits). Have the second group also throw an error on those dates.
[5 Mar 2007 13: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/21147 ChangeSet@1.2390, 2007-03-05 09:56:15+01:00, tnurnberg@mysql.com +11 -0 Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date) For dates given as integers, INSERT and friends parse as integers (meaning, dates with an odd number of digits are parsed right to left, zero-padding from the left), while CAST(), YEAR() etc. parse as strings (left-to-right). This can lead to counter-intuitive results for invalid dates (dates with no separators and an odd number of digits). Try our best to parse dates that have only digits (and, optionally, a T) as integers to get consistent behaviour.
[5 Mar 2007 15:22]
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/21157 ChangeSet@1.2490, 2007-03-05 16:22:08+01:00, tnurnberg@mysql.com +11 -0 Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date) For dates given as integers, INSERT and friends parse as integers (meaning, dates with an odd number of digits are parsed right to left, zero-padding from the left), while CAST(), YEAR() etc. parse as strings (left-to-right). This can lead to counter-intuitive results for invalid dates (dates with no separators and an odd number of digits). Try our best to parse dates that have only digits (and, optionally, a T) as integers to get consistent behaviour. --- Merge tnurnberg@bk-internal.mysql.com:/home/bk/mysql-5.1-maint into mysql.com:/home/tnurnberg/work/mysql-5.1-maint-23093
[5 Mar 2007 15:51]
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/21161 ChangeSet@1.2490, 2007-03-05 16:50:34+01:00, tnurnberg@mysql.com +11 -0 Bug #23093 Implicit conversion of 9912101 to date does not match cast(9912101 as date) For dates given as integers, INSERT and friends parse as integers (meaning, dates with an odd number of digits are parsed right to left, zero-padding from the left), while CAST(), YEAR() etc. parse as strings (left-to-right). This can lead to counter-intuitive results for invalid dates (dates with no separators and an odd number of digits). Try our best to parse dates that have only digits (and, optionally, a T) as integers to get consistent behaviour. --- Merge tnurnberg@bk-internal.mysql.com:/home/bk/mysql-5.1-maint into mysql.com:/home/tnurnberg/work/mysql-5.1-maint-23093
[26 Mar 2007 9:44]
Michael Widenius
get_date() now checks type of argument and handles things the appropriate way depending if the argument is a string or number. Fix will be in 5.1.18
[10 Apr 2007 15:43]
Tatiana Azundris Nuernberg
queued in 5.0.40-marvel 5.1.17-marvel
[18 Apr 2007 15:46]
Bugs System
Pushed into 5.1.18-beta
[24 Apr 2007 1:27]
Paul DuBois
Noted in 5.1.18 changelog. Implicit conversion of 9912101 to DATE did not match CAST(9912101 AS DATE). Setting report back to Patch Queued. Will this fix be merged to 5.0.x?
[14 May 2007 12:53]
Tatiana Azundris Nuernberg
pushed to 5.0.42-maint
[22 May 2007 17:00]
Bugs System
Pushed into 5.1.19-beta
[22 May 2007 17:02]
Bugs System
Pushed into 5.0.44
[12 Jun 2007 14:17]
Peter Lavin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Added to changelog for version 5.0.44. (Already noted in 5.1.18 changelog.)
Description: There is a difference between the implicit and the explicit conversion of an integer to a date. How to repeat: select @d:=9912101, year(@d), month(@d), day(@d), cast(@d as date); +-------------+----------+-----------+---------+------------------+ | @d:=9912101 | year(@d) | month(@d) | day(@d) | cast(@d as date) | +-------------+----------+-----------+---------+------------------+ | 9912101 | 1999 | 12 | 10 | 1999-12-10 | +-------------+----------+-----------+---------+------------------+ 1 row in set (0.00 sec) create table dts ( d date , dt datetime , ts timestamp ); insert into dts values (9912101,9912101,9912101) ; Query OK, 1 row affected, 3 warnings (0.01 sec) select * from dts; +------------+---------------------+---------------------+ | d | dt | ts | +------------+---------------------+---------------------+ | 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +------------+---------------------+---------------------+ 1 row in set (0.00 sec) select * from dts; Suggested fix: Well, the documentation never mentions a 7digit numeral date format. So I guess the implicit conversion is correct. However, I then would expect the functions too to report a warning as well, and return NULL. In fact, I expected that the implicit conversion would be completely the same as a call to a CAST(n as date) operation, but apperantly, something totally different is going on.