[hibernate-issues] [JIRA] (HHH-13944) HQL/JPQL size() does not work (anymore) with nested expression

[Alexandre Jacob (JIRA)]

Alexandre Jacob ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3Abe6a9f9e-5d42-485d-a407-922aaec65d08 ) *created* an issue

Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYTM4NDY0ODVmYjgwNDVkODg1ZTdjYTYyMTBjMmJjZjAiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HHH-13944?atlOrigin=eyJpIjoiYTM4NDY0ODVmYjgwNDVkODg1ZTdjYTYyMTBjMmJjZjAiLCJwIjoiaiJ9 ) HHH-13944 ( https://hibernate.atlassian.net/browse/HHH-13944?atlOrigin=eyJpIjoiYTM4NDY0ODVmYjgwNDVkODg1ZTdjYTYyMTBjMmJjZjAiLCJwIjoiaiJ9 ) HQL/JPQL size() does not work (anymore) with nested expression ( https://hibernate.atlassian.net/browse/HHH-13944?atlOrigin=eyJpIjoiYTM4NDY0ODVmYjgwNDVkODg1ZTdjYTYyMTBjMmJjZjAiLCJwIjoiaiJ9 )

Issue Type: Bug Affects Versions: 5.4.13 Assignee: Unassigned Components: hibernate-core Created: 10/Apr/2020 09:14 AM Priority: Critical Reporter: Alexandre Jacob ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%3Abe6a9f9e-5d42-485d-a407-922aaec65d08 )

The size() HQL/JPQL expression does not work anymore with nested path.

Given the following entities:
(TL;DR: a student can have a teacher (N - 1) a teacher can have multiple skills (N - N))

@Entity
public class Student {

   private Integer id;

   private Teacher teacher;

   @Id
   @GeneratedValue
   public Integer getId() {
       return id;
   }

   public void setId( Integer id) {
       this.id = id;
   }

   @ManyToOne(optional = false )
   @JoinColumn(name = "teacher_id" )
   public Teacher getTeacher() {
       return teacher;
   }

   public void setTeacher(Teacher teacher) {
       this.teacher = teacher;
   }
}

@Entity
public class Teacher {

   private Integer id;

   private Set<Student> students = new HashSet<>();

   private Set<Skill> skills = new HashSet<>();

   @Id
   @GeneratedValue
   public Integer getId() {
       return id;
   }

   public void setId( Integer id) {
       this.id = id;
   }

   @OneToMany(mappedBy = "teacher" , cascade = CascadeType.ALL, orphanRemoval = true )
   public Set<Student> getStudents() {
       return students;
   }

   public void setStudents(Set<Student> students) {
       this.students = students;
   }

   @Cascade(org.hibernate.annotations.CascadeType.SAVE_UPDATE)
   @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
   public Set<Skill> getSkills() {
       return skills;
   }

   public void setSkills(Set<Skill> skills) {
       this.skills = skills;
   }
}

@Entity
public class Skill {

   private Integer id;

   private Set<Teacher> teachers;

   @Id
   @GeneratedValue
   public Integer getId() {
       return id;
   }

   public void setId( Integer id) {
       this.id = id;
   }

   @ManyToMany(mappedBy = "skills" )
   public Set<Teacher> getTeachers() {
       return teachers;
   }

   public void setTeachers(Set<Teacher> teachers) {
       this.teachers = teachers;
   }
}

I expect the following query:

select student from Student student where size (student.teacher.skills) > 0

To translate into:

   select
       student0_.id as id1_1_,
       student0_.teacher_id as teacher_2_1_ 
   from
       Student student0_ cross 
   join
       Teacher teacher1_ 
   where
       student0_.teacher_id=teacher1_.id 
       and (
           select
               count (skills2_.teachers_id) 
           from
               Teacher_Skill skills2_ 
           where
               teacher1_.id=skills2_.teachers_id
       )>0

(this is the query generated by version 5.4.12 )

While it translates into:

   select
       student0_.id as id1_1_,
       student0_.teacher_id as teacher_2_1_ 
   from
       Student student0_ 
   where
       (
           select
               count (skills1_.teachers_id) 
           from
               Teacher_Skill skills1_ 
           where
               student0_.id = skills1_.teachers_id
       )>0

(since version 5.4.13 the where condition is not the good one anymore)

The subquery is comparing student.id (instead of teacher_id ) against teachers_id.

I think this is related to work done in HHH-13619 ( https://hibernate.atlassian.net/browse/HHH-13619 ) Closed (introduction of CollectionSizeNode )

( https://hibernate.atlassian.net/browse/HHH-13944#add-comment?atlOrigin=eyJpIjoiYTM4NDY0ODVmYjgwNDVkODg1ZTdjYTYyMTBjMmJjZjAiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13944#add-comment?atlOrigin=eyJpIjoiYTM4NDY0ODVmYjgwNDVkODg1ZTdjYTYyMTBjMmJjZjAiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100124- sha1:185bad4 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200410/f7f2df97/attachment.html