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

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

    Details

    • 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

      Description

      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.

      Example:

      interface ITDao extends JpaRepository<IT, Key> {
          @Modifying
          @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?

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: