JPQL query delete not accept a declared JOIN?

Question

I'm trying to understand why the Hibernate not accepts this follow JPQL:

@Modifying
@Query("delete from Order order JOIN order.credit credit WHERE credit.id IN ?1")
void deleteWithListaIds(List<Long> ids);

The error that I receive is:

Caused by: java.lang.IllegalArgumentException: node to traverse cannot be null!
    at org.hibernate.hql.internal.ast.util.NodeTraverser.traverseDepthFirst(NodeTraverser.java:46)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)

But accepts this:

@Modifying
@Query("delete from Order order WHERE order.credit.id IN ?1")
void deleteWithListaIds(List<Long> ids);

The entity Order (the entity Credit does not map the Orders):

@Entity
public class Order {

    @Id
    @Setter
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQUENCE)
    @SequenceGenerator(name = SEQUENCE, sequenceName = SEQUENCE, allocationSize = 1)
    @Column(name = "id", nullable = false)
    private Long id;

    @JoinColumn(name = "credit_id", foreignKey = @ForeignKey(name = "fk_order_credit"))
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private Credit credit;


}

In select statements, the two approaches are accepted, but I don't understand why Hibernate have this limitation or if I'm doing something wrong in my DELETE Jpql. I would like to declare the JOIN in the query.

The only way that I know to resolve this problem in more complex queries is create a subselect:

delete from Order order WHERE order.id IN (
    SELECT order.id FROM Order order
    JOIN order.credit credit
    WHERE credit.id in ?1)

Is this the right approach for more complex delete queries?

I'm using the Spring Jpa Repository in the code above and Spring Boot 1.5.10.RELEASE.

can you please post the relationship arguments (i.e. how are order in Credit and credit in Order) are defined — osama yaccoub, Mar 13 '18 at 18:06
@osamayaccoub thanks, I edited the question with the relevant code. — Dherik, Mar 13 '18 at 18:13
also you can allow hibernate to log queries and check the generated queries ... I think the finally generated queries wont be the same ... also some databases have restrictions on deletion from multiple tables ... which db engine are u using https://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server — osama yaccoub, Mar 13 '18 at 18:24
MySQL won't let you delete when a subselect is involved on the same table, it will fail with `SQL Error: 1093, SQLState: HY000 You can't specify target table 'order' for update in FROM clause` — salomvary, Sep 06 '22 at 05:23

score 8 · Accepted Answer · edited Jun 20 '20 at 09:12

I don't understand why Hibernate have this limitation.

It is specified as such in the JPA Spec in section 4.10:

delete_statement ::= delete_clause [where_clause]
delete_clause ::= DELETE FROM entity_name [[AS] identification_variable]

So joins aren't allowed in delete statements. Why this was decided this way is pure speculation on my side. But the select_clause or delete_clause specify what the query operates on. While it is totally fine for a select statement to operate on a combination of multiple entities a join for a delete doesn't really make much sense. It just forces you to specify which entity to delete.

The only way that I know to resolve this problem in more complex queries is to create a subselect:

Is this the right approach for more complex delete queries?

If you can't express it using simpler means then yes, this is the way to go.

JPQL query delete not accept a declared JOIN?

1 Answers1