MySQL Bugs: #33834: FRAC_SECOND: Applicability not clear in documentation

Bug #33834	FRAC_SECOND: Applicability not clear in documentation
Submitted:	12 Jan 2008 15:43	Modified:	2 Apr 2008 17:39
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
The manual mentions FRAC_SECOND only for the TIMESTAMPADD() function:

TIMESTAMPADD(unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

(See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)

However, FRAC_SECOND appears to be legal with DATE_ADD(), DATE_SUB(), and +/- INTERVAL:

mysql> select now(), date_add(now(), interval 1 frac_second);
+---------------------+-----------------------------------------+
| now()               | date_add(now(), interval 1 frac_second) |
+---------------------+-----------------------------------------+
| 2008-01-12 09:41:25 | 2008-01-12 09:41:25.000001              | 
+---------------------+-----------------------------------------+

mysql> select now(), date_sub(now(), interval 1 frac_second);
+---------------------+-----------------------------------------+
| now()               | date_sub(now(), interval 1 frac_second) |
+---------------------+-----------------------------------------+
| 2008-01-12 09:41:33 | 2008-01-12 09:41:32.999999              | 
+---------------------+-----------------------------------------+

mysql> select now(), now() + interval 1 frac_second;
+---------------------+--------------------------------+
| now()               | now() + interval 1 frac_second |
+---------------------+--------------------------------+
| 2008-01-12 09:41:48 | 2008-01-12 09:41:48.000001     | 
+---------------------+--------------------------------+

mysql> select now(), now() - interval 1 frac_second;
+---------------------+--------------------------------+
| now()               | now() - interval 1 frac_second |
+---------------------+--------------------------------+
| 2008-01-12 09:41:53 | 2008-01-12 09:41:52.999999     | 
+---------------------+--------------------------------+

If FRAC_SECOND should not be allowed in these contexts, there is a server bug.

If FRAC_SECOND should be allowed, the documentation is incorrect. In that case, please reassign this report to me and I will fix the manual. Thank you.

How to repeat:
See above.

The grammar appears to allow FRAC_SECOND as a synonym for MICROSECOND:

interval:
    interval_time_st    {}
    | DAY_HOUR_SYM      { $$=INTERVAL_DAY_HOUR; }
    | DAY_MICROSECOND_SYM   { $$=INTERVAL_DAY_MICROSECOND; }
    | DAY_MINUTE_SYM    { $$=INTERVAL_DAY_MINUTE; }
    | DAY_SECOND_SYM    { $$=INTERVAL_DAY_SECOND; }
    | HOUR_MICROSECOND_SYM  { $$=INTERVAL_HOUR_MICROSECOND; }
    | HOUR_MINUTE_SYM   { $$=INTERVAL_HOUR_MINUTE; }
    | HOUR_SECOND_SYM   { $$=INTERVAL_HOUR_SECOND; }
    | MICROSECOND_SYM   { $$=INTERVAL_MICROSECOND; }
    | MINUTE_MICROSECOND_SYM    { $$=INTERVAL_MINUTE_MICROSECOND; }
    | MINUTE_SECOND_SYM { $$=INTERVAL_MINUTE_SECOND; }
    | SECOND_MICROSECOND_SYM    { $$=INTERVAL_SECOND_MICROSECOND; }
    | YEAR_MONTH_SYM    { $$=INTERVAL_YEAR_MONTH; };

interval_time_st:
    DAY_SYM         { $$=INTERVAL_DAY; }
    | WEEK_SYM      { $$=INTERVAL_WEEK; }
    | HOUR_SYM      { $$=INTERVAL_HOUR; }
    | FRAC_SECOND_SYM   { $$=INTERVAL_MICROSECOND; }
    | MINUTE_SYM        { $$=INTERVAL_MINUTE; }
    | MONTH_SYM     { $$=INTERVAL_MONTH; }
    | QUARTER_SYM       { $$=INTERVAL_QUARTER; }
    | SECOND_SYM        { $$=INTERVAL_SECOND; }
    | YEAR_SYM      { $$=INTERVAL_YEAR; }
        ;

If that's so, the documentation needs updating. But I want that confirmed by a developer first.

Thank you for the bug report.

Currently the server accepts FRAC_SECOND as a synonym for MICROSECONDS in DATE_ADD(), DATE_SUB() and +/-INTERVAL.

However, using MICROSECONDS in TIMESTAMPADD() is not allowed:

mysql> SELECT TIMESTAMPADD(MICROSECOND,1,'2003-01-02');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MICROSECOND,1,'2003-01-02')' at line 1

It would be easy to make FRAC_SECONDS and MICROSECONDS synonyms, i.e. to make it possible to use them in all context interchangeably. 

I don't see any reasons why it cannot be done, but it looks like a question for PeterG.

