I’m trying to write an UPDATE query in Hibernate HQL and use a subquery in it that crashes with an error. Why is this SQL query not working?
@Query("""
UPDATE #{#entityName} p
SET
p.read = (
SELECT (pg.readByDefault = true or pg.portalAdmin = true)
FROM PermissionGroup pg
WHERE pg.id = p.permissionGroup.id
),
p.write = (
SELECT pg.writeByDefault
FROM PermissionGroup pg
WHERE pg.id = p.permissionGroup.id
),
p.control = (
SELECT pg.controlByDefault
FROM PermissionGroup pg
WHERE pg.id = p.permissionGroup.id
)
WHERE p.permissionObject.id = :objectId
""")
void updateAllPermissionsToDefault(@Param("objectId") Long objectId);
Below is a stacktrace that indicates an error in the word “or”, but it is not entirely clear why. If you put such a subquery into a regular query, the syntax will be completely correct and there will be no error.
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract void ru.inrecolan.inportal.repository.permissions.FieldPermissionRepository.updateAllPermissionsToDefaultByParentId(java.lang.Long)!
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:63)
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:76)
at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:56)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:140)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:207)
at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:78)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:574)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:567)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133)
at java.base/java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1054)
at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:569)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:559)
at java.base/java.util.Optional.map(Optional.java:265)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:559)
at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:332)
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:297)
at org.springframework.data.util.Lazy.getNullable(Lazy.java:212)
at org.springframework.data.util.Lazy.get(Lazy.java:94)
at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:300)
at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:121)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
... 75 common frames omitted
Caused by: java.lang.IllegalStateException: Unexpected AST: org.hibernate.hql.internal.ast.tree.SqlNode
-[OR] SqlNode: 'or'
at org.hibernate.hql.internal.ast.tree.SelectExpressionList.collectSelectExpressions(SelectExpressionList.java:48)
at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:127)
at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1026)
at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:794)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:689)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.newValue(HqlSqlBaseWalker.java:1256)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:1068)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:777)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:393)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:155)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)
at jdk.internal.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:366)
at com.sun.proxy.$Proxy223.createQuery(Unknown Source)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87)
... 104 common frames omitted
The problem was solved by replacing the SQL query with the following:
@Query("""
UPDATE #{#entityName} p
SET
p.read = (
SELECT (CASE pg.readByDefault WHEN TRUE THEN TRUE ELSE pg.portalAdmin END)
FROM PermissionGroup pg
WHERE pg.id = p.permissionGroup.id
),
p.write = (
SELECT pg.writeByDefault
FROM PermissionGroup pg
WHERE pg.id = p.permissionGroup.id
),
p.control = (
SELECT pg.controlByDefault
FROM PermissionGroup pg
WHERE pg.id = p.permissionGroup.id
)
WHERE p.permissionObject.id = :objectId
""")
void updateAllPermissionsToDefault(@Param("objectId") Long objectId);
, but I don’t understand why the first option doesn’t work. Is there something wrong with the subqueries in Hibernate?