MySQL Bugs: #1561: Unexpected results from INTERVAL function.

Bug #1561	Unexpected results from INTERVAL function.
Submitted:	15 Oct 2003 7:27	Modified:	13 Dec 2003 6:54
Reporter:	Charles Alcorn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	4.1.0 alpha	OS:	Linux (Linux (i686) kernel 2.4.18-14smp)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56)

Returns 0, not 1 as expected.  On large sets of data, the interval 1 is always missing.

How to repeat:
SELECT INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56)

4.0 returns correct result:

mysql> SELECT VERSION(), INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56);
+------------------+---------------------------------------------+
| VERSION()        | INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56) |
+------------------+---------------------------------------------+
| 4.0.15-debug-log |                                           1 |
+------------------+---------------------------------------------+

4.1 however is wrong:

mysql> SELECT VERSION(), INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56);
+-----------------------+---------------------------------------------+
| VERSION()             | INTERVAL(13, 7, 14, 21, 28, 35, 42, 49, 56) |
+-----------------------+---------------------------------------------+
| 4.1.1-alpha-debug-log |                                           0 |
+-----------------------+---------------------------------------------+
 
mysql> SELECT VERSION(), INTERVAL(13, 7, 14, 21, 28, 35, 42, 49);
+-----------------------+-----------------------------------------+
| VERSION()             | INTERVAL(13, 7, 14, 21, 28, 35, 42, 49) |
+-----------------------+-----------------------------------------+
| 4.1.1-alpha-debug-log |                                       1 |
+-----------------------+-----------------------------------------+

Note the difference between last two quewries. They must both return value of 1

The problem also happens with the latest build out of bitkeeper (4.1.2) 
 
mysql> select version(), interval (1,1,2,3,4,5,6,7); 
+-------------+----------------------------+ 
| version()   | interval (1,1,2,3,4,5,6,7) | 
+-------------+----------------------------+ 
| 4.1.1-alpha |                          1 | 
+-------------+----------------------------+ 
1 row in set (0.00 sec) 
 
mysql> select version(),interval (1,1,2,3,4,5,6,7,8); 
+-------------+------------------------------+ 
| version()   | interval (1,1,2,3,4,5,6,7,8) | 
+-------------+------------------------------+ 
| 4.1.1-alpha |                            0 | 
+-------------+------------------------------+ 
 
the problem also happens on data from tables: 
 
mysql> create table t( a int ); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t values (1),(2),(3),(4),(5),(6),(7),(8),(9); 
Query OK, 9 rows affected (0.00 sec) 
Records: 9  Duplicates: 0  Warnings: 0 
 
mysql> select interval(a,2,3,4,5,6,7,8), a from t; 
+---------------------------+------+ 
| interval(a,2,3,4,5,6,7,8) | a    | 
+---------------------------+------+ 
|                         0 |    1 | 
|                         1 |    2 | 
|                         2 |    3 | 
|                         3 |    4 | 
|                         4 |    5 | 
|                         5 |    6 | 
|                         6 |    7 | 
|                         7 |    8 | 
|                         7 |    9 | 
+---------------------------+------+ 
9 rows in set (0.00 sec) 
 
mysql> select interval(a,2,3,4,5,6,7,8,9), a from t; 
+-----------------------------+------+ 
| interval(a,2,3,4,5,6,7,8,9) | a    | 
+-----------------------------+------+ 
|                           0 |    1 | 
|                           0 |    2 |          <-- here 
|                           2 |    3 | 
|                           3 |    4 | 
|                           4 |    5 | 
|                           5 |    6 | 
|                           6 |    7 | 
|                           7 |    8 | 
|                           8 |    9 | 
+-----------------------------+------+ 
9 rows in set (0.00 sec) 
 
Why is this a problem mapped to client and not to the server?

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

A fix will come in 4.1.2