Bug #36633 MICROSECOND accepted, SQL_TSI_MICROSECOND not accepted
Submitted: 9 May 2008 17:52 Modified: 11 May 2008 3:03
Reporter: Paul Dubois Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0 and up OS:Any
Assigned to: CPU Architecture:Any
Tags: disabled
Triage: Triaged: D3 (Medium)

[9 May 2008 17:52] Paul Dubois
Description:
In the work done for Bug#33834, MICROSECOND was implemented as an allowable unit type for TIMESTAMPADD(). However, all other unit types can be specified with a SQL_TSI_ prefix. This seems to have been overlooked for SQL_TSI_MICROSECOND. It should be implemented to be consistent with the documentation for TIMESTAMPADD():

"
The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.
"

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

How to repeat:
mysql> select timestampadd(DAY,1,'2000-01-01 00:00:00');
+-------------------------------------------+
| timestampadd(DAY,1,'2000-01-01 00:00:00') |
+-------------------------------------------+
| 2000-01-02 00:00:00                       | 
+-------------------------------------------+
1 row in set (0.01 sec)

mysql> select timestampadd(SQL_TSI_DAY,1,'2000-01-01 00:00:00');
+---------------------------------------------------+
| timestampadd(SQL_TSI_DAY,1,'2000-01-01 00:00:00') |
+---------------------------------------------------+
| 2000-01-02 00:00:00                               | 
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timestampadd(MICROSECOND,1,'2000-01-01 00:00:00');
+---------------------------------------------------+
| timestampadd(MICROSECOND,1,'2000-01-01 00:00:00') |
+---------------------------------------------------+
| 2000-01-01 00:00:00.000001                        | 
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timestampadd(SQL_TSI_MICROSECOND,1,'2000-01-01 00:00:00');
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 'SQL_TSI_MICROSECOND,1,'2000-01-01 00:00:00')' at line 1

Suggested fix:
I suppose, add this to lex.h, in the section where the other SQL_TSI_ symbols are defined:

  { "SQL_TSI_MICROSECOND",  SYM(MICROSECOND_SYM)},
[11 May 2008 3:03] Miguel Solorzano
Thank you for the bug report.
[29 Jan 2010 11:12] Alexander Nozdrin
A part of func_time.test is disabled due to this bug.