Bug #1601 incorrect results using least(x, <large value>) in INTERVAL function

• Submitted: 19 Oct 2003 16:12
• Modified: 10 Dec 2003 4:53
• Reporter: marc slemko
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.15a
• OS: Linux (Debian 3.0 / Linux kernel 2.4.22)
• Assigned to: Bugs System
• CPU Architecture: Any

[19 Oct 2003 16:12] marc slemko

Description:
Incorrect results are obtained using the least() function in an interval statement where one of the values is a very large number, 

mysql> select '2003-10-17 12:00:46' + interval least(3600, 9223372036854775808.0) second;
+----------------------------------------------------------------------------+
| '2003-10-17 12:00:46' + interval least(3600, 9223372036854775808.0) second |
+----------------------------------------------------------------------------+
| 2003-10-17 12:00:46                                                        |
+----------------------------------------------------------------------------+

This query should return 2003-10-17 13:00:46.

How to repeat:
The simplified replication is in the description.  The following starts with the query I'm really trying to execute, and simplifies it to the query listed in the description:

The following works correctly:

mysql> select '2003-10-17 12:00:46' + interval least(3600, power(2,62)) second;
+------------------------------------------------------------------+
| '2003-10-17 12:00:46' + interval least(3600, power(2,62)) second |
+------------------------------------------------------------------+
| 2003-10-17 13:00:46                                              |
+------------------------------------------------------------------+
1 row in set (0.02 sec)

but then I change to 63... and...

mysql> select '2003-10-17 12:00:46' + interval least(3600, power(2,63)) second;
+------------------------------------------------------------------+
| '2003-10-17 12:00:46' + interval least(3600, power(2,63)) second |
+------------------------------------------------------------------+
| 2003-10-17 12:00:46                                              |
+------------------------------------------------------------------+
1 row in set (0.03 sec)

it doesn't add anything to the time.

ok, break it down:

mysql> select least(3600, power(2,62));
+--------------------------+
| least(3600, power(2,62)) |
+--------------------------+
|              3600.000000 |
+--------------------------+
1 row in set (0.01 sec)

thats ok, try:

mysql> select least(3600, power(2,63));
+--------------------------+
| least(3600, power(2,63)) |
+--------------------------+
|              3600.000000 |
+--------------------------+
1 row in set (0.01 sec)

still ok.  But as soon as I stick that least() into the interval, it fails to behave correctly.  Including the output from power(2,63) into the least directly also fails to work properly.

I can only guess that there is some improper type conversion going on somewhere, but types and implicit type conversion in mysql doesn't appear to be well documented.

[23 Oct 2003 2:38] Victor Vagin

bk commit - 4.0 tree (1.1582)

[10 Dec 2003 4:53] Michael Widenius

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

This is not something that we can fix easily as it's how least is defined to work.

If you use LEAST in integer context, we will evaluate each of the arguments as a signed 64 bit integer.  This is what happens when you use LEAST() as an argument to INTERVAL.

In the future we may change how LEAST() should work in integer context, but as this will force a redesign, we don't want to do it in a stable release.