MySQL Bugs: #111266: Can't delete a row

Bug #111266	Can't delete a row
Submitted:	3 Jun 2023 5:30	Modified:	5 Jun 2023 9:25
Reporter:	Doris Li	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	8.0.33	OS:	Ubuntu (20.04.2)
Assigned to:		CPU Architecture:	x86 (x86_64)

Description:
Can't delete a row of data.

How to repeat:
create table data_a_tmp(a1 float, a2 float, a3 float, a4 float);
insert into data_a(a1, a2, a3, a4) values (6.97,2.13,4.5,4.0);
insert into data_a(a1, a2, a3, a4) values (9.52,4.87,5.33,7.21);
insert into data_a(a1, a2, a3, a4) values (4.69,3.82,5.66,7.22);
insert into data_a(a1, a2, a3, a4) values (3.8,7.55,5.18,7.82);
insert into data_a(a1, a2, a3, a4) values (9.95,5.0,5.26,6.09);
insert into data_a(a1, a2, a3, a4) values (3.98,6.8,5.93,6.8);
insert into data_a(a1, a2, a3, a4) values (3.3,5.89,5.44,6.22);
insert into data_a(a1, a2, a3, a4) values (2.07,7.09,3.01,6.82);
insert into data_a(a1, a2, a3, a4) values (4.98,4.9,3.04,5.11);
insert into data_a(a1, a2, a3, a4) values (8.98,8.46,5.93,4.28);
insert into data_a(a1, a2, a3, a4) values (6.97,2.13,4.5,NULL);

delete from data_a_tmp where a4=NULL;
select * from data_a_tmp;
+------+------+------+------+
| a1   | a2   | a3   | a4   |
+------+------+------+------+
| 6.97 | 2.13 |  4.5 |    4 |
| 9.52 | 4.87 | 5.33 | 7.21 |
| 4.69 | 3.82 | 5.66 | 7.22 |
|  3.8 | 7.55 | 5.18 | 7.82 |
| 9.95 |    5 | 5.26 | 6.09 |
| 3.98 |  6.8 | 5.93 |  6.8 |
|  3.3 | 5.89 | 5.44 | 6.22 |
| 2.07 | 7.09 | 3.01 | 6.82 |
| 4.98 |  4.9 | 3.04 | 5.11 |
| 8.98 | 8.46 | 5.93 | 4.28 |
| 6.97 | 2.13 |  4.5 | NULL |
+------+------+------+------+
11 rows in set (0.00 sec)

The NULL row is not deleted.

not sure the `= NULL` is valid.

Try with:

 delete from data_a_tmp  where a4 is NULL;

In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false.

The reasoning is that a null means "unknown", the result of any comparison to a null is also "unknown". So you'll get no hit on rows by coding where my_column = null.

The correct syntax for testing if a column is null, via IS null and IS NOT null.

See the manual: https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

I finally figured out that it is not a bug.