Support eliminate common expression in selection and projection

[breezewish]

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)

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)

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:

[AilinKid]

seems its more like a heavy projection should be pushed down to selection, and its not always optimal, should be trade off with the cost model of the how heavy it is.