Message Title

Issue Type:	Bug
Affects Versions:	6.2.0.CR4
Assignee:	Unassigned
Created:	04/Apr/2023 06:27 AM
Priority:	Major
Reporter:	Stefan Seidel

Test Case: https://github.com/s-seidel/hibernate-test-case-templates/tree/entity-graph-where/orm/hibernate-orm-6

Test: https://github.com/s-seidel/hibernate-test-case-templates/blob/5a8447259306f9cf61522ac5e0f6b6e89867e185/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/ORMUnitTestCase.java#L84

The SQL query that is generated is:

                                  select
        e1_0."id",
        c1_0."employeeid",
        c1_0."skillid",
        s1_0."id",
        c1_0."end",
        c1_0."start" 
    from
        "employee" e1_0 
    left join
        "skillassignment" c1_0 
            on e1_0."id"=c1_0."employeeid" 
            and (
                c1_0."start" <= cast(current_timestamp as date) 
                and (
                    c1_0."end" >= cast(current_timestamp as date) 
                    or c1_0."end" is null
                )
            )  
            and (
                c1_0."start" <= cast(current_timestamp as date) 
                and (
                    c1_0."end" >= cast(current_timestamp as date) 
                    or c1_0."end" is null
                )
            )  
    join
        "skill" s1_0 
            on s1_0."id"=c1_0."skillid"
                              

But the expected (and in Hibernate 5, actual) Query should be:

                              select
        e1_0."id",
        c1_0."employeeid",
        c1_0."skillid",
        s1_0."id",
        c1_0."end",
        c1_0."start" 
    from
        "employee" e1_0 
    left join
        "skillassignment" c1_0 
            on e1_0."id"=c1_0."employeeid" 
            and (
                c1_0."start" <= cast(current_timestamp as date) 
                and (
                    c1_0."end" >= cast(current_timestamp as date) 
                    or c1_0."end" is null
                )
            )  
    LEFT join
        "skill" s1_0 
            on s1_0."id"=c1_0."skillid"
                              

So the where clause is duplicated and the join to skill is not a LEFT join anymore, leading to too few results - the idea is that skillassignment is optional, therefore skill must also be optional.

Add Comment

Get Jira notifications on your phone! Download the Jira Cloud app for Android or iOS

This message was sent by Atlassian Jira