MySQL Bugs: #111475: Faulty Subquery results in select all rows for IN clause

Bug #111475	Faulty Subquery results in select all rows for IN clause
Submitted:	18 Jun 2023 13:39	Modified:	23 Jun 2023 7:45
Reporter:	Pedro da Costa	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.033	OS:	Windows (11)
Assigned to:		CPU Architecture:	x86 (Ryzen 7)
Tags:	delete, IN, subquery

Description:
If i have a faulty subquery, that when it stands alone give an error message, but using it as subquery  in an IN clause for a DELETE, results in deleting all rows instead of giving also an error

How to repeat:
#create table
CREATE tABLE cars(id int ,model varchar(20),brand varchar(20),color varchar(20),make varchar(20));

# fill with Data
INSERT INTO cars(id,model,brand,color,make)
VALUES
(1, 'Model S', 'Tesla', 'Blue', 2018),
(2, 'EQS', 'Mercedes-Benz', 'Black', 2022),
(3, 'iX', 'BMW', 'Red', 2022),
(4, 'Ioniq 5', 'Hyundai', 'White', 2021),
(5, 'Model S', 'Tesla', 'Silver', 2018),
(6, 'Ioniq 5', 'Hyundai', 'Green', 2021);

#Show Data that will be deleted
SELECT MAX(id)
FROM cars
GROUP BY model,brand;

# produce an error
SELECT id FROM(
     SELECT MAX(id)
     FROM cars
     GROUP BY model,brand)AS X;

#error: Unknown column 'id' in 'field list'
DELETE
FROM cars
WHERE id IN(
       SELECT id FROM(
              SELECT MAX(id)
              FROM cars
              GROUP BY model,brand)AS X);

# Result: Table is empty.

Suggested fix:
Produce the same error, if the subquery has a bug.

Hi Mr. Costa,

Thank you for your bug report.

However, your bug is a duplicate of this, already verified, bug:

https://bugs.mysql.com/bug.php?id=111358

Duplicate.

This is not a bug.

The column reference "id" in the subquery "SELECT id FROM (SELECT MAX ...)"
is not found in the subquery (hence the error in the second query), thus
it is resolved as an outer reference in the DELETE statement, which effectively becomes

  DELETE FROM cars WHERE id = id;

Thank you, Roy.