| Bug #36891 | CAST/CONVERT date or datetime to unsigned may lose century information | ||
|---|---|---|---|
| Submitted: | 22 May 2008 18:09 | Modified: | 22 May 2008 21:32 |
| Reporter: | Tim Clark | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cast, convert, date, datetime, UNSIGNED | ||
[22 May 2008 19:13]
Sveta Smirnova
According to http://dev.mysql.com/doc/refman/5.1/en/datetime.html dates earlier 1000-01-01 are not supported: The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'. So i close the report as "Not a Bug"
[22 May 2008 20:42]
Tim Clark
I'm fine with this being closed as "not a bug", but I'm wondering why some storage engines differ from the documentation listed above. MyISAM and InnoDB both appear to allow dates outside of the "supported range."
For example:
mysql> create table test (d date) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values ('0018-01-01 00:00:00');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+------------+
| d |
+------------+
| 0018-01-01 |
+------------+
1 row in set (0.01 sec)
This then leads me to the problem that I described originally:
mysql> select cast(cast(d as unsigned ) as date) from test;
+------------------------------------+
| cast(cast(d as unsigned ) as date) |
+------------------------------------+
| 2018-01-01 |
+------------------------------------+
1 row in set (0.01 sec)
Obviously there is some inconsistency here. Is the documentation correct? Or is the storage engines' behavior correct?
[22 May 2008 21:21]
Paul DuBois
There is a "supported range of values" - in that range, you should get the correct result. Outside of that range, you might get the right result, but we make no guarantee. In particular, we do not specify that you *will* get an incorrect result. (In practice, you'll get the right result much of the time.)
Description: Casting a date or datetime value for the first century (years 0001 to 0099) to an unsigned integer and then back to a date/datetime causes the century to become either 19xx or 20xx. Centuries prior to the 11th century (years 100 to 999) cause the date/datetime to be NULL (with a warning about invalid data). Centuries from the 11th century on work without any problem. How to repeat: WRONG: mysql> select cast(cast(cast('0019-01-01' as date ) as unsigned ) as date); +--------------------------------------------------------------+ | cast(cast(cast('0019-01-01' as date ) as unsigned ) as date) | +--------------------------------------------------------------+ | 2019-01-01 | +--------------------------------------------------------------+ 1 row in set (0.00 sec) WRONG: mysql> select cast(cast(cast('874-01-01' as date ) as unsigned ) as date); +-------------------------------------------------------------+ | cast(cast(cast('874-01-01' as date ) as unsigned ) as date) | +-------------------------------------------------------------+ | NULL | +-------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) RIGHT: mysql> select cast(cast(cast('874-01-01' as date ) as unsigned ) as date); +-------------------------------------------------------------+ | cast(cast(cast('874-01-01' as date ) as unsigned ) as date) | +-------------------------------------------------------------+ | NULL | +-------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) Suggested fix: Casting a four-digit year should have a consistent behavior. I realize that the conversion was probably implemented this way in order to allow two digit years, but the inconsistency makes it difficult to use casting on four-digit years.