MySQL Bugs: #57097: LEAST function does not behave as documented

Bug #57097	LEAST function does not behave as documented
Submitted:	29 Sep 2010 11:38	Modified:	29 Sep 2014 16:01
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.51, 5.5.7	OS:	Any (Windows 7 64bit, Mac OS X)
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_least

says

Note that the preceding conversion rules can produce strange results in some borderline cases: 
mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
        -> -9223372036854775808

This happens because MySQL reads 9223372036854775808.0 in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.

How to repeat:
However I get (on 64 bit server)

SELECT VERSION(); -- 5.1.51-community
SELECT CAST(LEAST(3600, 9223372036854775808.0) AS SIGNED); -- returns '3600'
SELECT LEAST(3600, 9223372036854775808.0); -- also -- returns '3600'

but:
SELECT CAST(LEAST(3600, 9223372036854775808, 0) AS SIGNED); -- returns '-9223372036854775808'
SELECT LEAST(3600, 9223372036854775808, 0); -- also returns '-9223372036854775808'

Suggested fix:
I am setting thus as a documentation issue, but I am not sure. Docs also say

"If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers. 

If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals."

.. but this does not document what happens if the list is a mix of integers and reals (and only those).

I also do not understand the wording "The integer representation is not good enough to hold the value, so it wraps ...".  It does not look like it is simple integer overflow. Also I do not see why CAST is used in the docs.  Isn't this an unnecessary complication in describing this?

Btw: what does a 'REAL context' actually mean? Is it automatically such with the function list (3600, 9223372036854775808.0) - simply because as one list-item is REAL only a 'REAL context' can be used with such list?

Verified with mysql-5.5 on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) AS SIGNED);
+----------------------------------------------------+
| CAST(LEAST(3600, 9223372036854775808.0) AS SIGNED) |
+----------------------------------------------------+
|                                               3600 |
+----------------------------------------------------+
1 row in set, 1 warning (0.03 sec)

mysql> SELECT CAST(LEAST(3600, 9223372036854775808, 0) AS SIGNED);
+-----------------------------------------------------+
| CAST(LEAST(3600, 9223372036854775808, 0) AS SIGNED) |
+-----------------------------------------------------+
|                                -9223372036854775808 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
macbook-pro:5.5 openxs$ file libexec/mysqld 
libexec/mysqld: Mach-O executable i386

I'll take this one and see what I can do with it.

In the first case, you're comparing legal values. Truncation takes place as a result of the CAST, hence the warning:.

mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0, 0) AS SIGNED);
+-------------------------------------------------------+
| CAST(LEAST(3600, 9223372036854775808.0, 0) AS SIGNED) |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

In the second case, you're comparing with a value--it's larger than the maximum for signed int, and LEAST() has to accommodate signed numbers, so that's how it treats its arguments:

mysql> SELECT CAST(LEAST(3600, 9223372036854775808, 0) AS SIGNED);
+-----------------------------------------------------+
| CAST(LEAST(3600, 9223372036854775808, 0) AS SIGNED) |
+-----------------------------------------------------+
|                                -9223372036854775808 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

I really think this is all shown in or is simple to infer from the documentation.

If you consider the behaviour in the second case to be incorrect, please file a Server bug.

Thanks!