How do execute a native query for an Entity that has a property populated via @SecondaryTable

Hibernate ORM
1

I have an entity that uses a secondary table to populate a calculated value from a view we cannot figure out how to set up the query to allow us to return the entire entity in the initial query.

Here is a simplified example to illustrate what we are encountering.

@Entity
@Table(name = "test_entity")
@SecondaryTable(name = "test_entity_bar_vw")
public class TestEntity {

    @Id
    @Column(name = "test_entity_id", nullable = false, unique = true)
    @EqualsAndHashCode.Include
    UUID testEntityId;

    @Column(name = "bar", table = "test_entity_bar_vw", insertable = false, nullable = false, updatable = false)
    String bar;

    @NaturalId(mutable = false)
    @Column(length = 50, nullable = false, unique = true)
    String businessKey;

    @Column(length = 50, nullable = false, unique = false)
    String data;

This test fails:

 static final String QUERY = """
            SELECT
             {test_entity.*}
            FROM
             test_entity test_entity
            """;

    @Test
    void test() {

        this.em.createNativeQuery(QUERY, TestEntity.class, "test_entity")
                .getResultList();
    }

Here is the exception:

org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [
SELECT
 test_entity.test_entity_id test_ent1_0_0_,
 test_entity.business_key business2_0_0_,
 test_entity.data data3_0_0_
FROM
 test_entity test_entity
] [ERROR: column test_entity.bar does not exist
  Position: 307] [n/a]
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
	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.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52)
	at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
	at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:656)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at com.****.spring.data.jpa.repository.TestEntityIT.test(TestEntityIT.java:54)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: org.postgresql.util.PSQLException: ERROR: column test_entity.bar does not exist
  Position: 307
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
	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 com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246)
	... 17 more

I attempted to replace the table with a view that had the missing field and it too failed but this time with a different error:

 static final String QUERY = """
            SELECT
             {test_entity.*}
            FROM
             test_entity_vw test_entity
            """;

    @Test
    void test() {

        this.em.createNativeQuery(QUERY, TestEntity.class, "test_entity")
                .getResultList();
    }

The new error:

org.hibernate.sql.ast.tree.from.UnknownTableReferenceException: Unable to determine TableReference (`test_entity_bar_vw`) for `com.****.spring.data.jpa.test.postgres.entity.TestEntity(test_entity).bar`
	at org.hibernate.sql.ast.tree.from.ColumnReferenceQualifier.resolveTableReference(ColumnReferenceQualifier.java:74)
	at org.hibernate.query.results.dynamic.DynamicFetchBuilderStandard.lambda$getSelectableConsumer$0(DynamicFetchBuilderStandard.java:185)
	at org.hibernate.metamodel.mapping.BasicValuedModelPart.forEachSelectable(BasicValuedModelPart.java:54)
	at org.hibernate.query.results.dynamic.DynamicFetchBuilderStandard.buildFetch(DynamicFetchBuilderStandard.java:87)
	at org.hibernate.query.results.DomainResultCreationStateImpl.lambda$createFetchableConsumer$3(DomainResultCreationStateImpl.java:539)
	at org.hibernate.metamodel.mapping.internal.ImmutableAttributeMappingList.forEach(ImmutableAttributeMappingList.java:44)
	at org.hibernate.persister.entity.AbstractEntityPersister.visitFetchables(AbstractEntityPersister.java:6134)
	at org.hibernate.query.results.DomainResultCreationStateImpl.visitFetches(DomainResultCreationStateImpl.java:454)
	at org.hibernate.sql.results.graph.AbstractFetchParent.afterInitialize(AbstractFetchParent.java:30)
	at org.hibernate.sql.results.graph.entity.AbstractEntityResultGraphNode.afterInitialize(AbstractEntityResultGraphNode.java:69)
	at org.hibernate.persister.entity.AbstractEntityPersister.createDomainResult(AbstractEntityPersister.java:1319)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.lambda$buildResult$0(DynamicResultBuilderEntityStandard.java:147)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.buildResultOrFetch(DynamicResultBuilderEntityStandard.java:307)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.buildResult(DynamicResultBuilderEntityStandard.java:146)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.buildResult(DynamicResultBuilderEntityStandard.java:51)
	at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:229)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
	at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:656)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at com.****.spring.data.jpa.repository.TestEntityIT.test(TestEntityIT.java:54)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
2

