Spring Data fails to detect proper enumeration type for case/when/then in sum with CriteriaBuilder

[stefan-schilling]

Hello,
our application uses CriteriaBuilder to select data from the DB. In this case, we're returning conditional results:

SELECT SUM(CASE WHEN x THEN 1 ELSE 2)/100 FROM y;

The x in the code above represents an enumeration, which is saved using its ordinal numbers.
This works perfectly when using Spring Boot/Spring Data 3.2.12 for tests and productive usage.
But switching to version 3.4.5, it suddenly fails for the tests tests (H2 - 2.2.224 / 2.3.232) - while the productive (Postgres) usage continues to succeed.

Hibernate: 
    select
        oe1_0.common_name,
        ((sum(case oe1_0.our_status 
            when ? 
                then ? 
            else ? 
    end)/count(oe1_0.id))*cast(? as float(53))) 
from
    our_entity oe1_0 
group by
    1
2025-05-06T21:17:48.787+02:00  WARN 266149 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 90015, SQLState: 90015
2025-05-06T21:17:48.787+02:00 ERROR 266149 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SUM oder AVG auf falschem Datentyp für "SUM(CASE OE1_0.OUR_STATUS WHEN ?1 THEN ?2 ELSE ?3 END)"
SUM or AVG on wrong data type for "SUM(CASE OE1_0.OUR_STATUS WHEN ?1 THEN ?2 ELSE ?3 END)"; SQL statement:
select oe1_0.common_name,((sum(case oe1_0.our_status when ? then ? else ? end)/count(oe1_0.id))*cast(? as float(53))) from our_entity oe1_0 group by 1 [90015-232]

org.hibernate.exception.SQLGrammarException: could not prepare statement [SUM oder AVG auf falschem Datentyp für "SUM(CASE OE1_0.OUR_STATUS WHEN ?1 THEN ?2 ELSE ?3 END)"
SUM or AVG on wrong data type for "SUM(CASE OE1_0.OUR_STATUS WHEN ?1 THEN ?2 ELSE ?3 END)"; SQL statement:
select oe1_0.common_name,((sum(case oe1_0.our_status when ? then ? else ? end)/count(oe1_0.id))*cast(? as float(53))) from our_entity oe1_0 group by 1 [90015-232]] [select oe1_0.common_name,((sum(case oe1_0.our_status when ? then ? else ? end)/count(oe1_0.id))*cast(? as float(53))) from our_entity oe1_0 group by 1]

	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:66)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:191)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:153)
	at org.hibernate.sql.exec.internal.StandardStatementCreator.createStatement(StandardStatementCreator.java:49)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:235)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:171)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.<init>(JdbcValuesResultSetImpl.java:74)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:355)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:137)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.executeQuery(JdbcSelectExecutor.java:91)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:165)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$1(ConcreteSqmSelectQueryPlan.java:152)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:442)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:362)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:380)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:143)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:419)
	at jdk.proxy2/jdk.proxy2.$Proxy134.getResultList(Unknown Source)
	at org.spring.data.jpa.bugs.OurService.createPercentageQuery(OurService.java:41)
	at org.spring.data.jpa.bugs.OurServiceTest.test(OurServiceTest.java:64)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: SUM oder AVG auf falschem Datentyp für "SUM(CASE OE1_0.OUR_STATUS WHEN ?1 THEN ?2 ELSE ?3 END)"
SUM or AVG on wrong data type for "SUM(CASE OE1_0.OUR_STATUS WHEN ?1 THEN ?2 ELSE ?3 END)"; SQL statement:
select oe1_0.common_name,((sum(case oe1_0.our_status when ? then ? else ? end)/count(oe1_0.id))*cast(? as float(53))) from our_entity oe1_0 group by 1 [90015-232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.expression.aggregate.Aggregate.optimize(Aggregate.java:1009)
	at org.h2.expression.BinaryOperation.optimize(BinaryOperation.java:131)
	at org.h2.expression.BinaryOperation.optimize(BinaryOperation.java:131)
	at org.h2.command.query.Select.optimizeExpressionsAndPreserveAliases(Select.java:1344)
	at org.h2.command.query.Select.prepareExpressions(Select.java:1225)
	at org.h2.command.query.Query.prepare(Query.java:232)
	at org.h2.command.Parser.prepareCommand(Parser.java:489)
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:645)
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:561)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1164)
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93)
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:687)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:342)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$4.doPrepare(StatementPreparerImpl.java:151)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:180)
	... 25 more

The query we're using - look esp. for the criteriaBuilder.selectCase(..).when(..).otherwise(...).as(..) part

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        CriteriaQuery<OurEntityPercentageStatus> criteriaQuery = criteriaBuilder.createQuery(OurEntityPercentageStatus.class);
        Root<OurEntity> root = criteriaQuery.from(OurEntity.class);
        criteriaQuery.groupBy(root.get("commonName"));

        OurStatus ourStatus = OurStatus.STATUS_1;

        // Get percentage of ourEntities in status STATUS_1. PSEUDOCODE: SUM(case when our_status == STATUS_1 then 1 else 0 end) / cast(Count(*) as decimal)) * 100  as percentage_out
        Selection<Double> percentageOutSelection = criteriaBuilder.prod(criteriaBuilder.quot(criteriaBuilder.sum(
            criteriaBuilder.selectCase(root.get("ourStatus"))
                .when(ourStatus.ordinal(), 1.0)
                .otherwise(0.0)
                .as(Double.class)), criteriaBuilder.count(root)), 100.0).as(Double.class).alias("percentage_out");

        //Get objects with commonName and percentage_out
        criteriaQuery.select(
            criteriaBuilder.construct(OurEntityPercentageStatus.class, root.get("commonName"), percentageOutSelection));
        CriteriaQuery<OurEntityPercentageStatus> finalQuery = criteriaQuery.multiselect(root.get("commonName"),
            percentageOutSelection);
        return entityManager.createQuery(finalQuery).getResultList();

