Elimination of materialized view with GROUP BY in equi-join on grouping fields

If a query contains a left join where the inner table is materialized view or derived table with GROUP BY and join operation is an equi-jion on grouping fields then the following
transformation can be applied to the query:

• the derived is eliminated from the query
• the subquery from the view / derived table specification with WHERE condition enforced by join condition replaces the references to the fields of the view / derived table.

Here is an example of such transformation:

create table t1 (a int);

insert into t1 values (5), (1), (2), (9), (7), (2), (7);

create table t2 (a int, b int, index idx(a));

insert into t2 values (7,10), (1,20), (2,23), (7,18), (1,30), (4,71), (3,15), (7,82);

select t1.a,max from t1 left join (select a, max(t2.b) max from t2 group by t2.a) t on t1.a=t.a;

=> 

select t1.a, (select max(t2.b) max from t2 where t2.a=t1.a) max from t1;

This re-writing can be done on the SQL level: both queries are valid SQL queries and they are equivalent.
In more general case:

select t1.a,max,min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t on t1.a=t.a;

=>

select t1.a, (select max(t2.b) max, min(t2.b) min from t2 where t2.a=t1.a)  from t1;

the transformation produces an illegal query. However, it's obvious how this query can be executed.

The transformation with small changes can be applied to join queries as well.

This transformation allows to avoid materialization of grouping views / derived tables and improve the performance of many equi-join queries that use these views / derived tables.