Hibernate orm Panache query projection is not working

[ia3andy]

Describe the bug

As described here https://quarkus.io/guides/hibernate-orm-panache#query-projection, it should be possible to project a query in a class.

@Entity
public class Score extends PanacheEntity {
    public String name;
    public int points;

    public Score() {
    }

    public Score(String name, int points) {
        this.name = name;
        this.points = points;
    }

    public static List<Score> total() {
        return Score.find("select s.name, sum(s.points) from Score s group by s.name").project(Score.class).list();
    }
}

Expected behavior

This should project correctly

Actual behavior

It currently fail with this exception:

024-07-03 10:06:09,967 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (executor-thread-1) HTTP Request to /score failed, error id: acd0a377-6d5a-4073-b8df-43cddb5726c8-1: org.hibernate.query.QueryTypeMismatchException: Result type is 'Score' but the query returned a 'String'
	at org.hibernate.sql.results.internal.RowTransformerCheckingImpl.transformRow(RowTransformerCheckingImpl.java:33)
	at org.hibernate.sql.results.internal.StandardRowReader.readRow(StandardRowReader.java:102)
	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:205)
	at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)

How to Reproduce?

https://github.com/ia3andy/reproducer-hibernate-project

Run the test

Quarkus version or git rev

3.12.0

[quarkus-bot]

/cc @FroMage (panache), @gsmet (hibernate-orm), @loicmathieu (panache), @yrodiere (hibernate-orm)

[FroMage]

Probably an ORM bug related to group by?

[ia3andy]

I have found a workaround:

@Entity
public class Score extends PanacheEntity {
    public String name;
    public Long points;

    public Score() {
    }

    public Score(String name, Long points) {
        this.name = name;
        this.points = points;
    }

    public static List<Score> total() {
        return Score.find("select s.name, SUM(s.points) from Score s group by s.name").project(Score.class).list();
    }
}

so it seems using int or Integer won't work

[FroMage]

Looks like an ORM bug in int/long conversion? At the very least the error should be better. WDYT @yrodiere should we file it upstream? Probably needs a pure-ORM reproducer…

[yrodiere]

WDYT @yrodiere should we file it upstream? Probably needs a pure-ORM reproducer…

+1 please create a reproducer based on https://github.com/hibernate/hibernate-test-case-templates/blob/main/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/QuarkusLikeORMUnitTestCase.java

[DrongoX]

Hi everyone,
Could someone please link the corresponding issue on the Hibernate side if it was created ?

On a sidenote, I have this problem reproducing with Quarkus 3.12.0 and it is working fine on 3.11.2. I checked quickly, these versions seem to use the exact same Hibernate version.

[FroMage]

I am not sure it was filed upstream yet.

[yrodiere]

Tried to reproduce upstream, there's a problem with sum(point) being of type Long, but nothing like the error in the issue description.

Going to try to debug the reproducer...

[yrodiere]

Tried to reproduce upstream, there's a problem with sum(point) being of type Long, but nothing like the error in the issue description.

Forget that, a bug in the chair-keyboard interface. I do reproduce the problem with just Hibernate ORM.

BUT... it's already been reported and fixed, in 6.6.0.CR1: https://hibernate.atlassian.net/browse/HHH-18214

So, upgrading to Hibernate ORM 6.6 in #41359 will fix the problem.

In the meantime @ia3andy: HHH-18214 just caused the reported error to be nonsense, but there was a problem in your code. You can fix it by using Long as a parameter in your Score constructor instead of int. The reason you need that is that sum(points) has type Long, probably to avoid int overflows. As to why Long and not long, I can't say.

[lfarkas]

when do you plan to upgrade to Hibernate ORM 6.6?

[aaronz-vipaso]

when do you plan to upgrade to Hibernate ORM 6.6?

@lfarkas I think the Hibernate ORM 6.6.0 version is not yet released.