Query on one-to-one property returns incorrect results
Description
Environment
Attachments
Activity
Frédéric Delaporte January 23, 2017 at 10:25 PMEdited
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 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 forfrom 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...).
Frédéric Delaporte January 23, 2017 at 6:44 PM
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 September 28, 2015 at 1:48 PM
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.)
Neal Groothuis April 18, 2015 at 3:00 PM
Thanks for picking this up! Unfortunately, I no longer have convenient access to a .NET development environment.
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.