Why are you using createNativeQuery() and not just simply an HQL query like em.createQuery("select t from TestEntity t", TestEntity.class)? The latter should be able to handle secondary tables for you, without the need of creating dedicated views.

1 Like
3

This is a simplified example of the actual query.

The actual query needs to be native as it interrogates arbitrary fields in jsonb columns.
Since we don’t know the keys ahead of time we are limited in our indexing strategies, so what is shown in the below example has given us the best index utilization. Note that the keys and values are set via named parameters and each parameter is properly escaped json and checked for SQL injection attacks.


config->'someKey' = ANY(ARRAY['"someKeyValue1"','"someKeyValue2"']::jsonb[])
AND
config->'someOtherKey' = ANY(ARRAY['"someOtherKeyValue1"','"someOtherKeyValue2"']::jsonb[])

Further, I have also tried:

    static final String QUERY = """
                  SELECT
                      {test_entity.*},
                      test_entity_bar_vw."bar" AS {test_entity.bar}
                   FROM
                       test_entity test_entity
                    JOIN
                       test_entity_bar_vw test_entity_bar_vw ON (test_entity."test_entity_id" = test_entity_bar_vw."test_entity_id")
     """;

    @Test
    void test() {

        this.em.createNativeQuery(QUERY, TestEntity.class, "test_entity")
                .getResultList();
    }

That one fails with:

org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [SELECT
  test_entity."test_entity_id" test_ent1_0_0_,test_entity."business_key" business2_0_0_,test_entity."data" data3_0_0_,test_entity."bar" bar1_1_0_,
  test_entity_bar_vw."bar" AS bar1_1_0_
FROM
  test_entity test_entity
JOIN
  test_entity_bar_vw test_entity_bar_vw ON (test_entity."test_entity_id" = test_entity_bar_vw."test_entity_id")
] [ERROR: column test_entity.bar does not exist
  Position: 126] [n/a]
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
	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.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52)
	at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
	at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:656)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at com.*****.spring.data.jpa.repository.TestEntityIT.testQUERY_WITH_SEP(TestEntityIT.java:72)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: org.postgresql.util.PSQLException: ERROR: column test_entity.bar does not exist
  Position: 126
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
	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 com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246)
	... 17 more


	... 17 more
4

Try this query instead:

                  SELECT
                      {test_entity.*},
                      test_entity_bar_vw."bar" AS bar
                   FROM
                       test_entity test_entity
                    JOIN
                       test_entity_bar_vw test_entity_bar_vw ON (test_entity."test_entity_id" = test_entity_bar_vw."test_entity_id")
1 Like
5

No luck.

I tried it two ways: with byte code enhancement and Lazy Fetching and once without:

    @Column(name = "bar", table = "test_entity_bar_vw", nullable = true, unique = false, insertable = false,
        updatable = false)
    @Basic(fetch = FetchType.LAZY)
    String bar;


    @Column(name = "bar", table = "test_entity_bar_vw", nullable = true, unique = false, insertable = false,
        updatable = false)
    String bar;

This test fails for both:

    @Test
    void testWithOnJoin() {

        var query
                = """
                        SELECT
                            {test_entity.*},
                            test_entity_bar_vw."bar" AS bar
                         FROM
                             test_entity test_entity
                          JOIN
                             test_entity_bar_vw test_entity_bar_vw ON (test_entity."test_entity_id" = test_entity_bar_vw."test_entity_id")
                                            """;

        this.em.createNativeQuery(query, TestEntity.class, "test_entity")
                .getResultList();
    }

This is the exception I receive when using bytecode enhancement and Lazy fetching. (Which is the same error I receive when not using bytecode enhancement but using a view [see my original post], it appears that the view and the bytecode enhancement both get further along [ResultSetMappingImpl:229 vs ResultSetMappingImpl:193] before they fail than the plain jane example):

