pingcap / pingcap/tidb

TiDB should support equivalence class propagation for JOIN op

Open
#39,217 1 comment 0 reactions 1 assignee Claimed by @winoros View on GitHub
affects-6.5 may-affects-4.0 may-affects-5.0 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-6.0 may-affects-6.1 may-affects-6.2 may-affects-6.3 may-affects-6.4 sig/planner type/enhancement
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')`
![image](https://user-images.githubusercontent.com/3000021/202423252-708d011e-0851-4d08-8025-dd23560d010f.png)

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');"
![image](https://user-images.githubusercontent.com/3000021/202423000-09b85a58-6173-4c28-ade1-3081781076eb.png)

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

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.