Hibernate 6.2.4.Final in Quarkus 3.1.1.Final breaks GROUP BY statements in PostgreSQL when there is a JSON column type in the association

[gastaldi]

Describe the bug

Updating the Quarkus Registry to Quarkus 3.1.1.Final breaks the following HQL:

select pr from PlatformRelease pr
where pr.unlisted = false
and pr.platformStream.platform.platformType = 'C'
and (pr.platformStream, pr.versionSortable) in
    (
     select pr2.platformStream, max(pr2.versionSortable) from PlatformRelease pr2
         where pr2.platformStream.unlisted = false
         and pr2.unlisted = false
         group by pr2.platformStream
    )
order by pr.versionSortable desc, pr.platformStream.platform.isDefault desc

Expected behavior

No errors

Actual behavior

Tests fail with:

Caused by: org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type json
  Position: 690
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
	at io.agroal.pool.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:239)

How to Reproduce?

1. git clone https://github.com/quarkusio/registry.quarkus.io
2. mvn clean compile quarkus:dev
3. In a separate terminal: curl http://localhost:8080/client/platforms

Output of uname -a or ver

macOS m1

Output of java -version

JDK 17

GraalVM version (if different from Java)

No response

Quarkus version or git rev

3.1.1.Final

Build tool (ie. output of mvnw --version or gradlew --version)

Apache Maven 3.9.1

Additional information

Quarkus 3.1.0.Final produces the following SQL:

SELECT p1_0.id,
       p1_0.bom,
       p1_0.created_at,
       p1_0.member_boms,
       p1_0.metadata,
       p1_0.pinned,
       p1_0.platform_stream_id,
       p1_0.quarkus_core_version,
       p1_0.unlisted,
       p1_0.upstream_quarkus_core_version,
       p1_0.version,
       p1_0.version_sortable
FROM platform_release p1_0
JOIN platform_stream p2_0 ON p2_0.id=p1_0.platform_stream_id
JOIN platform p3_0 ON p3_0.id=p2_0.platform_id
WHERE p1_0.unlisted=FALSE
  AND p3_0.platform_type='C'
  AND (p1_0.platform_stream_id,
       p1_0.version_sortable) in
    (SELECT p4_0.platform_stream_id,
            max(p4_0.version_sortable)
     FROM platform_release p4_0
     JOIN platform_stream p5_0 ON p5_0.id=p4_0.platform_stream_id
     WHERE p5_0.unlisted=FALSE
       AND p4_0.unlisted=FALSE
     GROUP BY p4_0.platform_stream_id)
ORDER BY p1_0.version_sortable DESC,
         p3_0.is_default DESC

Quarkus 3.1.1.Final produces the following SQL:

SELECT p1_0.id,
       p1_0.bom,
       p1_0.created_at,
       p1_0.member_boms,
       p1_0.metadata,
       p1_0.pinned,
       p1_0.platform_stream_id,
       p1_0.quarkus_core_version,
       p1_0.unlisted,
       p1_0.upstream_quarkus_core_version,
       p1_0.version,
       p1_0.version_sortable
FROM platform_release p1_0
JOIN platform_stream p2_0 ON p2_0.id=p1_0.platform_stream_id
JOIN platform p3_0 ON p3_0.id=p2_0.platform_id
WHERE p1_0.unlisted=FALSE
  AND p3_0.platform_type='C'
  AND (p1_0.platform_stream_id,
       p1_0.version_sortable) in
    (SELECT p4_0.platform_stream_id,
            max(p4_0.version_sortable)
     FROM platform_release p4_0
     JOIN platform_stream p5_0 ON p5_0.id=p4_0.platform_stream_id
     WHERE p5_0.unlisted=FALSE
       AND p4_0.unlisted=FALSE
     GROUP BY p5_0.id,
              p5_0.created_at,
              p5_0.metadata,
              p5_0.name,
              p5_0.pinned,
              p5_0.platform_id,
              p5_0.stream_key,
              p5_0.stream_key_sortable,
              p5_0.unlisted)
ORDER BY p1_0.version_sortable DESC,
         p3_0.is_default DESC

[quarkus-bot]

/cc @Sanne (hibernate-orm), @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[gastaldi]

Looks like it was introduced in https://hibernate.atlassian.net/browse/HHH-16409

[gastaldi]

Notice how the GROUP BY clause changed:

GROUP BY p4_0.platform_stream_id

vs

GROUP BY p5_0.id,
              p5_0.created_at,
              p5_0.metadata,
              p5_0.name,
              p5_0.pinned,
              p5_0.platform_id,
              p5_0.stream_key,
              p5_0.stream_key_sortable,
              p5_0.unlisted

p5_0.metadata is a json column

[yrodiere]

Confirmed as a bug in Hibernate ORM and not Quarkus, reported here: https://hibernate.atlassian.net/browse/HHH-16771

It seems related to the fact the application uses the json type while Hibernate ORM generally expects (and is tested with) the jsonb type. I suggested a workaround here: quarkusio/registry.quarkus.io#199 (comment)

Closing this issue as this is not directly related to Quarkus; feel free to comment if you disagree.

[yrodiere]

Also, thanks for reporting @gastaldi!

[gastaldi]

@yrodiere thanks! I created on purpose in Quarkus to track when the fix would be available.

Perhaps leave it open until the PR that updates Hibernate is created?

[yrodiere]

I'm not a fan of tracking things twice, especially when Quarkus has nothing to do with the bug...

Also, time has proven that there's a non-negligible chance that we will just forget to update this ticket when the PR that updates the Hibernate ORM dependency is created.

So feel free to reopen, but no promise as to tracking. In your place I'd rather convert the column type to JSONB as I suggested and be done with this.

[gastaldi]

For the record, converting the column to JSONB isn't a valid workaround, as I explained here

[yrodiere]

Looks like my issue reported upstream was rejected; the problem was addressed for top-level queries but not for nested ones.

https://hibernate.atlassian.net/browse/HHH-16773 looks like it might address your problem, but is an improvement scheduled for ORM 6.3 (not stable yet).

As to the workaround, I'll continue the conversation there.

[yrodiere]

Also, time has proven that there's a non-negligible chance that we will just forget to update this ticket when the PR that updates the Hibernate ORM dependency is created.

Well, what a nice surprise: I did catch this one while hunting down issues that will be fixed by the upgrade to Hibernate ORM 6.4. Reopening for good measure :)