MySQL Bugs: #32180: DATE_ADD treats datetime numeric argument as DATE instead of DATETIME

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

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                        |
+--------------------------------------------+

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>

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.

regression from 2007-05-18

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).

pushed to 5.0.52, 5.1.23 in opt

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

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.