select with join return wrong result #37238
Description
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