MySQL Bugs: #120514: Wrong result for ALL quantified comparison involving interval arithmetic and PRIMARY KEY optimization

Bug #120514	Wrong result for ALL quantified comparison involving interval arithmetic and PRIMARY KEY optimization
Submitted:	21 May 9:19	Modified:	21 May 11:47
Reporter:	ss w	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A query involving an ALL quantified comparison may evaluate inconsistently between WHERE filtering and SELECT expression evaluation when combined with interval arithmetic, bitwise operations, UNKNOWN checks, and a PRIMARY KEY index.

In this case, the WHERE query returns one row, while evaluating the same predicate as a SELECT expression indicates that the condition is FALSE.

How to repeat:
CREATE TABLE t99 (c1 DECIMAL(20,0) PRIMARY KEY);
INSERT INTO t99 (c1) VALUES (64884539314);
SELECT t99.c1 FROM t99 WHERE (! (((('52.631097' - INTERVAL -45 QUARTER) ^ t99.c1) <= ALL (SELECT t99.c1 FROM t99)) IS UNKNOWN));
-- 1 rows
SELECT SUM(count) FROM (SELECT (! (((('52.631097' - INTERVAL -45 QUARTER) ^ t99.c1) <= ALL (SELECT t99.c1 FROM t99)) IS UNKNOWN)) IS TRUE AS count FROM t99) AS ta_norec;
-- 0

Thank you for the bug report.

However, the problem is not reproducible on our currently supported releases, 8.4 and 9.7. Release 8.0 is no longer supported.