TiDB should support equivalence class propagation for JOIN op

[xingyong]

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)

所有版本

[winoros]

Yes, this is a limitation of the current TiDB. We're going to improve it. But there's no specific timeline for it.