Delete using outer join can mistakenly delete the row whose handle=0

[ekexium]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table a (k1 int);
create table b (id int primary key, k1 int);
insert into a(k1) values(3);
insert into b values(2, 2);
insert into b values(0, 0);
delete from a, b using a left join b on a.k1 = b.k1;
select * from b;

2. What did you expect to see? (Required)

2 rows: (0,0) and (2,2), as in MySQL

mysql> select * from b;
+----+------+
| id | k1   |
+----+------+
|  0 |    0 |
|  2 |    2 |
+----+------+

3. What did you see instead (Required)

mysql> select * from b;
+----+------+
| id | k1   |
+----+------+
|  2 |    2 |
+----+------+

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v5.5.0-alpha-21-g722303bca
Edition: Community
Git Commit Hash: 722303b
Git Branch: HEAD
UTC Build Time: 2022-01-04 14:53:04
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ekexium]

The assertion feature introduced in #31547 will report an error when meeting the bug.

mysql-test in MergeCI contains such a case, and will therefore fail until the bug is fixed:

tk.MustExec("create table t1 (n numeric(10));")
tk.MustExec("create table t2 (n numeric(10));")
tk.MustExec("insert into t2 values (1),(2),(4),(8),(16),(32);")
tk.MustExec("select * from t2 left outer join t1  using (n);")
tk.MustExec("delete  t1,t2 from t2 left outer join t1  using (n);")
tk.MustExec("select * from t2 left outer join t1  using (n);")
tk.MustExec("drop table t1,t2 ;")

[morgo]

The test is called multi_update if you are looking for it. I ran into the same issue today.

[ekexium]

Copied from previous investigation:

It seems to me that MySQL uses an explicit variable to indicate null rows generated by outer joins.

set_null_row() and reset_null_row() are used by the join executor to
signal the presence or absence of a NULL-extended row for an outer joined
table.

https://github.com/mysql/mysql-server/blob/3290a66c89eb1625a7058e0ef732432b6952b435/sql/table.h#L1961-L1975

And there is a check to skip such rows when deleting.

https://github.com/mysql/mysql-server/blob/3290a66c89eb1625a7058e0ef732432b6952b435/sql/sql_delete.cc#L1040-L1041