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-join 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,maxval

from 

  t1 left join (select a, max(t2.b) as maxval 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) as maxval

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, maxval, minval

from 

  t1 left join 

  (select a, max(t2.b) as maxval, min(t2.b) as minval

    from t2 group by t2.a) t on t1.a=t.a;

=>

select 

  t1.a,

  (select 

      max(t2.b) as maxval, min(t2.b) as minval 

   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.