NH-3117 - Query on one-to-one property returns incorrect results

[nhibernate-bot]

Neal Groothuis created an issue — 17th April 2012, 14:01:22:

Given a pair of classes with a one-to-one relationship, attempting to query for items which do not have a corresponding item in the other class always returns an empty set.

For example, if I have Person and Employee tables a la section 5.1.1 of the manual (http://nhibernate.info/doc/nh/en/index.html#mapping-declaration-onetoone), the HQL query "from Person as person where person.Employee is null" returns an empty result set, even if there are entries in the Person table which do not have corresponding entries in the Employee table.

The attached project illustrates this. It is configured to talk to a local SQL Server 2008 database called "nhibernate", and DDL to initialize the tables is included in the file DDL.sql.

---

Luke Horsley added a comment — 25th February 2014, 11:06:09:

This issue is a real major issue which appears to affect HQL, Criteria and LINQ. Rather than performing a join to the employees table it merely checks the person identifier is not null.

---

Frédéric Delaporte added a comment — 17th April 2015, 9:53:21:

If the example link was used as it is currently ((http://nhibernate.info/doc/nh/en/index.html#mapping-declaration-onetoone)), the query is on the constrained side, meaning the other side forcibly exists.
So this would not be a bug.
By the way, in current example person id is declared as foreign. This means it is supposed to be generated on employee side, further enforcing the fact that in this model a person cannot exists without an employee entry.
(Yes this looks functionally a bit inverted, but technically this is what this example does.)

But the attachment is indeed quite different. It is exactly like the case for which I was looking whether a bug was already reported or not.
In the attachment case, both entities can live independently, having both their own identity generator. It is not the "true" one-to-one case, but the one relying on 'one-to-one property-ref' for matching on the side not holding the foreign-key, the other side using many-to-one.
And yes, there is a querying bug in this case as described in description of this jira issue.

Neal, may you convert your attachment to a nunit case please? It would help for this issue to get solved. (See https://nhibernate.jira.com/browse/NH-2290 for an example nunit attachment, or see http://nhibernate.info/blog/2008/10/03/the-best-way-to-solve-nhibernate-bugs-submit-good-unit-test.html )

---

Neal Groothuis added a comment — 18th April 2015, 15:00:02:

Thanks for picking this up! Unfortunately, I no longer have convenient access to a .NET development environment.

---

Frédéric Delaporte added a comment — 28th September 2015, 13:48:07:

It appears this bug occurs with "true" one-to-one cases too (at least with NH 4.0.3.4000).

"from Employee as e where e.Person is null" and "from Employee as e where e.Person.Id is null" get translated to a SQL query like "select e.* from EMPLOYEE as e where e.EMPLOYEE_ID is null".
Furthermore, the similar bug occurs on "is not null" condition.

Workaround: testing nullity of a non-null-able property of Person other than its identifier (if we have such a property).

(I will try to take the time for uploading a test case soon.)

---

Frédéric Delaporte added a comment — 23rd January 2017, 18:44:39:

At last, here is the test case. Unfortunately, I have messed up with the drag & drop and put directly some files instead of just the zip. Sorry for the inconvenience. I am not allowed to delete them myself.

---

Frédéric Delaporte added a comment — 23rd January 2017, 22:25:32:

Now I have written this test case, it looks to me this issue is indeed a "work as intended" case for the is null test the opener reported.
With NHibernate, navigating relationships without an explicit join is done with an inner join. The first two examples of <"select clause" documentation>(http://nhibernate.info/doc/nhibernate-reference/queryhql.html#queryhql-select) illustrates this.

So you can not test for non existence without an explicit join.

Your query should simply be written as:
from Employee as e left join e.Person as p where p is null
This one works.

Still, the "optimization" causing the test to occurs on parent side give some muddy result for
from Employee as e where e.Person is not null
This case should be equivalent to an inner join, but it is not, it does not filter out employee without person.
A better workaround than my previous one is then:
from Employee as e join e.Person as p

So now I will re-upload a test case with only the not null test case (hopefully without messing with the drag&drop again...).

[OlegUfaev]

Any suggestions on when this might go into release?

In our project we use the described use case. A fix would help to use cleaner and more understandable code.