[hibernate-issues] [JIRA] (HHH-16792) Incorrect generated SQL query when using CriteriaUpdate on an entity with inheritance

Ben Rachmian ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=60dab3d... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiN2QyNjliY2Zi... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16792?atlOrigin=eyJpIjoiN2QyNj... ) HHH-16792 ( https://hibernate.atlassian.net/browse/HHH-16792?atlOrigin=eyJpIjoiN2QyNj... ) Incorrect generated SQL query when using CriteriaUpdate on an entity with inheritance ( https://hibernate.atlassian.net/browse/HHH-16792?atlOrigin=eyJpIjoiN2QyNj... ) Issue Type: Bug Assignee: Unassigned Attachments: example.zip Created: 14/Jun/2023 00:58 AM Priority: Major Reporter: Ben Rachmian ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=60dab3d... ) I’m using a spring boot application version *3.1.0* with Hibernate *6.2.2 final* (managed by spring boot starter pom), Java 17, and PostgreSQL 14.3 I’m trying to update a patient entity using CriteriaUpdate class, and I get the following error: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [with id_cte (id) as materialized (select p1_0."id" from "ExampleSchema"."Patients" p1_0 join "ExampleSchema"."Users" p1_1 on p1_0."id"=p1_1."id" where p1_1."name"=?),"update_cte_ExampleSchema"."Patients" (id) as (update "ExampleSchema"."Patients" set "weight"=? where "id" in(select id.id from id_cte id) returning "id") select count(*) from id_cte id] [ERROR: syntax error at or near "." Position: 194] [n/a] at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:89) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:257) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:163) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advanceNext(JdbcValuesResultSetImpl.java:204) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:84) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:65) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:198) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:33) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:362) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:168) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:93) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.query.sqm.mutation.internal.cte.AbstractCteMutationHandler.execute(AbstractCteMutationHandler.java:207) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.query.sqm.mutation.internal.cte.CteMutationStrategy.executeUpdate(CteMutationStrategy.java:106) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.query.sqm.internal.MultiTableUpdateQueryPlan.executeUpdate(MultiTableUpdateQueryPlan.java:36) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(QuerySqmImpl.java:735) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(QuerySqmImpl.java:705) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] at com.hibernate.bug.example.hibernatebugexample.service.PatientService.updatePatient(PatientService.java:26) ~[classes/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.9.jar:6.0.9] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.9.jar:6.0.9] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.9.jar:6.0.9] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-6.0.9.jar:6.0.9] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.9.jar:6.0.9] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.9.jar:6.0.9] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.9.jar:6.0.9] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.9.jar:6.0.9] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-6.0.9.jar:6.0.9] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) ~[spring-aop-6.0.9.jar:6.0.9] at com.hibernate.bug.example.hibernatebugexample.service.PatientService$$SpringCGLIB$$0.updatePatient(<generated>) ~[classes/:na] at com.hibernate.bug.example.hibernatebugexample.service.PatientUpdater.updatePatientOnStartUp(PatientUpdater.java:15) ~[classes/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.context.event.ApplicationListenerMethodAdapter.doInvoke(ApplicationListenerMethodAdapter.java:343) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.event.ApplicationListenerMethodAdapter.processEvent(ApplicationListenerMethodAdapter.java:228) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.event.ApplicationListenerMethodAdapter.onApplicationEvent(ApplicationListenerMethodAdapter.java:165) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:143) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:437) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:370) ~[spring-context-6.0.9.jar:6.0.9] at org.springframework.boot.context.event.EventPublishingRunListener.ready(EventPublishingRunListener.java:109) ~[spring-boot-3.1.0.jar:3.1.0] at org.springframework.boot.SpringApplicationRunListeners.lambda$ready$6(SpringApplicationRunListeners.java:80) ~[spring-boot-3.1.0.jar:3.1.0] at java.base/java.lang.Iterable.forEach(Iterable.java:75) ~[na:na] at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:118) ~[spring-boot-3.1.0.jar:3.1.0] at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:112) ~[spring-boot-3.1.0.jar:3.1.0] at org.springframework.boot.SpringApplicationRunListeners.ready(SpringApplicationRunListeners.java:80) ~[spring-boot-3.1.0.jar:3.1.0] at org.springframework.boot.SpringApplication.run(SpringApplication.java:330) ~[spring-boot-3.1.0.jar:3.1.0] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1305) ~[spring-boot-3.1.0.jar:3.1.0] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1294) ~[spring-boot-3.1.0.jar:3.1.0] at com.hibernate.bug.example.hibernatebugexample.HibernateBugExampleApplication.main(HibernateBugExampleApplication.java:15) ~[classes/:na] Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "." Position: 194 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) ~[postgresql-42.6.0.jar:42.6.0] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) ~[postgresql-42.6.0.jar:42.6.0] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) ~[postgresql-42.6.0.jar:42.6.0] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) ~[postgresql-42.6.0.jar:42.6.0] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) ~[postgresql-42.6.0.jar:42.6.0] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.6.0.jar:42.6.0] at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql-42.6.0.jar:42.6.0] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.0.1.jar:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na] at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:239) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final] ... 55 common frames omitted It seems the following SQL query that is being generated is wrong: with id_cte (id) as materialized (select p1_0."id" from "ExampleSchema"."Patients" p1_0 join "ExampleSchema"."Users" p1_1 on p1_0."id"=p1_1."id" where p1_1."name"='patient1' ), "update_cte_ExampleSchema"."Patients" (id) as ( update "ExampleSchema"."Patients" set "weight"=100 where "id" in(select id.id from id_cte id) returning "id" ) select count(*) from id_cte id The following line seems to cause the syntax error: "update_cte_ExampleSchema"."Patients" (id) as ( Because when I manually changed the line to: "Patients" (id) as ( It worked. The same code I use worked in spring boot version 2.4.1 and Hibernate version 5.4.25.Final, Java 15 and PostgreSQL 14.3. I didn’t find any similar reported issues. I created a sample project in order to reproduce the issue using spring initializr, This is my pom file: <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>3.1.0</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.hibernate.bug.example</groupId> <artifactId>hibernate-bug-example</artifactId> <version>0.0.1-SNAPSHOT</version> <name>hibernate-bug-example</name> <description>Demo project for Spring Boot</description> <properties> <java.version>17</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> </dependency> <dependency> <groupId>org.mariadb.jdbc</groupId> <artifactId>mariadb-java-client</artifactId> </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project> This is how I build the tables: CREATE TABLE IF NOT EXISTS "Users" ( "id" UUID NOT NULL, "name" varchar(50) NOT NULL, PRIMARY KEY ("id") ); CREATE TABLE IF NOT EXISTS "Patients" ( "id" UUID NOT NULL, "weight" INTEGER NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "Users" ("id") ); insert into "Users" ("id", "name") values('4202c1f5-6963-4632-8472-d20132bf5eb1', 'patient1'); insert into "Patients" ("id", "weight") values('4202c1f5-6963-4632-8472-d20132bf5eb1', 65); I have these entities: @Entity @Table(name = "Users") @Inheritance(strategy = InheritanceType.JOINED) public abstract class UserEntity { @Id @Column(name = "id", unique = true) private UUID id; @Column private String name; @Entity @Table(name = "Patients") public class PatientEntity extends UserEntity { @Column private int weight; and this is the update method: @Component public class PatientService { @PersistenceContext private EntityManager entityManager; @Transactional public void updatePatient() { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaUpdate<PatientEntity> update = criteriaBuilder.createCriteriaUpdate(PatientEntity.class); Root<PatientEntity> entityRoot = update.from(PatientEntity.class); update.set(entityRoot.get("weight"), 100); update.where(criteriaBuilder.equal(entityRoot.get("name"), "patient1")); entityManager.createQuery(update).executeUpdate(); } } *It is worth mentioning that if I put all the columns in one table, without using inheritance (i.e. only the patient entity, without extending user entity) then it works as expected without any errors.* I also tried to update Hibernate to 6.2.4 Final and the same issue happens. I attached a full sample project that reproduces the problem, ( https://hibernate.atlassian.net/browse/HHH-16792#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16792#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=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100226- sha1:c25eebb )