Bug #96012 Query with GREATEST function malfunctions
Submitted: 26 Jun 2019 20:20 Modified: 24 Jul 2019 14:04
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.16, 5.7.26, 5.6.44 OS:Ubuntu
Assigned to: CPU Architecture:x86

[26 Jun 2019 20:20] Manuel Rigger
A query with GREATEST unexpectedly does not fetch a row for which the WHERE condition should yield true.

How to repeat:
INSERT INTO t0(c0) VALUES ("a");
SELECT * FROM t0 WHERE GREATEST((-1) & (-1), -t0.c0); -- expected: row is fetched, actual: row is not fetched

SELECT GREATEST((-1) & (-1), -t0.c0) IS TRUE FROM t0; -- 1

An interesting result can be observed when evaluating the -1 & -1 and passing the resulting literal as an argument:

SELECT * FROM t0 WHERE GREATEST(18446744073709551615, -t0.c0); -- expected: row is fetched, actual: ERROR 1690 (22003): BIGINT value is out of range in ...
SELECT GREATEST(18446744073709551615, -t0.c0) IS TRUE FROM t0; -- 1

It seems that the same condition causes an unexpected error when used in the WHERE clause, but no error when used directly after the SELECT.
[27 Jun 2019 4:21] MySQL Verification Team
Hello Manuel Rigger,

Thank you for the report.

[24 Jul 2019 14:04] Jon Stephens
Documented fix as follows in the MySQL 8.0.18 changelog:

    A query using GREATEST() in the WHERE clause could, in certain
    cases, fail to return a row where one was expected or raise a
    spurious error.