Bug #108150 ALL query with UNION and ROLLUP wrong result
Submitted: 16 Aug 2022 13:19 Modified: 27 Jan 2023 16:19
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: 8.0.30 OS:Ubuntu (22.04.1)
Assigned to: CPU Architecture:x86 (x86_x64)
Tags: ALL, rollup, UNION ALL

[16 Aug 2022 13:19] Pedro Ferreira
Description:
I am running a SQL oracle to find queries with wrong results. Applying it to this query:

SELECT 1 FROM (SELECT 8) t0(c0);

Adding the TRUE predicate:
SELECT 1 FROM (SELECT 8) t0(c0) WHERE (1 < ALL((SELECT 2) UNION DISTINCT (SELECT 1 GROUP BY 1 WITH ROLLUP)));

Adding the FALSE predicate:
SELECT 1 FROM (SELECT 8) t0(c0) WHERE (NOT (1 < ALL((SELECT 2) UNION DISTINCT (SELECT 1 GROUP BY 1 WITH ROLLUP))));

Adding the IS NULL predicate:
SELECT 1 FROM (SELECT 8) t0(c0) WHERE ((1 < ALL((SELECT 2) UNION DISTINCT (SELECT 1 GROUP BY 1 WITH ROLLUP))) IS NULL);

Both FALSE and IS NULL predicates return 1 row, but only one of them should. After more research, I found out IS NULL to be the culprit (with simplification):

SELECT 1 WHERE (1 < ALL((SELECT 2) UNION (SELECT 1 GROUP BY 1 WITH ROLLUP))) IS NULL;

The result should be empty.

How to repeat:
Run the IS NULL query above.
[16 Aug 2022 13:41] Pedro Ferreira
Applying the same oracle to this predicate:

SELECT 1 FROM (SELECT 8) t0(c0); -- 1 row
SELECT 1 FROM (SELECT 8) t0(c0) WHERE (NOT (0 < ALL(SELECT 2))); -- 0 rows
SELECT 1 FROM (SELECT 8) t0(c0) WHERE (NOT (NOT (0 < ALL(SELECT 2)))); -- 0 rows
SELECT 1 FROM (SELECT 8) t0(c0) WHERE ((NOT (0 < ALL(SELECT 2))) IS NULL); -- 0 rows

With some digging, the query:

SELECT 1 FROM (SELECT 8) t0(c0) WHERE (NOT (NOT (0 < ALL(SELECT 2))));

is giving a wrong result. It should output 1 row instead of 0.

Should this one be filed in another bug report?
[16 Aug 2022 16:44] Pedro Ferreira
Another one:

SELECT 1 WHERE 1 NOT IN (SELECT 1 WHERE TRUE);

Returns 1 row while it is expected none.
[17 Aug 2022 12:01] MySQL Verification Team
Hi Mr. Ferreira,

Thank you very much for your bug report.

We have managed to reproduce your report fully.

This is now a verified bug.
[27 Jan 2023 15:20] Pedro Ferreira
Set right OS
[27 Jan 2023 15:21] Pedro Ferreira
Set right MySQL version.
[27 Jan 2023 16:19] Pedro Ferreira
Set the right architecture.