Bug #32180 DATE_ADD treats datetime numeric argument as DATE instead of DATETIME
Submitted: 8 Nov 2007 9:43 Modified: 9 Jan 2008 15:00
Reporter: Alexander Dsugan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.45 OS:Any (verified on SuSe Linux and WinXP)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: datetime conversion regression
Triage: D2 (Serious)

[8 Nov 2007 9:43] Alexander Dsugan
Description:
DATE_ADD(YYYYMMDDHHMMSS, interval X day) returns date value 'YYYY-MM-DD' instead of datetime 'YYYY-MM-DD HH:MM:SS' as it used to in 5.0.41 and earlier MySQL versions.
Besides, DATE_ADD('YYYYMMDDHHMMSS', interval X day) preserves old behaviour.

So we lose HH:MM:SS part of the datetime in some queries which use DATE_ADD with the argument specified as a number.

How to repeat:
Please compare behaviours of the same query on MySQL 5.0.41 and 5.0.45.

Server version: 5.0.41-community-nt MySQL Community Edition (GPL):

mysql> select DATE_ADD(20071108181000, interval 1 day);
+------------------------------------------+
| DATE_ADD(20071108181000, interval 1 day) |
+------------------------------------------+
| 2007-11-09 18:10:00                      |
+------------------------------------------+

Server version: 5.0.45-community-nt MySQL Community Edition (GPL):

mysql> select DATE_ADD(20071108181000, interval 1 day);
+------------------------------------------+
| DATE_ADD(20071108181000, interval 1 day) |
+------------------------------------------+
| 2007-11-09                               |
+------------------------------------------+

whereas (5.0.45),

mysql> select DATE_ADD('20071108181000', interval 1 day);
+--------------------------------------------+
| DATE_ADD('20071108181000', interval 1 day) |
+--------------------------------------------+
| 2007-11-09 18:10:00                        |
+--------------------------------------------+
[8 Nov 2007 10:23] Miguel Solorzano
Thank you for the bug report.

c:\mysql-5.0.41-win32>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select DATE_ADD(20071108181000, interval 1 day);
+------------------------------------------+
| DATE_ADD(20071108181000, interval 1 day) |
+------------------------------------------+
| 2007-11-09 18:10:00                      |
+------------------------------------------+
1 row in set (0.00 sec)

[miguel@skybr 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select DATE_ADD(20071108181000, interval 1 day);
+------------------------------------------+
| DATE_ADD(20071108181000, interval 1 day) |
+------------------------------------------+
| 2007-11-09                               | 
+------------------------------------------+
1 row in set (0.02 sec)

mysql>
[16 Nov 2007 9:56] Tatiana Azundris Nuernberg
item_timefunc.cc:2092
bool Item_date_add_interval::get_date(MYSQL_TIME *ltime, uint fuzzy_date)

select DATE_ADD('20071108181000', interval 1 day);

args0 = Item_string
"200711081810000"

$1 = {year = 2007, month = 11, day = 8,
      hour = 18, minute = 10, second = 0, second_part = 0,
      neg = 0 '\0',
      time_type = MYSQL_TIMESTAMP_DATETIME}

select DATE_ADD(20071108181000, interval 1 day);

args0 = Item_int
200711081810000

$3 = {year = 2007, month = 11, day = 8, hour = 18,
      minute = 10, second = 0, second_part = 0,
      neg = 0 '\0',
      time_type = MYSQL_TIMESTAMP_DATE}

The information is there, but the time_type is set to
pretend it isn't.
[16 Nov 2007 10:17] Tatiana Azundris Nuernberg
regression from 2007-05-18
[16 Nov 2007 16:44] 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/37972

ChangeSet@1.2549, 2007-11-16 17:43:15+01:00, tnurnberg@mysql.com +3 -0
  Bug #32180: DATE_ADD treats datetime numeric argument as DATE instead of DATETIME
  
  This is a regression from 2007-05-18 when code to zero out the returned struct was
  added to number_to_datetime(); zero for time_type corresponds to MYSQL_TIMESTAMP_DATE.
  We now explicitly set the type we return (MYSQL_TIMESTAMP_DATETIME).
[17 Nov 2007 14:40] Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23 in opt
[14 Dec 2007 8:15] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:18] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:21] Bugs System
Pushed into 6.0.5-alpha
[9 Jan 2008 15:00] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

DATETIME arguments specified in numeric form were treated by
DATE_ADD() as DATE values.