MySQL Bugs: #96012: Query with GREATEST function malfunctions

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:	None
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

Description:
A query with GREATEST unexpectedly does not fetch a row for which the WHERE condition should yield true.

How to repeat:
CREATE TABLE t0(c0 TEXT);
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.

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

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.

Closed.