Agg cannot push down though foreign key
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
```
CREATE TABLE `t1` (
`c1` int NOT NULL,
`c2` char(255) DEFAULT NULL,
PRIMARY KEY (`c1`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `t2` (
`c3` int NOT NULL,
`c4` char(255) DEFAULT NULL,
PRIMARY KEY (`c3`) /*T![clustered_index] CLUSTERED */,
KEY `f1` (`c3`),
CONSTRAINT `f1` FOREIGN KEY (`c3`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `t3` (
`c5` int DEFAULT NULL,
`c6` char(255) DEFAULT NULL,
KEY `c5` (`c5`),
CONSTRAINT `f2` FOREIGN KEY (`c5`) REFERENCES `t1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
set @@tidb_opt_agg_push_down=1;
EXPLAIN SELECT COUNT(t1.c1), t1.c2
FROM t2
INNER JOIN t3 ON t3.c6 = t2.c4
INNER JOIN t1 ON t1.c1 = t2.c3
GROUP BY t2.c3, t1.c1, t1.c2;
```
### 2. What did you expect to see? (Required)
### 3. What did you see instead (Required)
```
+-----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
| HashAgg_17 | 8000.00 | root | | group by:test.t1.c1, test.t1.c2, test.t2.c3, funcs:count(test.t1.c1)->Column#8, funcs:firstrow(test.t1.c2)->test.t1.c2 |
| └─HashJoin_37 | 15609.38 | root | | inner join, equal:[eq(test.t2.c3, test.t1.c1)] |
| ├─HashJoin_61(Build) | 12487.50 | root | | inner join, equal:[eq(test.t2.c4, test.t3.c6)] |
| │ ├─TableReader_68(Build) | 9990.00 | root | | data:Selection_67 |
| │ │ └─Selection_67 | 9990.00 | cop[tikv] | | not(isnull(test.t3.c6)) |
| │ │ └─TableFullScan_66 | 10000.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
| │ └─TableReader_65(Probe) | 9990.00 | root | | data:Selection_64 |
| │ └─Selection_64 | 9990.00 | cop[tikv] | | not(isnull(test.t2.c4)) |
| │ └─TableFullScan_63 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_72(Probe) | 10000.00 | root | | data:TableFullScan_71 |
| └─TableFullScan_71 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------+
```
### 4. What is your TiDB version? (Required)
Contributor guide
Assessment
This issue has not been assessed yet.