Bug #15395 Interval Strings should be quoted, or at least ALLOW it.
Submitted: 1 Dec 2005 17:06 Modified: 1 Dec 2005 17:29
Reporter: Scott Marlowe Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16 OS:Linux (fedora core 2)
Assigned to: CPU Architecture:Any

[1 Dec 2005 17:06] Scott Marlowe
According to the SQL 2003 Specification, an <interval literal> is defined as follows:

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>

And an <interval string> is defined thusly:

<interval string> ::= <quote> <unquoted interval string> <quote>

Currently, MySQL 5.0.16 not only doesn't require quotes, as per the spec, it doesn't even allow them.

How to repeat:
select '2005-10-10 12:00:00' - interval '1 day'
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 '' at line 1

Suggested fix:
Allow quoting.  I would imagine that to strictly SQL 2003 compliant, you should error out on an unquoted interval string, but seeing as how this bug got into a production release, you might want to hold off on strict compliance til a later version.

But not allowing quotes is a very bad thing, since all other databases require them, so anything written for MySQL will break there, and vice versa.
[1 Dec 2005 17:29] Scott Marlowe
Never mind.  As long as I only quote the value it's ok.  Never mind, sorry.