Wrong HQL Query Generation in case of Composite ID

Hibernate ORM
1

We are migrating to Composite Primary Key in our code as we are using Postgres which requires Partition Key to be part of Primary Key.

Below is our schema:
Company Entity

public class Company {
    private Long id;

    @Id
    @Column(name = "COMPANY_SEQ")
    public Long getId() {
        return id;
    }


}

Event Entity

public class Event implements Serializable {

    private Long id = new Random().nextLong();
    private Company company;
    private Set<EventDetail> eventDetailSet;
    private String eventType;
    @Id
    @Column(name = "EVENT_SEQ")
    public Long getId() {
        return id;
    }

    @JoinColumn(name = "COMPANY_FK")
    @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY, optional = false)
    @Id
    public Company getCompany() {
        return company;
    }

    @Column(name = "EVENT_TYPE")
    public String getEventType() {
        return eventType;
    }

    public void setEventType(String eventType) {
        this.eventType = eventType;
    }

    @OneToMany(cascade =  CascadeType.MERGE  , mappedBy = "event" )
    Set<EventDetail> getEventDetailSet()
    {
        return eventDetailSet;
    }
}

EventDetail Entity

public class EventDetail implements Serializable {

    private Long id;
    private Company company;
    private Event event;
    @Id
    @Column(name = "EVENT_DETAIL_SEQ")
    public Long getId() {
        return id;
    }


    @JoinColumn(name = "COMPANY_FK")
    @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY, optional = true)
    @Id
    public Company getCompany() {
        return company;
    }

    private Long eventSeq;

    @Column(name = "EVENT_FK")
    public Long getEventSeq() {
        return eventSeq;
    }

    public void setEventSeq(Long eventSeq) {
        this.eventSeq = eventSeq;
    }

    public void setEvent(Event event) {
        this.event = event;
        this.eventSeq = event == null ? null : event.getId();
    }

    @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY, optional = true)
    @JoinColumns({
            @JoinColumn(name = "EVENT_FK", referencedColumnName = "EVENT_SEQ", insertable = false, updatable = false),
            @JoinColumn(name = "COMPANY_FK", referencedColumnName = "COMPANY_FK", insertable = false, updatable = false)
    })
    public Event getEvent() {
        return event;
    }
}

But on firing this hql query, we are getting wrong SQL Query generation.

HQL

select ed from EventDetail ed where ed.event.company  = :company

SQL Generated:

select
        ed1_0.COMPANY_FK,
        ed1_0.EVENT_DETAIL_SEQ,
        ed1_0.EVENT_FK 
    from
        EventDetail ed1_0 
    where
        ed1_0.COMPANY_FK=?

These sort of queries were working fine on Single Id, but starts behaving like this on moving to Composite ID.

Sample Test Case:

Branch: feature-composite-id-hql

I have shown both Single Id and Composite Id in the test case.
Can you please help to check this?

2

What’s the problem? Please describe what happens that you didn’t expect and state what you would have expected.

3

@beikov Basically, it is not joining EventDetail table with Event, but ideally it should have joined as Event can be null for EventDetail, in that case no results should come. But without join, results will come even if the Event is null.

Example HQL:

select ed from EventDetail ed where ed.event.company  = :company

Ideally the SQL generated should be:

select
        compositek0_.EVENT_DETAIL_SEQ as event_de1_2_,
        compositek0_.COMPANY_FK as company_3_2_,
        compositek0_.EVENT_FK as event_fk2_2_ 
    from
        EventDetail compositek0_ cross 
    join
        Event compositek1_ 
    where
        compositek0_.EVENT_FK=compositek1_.EVENT_SEQ 
        and compositek0_.COMPANY_FK=compositek1_.COMPANY_FK 
        and compositek1_.COMPANY_FK=?

But in our case, this is the SQL generated:

select
        ed1_0.COMPANY_FK,
        ed1_0.EVENT_DETAIL_SEQ,
        ed1_0.EVENT_FK 
    from
        EventDetail ed1_0 
    where
        ed1_0.COMPANY_FK=?
4

Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

5

@beikov I have already added the issue reproduction in the test case template.
Created this JIRA as well:
https://hibernate.atlassian.net/browse/HHH-18262

6

That’s great, thanks. Always remember to share the link here in the forum as well so others can follow it. We’ll look into it as soon as possible. In the meantime, you can add ed.event.id is not null as condition, because that is how we will fix this.

7

@beikov Yes, I had already posted the reproduction issue link in this post itself.
Anyway, but why do we require adding the condition ed.event.id in the HQL.
If we are using ed.event.company in the query, Hibernate should automatically add a join between EventDetail and Event. And that join will prevent the records with null Event.
The same thing was happening when we had a single Id.

Hibernate join optimisation should happen only when none of the fields of the Composite Key can be null.

8

Anyway, but why do we require adding the condition ed.event.id in the HQL.

I’m just suggesting you a workaround. This is a bug, and we will fix that.

If we are using ed.event.company in the query, Hibernate should automatically add a join between EventDetail and Event

We try to be smart and avoid the join so that you can have faster queries :wink: