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:
None 
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 18:45] Roland Bouman
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.
[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.)