pingcap/tidb

Executor, Planner: convert in_subquery to hash joins when in_subquery just involves one column

Open

#13,972 建立於 2019年12月9日

在 GitHub 查看
 (1 留言) (0 反應) (1 負責人)Go (40,090 star) (6,186 fork)batch import
help wantedsig/executionsig/plannertype/enhancement

描述

Feature Request

Is your feature request related to a problem? Please describe:

select * from S where S.a in (select a from T); where s.a and t.a are nullable. in TiDB, this query is converted to a NestLoopJoin, which is time-consuming. in Mysql, this query is transformed into looking up a materialized table using an equal key, which is like a hash join.

Describe the feature you'd like:

Supporting a nullable join key in current hash join of TiDB, NOTE only one key. Otherwise, it should take the NestLoopJoin .

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html

貢獻者指南

技術棧
go
領域
databasebackendperformance
議題類型
feature
難度面向新貢獻者的預計實作難度，1 表示很小改動，5 表示專家級工作。
4
預計時間有經驗貢獻者完成調查、實作、測試並準備 pull request 的粗略時間範圍。
over 1 week
活動狀態議題目前的可參與程度：新鮮、活躍、陳舊、阻塞或等待維護者輸入。
blocked
清晰度議題是否清楚說明預期改動、驗收標準和下一步。
clear
前置要求
Go programmingTiDB executorhash join conceptssubquery optimization
新手友善度1-100 的估計分數，表示該議題對首次貢獻者的友善程度。
20
研究方向
This issue requires implementing nullable join key support in TiDB's hash join for single column in subquery. Start by examining the current hash join implementation in the executor package (e.g., `executor/join.go` and related files). Understand how the nested loop join currently handles this case. The referenced MySQL documentation on subquery materialization provides a reference behavior. Check if there are any existing discussions in the issue comments about design constraints. Since there is an assignee, coordinate with them to avoid duplication.