# Question about subselects in Hibernate 6

**URL:** <https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609>\
**Category:** Hibernate ORM\
**Created:** [August 29, 2022, 10:59am UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609 "2022-08-29T10:59:39Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Arne](https://avatars.discourse-cdn.com/v4/letter/a/9de053/32.png) [@Arne](https://discourse.hibernate.org/u/Arne)\
**Post date:** [August 29, 2022, 10:59am UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609/1 "2022-08-29T10:59:39Z")

</div>

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:

```auto
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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [August 29, 2022, 11:43am UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609/2 "2022-08-29T11:43:46Z")

</div>

Can you please create an issue in the issue tracker([https://hibernate.atlassian.net](https://hibernate.atlassian.net)) with a test case([hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java)) 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)));

```

---

<div class="post-metadata">

**Author:** ![Arne](https://avatars.discourse-cdn.com/v4/letter/a/9de053/32.png) [@Arne](https://discourse.hibernate.org/u/Arne)\
**Post date:** [August 29, 2022, 1:21pm UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609/3 "2022-08-29T13:21:09Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [August 29, 2022, 4:28pm UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609/4 "2022-08-29T16:28:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Arne](https://avatars.discourse-cdn.com/v4/letter/a/9de053/32.png) [@Arne](https://discourse.hibernate.org/u/Arne)\
**Post date:** [September 9, 2022, 12:06pm UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609/5 "2022-09-09T12:06:03Z")

</div>

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](https://hibernate.atlassian.net/browse/HHH-15477).

I’ll try to do better next time.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [September 9, 2022, 12:34pm UTC](https://discourse.hibernate.org/t/question-about-subselects-in-hibernate-6/6609/6 "2022-09-09T12:34:53Z")

</div>

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.