pingcap / pingcap/tidb

Agg cannot push down though foreign key

Open
#63,806 0 comments 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
severity/moderate sig/planner type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.