Bug #36466 Adding days to day_microsecond changes interpretation of microseconds
Submitted: 2 May 2008 9:57 Modified: 7 Mar 2010 18:45
Reporter: Janek Bogucki Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.45-Debian_1ubuntu3.3-log, 4.1, 5.0, 5.1, 6.0 BK OS:Linux
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[2 May 2008 9:57] Janek Bogucki
Description:
Adding a day part to the day_microsecond expression changes the interpretation of the microseconds part from 5 microseconds to 500,000 microseconds

mysql> select date_add('1000-01-01 00:00:00', interval '03:02:01.5' day_microsecond);
+------------------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '03:02:01.5' day_microsecond) |
+------------------------------------------------------------------------+
| 1000-01-01 03:02:01.000005                                             |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('1000-01-01 00:00:00', interval '1 03:02:01.5' day_microsecond);
+--------------------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1 03:02:01.5' day_microsecond) |
+--------------------------------------------------------------------------+
| 1000-01-02 03:02:01.500000                                               |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

--------------------------

The documentation for day_microseconds appears to be wrong:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add

The format for the unit DAY_MICROSECOND is 

 DAYS HOURS:MINUTES:SECONDS.MICROSECONDS

not DAYS.MICROSECONDS as the documentation currently states.

These statements demonstrate that

mysql> select date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond);
+-----------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond) |
+-----------------------------------------------------------------+
| 1000-01-01 00:00:01.000002                                      |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('1000-01-01 00:00:00', interval '1 2:3:4.5' day_microsecond);
+-----------------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1 2:3:4.5' day_microsecond) |
+-----------------------------------------------------------------------+
| 1000-01-02 02:03:04.500000                                            |
+-----------------------------------------------------------------------+

How to repeat:
Enter these statements

select date_add('1000-01-01 00:00:00', interval '03:02:01.5' day_microsecond);

select date_add('1000-01-01 00:00:00', interval '1 03:02:01.5' day_microsecond);
[2 May 2008 18:31] Sveta Smirnova
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

According to http://dev.mysql.com/doc/refman/5.0/en/datetime.html ("As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.") you can use almost any delimiter:

mysql> select date_add('1000-01-01 00:00:00', interval '1.03:02:01.5' day_microsecond);
+--------------------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1.03:02:01.5' day_microsecond) |
+--------------------------------------------------------------------------+
| 1000-01-02 03:02:01.500000                                               | 
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('1000-01-01 00:00:00', interval '1#03:02:01.5' day_microsecond);
+--------------------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1#03:02:01.5' day_microsecond) |
+--------------------------------------------------------------------------+
| 1000-01-02 03:02:01.500000                                               | 
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
[2 May 2008 22:44] Paul Dubois
No, there is a bug here.  Consider this example in the original report:

mysql> select date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond);
+-----------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '1.2' day_microsecond) |
+-----------------------------------------------------------------+
| 1000-01-01 00:00:01.000002                                      |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

The ".2" part of the interval value is being treated like .000002, not .200000.

I expect the result of the query to be 1000-01-01 00:00:01.200000 rather than the value shown.
[6 May 2008 9:02] Sveta Smirnova
Paul,

thank you for the explanation. Got what I misread in original description.

Verified as described.
[13 May 2008 6:48] 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/46642

ChangeSet@1.2624, 2008-05-13 08:47:36+02:00, tnurnberg@noir.wlan.koehntopp.de +3 -0
  Bug #36466: Adding days to day_microsecond changes interpretation of microseconds
  
  When less than six places are given for microseconds, we zerofill from
  the right (leftmost place is always 1/10s). We only did this when all
  announced date/time fields were given; now we also format fractional
  seconds when more significant fields are left out.
[13 May 2008 6:51] Tatiana Azundris Nuernberg
docs team, please remember that these are two bugs:

- code bug: fractional seconds were not formatted/parsed correctly when more significant fields were implied/left out (patch attached)

- docs bug: DAY_MICROSECOND, like the other INTERVAL-types, implies not just DAY and MICROSECOND, but everything in between.

thanks, -T-
[8 Jan 2009 2:43] Tatiana Azundris Nuernberg
queued for 6.0.10 in bugteam tree
[20 Jan 2009 18:54] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:mattias.jonsson@sun.com-20090108101840-vq8vzx31v17wzurz) (merge vers: 6.0.10-alpha) (pib:6)
[6 Feb 2009 20:31] Paul Dubois
Noted in 6.0.10 changelog.

When parsing or formatting interval values of DAY_MICROSECOND type, 
fractional seconds were not handled correctly when more-significant
fields were implied or omitted.

Also: Fixed expected format for DAY_MICROSECOND type in functions chapter.
[6 Feb 2009 20:36] Paul Dubois
Also: Fixed expected format for MINUTE_MICROSEOND, HOUR_MICROSECOND, which were similarly inaccurate.
[3 Nov 2009 22:29] 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/89230

2905 Magne Mahre	2009-11-03
      Bug #36466: Adding days to day_microsecond changes interpretation of microseco
            
      When less than six places are given for microseconds, we zerofill from
      the right (leftmost place is always 1/10s). We only did this when all
      announced date/time fields were given; now we also format fractional
      seconds when more significant fields are left out.
     @ mysql-test/r/func_time.result
        show that we treat fractions of seconds correctly (zerofill from
        right to six places) even if we left out fields on the left
     @ mysql-test/t/func_time.test
        show that we treat fractions of seconds correctly (zerofill from
        right to six places) even if we left out fields on the left
     @ sql/item_timefunc.cc
        format fractions of seconds even if announced
        more significant fields were left out
[3 Nov 2009 22:30] Magne Mæhre
Backported to 5.5.0   (6.0-codebase revid: 2944.2.1)
[20 Nov 2009 12:55] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[20 Nov 2009 12:58] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:13)
[22 Nov 2009 0:03] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 10:53] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:jon.hauglid@sun.com-20090928163426-2lg1gofzz44xzzxf) (merge vers: 6.0.14-alpha) (pib:16)
[7 Mar 2010 18:45] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.