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: Sinisa Milivojevic CPU Architecture:Any

[15 Oct 2003 7:27] Charles Alcorn
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)
[15 Oct 2003 12:39] Alexander Keremidarski
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
[12 Dec 2003 4:34] [ name withheld ]
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?
[13 Dec 2003 6:54] Sinisa Milivojevic
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