Cannot project Panache entity with null @OneToOne reference

[Wouter684]

Describe the bug

When trying to project an entity which has a one-to-one or one-to-many relation, entities that do not have a coupled entity are not returned in query results. They are returned without projection. We noticed that Panache emits a query containing a regular join, that might be the issue.

Expected behavior

We expect that the projected result is still returned, with a null value for the coupled entity.

Actual behavior

The parent entity is not returned in query results.

How to Reproduce?

https://github.com/Wouter684/cannot-project-with-null

Run the unit test in the project. (mvn verify)

Output of uname -a or ver

No response

Output of java -version

openjdk version "21.0.3" 2024-04-16 LTS
OpenJDK Runtime Environment Temurin-21.0.3+9 (build 21.0.3+9-LTS)
OpenJDK 64-Bit Server VM Temurin-21.0.3+9 (build 21.0.3+9-LTS, mixed mode)  

Quarkus version or git rev

3.13.3

Build tool (ie. output of mvnw --version or gradlew --version)

Apache Maven 3.9.8 (36645f6c9b5079805ea5009217e36f2cffd34256)
Maven home: /Users/<redacted>/.sdkman/candidates/maven/current
Java version: 21.0.3, vendor: Eclipse Adoptium, runtime: /Users/<redacted>/.sdkman/candidates/java/21.0.3-tem
Default locale: nl_NL, platform encoding: UTF-8
OS name: "mac os x", version: "14.6.1", arch: "aarch64", family: "mac"

Additional information

No response

[quarkus-bot[bot]]

/cc @FroMage (panache), @loicmathieu (panache)

[Caster]

Related question on Stack Overflow (no answer, unfortunately).

[kosti31]

Strange thing is the same query .count() returns the correct number of records, but the .list() returns empty.

[Mohamed-Sghaier]

Any news for this projection issue? Thank you.

[FroMage]

The HQL sent to Hibernate is:

SELECT new org.example.MyProjectedEntity (id,field,bar,myRelatedEntity) FROM `org.example.MyEntity` ORDER BY `id`

This is turned into this SQL:

    select
        me1_0.id,
        me1_0.field,
        me1_0.bar,
        mre1_0.id,
        mre1_0.fieldName 
    from
        MyEntity me1_0 
    join
        MyRelatedEntity mre1_0 
            on mre1_0.id=me1_0.related_entity_id 
    order by
        me1_0.id

It works for .project(MyProjectedEntity.class).count() because ORM produces this count query:

    select
        count(*) 
    from
        MyEntity me1_0

(no join, so it works)

Without projection, we get this HQL:

FROM `org.example.MyEntity` ORDER BY `id`

Turned into this SQL:

    select
        me1_0.id,
        me1_0.bar,
        me1_0.field,
        me1_0.foo,
        me1_0.related_entity_id 
    from
        MyEntity me1_0 
    order by
        me1_0.id

Notice the lack of join, which makes it work.

This sounds like an upstream ORM issue, no @lucamolteni @yrodiere ?

[mbellade]

Yes @FroMage, the difference is the @OneToOne association is joined in the projection (i.e. dynamic instantiation) query, since we need the result immediately. I'm guessing in the other query, the join is not produced but you get a subsequent select for the related entity (if the FK value is non-null).

Hibernate will always produce an inner join for selected associations, including this case where the selection is used as argument for the dynamic instantiation. To produce a left-join, the correct way to generate the query would be:

SELECT new org.example.MyProjectedEntity(id, field, bar, related) FROM `org.example.MyEntity` m left join m.myRelatedEntity related ORDER BY m.id

[yrodiere]

Related, though not exactly the same problem since the "navigation operator" is implicit in your case:

https://github.com/quarkusio/quarkus/issues/36497 https://github.com/jakartaee/persistence/issues/697 https://hibernate.atlassian.net/issues/HHH-19401

I guess if we introduce a safe navigation operator, you'll be able to use this?.related in the projection?

Not saying it's great, but it's something, at least :)

[FroMage]

Well, not great, no, because it means I'd have to either prefix every projection part with this?. (well, I guess this is easy if it works for every member type), or inspect the members to see whether I need to prefix it.

I'll take a look at how ORM projections deal with this, and Jakarta Data.

[FroMage]

ORM projections with:

session.createSelectionQuery("select id, field, bar, myRelatedEntity from MyEntity", MyProjectedEntity.class).list();

Behave the same and generate:

    select
        me1_0.id,
        me1_0.field,
        me1_0.bar,
        mre1_0.id,
        mre1_0.fieldName 
    from
        MyEntity me1_0 
    join
        MyRelatedEntity mre1_0 
            on mre1_0.id=me1_0.related_entity_id

So that's coherent.

I'm not sure how to test JD projections, I think I would need a snapshot of ORM since projections are in Jakarta Data 1.1 IIUC.

Well, I'm not sure how to resolve this issue then. Call it a "known trap that is not a bug" and document it?

I guess we're not changing the JPA spec for this. On the other hand it does seem counterintuitive.

Any opinion, @gavinking ?