We switched from Quarkus 3.8 to 3.15 which also involved updating Hibernate from 6.4.8 to 6.6.1. We ran into a regression where a JPQL query for an entity using joined inheritance does not render into a valid SQL query (PostgreSQL) any more. The entity model and the query is quite complex therefore I wasn’t able to create a minimal reproducer yet but maybe showing the issue will already ring a bell with someone.
Entities are as follows (simplified):
@Entity
@Table(name = "deployments")
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Deployment {
@Column(name = "id")
@Id
private UUID m_id;
// ... many other properties
}
@Entity
@Table(name = "data_app_deployments")
public class DataAppDeployment extends Deployment {
// some additional properties
}
@Entity
@IdClass(ResourcePk.class)
@Table(name = "tx_local_resource_id_cache")
public class ResourceIdCacheEntry {
@Id
@Column(name = "cache_id")
private UUID m_cacheId;
@Id
@Column(name = "resource_id")
private String m_resourceId ;
}
We have a JPQL query which looks as follows:
SELECT s FROM DataAppDeployment s
WHERE
(CAST(:id AS text) IS null OR s.m_id = :id)
AND
(CAST(:name AS text) IS null or s.m_name = :name)
AND
(CAST(:itemVersion AS text) IS null OR s.m_spaceVersion = :itemVersion)
AND
(CAST(:workflowId AS text) IS null OR s.m_workflowId = :workflowId)
AND
(CAST(:workflowPath AS text) IS null OR s.m_workflowPath = :workflowPath)
AND
(CAST(:scope AS text) IS null OR s.m_scope = :scope)
AND
(CAST(:creator AS text) IS null OR s.m_creator = :creator)
AND
(CAST(:type AS text) IS null OR s.m_type = :type)
AND
(CAST(:executionContextId AS text) IS null OR s.m_executionContext.m_id = :executionContextId)
AND
CAST(s.m_id AS text) IN (
SELECT r.m_resourceId FROM ResourceIdCacheEntry r WHERE r.m_cacheId = CAST(:cacheId AS UUID)
)
Note the IN condition at the end.
Executing thie JPQL query works fine with Hibernate 6.4.8. However, with 6.6.1 we get the following SQL error (query slightly stripped):
org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [
select count(*) from data_app_deployments dad1_0 where (cast(? as text) is null or dad1_0.id=?) and (cast(? as text) is null or dad1_1.name=?) and ... and cast(dad1_0.id as text) in (select rice1_0.resource_id from tx_local_resource_id_cache rice1_0 where rice1_0.cache_id=cast('e7106b3d-f902-44f4-9024-fac8836e1375' as uuid))
]
[ERROR: missing FROM-clause entry for table "dad1_1" Position: 132] [n/a]
As you can see the JOIN between data_app_deployment and the deployments is missing and hence the corresponding table alias dad1_1 does not exist.
And now the interesting part: if I move the IN condition from the end of the WHERE condition to just the second-last position, the JOIN is correctly added and everything works as expected.
SELECT s FROM DataAppDeployment s
WHERE
...
CAST(s.m_id AS text) IN (
SELECT r.m_resourceId FROM ResourceIdCacheEntry r WHERE r.m_cacheId = :cacheId
)
AND
(CAST(:executionContextId AS text) IS null OR s.m_executionContext.m_id = :executionContextId)
Unfortunately, this doesn’t ring a bell, so we’d really need the reproducer to understand what is going on.
I gave your example a quick try, but couldn’t reproduce this.
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.
Using the test case template, I tried the following which works just fine:
package org.hibernate.bugs;
import java.util.UUID;
import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Inheritance;
import jakarta.persistence.InheritanceType;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
/**
* This template demonstrates how to develop a standalone test case for Hibernate ORM. Although this is perfectly
* acceptable as a reproducer, usage of ORMUnitTestCase is preferred!
*/
class ORMStandaloneTestCase {
private SessionFactory sf;
@BeforeEach
void setup() {
StandardServiceRegistryBuilder srb = new StandardServiceRegistryBuilder()
// Add in any settings that are specific to your test. See resources/hibernate.properties for the defaults.
.applySetting( "hibernate.show_sql", "true" )
.applySetting( "hibernate.format_sql", "true" )
.applySetting( "hibernate.hbm2ddl.auto", "create-drop" );
Metadata metadata = new MetadataSources( srb.build() )
// Add your entities here.
.addAnnotatedClass( Deployment.class )
.addAnnotatedClass( DataAppDeployment.class )
.addAnnotatedClass( ResourceIdCacheEntry.class )
.addAnnotatedClass( ExecutionContext.class )
.buildMetadata();
sf = metadata.buildSessionFactory();
}
@AfterEach
void teardown() {
if ( sf != null ) {
sf.close();
}
}
// Add your tests, using standard JUnit 5:
@Test
void hhh123Test1() throws Exception {
sf.inTransaction( session -> {
session.createQuery(
"""
SELECT s FROM DataAppDeployment s
WHERE
(CAST(:id AS text) IS null OR s.m_id = :id)
AND
(CAST(:name AS text) IS null or s.m_name = :name)
AND
(CAST(:itemVersion AS text) IS null OR s.m_spaceVersion = :itemVersion)
AND
(CAST(:workflowId AS text) IS null OR s.m_workflowId = :workflowId)
AND
(CAST(:workflowPath AS text) IS null OR s.m_workflowPath = :workflowPath)
AND
(CAST(:scope AS text) IS null OR s.m_scope = :scope)
AND
(CAST(:creator AS text) IS null OR s.m_creator = :creator)
AND
(CAST(:type AS text) IS null OR s.m_type = :type)
AND
(CAST(:executionContextId AS text) IS null OR s.m_executionContext.m_id = :executionContextId)
AND
CAST(s.m_id AS text) IN (
SELECT r.m_resourceId FROM ResourceIdCacheEntry r WHERE r.m_cacheId = CAST(:cacheId AS uuid)
)
"""
)
.setParameter( "id", UUID.randomUUID() )
.setParameter( "name", null )
.setParameter( "itemVersion", null )
.setParameter( "workflowId", null )
.setParameter( "workflowPath", null )
.setParameter( "scope", null )
.setParameter( "creator", null )
.setParameter( "type", null )
.setParameter( "executionContextId", null )
.setParameter( "cacheId", null )
.getResultList();
} );
}
@Entity(name = "Deployment")
@Table(name = "deployments")
@Inheritance(strategy = InheritanceType.JOINED)
public static abstract class Deployment {
@Column(name = "id")
@Id
private UUID m_id;
private String m_name;
private String m_spaceVersion;
private String m_workflowId;
private String m_workflowPath;
private String m_scope;
private String m_creator;
private String m_type;
@ManyToOne
private ExecutionContext m_executionContext;
// ... many other properties
}
@Entity(name = "DataAppDeployment")
@Table(name = "data_app_deployments")
public static class DataAppDeployment extends Deployment {
// some additional properties
}
@Entity(name = "ResourceIdCacheEntry")
@Table(name = "tx_local_resource_id_cache")
public static class ResourceIdCacheEntry {
@Id
@Column(name = "cache_id")
private UUID m_cacheId;
@Id
@Column(name = "resource_id")
private String m_resourceId;
}
@Entity(name = "ExecutionContext")
@Table(name = "execution_context")
public static class ExecutionContext {
@Id
@Column(name = "id")
private UUID m_id;
private String e_name;
}
}
I tried stripping our code to fit into the template but it never triggered the problem. Could it be related to the DB dialect (H2 vs. PostgreSQL)? Also, is it possible to get the created SQL without needing a DB connection? This would also help creating a reproducer, especially if it’s related to the dialect.
@sithmein you can customize connection parameters in the META-INF/persistence.xml file inside the test case templates repo to point to your PostgreSQL database, at least we can verify if it’s database-dependant.