pingcap/tidb

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

Open

#13,972 opened on Dec 9, 2019

View on GitHub
 (1 comment) (0 reactions) (1 assignee)Go (40,090 stars) (6,186 forks)batch import
help wantedsig/executionsig/plannertype/enhancement

Description

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

Contributor guide

Tech stack
go
Domain
databasebackendperformance
Issue type
feature
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
4
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
over 1 week
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
blocked
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
clear
Prerequisites
Go programmingTiDB executorhash join conceptssubquery optimization
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
20
Research direction
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.