MySQL Bugs: #110874: Possible missing query error

Bug #110874	Possible missing query error
Submitted:	1 May 2023 12:10	Modified:	2 May 2023 14:00
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:	IN

Description:
Create the table:

CREATE TABLE t0 (c0 INT);

Then run these queries:

SELECT 1 FROM t0 WHERE 1 NOT IN (SELECT count(t0.c0) WHERE FALSE);
SELECT CAST(sum(c1) AS SIGNED) FROM (SELECT CAST(1 NOT IN (SELECT count(t0.c0) WHERE FALSE) AS SIGNED) FROM t0) t1 (c1);

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.

By looking more carefully, the first query is using an aggregate from the outer table inside a subquery in the WHERE clause. This binding I suppose it shouldn't be allowed, and an error must be thrown.

How to repeat:
Run the queries above.

Hi Mr. Ferreira,

Thank you for your bug report.

However, it is not a bug.

According to the SQL standards, the first query must be (internally) rewritten from:

SELECT 1 FROM t0 WHERE 1 NOT IN (SELECT count(t0.c0) WHERE FALSE);

into 

SELECT 1 FROM t0 WHERE 1 NOT IN (SELECT count(t0.c0) WHERE FALSE AND count(t0.c0) != 1);

Similar rules are valid for the second query ......

However, much more important fact that the one mentioned is the one that those two queries are not identical and you can not reduce second one into the first one.

There are query reduction and translation rules and none of those would enable reducing the second query to the first one. Since , this forum is not for discussions,  but for bug verifications, no other explanations are necessary.

Not a bug.