Pushdown conditions into non-mergeable views/derived tables

Description

It is possible to do condition pushdown into non-mergable VIEWs or derived tables.

Example:

1 2 3 4 5 6 7 select ... from (select col1, max(col2) as max_val from t2 group by t1 ) TBL where col1 !='foo' and max_val > 100

here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause.

Implementation challenges

Moving condition from one select into another changes the context which condition is used in.
Table condition pushdown produces strictest-possible conditions, but has a property that certain part of condition can be attached to multiple tables.
Doing the same thing here will cause trouble: if the same part of the condition is both in subquery's HAVING and in upper select's WHERE, it should e.g. automagically changes its used_tables() depending on what context we're looking at it from.
The solution to this is to do limited pushdown: only push down expressions that we can completely remove from upper query's WHERE.

Pushdown may also require adjustments to ref_pointer_array. Sanja and Igor seem to understand the details of this.

Environment

None

Status

Assignee

Unassigned

Reporter

Sergei Petrunia

Labels

External issue ID

None

External issue ID

None

Time tracking

80h

Components

Fix versions

Priority

Minor