[JIRA] (HHH-16771) group by someEntity where entity has json (not jsonb) columns results in failure on PostgreSQL
by Yoann Rodière (JIRA)
Yoann Rodière ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNmRkYzQ5ZDRi... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16771?atlOrigin=eyJpIjoiNmRkYz... ) HHH-16771 ( https://hibernate.atlassian.net/browse/HHH-16771?atlOrigin=eyJpIjoiNmRkYz... ) group by someEntity where entity has json (not jsonb) columns results in failure on PostgreSQL ( https://hibernate.atlassian.net/browse/HHH-16771?atlOrigin=eyJpIjoiNmRkYz... )
Issue Type: Bug Affects Versions: 6.2.4 Assignee: Unassigned Components: hibernate-core Created: 08/Jun/2023 04:37 AM Fix Versions: 6.2.5 Priority: Major Reporter: Yoann Rodière ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
Note this requires the json type on PostgreSQL, because oddly this type doesn’t have an equality operator, while jsonb does… Probably because json representations can differ only by their formatting (extra spaces, …).
Anyway, with this model:
@Entity( name = "EntityA" )
public static class EntityA {
@Id
private Long id;
private Integer amount;
@ManyToOne
@JoinColumn( name = "secondary_id" )
private EntityB secondary;
public EntityA() {
}
public EntityA(Long id, Integer amount, EntityB secondary) {
this.id = id;
this.amount = amount;
this.secondary = secondary;
}
public Long getId() {
return id;
}
public Integer getAmount() {
return amount;
}
public EntityB getSecondary() {
return secondary;
}
}
@Entity( name = "EntityB" )
public static class EntityB {
@Id
@Column( name = "id_col" )
private Long id;
private String name;
// We need a 'json' column to reproduce the bug; 'jsonb' would work just fine.
@JdbcType(PostgreSQLJsonPGObjectJsonType.class)
// For some reason PostgreSQLJsonPGObjectJsonType
// will still lead to the column being defined as jsonb...
@Column(columnDefinition = "json")
private Map<String, Object> metadata;
public EntityB() {
}
public EntityB(Long id, String name, Map<String, Object> metadata) {
this.id = id;
this.name = name;
this.metadata = metadata;
}
public Long getId() {
return id;
}
public String getName() {
return name;
}
public Map<String, Object> getMetadata() {
return metadata;
}
}
This query will fail:
select b.name from EntityB b group by b
With the following exception:
org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select e1_0.name from EntityB e1_0 group by e1_0.id_col,e1_0.metadata,e1_0.name] [ERROR: could not identify an equality operator for type json
Position: 57] [n/a]
at app//org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:89)
at app//org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
at app//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
at app//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
at app//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:257)
at app//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:163)
at app//org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:254)
at app//org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:134)
at app//org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:19)
at app//org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:66)
at app//org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:198)
at app//org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33)
at app//org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:361)
at app//org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:168)
at app//org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:93)
at app//org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31)
at app//org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:115)
at app//org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:311)
at app//org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:252)
at app//org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:518)
at app//org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:430)
at app//org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:536)
at app//org.hibernate.orm.test.query.EntityValuedPathsGroupByWithNoEqualityOperatorTest.lambda$testRootGroupBy$2(EntityValuedPathsGroupByWithNoEqualityOperatorTest.java:67)
at app//org.hibernate.testing.orm.transaction.TransactionUtil.wrapInTransaction(TransactionUtil.java:49)
at app//org.hibernate.testing.orm.transaction.TransactionUtil.inTransaction(TransactionUtil.java:24)
at app//org.hibernate.testing.orm.junit.SessionFactoryExtension$SessionFactoryScopeImpl.inTransaction(SessionFactoryExtension.java:375)
at app//org.hibernate.testing.orm.junit.SessionFactoryExtension$SessionFactoryScopeImpl.inTransaction(SessionFactoryExtension.java:352)
at app//org.hibernate.orm.test.query.EntityValuedPathsGroupByWithNoEqualityOperatorTest.testRootGroupBy(EntityValuedPathsGroupByWithNoEqualityOperatorTest.java:64)
[...]
Caused by: org.postgresql.util.PSQLException: ERROR: could not identify an equality operator for type json
Position: 57
at app//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at app//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at app//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at app//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at app//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at app//org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at app//org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
at app//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:239)
... 106 more
( https://hibernate.atlassian.net/browse/HHH-16771#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16771#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100225- sha1:62413c2 )
2 years, 10 months