Bug #9372 Bug with using INTERVAL() function

• Submitted: 23 Mar 2005 23:58
• Modified: 4 Oct 2005 14:14
• Reporter: Robert Montgomery
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.9
• OS: Linux (redhat enterprise 3)
• CPU Architecture: Any

[23 Mar 2005 23:58] Robert Montgomery

Description:
The bug doesnt seem to be with the function on its own, but when adding the result of the function to another integer, you get errors depending on how you write the query.

See examples below - this is very easy to repeat.

How to repeat:
Repeat the following queries.   My goal was to simply add 1 to the result of the INTERVAL() function.   The first 2 queries work, the third doesnt!

> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);

> SELECT 1 + INTERVAL(23, 1, 15, 17, 30, 44, 200);

> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200) + 1;

The failed query DOES work on Mysql 3.23.58,  but it does NOT work on Mysql 4.1.9

[24 Mar 2005 1:18] MySQL Verification Team

I got a different result (an error syntax message) for the third query:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.24-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200) + 1;
+------------------------------------------+
| INTERVAL(23, 1, 15, 17, 30, 44, 200) + 1 |
+------------------------------------------+
|                                        4 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.11-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200) + 1;
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
mysql>

[9 Aug 2005 20:35] Andrey Hristov

SELECT (INTERVAL(23, 1, 15, 17, 30, 44, 200))+1;
works. The problem is in that INTERVAL is a reserved word for datetime computations. It seems that the parser "eats" "+1" but with explicit parentheses there is no problem.

[4 Oct 2005 13:27] Mark Matthews

Reverify, please.

[4 Oct 2005 14:14] MySQL Verification Team

According Andrey note:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT (INTERVAL(23, 1, 15, 17, 30, 44, 200))+1;
+------------------------------------------+
| (INTERVAL(23, 1, 15, 17, 30, 44, 200))+1 |
+------------------------------------------+
|                                        4 |
+------------------------------------------+
1 row in set (0.04 sec)

mysql>

[27 Apr 2008 9:25] Ondra Zizka

I have the same problem with 5.0.51a:

SELECT INTERVAL( 1, 0,1,2 )+1;

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

However, it works in 6.0

Is this going to be fixed?

[27 Apr 2008 14:53] Davi Arnaut

Ondra, your issue seems to be related to Bug#22312.