rollup will output more rows than expected #62551
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
create table `testpay`(
`bill_code` varchar(100),
`org_id` decimal(19,0),
`amt` decimal(15,2),
`pay_date` varchar(10),
PRIMARY KEY (`bill_code`));
create table `testreturn`(
`bill_code` varchar(100),
`org_id` decimal(19,0),
`amt` decimal(15,2),
`ret_date` varchar(10),
PRIMARY KEY (`bill_code`));
create table `testorg`(
`org_id` decimal(19,0),
`org_code` varchar(100),
`org_name` varchar(100),
`org_type` varchar(100),
PRIMARY KEY (`org_id`));
insert into testorg (org_id,org_code,org_name,org_type) values(1,'ORG0001','部门1','DEPT');
insert into testorg (org_id,org_code,org_name,org_type) values(2,'ORG0002','部门2','DEPT');
insert into testorg (org_id,org_code,org_name,org_type) values(3,'ORG0003','部门3','DEPT');
insert into testorg (org_id,org_code,org_name,org_type) values(4,'ORG0004','部门4','DEPT');
insert into testorg (org_id,org_code,org_name,org_type) values(5,'ORG0005','公司1','ORG');
insert into testorg (org_id,org_code,org_name,org_type) values(6,'ORG0006','公司2','ORG');
insert into testorg (org_id,org_code,org_name,org_type) values(7,'ORG0007','公司3','ORG');
insert into testpay (bill_code,org_id,amt,pay_date) values('PAY0001',1,100,'2024-06-01');
insert into testpay (bill_code,org_id,amt,pay_date) values('PAY0002',2,200,'2024-06-02');
insert into testpay (bill_code,org_id,amt,pay_date) values('PAY0003',3,300,'2024-06-03');
insert into testpay (bill_code,org_id,amt,pay_date) values('PAY0004',4,400,'2024-07-01');
insert into testpay (bill_code,org_id,amt,pay_date) values('PAY0005',5,500,'2024-07-02');
insert into testpay (bill_code,org_id,amt,pay_date) values('PAY0006',6,600,'2024-07-03');
insert into testreturn (bill_code,org_id,amt,ret_date) values('RET0001',1,100,'2024-06-01');
insert into testreturn (bill_code,org_id,amt,ret_date) values('RET0002',2,200,'2024-06-02');
insert into testreturn (bill_code,org_id,amt,ret_date) values('RET0003',3,300,'2024-06-03');
insert into testreturn (bill_code,org_id,amt,ret_date) values('RET0004',4,400,'2024-07-01');
SELECT
SUM(IFNULL(pay.payamt, 0)) AS payamt,
SUM(IFNULL(ret.retamt, 0)) AS retamt,
org.org_type,
org.org_id,
org.org_name
FROM testorg org
LEFT JOIN (
SELECT
SUM(IFNULL(amt, 0)) AS payamt,
org_id
FROM testpay tp
WHERE tp.pay_date BETWEEN '2024-06-01' AND '2024-07-31'
GROUP BY org_id
) pay ON pay.org_id = org.org_id
LEFT JOIN (
SELECT
SUM(IFNULL(amt, 0)) AS retamt,
org_id
FROM testreturn tr
WHERE tr.ret_date BETWEEN '2024-06-01' AND '2024-07-31'
GROUP BY org_id
) ret ON ret.org_id = org.org_id
GROUP BY org.org_type, org.org_id WITH ROLLUP;
SELECT * FROM ( SELECT SUM(IFNULL(pay.payamt, 0)) AS payamt, SUM(IFNULL(ret.retamt, 0)) AS retamt, GROUPING(org.org_type) AS grouptype, org.org_type, GROUPING(org.org_id) AS groupid, org.org_id, org.org_name FROM testorg org LEFT JOIN ( SELECT SUM(IFNULL(amt, 0)) AS payamt, org_id FROM testpay tp WHERE tp.pay_date BETWEEN '2024-06-01' AND '2024-07-31' GROUP BY org_id ) pay ON pay.org_id = org.org_id LEFT JOIN ( SELECT SUM(IFNULL(amt, 0)) AS retamt, org_id FROM testreturn tr WHERE tr.ret_date BETWEEN '2024-06-01' AND '2024-07-31' GROUP BY org_id ) ret ON ret.org_id = org.org_id GROUP BY org.org_type, org.org_id WITH ROLLUP ) t WHERE groupid = 1 AND grouptype = 1;
2. What did you expect to see? (Required)
17 rows
the top agg seems be eliminated by some means
mysql> explain SELECT SUM(IFNULL(pay.payamt, 0)) AS payamt, SUM(IFNULL(ret.retamt, 0)) AS retamt, org.org_type, org.org_id, org.org_name FROM testorg org LEFT JOIN ( SELECT SUM(IFNULL(amt, 0)) AS payamt, org_id FROM testpay tp WHERE tp.pay_date BETWEEN '2024-06-01' AND '2024-07-31' GROUP BY org_id ) pay ON pay.org_id = org.org_id LEFT JOIN ( SELECT SUM(IFNULL(amt, 0)) AS retamt, org_id FROM testreturn tr WHERE tr.ret_date BETWEEN '2024-06-01' AND '2024-07-31' GROUP BY org_id ) ret ON ret.org_id = org.org_id GROUP BY org.org_type, org.org_id, org.org_name WITH ROLLUP;
+--------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_21 | 7.00 | root | | cast(ifnull(Column#9, 0.00), decimal(59,2) BINARY)->Column#21, cast(ifnull(Column#14, 0.00), decimal(59,2) BINARY)->Column#22, Column#15->Column#23, Column#16->Column#24, Column#17->Column#25 |
| └─Expand_25 | 7.00 | root | | level-projection:[Column#9, Column#14, <nil>->Column#15, <nil>->Column#16, 0->Column#17],[Column#9, Column#14, Column#15, <nil>->Column#16, 1->Column#17],[Column#9, Column#14, Column#15, Column#16, 3->Column#17],[Column#9, Column#14, Column#15, Column#16, 7->Column#17]; schema: [Column#9,Column#14,Column#15,Column#16,Column#17] |
| └─Projection_28 | 7.00 | root | | Column#9, Column#14, test.testorg.org_type->Column#15, test.testorg.org_id->Column#16, test.testorg.org_name->Column#17 |
| └─Projection_29 | 7.00 | root | | test.testorg.org_id, test.testorg.org_name, test.testorg.org_type, Column#9, Column#14 |
| └─HashJoin_30 | 7.00 | root | | left outer join, equal:[eq(test.testorg.org_id, test.testpay.org_id)] |
| ├─HashAgg_48(Build) | 1.00 | root | | group by:Column#33, funcs:sum(Column#32)->Column#9, funcs:firstrow(Column#33)->test.testpay.org_id |
| │ └─Projection_57 | 0.15 | root | | ifnull(test.testpay.amt, 0.00)->Column#32, test.testpay.org_id->Column#33 |
| │ └─TableReader_55 | 0.15 | root | | data:Selection_54 |
| │ └─Selection_54 | 0.15 | cop[tikv] | | ge(test.testpay.pay_date, "2024-06-01"), le(test.testpay.pay_date, "2024-07-31"), not(isnull(test.testpay.org_id)) |
| │ └─TableFullScan_53 | 6.00 | cop[tikv] | table:tp | keep order:false, stats:pseudo |
| └─HashJoin_32(Probe) | 7.00 | root | | left outer join, equal:[eq(test.testorg.org_id, test.testreturn.org_id)] |
| ├─HashAgg_38(Build) | 1.00 | root | | group by:Column#31, funcs:sum(Column#30)->Column#14, funcs:firstrow(Column#31)->test.testreturn.org_id |
| │ └─Projection_56 | 0.10 | root | | ifnull(test.testreturn.amt, 0.00)->Column#30, test.testreturn.org_id->Column#31 |
| │ └─TableReader_45 | 0.10 | root | | data:Selection_44 |
| │ └─Selection_44 | 0.10 | cop[tikv] | | ge(test.testreturn.ret_date, "2024-06-01"), le(test.testreturn.ret_date, "2024-07-31"), not(isnull(test.testreturn.org_id)) |
| │ └─TableFullScan_43 | 4.00 | cop[tikv] | table:tr | keep order:false, stats:pseudo |
| └─TableReader_35(Probe) | 7.00 | root | | data:TableFullScan_34 |
| └─TableFullScan_34 | 7.00 | cop[tikv] | table:org | keep order:false, stats:pseudo |
+--------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (2 min 43.843 sec)
+---------+---------+-----------+----------+---------+--------+----------+
| payamt | retamt | grouptype | org_type | groupid | org_id | org_name |
+---------+---------+-----------+----------+---------+--------+----------+
| 2100.00 | 1000.00 | 1 | NULL | 1 | NULL | 部门1 |
+---------+---------+-----------+----------+---------+--------+----------+
3. What did you see instead (Required)
28 rows
+---------+---------+-----------+----------+---------+--------+----------+
| payamt | retamt | grouptype | org_type | groupid | org_id | org_name |
+---------+---------+-----------+----------+---------+--------+----------+
| 1100.00 | 0.00 | 0 | ORG | 1 | NULL | 公司1 |
| 2100.00 | 1000.00 | 1 | NULL | 1 | NULL | 部门1 |
| 1000.00 | 1000.00 | 0 | DEPT | 1 | NULL | 部门1 |
+---------+---------+-----------+----------+---------+--------+----------+
4. What is your TiDB version? (Required)
master