That’s simply not how you should do this. There are many issues with this approach.

• Potentially sending long lists twice
• Confusing the query planner
• Destroying reasonable planner estimates when the prepared query switches to a generic statement

Just do what everyone else does who want conditional filtering and only append that condition to the query when you actually have a non-empty list of departments. With e.g. JPA Criteria you’d simply not add that IN predicate to your predicate list if no value is given.

And if you are using HQL, then consider switching to JPA Criteria for such dynamic filtering use cases.

The only possible change I would support is to throw an exception when using coalesce(:param) where :param refers to a list parameter, because list parameters are actually only valid in the IN predicate.