This query includes a non correlated subquery that can be materialized:
select * from t1 where c11 = (select count(*) from t2)
The first step in the abstract plan materializes the scalar aggregate in the subquery. The second step uses the result to scan t1:
( plan
( i_scan i_c21 ( table t2 ( in (subq 1 ) ) ) )
( i_scan i_c11 t1 )
)
This query includes a vector aggregate in the subquery:
select *
from t1
where c11 in (select max(c21)
from t2
group by c22)
The abstract plan materializes the subquery in the first step, and joins it to the outer query in the second step:
( plan
( store Worktab1
( t_scan ( table t2 ( in (subq 1 ) ) ) )
)
( nl_g_join
( t_scan t1 )
( t_scan ( work_t Worktab1 ) )
)
)
