Today I ran into an issue with Hibernate 6 and Firebird 4 database which is best demonstrated by the following query:

select * from rdb$relations 
where rdb$relation_id in ((select rdb$relation_id from rdb$relations))

Upon execution I get the following error:

SQL Error [335544652] [21000]: multiple rows in singleton select [SQLState:21000, ISC error code:335544652]

It comes because Firebird internally understands two forms of IN() and handles them differently – the <table subquery> and <scalar subquery>. The use of double parentheses let Firebird interpret the subquery as <scalar subquery> that must have a most one result.

The following (example) query is executed as expected but is not generated by Hibernate 6:

select * from rdb$relations 
where rdb$relation_id in (select rdb$relation_id from rdb$relations)

The problem came into out project after moving from Hibernate 5 to Hibernate 6. While generating SQL from Criteria API Hibernate uses an own implementation of the CriteriaBuilder interface. This implementation handles the IN() constructs by generating an appropriate Predicate instance. In Hibernate 5 there was only the InPredicate implementation. This one handled internally correctly both cases – list of values and subquery. In Hibernate 6 the aforementioned predicate implementation has been replaced with InListPredicate and InSubQueryPredicate. The problem is now that the CriteriaBuilder implementation in Hibernate 6 – SqmCriteriaNodeBuilder – creates the wrong Predicate instance – it creates only InListPredicate which generates double parentheses in the resulting SQL.

The only one workaround that I currently see but don’t really like is not to use JPA API but Hibernate specific API. So instead of

public Predicate getFilterPredicate(Root root, CriteriaQuery query, String name, String filterValue, MatchMode matchMode, CriteriaBuilder builder) {
 Subquery<Customer> subquery = query.subquery(Customer.class);
 Root<Address> from = subquery.from(Address.class);
 subquery.select(from.get("customer"));
 . . . 
 return rootQuery.in(subquery); 
}

use the following

public Predicate getFilterPredicate(Root root, CriteriaQuery query, String name, String filterValue, MatchMode matchMode, CriteriaBuilder builder) {
 Subquery<Customer> subquery = query.subquery(Customer.class);
 Root<Address> from = subquery.from(Address.class);
 subquery.select(from.get("customer"));
 . . . 
 return ((NodeBuilder)builder).in(rootQuery, (SqmSubQuery)subquery);
}

Does somebody have a clue how to let Hibernate 6 create and use InSubQueryPredicate in my case while still sticking to standard JPA API?