MySQL Bugs: #115125: GREATEST Function Returns Incorrect Result

Bug #115125	GREATEST Function Returns Incorrect Result
Submitted:	25 May 2024 16:18	Modified:	27 May 2024 9:51
Reporter:	Wenqian Deng	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The GREATEST function in MySQL returns an incorrect result when comparing a DOUBLE type with a TEXT type. 

How to repeat:
Create the table and insert the values:

CREATE TABLE t0 (c_0 DOUBLE, c_1 TEXT);
INSERT INTO t0 VALUES (2.581201878199644e+116, '57892374');

Execute the following query:

SELECT *, (GREATEST(t0.c_0, t0.c_1)) FROM t0;

| c_0                   | c_1      | (GREATEST(t0.c_0, t0.c_1)) |
|-----------------------|----------|-----------------------------|
| 2.581201878199644e116 | 57892374 | 57892374                    |

Expected Behavior

The GREATEST function should return 2.581201878199644e116, as it is the larger numeric value compared to 57892374.

Actual Behavior

The GREATEST function returns 57892374, which is incorrect.

The problem is also found in LEAST().

Hi Mr. Deng,

Thank you for your bug report.

However, it is not a bug.

The precision of the DOUBLE data type, as described in our Manual is 15 digits.

Hence, what you get is expected result.

Not a bug.

Thanks for this quick response. However, I do not think it is a double-precision issue. The result is still wrong when I change the double value to '2.5e+116'. 

The problem is probably that the way the GREATEST function performs the comparison is problematic. I also found that similar DBMSs like MariaDB return the expected result.

Hi Mr. Deng,

You have stipulated the reason on why this is not a bug, in the following sentence:

"
The GREATEST function in MySQL returns an incorrect result when comparing a DOUBLE type with a TEXT type. 
"

According to SQL Standard, comparing values of the different data types should always result in no rows or values being returned. Instead, it is stipulated that a hard error should be returned.

However, each database products tries to find the best set of common denominators in order to return some result. 

Hence, in short, there is no such thing as correct result when mixing different data types in an expression or a function.

Not a bug !

Quoting the manual:

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are
compared using the following rules:
• If any argument is NULL, the result is NULL. No comparison is needed.
• If all arguments are integer-valued, they are compared as integers.
• If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as DECIMAL values.
• If the arguments comprise a mix of numbers and strings, they are compared as strings.
• If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
• In all other cases, the arguments are compared as binary strings.

If you expect a numeric result, use a proper CAST for all non-numeric arguments.

Thank you, so much, Roy.