MySQL tries to copy some ODBC functions including TIMESTAMPADD
http://msdn2.microsoft.com/en-us/library/ms714639(VS.85).aspx
but
(a) in ODBC's TIMESTAMPADD, SQL_TSI_FRAC_SECOND is not a synonym
for MICROSECOND, it is "billionths of a second".
(b) SQL Server allows MICROSECOND and NANOSECOND for "date adding"
http://msdn2.microsoft.com/en-us/library/ms186819(SQL.100).aspx
(This is new in SQL Server 2008.)
(c) In a DB2 "labeled duration" it's MICROSECOND or MICROSECONDS
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.s...
... therefore it appears that MySQL's FRAC_SECOND is a mistake,
and the way of the future is to use MICROSECOND.

I suggest that the use of FRAC_SECOND, for anything other than
TIMESTAMPADD / TIMESTAMPDIFF, is a server error.
It could be fixed by making FRAC_SECOND a synonym for MICROSECOND,
and that would not be a violation of SQL standard requirements,
but that would be a change to an existing feature.
Let's not encourage FRAC_SECOND use by increasing its applicability.

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

ChangeSet@1.2607, 2008-02-18 16:41:34+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #33834: FRAC_SECOND: Applicability not clear in 
                      documentation
  
  While the manual mentions FRAC_SECOND only for the TIMESTAMPADD()
  function, it was also possible to use FRAC_SECOND with DATE_ADD(),
  DATE_SUB() and +/- INTERVAL.
  
  Fixed the parser to match the manual, i.e. using FRAC_SECOND for 
  anything other than TIMESTAMPADD()/TIMESTAMPDIFF() now produces a 
  syntax error.

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

ChangeSet@1.2607, 2008-02-20 17:25:25+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #33834: FRAC_SECOND: Applicability not clear in 
                      documentation
  
  While the manual mentions FRAC_SECOND only for the TIMESTAMPADD()
  function, it was also possible to use FRAC_SECOND with DATE_ADD(),
  DATE_SUB() and +/- INTERVAL.
  
  Fixed the parser to match the manual, i.e. using FRAC_SECOND for 
  anything other than TIMESTAMPADD()/TIMESTAMPDIFF() now produces a 
  syntax error.

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

ChangeSet@1.2607, 2008-02-22 12:22:07+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #33834: FRAC_SECOND: Applicability not clear in 
                      documentation
  
  While the manual mentions FRAC_SECOND only for the TIMESTAMPADD()
  function, it was also possible to use FRAC_SECOND with DATE_ADD(),
  DATE_SUB() and +/- INTERVAL.
  
  Fixed the parser to match the manual, i.e. using FRAC_SECOND for 
  anything other than TIMESTAMPADD()/TIMESTAMPDIFF() now produces a 
  syntax error.
  
  Additionally, the patch allows MICROSECOND to be used in TIMESTAMPADD/
  TIMESTAMPDIFF and marks FRAC_SECOND as deprecated.

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

ChangeSet@1.2607, 2008-02-25 13:25:57+03:00, kaa@kaamos.(none) +3 -0
  Fix for bug #33834: FRAC_SECOND: Applicability not clear in 
                      documentation
  
  While the manual mentions FRAC_SECOND only for the TIMESTAMPADD()
  function, it was also possible to use FRAC_SECOND with DATE_ADD(),
  DATE_SUB() and +/- INTERVAL.
  
  Fixed the parser to match the manual, i.e. using FRAC_SECOND for 
  anything other than TIMESTAMPADD()/TIMESTAMPDIFF() now produces a 
  syntax error.
  
  Additionally, the patch allows MICROSECOND to be used in TIMESTAMPADD/
  TIMESTAMPDIFF and marks FRAC_SECOND as deprecated.

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

ChangeSet@1.2668, 2008-02-25 16:03:28+03:00, kaa@kaamos.(none) +2 -0
  Post-merge fixes for bug #33834.

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

ChangeSet@1.2810, 2008-02-26 16:06:58+03:00, kaa@kaamos.(none) +1 -0
  Post-merge fixes for bug #33834.

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

ChangeSet@1.2608, 2008-02-27 18:12:08+03:00, kaa@kaamos.(none) +1 -0
  Fixed test suite failures with --ps-protocol introduced in PB by
  the patch for bug #33834.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Documented in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:
        
        It was possible to use FRAC_SECOND as a synonym for MICROSECOND with  
        DATE_ADD(), DATE_SUB(), and INTERVAL; now, using FRAC_SECOND with anything 
        other than TIMESTAMPADD() or TIMESTAMPDIFF() produces a syntax error.
        
        In addition, FRAC_SECOND is now deprecated.

And since 5.5.3, TIMESTAMPDIFF will not work with FRAC_SECOND anymore.