Description:
This is side effect of commit 44861c7d7134f6c71e0530417b14d5ac3274f52b
How to repeat:
drop table if exists t3;
create table t3 (a int);
insert into t3 values (0), (5), (6), (7);
delete ignore t3.* from t3 where a != (SELECT 1 UNION SELECT 2);
select * from t3;
+------+
| a |
+------+
| 0 |
+------+
but the intention was to delete every row where a is NOT 1 or 2.
Similarly:
drop table if exists t3;
create table t3 (a int);
insert into t3 values (0), (5), (6), (7);
delete ignore t3.* from t3 where a = (SELECT 1 UNION SELECT 2);
select * from t3;
+------+
| a |
+------+
| 5 |
| 6 |
| 7 |
+------+
but the intention was to delete every row where a is 1 or 2.
In simple:
where a = (SELECT 1 UNION SELECT 2) => where a = 0
where a != (SELECT 1 UNION SELECT 2) => where a != 0
Suggested fix:
In my opinion the expected result should be failure of the query, as 'in' or 'not in' respectively should be used (not =, !=) and the table should not be affected at all.
or
= should evaluate to 'in'
!= should evaluate to 'not in'
and proper rows should be deleted
