One-to-zero-or-one relation not returning data when checking for null

[marcelstuder]

Issue/Scenario

We are having troubles with some of our queries that contain null checks and don't return any data when the null check is in place. Our setup is as follows:

• database first (legacy database)
• one-to-zero-or-one relationship between two tables
• some queries contain null checks on the zero-or-one side and do not return data because of the null check

We are not sure if this is

• expected behavior/by design (zero-to-one-side mapped with Id generated by foreign)
• wrong mapping on our side
• a bug

Steps to reproduce

• Query data:
  • .Query<Sheep>().Where(s => s.Name == "Dolly")
  • **Result: *** Sheep "Dolly" returned
• Add a null check to the query (zero-or-one-side):
  • .Query<Sheep>().Where(s => s.Name == "Dolly" && s.SlaughterInfo == null)
  • **Result: *** no data returned due to generated SQL statement containing (slaughterInfo.SheepId is null)

I have created a sample project that demonstrates the issue.

Even if this is the expected behavior: from a developer's perspective who writes the Linq query it is not obvious that this null check is causing the query to fail without looking at the underlying mapping (or writing a test :-)).

Notes

We are using the following Versions:

• NHibernate 5.3.5
• FluentNhibernate 3.1.0

Workarounds

• check all queries in code and remove null checks if possible

[bahusoid]

Looks like something is not right with fix made in #2081

[bahusoid]

See for workaround: https://stackoverflow.com/a/57462715/9667775

So .Where(s => s.Name == "Dolly" && s.SlaughterInfo.Name == null) should work properly.

[ebalders]

@fredericDelaporte Shouldn't this be tagged as a regression? #2881 indicates it was introduced in 5.3.0 and significantly changes the results that are returned. This is a major breaking change for anyone upgrading from prior to 5.3.0

#2809 Fixed the issue with select(#2772), but this remains a problem for the query.

[bahusoid]

significantly changes the results that are returned. This is a major breaking change for anyone upgrading from prior to 5.3.0

It really affects only queries with is null check for many-to-one with not-found=ignore mapping. Are you sure you are using not-found=ignore mapping properly? If not-found=ignore is really required than your queries don't return all results too.

[fredericDelaporte]

not-found=ignore is from my view point quite a bad legacy. Maybe Hibernate has been smarter than us by not proposing this feature which appears to be quite difficult to get right in many edge cases.
Anyway we have to keep supporting it and avoid breaking changes about it, but I would advise to try as hard as possible to fix the db to make it impossible to have staled references on deleted data, instead of going on with using this feature.

[fredericDelaporte]

Fixed by #2889