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.