blaze-persistence icon indicating copy to clipboard operation
blaze-persistence copied to clipboard
verified publisher icon Blazebit

Limit annotation with multiple count queries from Mapping with SIZE broken

Open mj-p opened this issue 1 year ago • 2 comments

Hello, it's me again. Considering the following example, I added two new Entities named A and B where A holds a @OneToMany collection of Bs. Person and Cat can both hold an A.

@EntityView(A.class)
public interface AView {
    @IdMapping
    Long getId();

    String getName();

    @Mapping("SIZE(bs)")
    Long getBsSize();
}

@EntityView(Cat.class)
public interface CatView {
    @IdMapping
    Long getId();

    String getName();

    AView getA();
}

@EntityView(Person.class)
public interface PersonView {
    @IdMapping
    Long getId();

    String getName();

    AView getA();

    @Limit(limit = "4", order =  {"age DESC", "id DESC"})
    List<CatView> getKittens();
}

When the AView uses @Mapping("SIZE(bs)") and the PersonView has a @Limit on their Kittens it produces a NPE because the "pluralAttributeMapping" for the path Person(generatedPerson_0).a(a_1).bs(bs) is null

java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.mapping.PluralAttributeMapping.createTableGroupJoin(org.hibernate.spi.NavigablePath, org.hibernate.sql.ast.tree.from.TableGroup, String, org.hibernate.sql.ast.spi.SqlAliasBase, org.hibernate.sql.ast.SqlAstJoinType, boolean, boolean, org.hibernate.sql.ast.spi.SqlAstCreationState)" because "pluralAttributeMapping" is null
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeAttributeJoin(BaseSqmToSqlAstConverter.java:3295) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeExplicitJoin(BaseSqmToSqlAstConverter.java:3228) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeJoins(BaseSqmToSqlAstConverter.java:2888) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeFromClauseRoot(BaseSqmToSqlAstConverter.java:2822) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596) ~[na:na]
	at org.hibernate.query.sqm.tree.from.SqmFromClause.visitRoots(SqmFromClause.java:80) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFromClause(BaseSqmToSqlAstConverter.java:2573) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:2055) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:440) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:125) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:218) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1915) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1600) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:440) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:228) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:776) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:399) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:324) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:300) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:509) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.Query.getResultList(Query.java:120) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at com.blazebit.persistence.impl.query.TypedQueryWrapper.getResultList(TypedQueryWrapper.java:49) ~[blaze-persistence-core-impl-jakarta-1.6.11.jar:1.6.11]
	at com.blazebit.persistence.impl.query.ObjectBuilderTypedQuery.getResultList(ObjectBuilderTypedQuery.java:63) ~[blaze-persistence-core-impl-jakarta-1.6.11.jar:1.6.11]
	at com.blazebit.persistence.spring.data.base.repository.AbstractEntityViewAwareRepository.findAll(AbstractEntityViewAwareRepository.java:528) ~[blaze-persistence-integration-spring-data-base-3.1-1.6.11.jar:1.6.11]
	at com.blazebit.persistence.spring.data.impl.repository.EntityViewAwareRepositoryImpl.findAll(EntityViewAwareRepositoryImpl.java:47) ~[blaze-persistence-integration-spring-data-3.1-1.6.11.jar:1.6.11]

The produced HQL is the following:

SELECT    generatedPerson_0.id   AS PersonView_id,
          generatedPerson_0.a.id AS PersonView_a_id,
          (SELECT Count(*) FROM a_1.bs bs) AS PersonView_a_bsSize,
          a_1.name                    AS PersonView_a_name,
          correlated_ext_kittens.id   AS PersonView_kittens_id,
          correlated_ext_kittens.a.id AS PersonView_kittens_a_id,
          Count(DISTINCT bs_1.id)     AS PersonView_kittens_a_bsSize,
          a_3.name                    AS PersonView_kittens_a_name,
          correlated_ext_kittens.name AS PersonView_kittens_name,
          generatedPerson_0.name      AS PersonView_name
FROM      Person generatedPerson_0
LEFT JOIN generatedPerson_0.a a_1
LEFT JOIN Cat correlated_ext_kittens
ON        (
                    correlated_ext_kittens IN (
                    (
                             SELECT   correlated_kittens
                             FROM     Cat correlated_kittens
                             WHERE    correlated_kittens.owner = generatedPerson_0
                             ORDER BY correlated_kittens.age DESC NULLS LAST,
                                      correlated_kittens.id DESC LIMIT 4)))
LEFT JOIN correlated_ext_kittens.a a_3
LEFT JOIN a_3.bs bs_1
GROUP BY  a_3.id,
          generatedPerson_0.id,
          generatedPerson_0.a.id,
          a_1.id,
          a_1.name,
          correlated_ext_kittens.id,
          correlated_ext_kittens.a.id,
          a_3.name,
          correlated_ext_kittens.name,
          generatedPerson_0.name

Removing the @Limit results in the following HQL which works fine even though the count subquery is the same (SELECT Count(*) FROM a_1.bs bs) AS PersonView_a_bsSize,

SELECT    generatedPerson_0.id   AS PersonView_id,
          generatedPerson_0.a.id AS PersonView_a_id,
          (SELECT Count(*) FROM a_1.bs bs) AS PersonView_a_bsSize,
          a_1.name                 AS PersonView_a_name,
          kittens_1.id             AS PersonView_kittens_id,
          kittens_1.a.id           AS PersonView_kittens_a_id,
          Count(DISTINCT bs_1.id)  AS PersonView_kittens_a_bsSize,
          a_3.name                 AS PersonView_kittens_a_name,
          kittens_1.name           AS PersonView_kittens_name,
          generatedPerson_0.name   AS PersonView_name
FROM      Person generatedPerson_0
LEFT JOIN generatedPerson_0.a a_1
LEFT JOIN generatedPerson_0.kittens kittens_1
LEFT JOIN kittens_1.a a_3
LEFT JOIN a_3.bs bs_1
GROUP BY  a_3.id,
          generatedPerson_0.id,
          generatedPerson_0.a.id,
          a_1.id,
          a_1.name,
          kittens_1.id,
          kittens_1.a.id,
          a_3.name,
          kittens_1.name,
          generatedPerson_0.name

I have zero clue yet why Hibernate likes the second one but not the first one and am wondering if this in real a Hibernate bug. Also worth noting removing the AView from either PersonView or CatView is working fine and the count part always looks like Count(DISTINCT bs_1.id) and only with a second AView there is a count subquery like (SELECT Count(*) FROM a_1.bs bs) generated.

Any ideas what might be going on here? Also I would have expected another join instead of a subquery, is there a reason for a subquery?

Version: 1.6.11 JPA-Provider: Hibernate 6.4.1

mj-p avatar Feb 25 '24 18:02 mj-p

A NPE certainly is something that shouldn't happen, so yeah, there is definitely a Hibernate ORM bug lurking. Can you try with ORM 6.4.4 though?

beikov avatar Mar 07 '24 11:03 beikov

I tested a bit more and 6.4.4 doesn't change anything. When using one of the blaze-persistence examples with javax and version 5.6.10 the scenario actually works. There the generated HQL has a subquery like this (SELECT COUNT(*) FROM B bs WHERE bs.a.id = a_1.id) AS PersonView_a_bsSize instead of (SELECT Count(*) FROM a_1.bs bs) AS PersonView_a_bsSize like with 6.4.4.

mj-p avatar Mar 08 '24 18:03 mj-p