CTE based queries fail if hibernate filters are being used

[nlippke]

Description

If a CTE is build around an entity that also supports hibernate filters the query will fail, because the inner query is missing the filter clause but the executor tries to set an extra filter parameter.

A generated SQL looks like

select * from Cat cat0_
where cat0_.gender = ? // filter applied
and (cat0_.id in ((select catcte1_.id from (select cat0_.id as col_0_0_,
              DENSE_RANK() OVER (PARTITION BY cat1_.id ORDER BY cat0_.id ASC NULLS LAST) as col_1_0_
              from Cat cat0_
              left outer join Cat cat1_ on cat0_.mother_id = cat1_.id
              where cat1_.id in (?, ?) // filter missing
    ) catcte1_(id,rank)
    where catcte1_.rank>=?)))

So you always end up with an error, such as

Invalid value "5" for parameter "parameterIndex" [90008-200]

because the query itself only requires 4 arguments.

Steps to reproduce

(CteTest) shows a test case that fails.

[nlippke]

Honestly I'm not really sure about the source of the problem. Maybe it's a hibernate issue?

[beikov]

Hey there, it's probably a bug in the integration, since we need to extract and transform SQL to render CTEs, I guess that we didn't think about filters too much.

Thanks for the test case! Do you think you could workaround the problem for now by applying the filter manually in the WHERE clause? I'm not sure how easy it is to fix this yet, but I believe that the SQL extraction is maybe not taking the filter into account properly.

[jwgmeligmeyling]

Should be easy to construct a test case derived from CTESubselectFilteringTest.

It already appears to use a CTE on an entity with an active filter, so I am not sure what exactly is the difference here.

[nlippke]

Thanks for the test case! Do you think you could workaround the problem for now by applying the filter manually in the WHERE clause?

I don't know how. If I add extra condition than the I'll get the same error with increments argument index.

It looks like your not passing active filters to hibernate in getOriginalQueryPlan(). Instead you're passing an empty collection so that there resulting SQL does not contain the filter clause.