MySQL Bugs: #111415: Correlated group query wrong result

Bug #111415	Correlated group query wrong result
Submitted:	14 Jun 2023 9:39	Modified:	19 Jun 2023 11:31
Reporter:	Pedro Ferreira	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	count, GROUP BY, order by

Description:
Run these statements:

CREATE TABLE t0 (c0 TEXT);
INSERT INTO t0(c0) VALUES ('a'),('b');
SELECT CAST(sum(c1) AS SIGNED) FROM (SELECT CAST((SELECT NULL GROUP BY t0.c0 ORDER BY COUNT(t0.c0)) IS NULL AS SIGNED) FROM t0) t1 (c1);

The SELECT query returns 1, while the table has 2 distinct rows, so 2 groups, then 2 NULL values. The result should be 2.

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 statements above.

Added tags

Hi Mr. Ferreira,

Thank you for your bug report.

However, this is not a bug.

The query returns the correct result, since the inner query:

SELECT (SELECT NULL GROUP BY t0.c0 ORDER BY COUNT(t0.c0)) IS NULL  FROM t0;

returns 1. This is correct since, the internal query returns only one result , which is true, id est 1; 

Simply, SELECT NULL IS NULL , returns 1;

Not a bug.

If I remove `ORDER BY COUNT(t0.c0)` which is cosmetic here, I get the right result: 2. I still believe the query result is wrong.

Hi,

This is still not a bug.

If you have ORDER BY a column or expression that is not present in the SELECT list, then it is automatically added there. Hence, this makes a totally different query and totally different result.

HI Mr. Ferreira,

This turns out to be a low severity bug , but still a bug.

Removing ORDER BY from a query should not affect the number of rows output from it, so this is certainly a bug.

Verified.