Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1
Description
The documentation of LEAST(), which GREATEST() refers to for its comparison rules, ends with:
"In all other cases, the arguments are compared as case-insensitive strings."
Mixed string/number argument lists fall into that case, but the comparison actually performed is numeric: the string arguments are coerced to numbers. With the default utf8mb4_general_ci collation, reproduced on 10.11.19 and on 13.1 (debug builds):
SELECT GREATEST('B', 0); -- 0 ('B' -> 0; as strings 'B' > '0', so the answer should be 'B') |
SELECT GREATEST('abc', 5); -- 5 (as strings 'abc' > '5', so the answer should be 'abc') |
SELECT LEAST('abc', 5); -- 0 (as strings '5' < 'abc', so the answer should be '5') |
SELECT GREATEST('5', 10); -- 10 (as strings '5' > '10', so the answer should be '5') |
SELECT GREATEST('999', 1000); -- 1000 (as strings '999' > '1000', so the answer should be '999') |
In every case a different argument wins than the documented rule selects — not just a different value, a different operand. The first three examples cannot be explained by the first documented rule either ("all arguments are integer-valued ... compared as integers"), because 'B' and 'abc' are not integer-valued.
MySQL follows the documented rule for the same statements: GREATEST('abc',5) returns 'abc', GREATEST('5',10) returns '5', GREATEST('999',1000) returns '999'.