Skip to content

Native Query with clause and delete broken in spring-data-jpa 3.4.x #3817

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
markxj opened this issue Mar 17, 2025 · 2 comments
Closed

Native Query with clause and delete broken in spring-data-jpa 3.4.x #3817

markxj opened this issue Mar 17, 2025 · 2 comments
Assignees
@mp911de
Labels
for: external-project For an external project and not something we can fix

Comments

@markxj
Copy link

markxj commented Mar 17, 2025 •
edited by mp911de
Loading

In springboot 3.3.9 (using spring-data-jpa 3.3.9 and hibernate 6.5.3), a with clause supports delete operations - e.g.

@Transactional
@Modifying
@Query(nativeQuery = true,
    value = """
    with to_delete as (select id from person where birthday < :birthday)
            delete from person where id in (select id from to_delete)
    """)
int deleteByBirthday(@Param("birthday") Date birthday);

In springboot 3.4.3 (using spring-data-jpa 3.4.3 and hibernate 6.6.8), this same query generates the following error

Syntax error in SQL statement "with to_delete as (select id from person where birthday < ?)\000a [*]delete from person where id in (select id from to_delete)\000a"; expected ",, (, SELECT, TABLE, VALUES"; SQL statement:
with to_delete as (select id from person where birthday < ?)
delete from person where id in (select id from to_delete)

The attached sample project shows the issue - just change the springboot version in the pom

In my real world scenario I use this form of query in a loop where I also add an order by and limit clause so I do not overload my DB trying to delete 1000s of rows in a single transaction.

The springboot team also overrode my sample to use Hibernate 6.5.3, and it still failed, so the error appears to be in spring-data-jpa - ref spring-projects/spring-boot#44744

with-clause.zip
@spring-projects-issues spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged label Mar 17, 2025
@mp911de mp911de self-assigned this Mar 18, 2025
@mp911de
Copy link
Member

mp911de commented Mar 19, 2025

The full exception is:

2025-03-19T09:20:05.485+01:00 ERROR 31827 --- [demo] [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Syntax Fehler in SQL Befehl "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; erwartet ",, (, SELECT, TABLE, VALUES"
Syntax error in SQL statement "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; expected ",, (, SELECT, TABLE, VALUES"; SQL statement:
with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
 [42001-232]

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement [Syntax Fehler in SQL Befehl "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; erwartet ",, (, SELECT, TABLE, VALUES"
Syntax error in SQL statement "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; expected ",, (, SELECT, TABLE, VALUES"; SQL statement:
with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
 [42001-232]] [with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
]; SQL [with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
]

	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:277)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:560)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:343)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:160)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:136)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy2/jdk.proxy2.$Proxy104.deleteByBirthday(Unknown Source)
	at com.example.demo.DemoApplicationTests.invoke(DemoApplicationTests.java:19)
	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.hibernate.exception.SQLGrammarException: could not prepare statement [Syntax Fehler in SQL Befehl "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; erwartet ",, (, SELECT, TABLE, VALUES"
Syntax error in SQL statement "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; expected ",, (, SELECT, TABLE, VALUES"; SQL statement:
with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
 [42001-232]] [with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
]
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:66)
	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.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:191)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:81)
	at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.lambda$executeUpdate$0(NativeNonSelectQueryPlanImpl.java:82)
	at org.hibernate.sql.exec.internal.StandardJdbcMutationExecutor.execute(StandardJdbcMutationExecutor.java:67)
	at org.hibernate.query.sql.internal.NativeNonSelectQueryPlanImpl.executeUpdate(NativeNonSelectQueryPlanImpl.java:76)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:973)
	at org.hibernate.query.spi.AbstractQuery.executeUpdate(AbstractQuery.java:651)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:267)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:93)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:152)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:140)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:170)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
	... 9 more
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax Fehler in SQL Befehl "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; erwartet ",, (, SELECT, TABLE, VALUES"
Syntax error in SQL statement "with to_delete as (select id from person where birthday < ?)\000a        [*]delete from person where id in (select id from to_delete)\000a"; expected ",, (, SELECT, TABLE, VALUES"; SQL statement:
with to_delete as (select id from person where birthday < ?)
        delete from person where id in (select id from to_delete)
 [42001-232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
	at org.h2.message.DbException.getSyntaxError(DbException.java:261)
	at org.h2.command.ParserBase.getSyntaxError(ParserBase.java:762)
	at org.h2.command.ParserBase.read(ParserBase.java:369)
	at org.h2.command.Parser.parseQueryPrimary(Parser.java:2696)
	at org.h2.command.Parser.parseQueryTerm(Parser.java:2547)
	at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2526)
	at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2519)
	at org.h2.command.Parser.parseQueryExpression(Parser.java:2505)
	at org.h2.command.Parser.parseQuery(Parser.java:2479)
	at org.h2.command.Parser.parsePrepared(Parser.java:610)
	at org.h2.command.Parser.parse(Parser.java:581)
	at org.h2.command.Parser.parse(Parser.java:561)
	at org.h2.command.Parser.prepareCommand(Parser.java:484)
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:645)
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:561)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1164)
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93)
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:315)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:328)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:96)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:180)
	... 30 more

@mp911de
Copy link
Member

mp911de commented Mar 19, 2025

As you can see, the exception is rooted in H2. Downgrading H2 to 2.2.224 (from 2.3.232) causes the difference. Version 2.3.230 introduced several changes around CTE's, I think that has changed H2's behavior. Nothing that we can address.

@mp911de mp911de closed this as not planned Won't fix, can't repro, duplicate, stale Mar 19, 2025
@mp911de mp911de added for: external-project For an external project and not something we can fix and removed status: waiting-for-triage An issue we've not yet triaged labels Mar 19, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mp911de mp911de

Labels
for: external-project For an external project and not something we can fix
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@mp911de @spring-projects-issues @markxj