Hibernate Reactive eager fetching the wrong record for ManyToOne

[oaklandcorp-jkaiser]

Describe the bug

Automated tests are showing me some incorrect answers after upgrading an existing API. It seems like when many records are returned from SQL and each of those records have a unique nested record eagerly fetched and joined in those nested records may be the wrong one. I believe the bug I'm seeing is mostly related to a field with the decorator @ManyToOne(fetch = FetchType.EAGER, optional = false).

Expected behavior

The correct nested record is joined.

Actual behavior

Sometimes the wrong nested record is joined.

How to Reproduce?

I created a commit off of the 3.15 branch of the quarkus-quickstarts project that I believe is representative of the issue I'm seeing in a real project. The single new test case should demo the API returning the wrong response.

quarkusio/quarkus-quickstarts@56355c0

Output of uname -a or ver

Linux dev 6.8.0-52-generic #53~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Jan 15 19:18:46 UTC 2 x86_64 x86_64 x86_64 GNU/Linux

Output of java -version

openjdk 17.0.6 2023-01-17 LTS

Quarkus version or git rev

3.15.4

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

Apache Maven 3.8.6 (84538c9988a25aec085021c365c560670ad80f63)

Additional information

After the release of Quarkus 3.15.4 I've tried to upgrade an API that has been stuck back in Quarkus 3.8 due to Hibernate Reactive bugs. The last issue I encountered has been fixed but now automated tests show some incorrect answers coming back from the API.

I did test this on Quarkus 3.20 as well and it seems to suffer from the same issue.

[quarkus-bot]

/cc @DavideD (hibernate-reactive), @gavinking (hibernate-reactive)

[oaklandcorp-jkaiser]

As always thanks for the support on issues like this! Please let me know if I can provide an extra details to help. I do have a pretty strong automated test suite to verify the correct existing behavior of the API but it takes a bit of work to fairly translate it to a standalone project.

[gsmet]

@DavideD it's probably a good idea to check if it's a HR only issue or if ORM is affected as well, as it could be rather serious from a data integrity point of view.

[yrodiere]

Thanks for the reproducer, I just had a look. You'll find my modified version of the reproducer there: https://github.com/yrodiere/quarkus-quickstarts/tree/wrong-eager-fetch . It's based on the latest snapshot of Quarkus, and applies the same test to Hibernate ORM (blocking).

The good news is Hibernate ORM (blocking) is not affected.
The bad news is Hibernate Reactive most definitely has a problem, even in the most recent version of Quarkus. Essentially it seems to fetch the association for the first encountered element, and to stick to that for the next ones.

See the (correct) SQL in Hibernate ORM:

[Hibernate] 
    select
        f1_0.id,
        f1_0.friendId,
        f1_0.name 
    from
        known_fruits f1_0 
    order by
        f1_0.id
[Hibernate] 
    select
        f1_0.id,
        f1_0.name 
    from
        known_flowers f1_0 
    where
        f1_0.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

And the (incorrect) one in Hibernate Reactive:

[Hibernate] 
    select
        f1_0.id,
        f1_0.friendId,
        f1_0.name 
    from
        known_fruits f1_0 
    order by
        f1_0.id
[Hibernate] 
    select
        f1_0.id,
        f1_0.name 
    from
        known_flowers f1_0 
    where
        f1_0.id=$1

Notice how there's only one query to fetch the association, with a single requested ID...

So, yeah. This is a bug in Hibernate Reactive, and we need to handle that upstream.

[yrodiere]

BTW I moved this up our list priorities, since it's really bad, but this week and the next one are really bad timing due to travel and PTO, so we might not make speedy progress... we're on it though.

[oaklandcorp-jkaiser]

Nice! Glad you were able to make sense of my test case and have made some traction in identifying what is wrong. I've been blocked from upgrading my project for a while now so a bit more time isn't going to be the end of the world 😄. Please keep this issue up-to-date with any news of a fix, release, or workaround that might help. I'll do my best to be your canary to test early fixes!

[yrodiere]

See hibernate/hibernate-reactive#2166 , this should be fixed in the next release of Hibernate Reactive (2.4.6?) and will propagate to Quarkus once we upgrade to that version.

[oaklandcorp-jkaiser]

Great! I look forward to using it officially.

Just curious... once the new version of Hibernate Reactive is released and there is the lull before it is integrated into a Quarkus release... is possible to use Maven to override the internal version of the library used in a Quarkus project to try this fix sooner? Would something like that work to incorporate this fix sooner without breaking a bunch of Quarkus internals? If something like this was plausible I'd probably like to try it to hunt for additional Hibernate Reactive bugs sooner rather then later.