Bug #19765 CAST(), DATE() and TIME() returning wrong data type for dates?

• Submitted: 12 May 2006 11:30
• Modified: 3 Aug 2007 14:13
• Reporter: Hartmut Holzgraefe
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0-BK, 5.1-BK
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[12 May 2006 11:30] Hartmut Holzgraefe

Description:
Stumbled across this while checking bug #19747:

Adding 0 to a date field returns the date as integer in the YYYYMMDD format

Adding 0 to a CAST(... AS date)+0 returns only YYYY as if you were adding 0 
to a date string instead of a regular DATE value

How to repeat:
mysql> create table t1 (d date);

mysql> insert into t1 values (now);

mysql> select d from t1;
+------------+
| d          |
+------------+
| 2006-05-12 |
+------------+
1 row in set (0.00 sec)

mysql> select d+0 from t1;
+----------+
| d+0      |
+----------+
| 20060512 |
+----------+
1 row in set (0.00 sec)

mysql> select cast(d as date)+0 from t1;
+-------------------+
| cast(d as date)+0 |
+-------------------+
| 2006              |
+-------------------+
1 row in set (0.00 sec)

[12 May 2006 11:46] Mark Leith

Verified on 5.0 and 5.1 BK trees.

5.1:

mysql> select cast(d as date)+0 from t1;
+-------------------+
| cast(d as date)+0 |
+-------------------+
|              2006 | 
+-------------------+
1 row in set (0.00 sec)

5.0:

mysql> select cast(d as date)+0 from t1;
+-------------------+
| cast(d as date)+0 |
+-------------------+
|              2006 | 
+-------------------+
1 row in set (0.04 sec)

[12 May 2006 14:56] Hartmut Holzgraefe

It's not clear from the documentation but i think 
DATE() and TIME() are also affected as these
should return DATE and TIME and not STRING
results.

[12 May 2006 15:01] Hartmut Holzgraefe

mysqltest test case

Attachment: bug19765.tar.gz (application/x-gzip, text), 981 bytes.

[10 Jul 2006 17:15] Martin Friebe

changing the result type of time() from string to time, might break existing functionality.

See discusion on bug #11655

time can currently accept values outside the time-range as input, which would return a cut-off result if the result-type was time, instead of string
 select time("20000:00:01.1");
+-----------------------+
| time("20000:00:01.1") |
+-----------------------+
| 20000:00:01.100000    |
+-----------------------+
1 row in set (0.00 sec)

strictly spoken that is allready a bug, because we are suplying a string, with an invalid time as input.
so other than tme_to_sec(), time() should never be able to return a result that needs cutting off

I just thought I point it out.

[6 Nov 2006 19:52] Chad MILLER

Similar to Bug#16546.

[3 Aug 2007 14:13] Konstantin Osipov

This bug was fixed by the fix for Bug#16546

mysql> insert into t1 values (now());
Query OK, 1 row affected (0.00 sec)

mysql> select d from t1;
+------------+
| d          |
+------------+
| 2007-08-03 | 
+------------+
1 row in set (0.00 sec)

mysql> select d+0 from t1;
+----------+
| d+0      |
+----------+
| 20070803 | 
+----------+
1 row in set (0.00 sec)

mysql> select cast(d as date)+0 from t1;
+-------------------+
| cast(d as date)+0 |
+-------------------+
|          20070803 | 
+-------------------+
1 row in set (0.00 sec)