ENABLE_WITH_CLAUSE_MATERIALIZATION bugged?

dimitri_p

It looks to me like ENABLE_WITH_CLAUSE_MATERIALIZATION does something wrong when a CTE is using row_number/limit. For example, try these on some big table:

with /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/
preproc as
(select * from some_really_big_table limit 1)
select count(1) from preproc

with  /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/
preproc as
(select * from (select *,row_number() over() as rn from some_really_big_table) a where rn<2)
select count(1) from preproc

without the hint both of these queries return in a second or so. But with the hint they seem to take forever as if Vertica tries to materialize the entire big table.

Tested on Vertica 12.0.3

SruthiA

Please create a support case and share the reproducer so that we can review it further.