select with join return wrong result

[crazycs520]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop database test;
create database test;
use test;

create table test.Tab_A (id int primary key,bid int,cid int,name varchar(20),type varchar(20),num int,amt decimal(11,2));
create table test.Tab_B (id int primary key,name varchar(20));
create table test.Tab_C (id int primary key,name varchar(20),amt decimal(11,2));

insert into test.Tab_A values(2,2,2,'A01','01',112,111);
insert into test.Tab_A values(4,4,4,'A02','02',112,111);
insert into test.Tab_B values(2,'B01');
insert into test.Tab_B values(4,'B02');
insert into test.Tab_C values(2,'C01',22);
insert into test.Tab_C values(4,'C01',5);

select Tab_A.name AAA,Tab_B.name BBB,Tab_A.amt Aamt, Tab_C.amt Bamt,IFNULL(Tab_C.amt, 0)
FROM Tab_A left join Tab_B on Tab_A.bid=Tab_B.id
left join Tab_C on Tab_A.cid=Tab_C.id and Tab_A.type='01'
where Tab_A.num=112;

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

MySQL result is:

+-----+-----+--------+--------+----------------------+
| AAA | BBB | Aamt   | Bamt   | IFNULL(Tab_C.amt, 0) |
+-----+-----+--------+--------+----------------------+
| A01 | B01 | 111.00 | 22.00  | 22.00                |
| A02 | B02 | 111.00 | <null> | 0.00                 |
+-----+-----+--------+--------+----------------------+

3. What did you see instead (Required)

TiDB result is:

+-----+--------+--------+-------+----------------------+
| AAA | BBB    | Aamt   | Bamt  | IFNULL(Tab_C.amt, 0) |
+-----+--------+--------+-------+----------------------+
| A01 | B01    | 111.00 | 22.00 | 22.00                |
| A02 | <null> | 111.00 | 5.00  | 5.00                 |
+-----+--------+--------+-------+----------------------+

4. What is your TiDB version? (Required)

***************************[ 1. row ]***************************
tidb_version() | Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f
Git Branch: HEAD
UTC Build Time: 2022-07-05 08:51:40
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[crazycs520]

Add hint will return right result:

select /*+ HASH_JOIN(Tab_A, Tab_B) */ Tab_A.name AAA,Tab_B.name BBB,Tab_A.amt Aamt, Tab_C.amt Bamt,IFNULL(Tab_C.amt, 0)
FROM Tab_A left join Tab_B on Tab_A.bid=Tab_B.id
left join Tab_C on Tab_A.cid=Tab_C.id and Tab_A.type='01'
where Tab_A.num=112;

+-----+-----+--------+--------+----------------------+
| AAA | BBB | Aamt   | Bamt   | IFNULL(Tab_C.amt, 0) |
+-----+-----+--------+--------+----------------------+
| A01 | B01 | 111.00 | 22.00  | 22.00                |
| A02 | B02 | 111.00 | <null> | 0                    |
+-----+-----+--------+--------+----------------------+

Here is the plan contrast: