MySQL Bugs: #111358: delete handl error

Bug #111358	delete handl error
Submitted:	12 Jun 2023 8:41	Modified:	13 Jun 2023 12:25
Reporter:	sherman chen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	delete, subquery, syntax error

Description:
Using subqueries to delete other records from the same table can cause issues. If the field name is written incorrectly, it can result in the entire table being deleted. When executed individually,  it will throw a syntax error.

version:8.0.33

How to repeat:
CREATE TABLE `platform_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `val` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `open_id` (`val`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=25129 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `platform_user`(`id`, `val`) VALUES (25126, '1');
INSERT INTO `platform_user`(`id`, `val`) VALUES (25127, '2');
INSERT INTO `platform_user`(`id`, `val`) VALUES (25128, '1');

delete from platform_user where id in (
select id from (
select max(id) as mid from platform_user
where val!=''
GROUP BY val
HAVING count(*)>=2 
) t1
) 

max(id) as mid，but select id from (...) will throw a syntax error.but whold sql not throw a syntax error ,and delete whole the table

Suggested fix:
throw a syntax error

Hi Mr. chen,

Thank you for your bug report.

However, it is impossible for us to understand what kind of bug are you reporting.

First of all, please write the entire query in the single line.

Second, write the query, that is a part of the first query, fully and in one line only.

Waiting on your feedback.

Hi,

Next, if you use GROUP BY, you can not include COUNT in the HAVING clause. HAVING clause has to contain only fields that are listed in the SELECT list. 

This is basic SQL, which is why this is not a bug.

Hi,

Thank you for your response. I apologize for not providing clear information in my initial report. Here's the updated version:

First, I created a table using the following SQL statement:

CREATE TABLE `platform_user` (`id` int NOT NULL AUTO_INCREMENT, `val` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Next, I inserted three rows of data into the table using the following INSERT statements:

INSERT INTO `platform_user`(`id`, `val`) VALUES (25126, '1');
INSERT INTO `platform_user`(`id`, `val`) VALUES (25127, '2');
INSERT INTO `platform_user`(`id`, `val`) VALUES (25128, '1');

The problematic query that caused the issue is as follows:

DELETE FROM platform_user WHERE id IN (SELECT id FROM (SELECT MAX(id) AS mid,count(*) as num FROM platform_user WHERE val != '' GROUP BY val HAVING num >= 2) t1)

In the above delete statement, I moved the "count(*) >= 2" condition from HAVING clause to the inner SELECT statement and renamed it as "num >= 2." However, the issue still persists.

Executing the subquery individually produces the expected result:

SELECT MAX(id) AS mid,count(*) as num FROM platform_user WHERE val != '' GROUP BY val HAVING num >= 2

Result:
25128 2

However, when I include an additional level of nesting in the query as shown below, it triggers a syntax error:

SELECT id FROM (SELECT MAX(id) AS mid,count(*) as num FROM platform_user WHERE val != '' GROUP BY val HAVING num >= 2) t1

Error Message:
Unknown column 'id' in 'field list'

Surprisingly, in the complete delete statement, there was no syntax error reported, but it deleted all three records from the table directly:

DELETE FROM platform_user WHERE id IN (SELECT id FROM (SELECT MAX(id) AS mid,count(*) as num FROM platform_user WHERE val != '' GROUP BY val HAVING num >= 2) t1)

Affected rows: 3

Thank you for your attention to this matter.Please let me know if you require any further information from my end.

Sincerely

Hi Mr. chen,

We managed to repeat the behaviour.

Here is the output that we get, the last one showing that the table is empty:

+-------+-----+
| mid   | num |
+-------+-----+
| 25128 |   2 |
+-------+-----+
ERROR 1054 (42S22) at line 14: Unknown column 'id' in 'field list'
+--------------+
| ------------ |
+--------------+
| ------------ |
+--------------+

A bug is in DELETE statement, of course.

Verified as reported.

Posted by developer:
 
I don't think this is a bug.

In the clause:

  WHERE id IN (SELECT id

I think that the "id" in the subquery is mistaken for "mid". "id" is not a column from the subquery tables, hence it is an outer reference resolved from the table to be deleted from, thus the query becomes something like:

  DELETE FROM platform_user WHERE id = id

which effectively deletes all rows in the table.