Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: SQL结果集有问题
|
username: 特雷西-迈克-格雷迪
【TiDB Usage Environment】Production
【TiDB Version】v6.1
【Problem Encountered】The result set of the query is incorrect
【Reproduction Path】What operations were performed to cause the issue
【Problem Phenomenon and Impact】
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);
The following SQL is executed
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
±--------------------------------------------+
【Attachments】
Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: