TiDB should support equivalence class propagation for JOIN op
- 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 p0 (
tenant_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
poi_id bigint(20) unsigned NOT NULL,
spu_id bigint(20) unsigned NOT NULL,
type tinyint(4) unsigned NOT NULL,
name varchar(256) NOT NULL,
tenant_poi_id varchar(30) DEFAULT NULL,
UNIQUE KEY tenant_id(tenant_id,poi_id,spu_id,org_id),
KEY idx_tenant_poi_id (tenant_poi_id),
KEY idx_tps (tenant_id,poi_id,spu_id),
KEY idx_tpt (tenant_poi_id,type)
);
CREATE TABLE p1 (
id bigint(20) unsigned NOT NULL,
tenant_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
poi_id bigint(20) unsigned NOT NULL,
spu_id bigint(20) DEFAULT NULL,
sku_id bigint(20) unsigned NOT NULL,
tenant_poi_id varchar(30) DEFAULT NULL,
UNIQUE KEY tenant_id (tenant_id,id),
UNIQUE KEY unique_tenant_id_org_id_poi_id_sku_id (tenant_id,org_id,poi_id,sku_id),
KEY idx_spu_id (spu_id),
KEY idx_tenant_id_org_id_poi_id_spu_id (tenant_id,poi_id,spu_id),
KEY tentpoi_spu (tenant_poi_id,spu_id),
KEY idx_tenant_id_poi_id_sku_id (tenant_id,poi_id,sku_id)
);
CREATE TABLE p2 (
id bigint(20) NOT NULL AUTO_INCREMENT,
tenant_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
poi_id bigint(20) unsigned NOT NULL,
category_id bigint(20) unsigned NOT NULL,
spu_id bigint(20) unsigned NOT NULL,
tenant_poi_id varchar(30) DEFAULT NULL,
UNIQUE KEY id (id),
UNIQUE KEY idx_tenant_poi_id_spu_id (tenant_poi_id,spu_id)
);
CREATE TABLE p3 (
id bigint(20) unsigned NOT NULL,
tenant_id bigint(20) unsigned NOT NULL,
org_id bigint(20) unsigned NOT NULL,
poi_id bigint(20) unsigned NOT NULL,
category_id bigint(20) unsigned NOT NULL,
type tinyint(4) unsigned NOT NULL,
name varchar(256) NOT NULL,
tenant_poi_id varchar(30) DEFAULT NULL,
UNIQUE KEY tenant_id (tenant_id,poi_id,category_id,org_id),
KEY idx_tenant_poi_id (tenant_poi_id)
);
```
两个sql语句,差别只有黑体部分,输出结果差异是 p3 是否做了 FullIndexScan
sql1 :=
desc
SELECT
/\*+TIDB_HJ(p0,p1,p3),TIDB_INLJ(p2)\*/
p0.tenant_id,
p0.poi_id,
p0.spu_id,
p0.name AS spu_name
FROM
p0
JOIN p1 ON p0.tenant_id = p1.tenant_id
AND p0.poi_id = p1.poi_id
AND p0.spu_id = p1.spu_id
JOIN p2 ON p1.tenant_poi_id = p2.tenant_poi_id
AND p1.spu_id = p2.spu_id
LEFT JOIN p3 ON **p1.tenant_id = p3.tenant_id
AND p1.poi_id = p3.poi_id**
AND p2.category_id = p3.category_id
WHERE
p0.tenant_id IN ('10312060')
AND p0.poi_id IN ('600491386')
AND p0.spu_id IN ('2840204741')`

sql2 := "desc
SELECT
/\*+TIDB_HJ(p0,p1,p3),TIDB_INLJ(p2)\*/
p0.tenant_id,
p0.poi_id,
p0.spu_id,
p0.name AS spu_name
FROM
p0
JOIN p1 ON p0.tenant_id = p1.tenant_id
AND p0.poi_id = p1.poi_id
AND p0.spu_id = p1.spu_id
JOIN p2 ON p1.tenant_poi_id = p2.tenant_poi_id
AND p1.spu_id = p2.spu_id
LEFT JOIN p3 ON **p0.tenant_id = p3.tenant_id
AND p0.poi_id = p3.poi_id**
AND p2.category_id = p3.category_id
WHERE
p0.tenant_id IN ('10312060')
AND p0.poi_id IN ('600491386')
AND p0.spu_id IN ('2840204741');"

Join 形式是:((p0 inner join p1) inner join p2) left outer join p3
sql1中,p1的等值查询常量,没有传递给 p3,造成了 FullIndexScan
问题的原因是:逻辑优化阶段递归执行,给p3做常量传播优化时,((p0 inner join p1) inner join p2) 还没有做优化,还没有p2的等值谓词,所以关于p0的等值谓词,无法传递给p3。
### 2. What did you expect to see? (Required)
sql1没有对p3做优化
### 3. What did you see instead (Required)
sql1 也能产生sql2的优化结果
### 4. What is your TiDB version? (Required)
所有版本
Contributor guide
Assessment
This issue has not been assessed yet.