MySQL Bugs: #110977: ANY query with wrong result 2

Bug #110977	ANY query with wrong result 2
Submitted:	10 May 2023 14:56	Modified:	11 May 2023 13:59
Reporter:	Pedro Ferreira	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	ANY

Description:
Run these statements:

CREATE TABLE t0 (c0 INT);
CREATE TABLE t1 (c0 INT);
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t1(c0) VALUES (1);

Then these queries:

SELECT 1 FROM t0 WHERE ((SELECT 0 WHERE FALSE) > ALL((SELECT 1 FROM t1)));
SELECT 1 FROM t0 WHERE (NOT ((SELECT 0 WHERE FALSE) > ALL((SELECT 1 FROM t1))));
SELECT 1 FROM t0 WHERE (((SELECT 0 WHERE FALSE) > ALL((SELECT 1 FROM t1))) IS NULL);

By the partition logic (true, false, NULL) one of them should return one row, but all of them return empty. By looking closely, the last query seems to be wrong, and it should return 1 row. (SELECT 0 WHERE FALSE) is empty and empty > ALL is NULL.

How to repeat:
Run the statements above.

Hi Mr. Ferreira,

Thank you for your bug report.

However this is not a bug.

Any SELECT node of the kind:

SELECT ..... WHERE FALSE

will return the empty row. Empty row compared with any other SELECT node or nodes will also return empty result.

Hence, this is not a bug.

I tested these queries on another DBMS and the last query returns one row.

Hi,

You can file a bug report about it on the site of another's DBMS.

To us, that definitely looks like a bug, if a row is returned.