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.

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.