Support eliminate common expression in selection and projection
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Feature Request
**Is your feature request related to a problem? Please describe:**
It is a common pattern that we want to filter by an expression as well as know the result of that expression, like: (this example comes from in [MySQL manual](https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#operator_json-column-path))
```sql
create table j(a json);
select JSON_EXTRACT(a, "$.id") FROM j WHERE JSON_EXTRACT(a, "$.id") > 1;
```
An ideal execution plan is:
```
TableScan → Projection(JSON_EXTRACT(a, "$.id") → Col#1) → Selection(Col#1 > 1)
```
The current execution plan is:
```
TableScan → Selection(JSON_EXTRACT(a, "$.id") > 1) → Projection(JSON_EXTRACT(a, "$.id") → Col#1)
```
The ideal execution plan is more efficient due to reducing duplicated function calculations:
Suppose there are N rows in TableScan and K rows after filtering.
- In ideal plan, JSON_EXTRACT() executes N times, GT() executes N times
- In current plan, JSON_EXTRACT() executes N+K times, GT() executes N times
This optimization is very useful when the common expression is heavy. Common expression is also very commonly used, for example, in scenarios like Full Text Search, to return the search rank: (this is a recommended way of using FTS by [MySQL manual](https://dev.mysql.com/doc/refman/8.4/en/fulltext-natural-language.html))
```sql
mysql> SELECT id, body, MATCH (title,body)
-> AGAINST ('Security implications of running MySQL as root'
-> IN NATURAL LANGUAGE MODE) AS score
-> FROM articles
-> WHERE MATCH (title,body)
-> AGAINST('Security implications of running MySQL as root'
-> IN NATURAL LANGUAGE MODE);
```
**Describe the feature you'd like:**
Optimizer generates the ideal plan.
**Describe alternatives you've considered:**
**Teachability, Documentation, Adoption, Migration Strategy:**
Contributor guide
Assessment
This issue has not been assessed yet.