Abstract plans for queries containing aggregates

This query returns a scalar aggregate:

select max(c11) from t1

The first step computes the scalar aggregate and stores it in an internal variable. The second step is empty, as it only returns the variable, in a step with nothing to optimize:

( plan 
        ( t_scan t1 ) 
        ( ) 
)

Vector aggregates are also two-step queries:

select max(c11)
from t1
group by c12

The first step processes the aggregates into a worktable; the second step scans the worktable:

( plan 
        ( store Worktab1 
                ( t_scan t1 ) 
        )
        ( t_scan ( work_t Worktab1 ) ) 
)

Nested aggregates are a Transact-SQL extension:

select max(count(*))
from t1
group by c11

The first step processes the vector aggregate into a worktable, the second scans it to process the nested scalar aggregate into an internal variable, and the third step returns the value.

( plan 
     ( store Worktab1 
          ( i_scan i_c12 t1 ) 
     ) 
     ( t_scan ( work_t Worktab1 ) ) 
     ( ) 
)

Extended columns in aggregate queries are a Transact-SQL extension:

select max(c11), c11
from t1
group by c12

The first step processes the vector aggregate; the second one joins it back to the base table to process the extended columns:

( plan 
     ( store Worktab1 
          ( t_scan t1 ) 
     ) 
     ( g_join 
          ( t_scan t1 ) 
          ( i_scan i_c11 ( work_t Worktab1 ) ) 
     ) 
)

This example contains an aggregate in a merged view:

create view v4
as
select max(c11) as c41, c12 as c42
from t1
group by c12

select * from t2, v4
where c21 = 0
    and c22 > c41

The first step processes the vector aggregate; the second joins it to the main query table:

( plan 
    ( store Worktab1 
        ( t_scan ( table t1 ( in (view v4 ) ) ) )
    ) 
    ( g_join 
        ( i_scan i_c22 t2 ) 
        ( t_scan ( work_t Worktab1 ) ) 
    ) 
)

This example includes an aggregate that is processed using a materialized view:

create view v5
as
select distinct max(c11) as c51, c12 as c52
from t1
group by c12

select * from t2, v5
where c21 = 0
    and c22 > c51

The first step processes the vector aggregate into a worktable. The second step scans it into a second worktable to process the materialized view. The third step joins this second worktable in the main query:

( plan 
    ( store Worktab1 
        ( t_scan ( table t1 ( in (view v5 ) ) ) )
    ) 
    ( store Worktab2 
        ( t_scan ( work_t Worktab1 ) ) 
    ) 
    ( g_join 
        ( i_scan i_c22 t2 ) 
        ( t_scan ( work_t Worktab2 ) ) 
    ) 
)