JPQL Left join translated to sql incorrectly

[dtaimanov]

Description:

Left join in JPQL translated into inner join in SQL if the same reference attribute used in join and in where independently. JPQL:

select n from Note n left join n.user u 
where n.user is null or n.user.id = :user_id 
order by u.name desc

Actual SQL:

SELECT t1.ID, t1.TEXT, t1.USER_ID FROM USER_ t0, NOTE t1 
WHERE (((t1.USER_ID IS NULL) OR (t1.USER_ID = ?)) AND (t0.ID = t1.USER_ID)) 
ORDER BY t0.NAME DESC

Expected SQL:

SELECT t1.ID, t1.TEXT, t1.USER_ID FROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID = t1.USER_ID) 
WHERE ((t1.USER_ID IS NULL) OR (t1.USER_ID = ?)) 
ORDER BY t0.NAME DESC

or

SELECT t1.ID, t1.TEXT, t1.USER_ID FROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID = t1.USER_ID) 
WHERE ((t1.USER_ID IS NULL) OR (t0.ID = ?)) 
ORDER BY t0.NAME DESC

Problem Entities without specified user are absent in result list because of this behaviour but at the same time they are present if the same order by or where are used separately (please, see "Additional context")

To Reproduce

Run tests in example project: joinNarrowingBug.zip Actual Result: test JoinTranslationTest#leftJoinLost will fail because SQL generated as described above Expected result: test JoinTranslationTest#leftJoinLost will pass

• Eclipselink version: 2.7.9, 4.0.1, 4.0.2
• Java version: 17
• Entity source:

@Table(name = "NOTE")
@Entity
public class Note {
    @Column(name = "ID",nullable = false)
    @Id
    private UUID id;

    @Column(name = "TEXT", length = 1000)
    private String text;

    @JoinColumn(name = "USER_ID")
    @ManyToOne(fetch = FetchType.LAZY)
    User user;

    // getters and setters
}

@Entity
@Table(name = "USER_")
public class User {
    @Column(name = "ID",nullable = false)
    @Id
    private UUID id;

    @Column(name = "NAME", nullable = false)
    String name;

   // getters and setters
}

• JPA context:

         <property name="eclipselink.logging.logger" value="DefaultLogger" />
         <property name="eclipselink.logging.level" value="INFO" />
         <property name="eclipselink.logging.level.sql" value="FINE"/>
         <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
         <property name="jakarta.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
         <property name="jakarta.persistence.jdbc.url" value="jdbc:hsqldb:mem:standalone"/>
         <property name="jakarta.persistence.jdbc.user" value="none"/>
         <property name="jakarta.persistence.jdbc.password" value="none"/>

• Database provider: HSQL (in memory)
• JDBC driver provider/version: org.hsqldb.jdbcDriver (HSQLDB 2.7.2)

Additional context SQL generated correctly when the same where and order by used separately from each other (see JoinTranslationTest#joinTranslatedCorrectly in attached project): 1.

select n from Note n 
where n.user is null or n.user.id = :user_id

SELECT ID, TEXT, USER_ID FROM NOTE 
WHERE ((USER_ID IS NULL) OR (USER_ID = ?))

select n from Note n left join n.user u 
order by u.name desc

SELECT t1.ID, t1.TEXT, t1.USER_ID FROM NOTE t1 LEFT OUTER JOIN USER_ t0 ON (t0.ID = t1.USER_ID) 
ORDER BY t0.NAME DESC