MySQL Bugs: #24525: ADDDATE returns string value

Bug #24525	ADDDATE returns string value
Submitted:	22 Nov 2006 22:58	Modified:	27 Feb 2008 14:42
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.42	OS:	Windows (WinXP, Vista)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	adddate, cast, compare, comparison, date, sql datetime

Description:
SELECT '2006-11-30 13:00:00' >= '2006-11-30 9:00'

-- should return true but returns false
-- 9:00 is being treated as 9PM

How to repeat:
SELECT '2006-11-30 13:00:00' >= '2006-11-30 9:00'

Suggested fix:
Why the #$@% is MySQL assumming that YYYY-MM-DD 9:00 = YYYY-MM-DD 17:00 OR = YYYY-MM-DD 9:00 PM

VB.NET calculates expression correctly.

?convert.ToDateTime("2006-11-30 13:00:00") >= convert.ToDateTime("2006-11-30 9:00")
True

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully about type conversion in expression evaluation at http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

LOOK AT ...

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

<SNIP>
For values specified as strings that include date part delimiters, it is not necessary to specify two digits for month or day values that are less than 10. '1979-6-9' is the same as '1979-06-09'. Similarly, for values specified as strings that include time part delimiters, it is not necessary to specify two digits for hour, minute, or second values that are less than 10. '1979-10-30 1:2:3' is the same as '1979-10-30 01:02:03'.
</SNIP>

This *IS* a bug because my string is a "Relaxed" date time formatted with "time part delimiters".

Further more this documentation illustrates the bug because the follwing expressioin straight from official 5.0 documentation fails on MySQL Server 5.0.27

SELECT '1979-10-30 1:2:3' = '1979-10-30 01:02:03'
>0

Thank you for the quote.

I mark this "Verified". But severity should be lower, because there is workaround: simple use 'YYYY-MM-DD HH:MM:SS' instead of 'YYYY-MM-DD H:MM:SS' or use CAST.

No bug here.
When you write

SELECT '1979-10-30 1:2:3' = '1979-10-30 01:02:03'

you compare two strings. MySQL has no way of knowing that you wanted to compare them as dates. Note than in your VB.NET example you converts strings to dates explicitly. You need to do the same in MySQL:

select cast('1979-10-30 1:2:3' as datetime)>= cast('1979-10-30 01:02:03' as datetime);
> 1

Thanks Sergei,

Thanks for pointer on comparing date strings.  I will now adjust my bug and re-write it.

SELECT CAST('2006-11-30 13:00:00' AS DATETIME) > '2006-11-30 9:00';

This is similar to actual sql that has screwed up database integrity for one of my clients.  Except I have replace the actual datetime column with the cast function.  

Shouldn't 13:00 hours be greater than 9:00 hours?  Whys hould I have to prep 9 with HH or even do a cast on a string format when you claim to support 'Relaxed' part delimitered strings.

Do you mean, in your code you actually had a DATETIME *column* and not a cast ?

Please, provide the *exact* query, don't try to replace parts of it with "identical" calues, CAST(), constants, and columns use completely different code internally, even if the behaviour is similar it would be three different bugs.

Simple database that replicate my clients DB

Attachment: Sample DB.sql (text/plain), 1.57 KiB.

For simplicity I have had to replicate the issue.  The problem would appear to be with the ADDDATE keyword, my bad for not initially reporting this earlier.

Using the sample DB provided run the following query..

SELECT * FROM roomsales WHERE ADDDATE(depart, INTERVAL 3 HOUR) >= '2006-11-30 9:00'

Basically, ADDDATE is adding 3 hours to depart datetime, but it doing so has rendered the field as a string and then measure agaisnt my 'Relaxed' date string is failing to return results although we all know 13:00 hours is greater than 9:00 hours.

I've had a quick look at documentation, and in my opinion this is a bug.

Thanks
Jared

It is repeatable on Linux. I'll mark it verify again. All versions are affected.

To repeat:

CREATE TABLE roomsales(depart DATETIME);

INSERT INTO roomsales VALUES('2006-11-30 10:00:00');

SELECT * FROM roomsales WHERE ADDDATE(depart, INTERVAL 3 HOUR) >= '2006-11-30 9:00';

SELECT * FROM roomsales WHERE ADDDATE(depart, INTERVAL 3 HOUR) < '2006-11-30 9:00';

SELECT ADDDATE(depart, INTERVAL 3 HOUR) FROM roomsales;

This comment is a self contained reproduction script.  Hope now that this bug is simpler to resolve.

-- expected results when ADDDATE is wrapped with CAST
SELECT CAST(ADDDATE('2006-11-30 9:00:00', INTERVAL 3 HOUR) AS DATETIME) > ('2006-11-30 9:00')

-- unexpected results when ADDDATE is used by itself (cause no leading zero with 9AM)
SELECT ADDDATE('2006-11-30 9:00:00', INTERVAL 3 HOUR) > ('2006-11-30 9:00')

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/40126

ChangeSet@1.2546, 2007-12-18 11:04:47+01:00, mhansson@linux-st28.site +5 -0
  Bug#24525: ADDDATE returns string value
  
  When DATETIME values are compared to constants, the constants should be 
  converted to TIMESTAMP. But this depended on field_type, which is 
  MYSQL_TYPE_STRING for ADDDATE when invoked with a string. 
  Fixed by adding a property comparison_type to Item class that defaults to 
  the field_type, but overriding it for Item_date_func to always return TIMESTAMP.

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/40136

ChangeSet@1.2546, 2007-12-18 14:41:55+01:00, mhansson@linux-st28.site +4 -0
  Bug#24525: ADDDATE returns string value
  
  When DATETIME values are compared to constants, the constants should be 
  converted to TIMESTAMP. But this depended on field_type, which is 
  MYSQL_TYPE_STRING for ADDDATE when invoked with a string. 
  Fixed by adding a case for functions returning DATETIME/DATE but have 
  STRING result.

Behaves well in my tests. Thunderpush are go!

The following comment was found in the code.

    The field type for the result of an Item_date function is defined as
    follows:

    - If first arg is a MYSQL_TYPE_DATETIME result is MYSQL_TYPE_DATETIME
    - If first arg is a MYSQL_TYPE_DATE and the interval type uses hours,
      minutes or seconds then type is MYSQL_TYPE_DATETIME.
    - Otherwise the result is MYSQL_TYPE_STRING
      (This is because you can't know if the string contains a DATE, MYSQL_TIME or
      DATETIME argument)

The consequence of this is that this is not a bug. DATE_ADD/ADDDATE should be called with a DATETIME argument in order to have return type string.

What you should do is always cast the argument to DATETIME, like so:

SELECT DATE_ADD(CAST('2006-11-30 9:00:00' AS DATETIME),INTERVAL 0 HOUR) = '2006-11-30 9';

My complaint was that an actual DateTime column from MySQL Databse was not being respected.

It has been over a year and looks like the reported issue has been fixed anyway.

http://bugs.mysql.com/bug.php?id=28261
http://bugs.mysql.com/bug.php?id=28208
http://bugs.mysql.com/bug.php?id=21103

Bug should really be marked , non-repo or duplicate.

My comment was an internal request for the documentation team.

Reclassifying as documentation bug: Need better indication of return type for ADDDATE()/DATE_ADD() in the manual. Assigning to myself.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The return value depends on the arguments:
                
- DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or
if the first argument is a DATE and the unit value uses HOURS,
MINUTES, or SECONDS. 
        
- String otherwise.

To ensure that the result is DATETIME, you can use CAST() to convert
the first argument to DATETIME.

(This change made for manuals for 5.0 and up only.)