org.hibernate.sql.ast.tree.from.UnknownTableReferenceException: Unable to determine TableReference (`test_entity_bar_vw`) for `com.*****.spring.data.jpa.test.postgres.entity.TestEntity(TestEntity).bar`
	at org.hibernate.sql.ast.tree.from.ColumnReferenceQualifier.resolveTableReference(ColumnReferenceQualifier.java:74)
	at org.hibernate.query.results.dynamic.DynamicFetchBuilderStandard.lambda$getSelectableConsumer$0(DynamicFetchBuilderStandard.java:185)
	at org.hibernate.metamodel.mapping.BasicValuedModelPart.forEachSelectable(BasicValuedModelPart.java:54)
	at org.hibernate.query.results.dynamic.DynamicFetchBuilderStandard.buildFetch(DynamicFetchBuilderStandard.java:87)
	at org.hibernate.query.results.DomainResultCreationStateImpl.lambda$createFetchableConsumer$3(DomainResultCreationStateImpl.java:539)
	at org.hibernate.metamodel.mapping.internal.ImmutableAttributeMappingList.forEach(ImmutableAttributeMappingList.java:44)
	at org.hibernate.persister.entity.AbstractEntityPersister.visitFetchables(AbstractEntityPersister.java:6134)
	at org.hibernate.query.results.DomainResultCreationStateImpl.visitFetches(DomainResultCreationStateImpl.java:454)
	at org.hibernate.sql.results.graph.AbstractFetchParent.afterInitialize(AbstractFetchParent.java:30)
	at org.hibernate.sql.results.graph.entity.AbstractEntityResultGraphNode.afterInitialize(AbstractEntityResultGraphNode.java:69)
	at org.hibernate.persister.entity.AbstractEntityPersister.createDomainResult(AbstractEntityPersister.java:1319)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.lambda$buildResult$0(DynamicResultBuilderEntityStandard.java:147)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.buildResultOrFetch(DynamicResultBuilderEntityStandard.java:307)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.buildResult(DynamicResultBuilderEntityStandard.java:146)
	at org.hibernate.query.results.dynamic.DynamicResultBuilderEntityStandard.buildResult(DynamicResultBuilderEntityStandard.java:51)
	at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:229)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
	at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:656)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at com.****.spring.data.jpa.repository.TestEntityIT.testWithOnJoin(TestEntityIT.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)

The exception when not using bytecode enhancement:

org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [SELECT
    test_entity.test_entity_id test_ent1_0_0_,test_entity.business_key business2_0_0_,test_entity.data data3_0_0_,test_entity.bar bar1_1_0_,
    test_entity_bar_vw."bar" AS bar
 FROM
     test_entity test_entity
  JOIN
     test_entity_bar_vw test_entity_bar_vw ON (test_entity."test_entity_id" = test_entity_bar_vw."test_entity_id")
] [ERROR: column test_entity.bar does not exist
  Position: 122] [n/a]
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
	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.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52)
	at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
	at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:656)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
	at org.hibernate.query.Query.getResultList(Query.java:120)
	at com.****.spring.data.jpa.repository.TestEntityIT.testWithOnJoin(TestEntityIT.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: org.postgresql.util.PSQLException: ERROR: column test_entity.bar does not exist
  Position: 122
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
	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 com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:246)
	... 17 more

Config info:

Hibernate: 6.4.4
Postgres: 16.2
Java: 17.0.10
Spring Data JPA: 3.2.2
HikariCP: 5.1.0

Settings:

hibernate.archive.scanner=org.hibernate.boot.archive.scan.internal.DisabledScanner,
hibernate.cdi.extensions=true,
hibernate.connection.datasource=HikariDataSource (HikariPool-1),
hibernate.connection.handling_mode=DELAYED_ACQUISITION_AND_HOLD,
hibernate.format_sql=true,
hibernate.generate_statistics=true,
hibernate.globally_quoted_identifiers=false,
hibernate.implicit_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy,
hibernate.jmx.enabled=false,
hibernate.jmx.usePlatformServer=false,
hibernate.persistenceUnitName=default,
hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy,
hibernate.resource.beans.container=org.springframework.orm.hibernate5.SpringBeanContainer@53525379,
hibernate.show_sql=true,
hibernate.transaction.coordinator_class=class org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorBuilderImpl,
hibernate.transaction.jta.platform=org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform@1be3f8f8,
hibernate.type.json_format_mapper=org.hibernate.type.format.jackson.JacksonJsonFormatMapper@2487e20,
jakarta.persistence.nonJtaDataSource=HikariDataSource (HikariPool-1),
jakarta.persistence.sharedCache.mode=UNSPECIFIED,
jakarta.persistence.validation.mode=AUTO,
6

Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

1 Like
7

Issue here:
https://hibernate.atlassian.net/browse/HHH-17756

Test cases here: hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs at bugs/HHH-17756-SecondaryTable-native-query-issue · jmax01/hibernate-test-case-templates · GitHub

1 Like