【 TiDB 使用环境】线上
【 TiDB 版本】v6.1
【遇到的问题】查询的结果集有问题
【复现路径】做过哪些操作出现的问题
【问题现象及影响】
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,COUNT(Tab_A.id) times,
SUM(Tab_A.amt) - SUM(IFNULL(Tab_C.amt, 0)) amt
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 times amt
A01 B01 1 89.00
A02 NULL 1 106.00
±--------------------------------------------+
【附件】
请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。
