Bug #109608 Exists query with wrong result

• Submitted: 12 Jan 2023 15:11
• Modified: 24 Jan 2023 11:39
• Reporter: Pedro Ferreira
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.31
• OS: Ubuntu (22.04)
• CPU Architecture: x86 (x86_64)
• Tags: exists

[12 Jan 2023 15:11] Pedro Ferreira

Description:
With this table:

CREATE TABLE t1 (c0 BOOLEAN);

Run these two queries:

SELECT 1 FROM (SELECT 1 FROM t1) t1(c0) WHERE EXISTS (SELECT min(t1.c0));

SELECT sum(c0) FROM (SELECT CAST(EXISTS (SELECT min(t1.c0)) AS SIGNED) FROM (SELECT 1 FROM t1) t1(c0)) t0(c0);

Although they are equivalent, the first query returns no rows, while the global aggregate on the second query returns 1. The number of rows of the first should be the same as the sum result. The first query is attempting an aggregate from the outer query inside the WHERE clause, which should result in error?

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

[12 Jan 2023 15:18] MySQL Verification Team

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

[24 Jan 2023 11:39] Pedro Ferreira

Got another example today:

CREATE TABLE t0 (c0 INT);
SELECT 1 FROM t0 WHERE (SELECT LEAST(COUNT(t0.c0), NULL)) IS NULL;
SELECT CAST(sum(c0) AS SIGNED) FROM (SELECT CAST((SELECT LEAST(COUNT(t0.c0), NULL)) IS NULL AS SIGNED) FROM t0) t0(c0);

The first SELECT query has wrong result.