Somehow, the return type of the values produced by the CASE/WHEN/THEN is not detected properly, leading to org.h2.expression.SimpleCase:optimize(SessionLocal) returns TypeInfo.TYPE_VARCHAR - which is not correct. But I don't know, if that's a

1. H2 issue (which would make sense, since the implementation works when using productive Postgres)
2. Spring Data issue (which would make sense, since it's sufficient to switch the Spring Boot/Data version from 3.2.12 to 3.4.5, but keep the H2 version stable to bring up the issue)

I've attached
query-sum-enumeration-test.zip, containing the sample code - just jump in the pom.xml between the two Spring Boot versions.

Thanks for your help and do not hesitate to contact me, if you have any questions.
Stefan

[mp911de]

I fail to see any connection to Spring Data. This is a Hibernate issue, especially given that you're using the JPA API only. Please file a ticket with the Hibernate issue tracker.

[stefan-schilling]

Hello @mp911de ,

sorry for coming back. I did as requested - and the Hibernate guys say, it's a Spring issue.
I did some more testing, and using pure Hibernate with the Hibernate versions used by Spring Data 3.4.5, the issue does not come up.
I added a test case and the SQL queries created to the Hibernate issue - would be great, if you could take a look anyway: https://hibernate.atlassian.net/browse/HHH-19431?focusedCommentId=121131

There must be an issue with Spring Boot/Data 3.4.5 / Hibernate-Core 6.6.13.Final / H2 2.3.232.

Thanks.
Stefan

[schauder]

Sorry, we need a clean reproducer for this, preferable as a Github repository.

In the Hibernate issue you talk about switching Spring versions, but the reproducer linked in the same issue doesn't mention Hibernate at all.

Some Notes

1. In the reproducer referenced in the Hibernate issue you change the Dialect to Postgres, but the driver is still H2. This is bound to cause problems, possibly the one you are seeing.

   https://github.com/hibernate/hibernate-test-case-templates/pull/508/files#diff-14d4ee0d8cf8dc641de69cab6e6038f3fb048dff6a8ad2b6a49438b4d5425980R8

2. Don't use an in-memory database for integration testing when you are using a "real" database for production. Use Testcontainers instead.

[stefan-schilling]

Hello @schauder,

the test case you're asking for is present already, please see hibernate/hibernate-test-case-templates#507 . Note: in the pom.xml, I've configured both, Spring Boot 3.4.5 AND 3.2.12, with the first being active right now.

Concerning your notes:

1. I heard, this might be an issue. But it's been working like that now for years. Maybe that changed - but that's what I'm trying to find out.
2. I've been playing with Testcontainers, but haven't finished doing so. We'll see, what we can do here.

Thanks.
Stefan

[stefan-schilling]

Sorry, we need a clean reproducer for this, preferable as a Github repository.

In the Hibernate issue you talk about switching Spring versions, but the reproducer linked in the same issue doesn't mention Hibernate at all.

Some Notes

1. In the reproducer referenced in the Hibernate issue you change the Dialect to Postgres, but the driver is still H2. This is bound to cause problems, possibly the one you are seeing.
   https://github.com/hibernate/hibernate-test-case-templates/pull/508/files#diff-14d4ee0d8cf8dc641de69cab6e6038f3fb048dff6a8ad2b6a49438b4d5425980R8
2. Don't use an in-memory database for integration testing when you are using a "real" database for production. Use Testcontainers instead.

Hello @schauder

I just played a bit w/ the test. I changed the dialect to org.hibernate.dialect.H2Dialect and disabled schema creation (boot would fail otherwise).
With these settings in place, I get the following statement:

select
    d1_0.id c0, (
        (
            sum(
                case v1_0.geofencestatus 
                    when ? 
                        then cast(? as float(53)) 
                    else cast(? as float(53)) 
                end
            )/count(d1_0.id)
        )*cast(? as float(53))
    ) c1 
from
    dealer d1_0 
left join
    vehicle v1_0 
        on d1_0.id=v1_0.dealer 
where
    d1_0.country=? 
    and v1_0.active=? 
group by
    c0

... H2Dialect therefore generates the correct statement, applying the casts to the when/thens.

I know, one shouldn't use H2 with PostgresDialect - but still: why is just the cast not working?

Thanks + have a good week.
Stefan

[schauder]

Well, the "SQL Standard" is a bit of a joke. Every database out there has its own tweaks and special pieces of syntax or semantics.
Therefore, you can't expect a SQL statement that works on one RDBMS to work on another one.
This is why libraries like Hibernate have "Dialects" which translates "I want to do X in database Y give me the right SQL snippet for that please.

Some in memory databases try to emulate other databases, but that is always just a best effort approximation.
That is exactly, why one should use Testcontainers instead of in memory databases for testing.

If you want to know why exactly H2 doesn't support the original statement, I recommend a H2 forum, or StackOverflow with H2 tag.