have the wrong result

[jiangxiaom]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

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(1,1,1,'A01','01',111,111);
insert into test.Tab_A values(2,2,2,'A01','01',112,111);
insert into test.Tab_A values(3,3,3,'A02','02',113,111);
insert into test.Tab_A values(4,4,4,'A02','02',112,111);
insert into test.Tab_A values(5,5,5,'A01','01',111,111);
insert into test.Tab_A values(6,6,6,'A02','02',113,111);
insert into test.Tab_A values(7,5,7,'A01','01',111,88);
insert into test.Tab_A values(8,6,8,'A02','02',113,88);
insert into test.Tab_B values(1,'B01');
insert into test.Tab_B values(2,'B01');
insert into test.Tab_B values(3,'B02');
insert into test.Tab_B values(4,'B02');
insert into test.Tab_B values(5,'B02');
insert into test.Tab_B values(6,'B02');
insert into test.Tab_C values(1,'C01',3);
insert into test.Tab_C values(2,'C01',22);
insert into test.Tab_C values(3,'C01',32);
insert into test.Tab_C values(4,'C01',5);
insert into test.Tab_C values(5,'C01',6);
insert into test.Tab_C values(6,'C01',9);

以下执行SQL

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
+----------------------------------------------+
AAA BBB Aamt Bamt IFNULL(Tab_C.amt, 0)
A01 B01 111.00 22.00 22.00
A02 B02 111.00 NULL 0
+----------------------------------------------+

select Tab_A.name AAA,Tab_B.name BBB,(Tab_A.amt - SUM(IFNULL(Tab_C.amt, 0))) amt,COUNT(Tab_A.id) times
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
group by Tab_A.name,Tab_B.name
+-------------------------+
AAA BBB amt times
A02 NULL 106.00 1
A01 B01 89.00 1
+-------------------------+

2. What did you expect to see?

see the right result

3. What did you see instead?

see the wrong result
select Tab_A.name AAA,Tab_B.name BBB,(Tab_A.amt - SUM(IFNULL(Tab_C.amt, 0))) amt,COUNT(Tab_A.id) times
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
group by Tab_A.name,Tab_B.name
+-------------------------+
AAA BBB amt times
A02 NULL 106.00 1
A01 B01 89.00 1
+-------------------------+

4. What version of TiUP are you using (tiup --version)?

tiup --version
1.10.3 tiup
Go Version: go1.18.5
Git Ref: v1.10.3
GitHash: e198ac5

select VERSION()
+---------------------+
5.7.25-TiDB-v6.1.0
+---------------------+

[qqqdan]

This will be addressed in pingcap/tidb#37238