Uploaded image for project: 'Hibernate ORM'
  1. HHH-12492

JPA delete query generated has missing table alias and thus incorrect semantics


    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.0.12, 5.2.16
    • Fix Version/s: 5.3.4, 5.4.0.CR1
    • Component/s: hibernate-core
    • Environment:
      Hibnernate with spring-data-jpa 1.11.11.RELEASE, spring-boot 1.5.12.RELEASE, java 8, 9 or 10


      2 Entities, both with a primary key named id as

      class Key implements Serializable {
          String pre
          long id
      Entity 1: IT
      class IT extends AbstractPersistable<Key> {
       // etc.
      Entity 2: PT with ManyToOne ForeignKey To IT
      class PT extends AbstractPersistable<Key> {
       @ManyToOne(optional = false)
       IT it

      The DAO method with a @Query for deletion generates the wrong SQL because the join at the end of the statement does not contain the alias or table name of the target table.


      interface ITDao extends JpaRepository<IT, Key> {
          @Query("delete from IT i where not exists (select p from PT p where p.it = i)")
          int deleteUnreferenced()

      SQL generated by Hibernate is:
      delete from IT where not (exists (select (pt1_.id, pt1_.pre) from PT pt1_ where (pt1_.it_id, pt1_.it_pre)=(id, pre))

      because "(id, pre)" does not have any alias or table name, the statement does not join wiht IT but with PT which is wrong.

      SQL generated should be:
      delete from IT where not (exists (select (pt1_.id, pt1_.pre) from PT pt1_ where (pt1_.it_id, pt1_.it_pre)=(IT.id, IT.pre))

      or it could be:
      delete from IT i where not (exists (select (pt1_.id, pt1_.pre) from PT pt1_ where (pt1_.it_id, pt1_.it_pre)=(i.id, i.pre))

      Or am I missing something?




            • Votes:
              1 Vote for this issue
              5 Start watching this issue


              • Created: