spring data rest update produce cross join sql error

Question

I want to use spring data rest to update rows of certain user , but at run time this query has strange "cross join" added to the query .

spring data rest method

 @Modifying
@Transactional
@Query("Update Notification n SET n.noticed = true Where n.notificationPost.owner.userId = 1 ")
public void postNoticed();

run time created query

Hibernate: update notification cross join  set noticed=true where owner_id=?

My only concern is why "cross join" added as it gives sql error

org.postgresql.util.PSQLException: ERROR: syntax error at or near "cross"

I call this method directly by rest invoke , and also from mvc controller, both ways produce the same error

Thanks in advance.

Show us some entities code snippet. – Blank Nov 23 '16 at 02:38 — Blank, Nov 23 '16 at 02:38

score 18 · Accepted Answer · answered Nov 23 '16 at 23:28

Found solution as stated in http://forum.spring.io/forum/spring-projects/data/114271-spring-data-jpa-modifying-query-failure

"No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins. "(Hibernate doc reference: http://docs.jboss.org/hibernate/core.../#batch-direct)."

So I edited my code to use sub query

@Modifying
@Transactional
@Query("Update Notification n SET n.noticed = true Where n.notificationPost.postId in (SELECT n2.notificationPost.postId FROM Notification n2  where n2.notificationPost.owner.userId =:#{#security.principal.user.userId}) ")
public int postNoticed();

This applies only to DML-style operations. – Alex78191 May 23 '17 at 23:11 — Alex78191, May 23 '17 at 23:11

spring data rest update produce cross join sql error

1 Answers1

Linked