Question about subselects in Hibernate 6

Hibernate ORM
1

Hello,
if I have a One-To-Many relationship between 2 tables, let’s say person and car and I want to select every person without a car, my SQL would look something like this:

select * from person p where 
    (select count(*) from car c left join person per on c.person = per.id where per.id = p.id) = 0;

In Hibernate 5 we used a generalized specification for these cases. Something like this:

public class HasNoElementsSpecificationOneToMany<T, F> implements Specification<T>
{
	private final String subPath;
	private final Class<F> subClass;

	public HasNoElementsSpecificationOneToMany(final String subPath, final Class<F> subClass)
	{
		this.subPath = subPath;
		this.subClass = subClass;
	}

	@Override
	public Predicate toPredicate(final Root<T> root, final CriteriaQuery<?> query, final CriteriaBuilder cb)
	{
		final Subquery<Long> sub = query.subquery(Long.class);
		final Root<F> subFrom = sub.from(this.subClass);
		final Path<T> processedPath = subFrom.get(this.subPath);

		sub.select(cb.count(subFrom));
		sub.where(cb.equal(processedPath, root));

		return cb.equal(sub, Long.valueOf(0));
	}
}

This worked as expected, however after migrating to Hibernate 6 we’re getting an exception with this root cause:

Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [SqmRoot not yet resolved to TableGroup]; this may indicate a semantic (user query) problem or a bug in the parser [SqmRoot not yet resolved to TableGroup]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitRootPath(BaseSqmToSqlAstConverter.java:3413) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitRootPath(BaseSqmToSqlAstConverter.java:415) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.tree.from.SqmRoot.accept(SqmRoot.java:160) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:6562) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:415) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.accept(SqmComparisonPredicate.java:104) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2263) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1834) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:415) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:122) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:213) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1690) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:6049) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:415) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.tree.select.SqmSubQuery.accept(SqmSubQuery.java:641) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:6552) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:415) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.accept(SqmComparisonPredicate.java:104) ~[hibernate-core-6.1.2.Final.jar:6.1.2.Final]
...

Is there a way I can fix the specification so it works with the current version? I’m currently unsure why the object mapper doesn’t seem to like this subselect anymore.

Any insights would be very much appreciated.

2

Can you please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue.

I think though, the issue is that the outer root is not correlated i.e. you need to use the following code:

	sub.where(cb.equal(processedPath, sub.correlate(root)));
3

Ah, the correlate solves the problem, thanks. Good to know that this is now necessary.

Should I still create an issue, or is everything working as intended?

4

Yes, please create an issue. We initially wanted to be stricter about the requirement for the call to correlate and also give proper errors, but the JPA TCK appears to require to support even the case when correlate is not called, which kind of makes correlate useless IMO, but whatever.

5

Sorry, I just now found the time to get back to this. Looks like that was too slow though. While building a test case I saw that one already existed that pointed to this Issue.

I’ll try to do better next time.

6

Actually, just for completeness, I’d like to point out that my understanding of correlate was a bit too strict. The use of correlate is not necessary, except if you want to make sure joins are put into the scope of the subquery instead of the query where the From object originates from.

I fixed that now and everything should work as in previous Hibernate versions.