Nullable many-to-one relationship wrongly affected by filter
Description
Environment
Attachments
Activity
Long discussion about the feature when filters on many-to-one where first implemented:
https://groups.google.com/forum/#!topic/nhibernate-development/3KNmxAeyikI
There seems to be a lot of weird cases and non-consensus. Note that you can disable filters for many-to-one by setting use-many-to-one to false on the filter-def.
It doesn't seem obvious what the correct solution is here.
https://nhibernate.jira.com/browse/NH-1930#icft=NH-1930 and https://nhibernate.jira.com/browse/NH-3506#icft=NH-3506 propose that filter conditions on many to one should be in the join clause instead of in the where clause as they currently are. If that change is applied your criteria with the filter applied would become this SQL:
This will in fact return all three children, not just one. The 'B' parent will be excluded by the join, but since it's an outer join the child will still be returned, though with its parent property set to null (at least if eager fetching is used).
For comparison:
Simple HQL query does not bother about the filter on Parent and returns all three children:
HQL with condition on the parent is an implied join. The parent filter is in the where clause, which is fine as long as where're okay with this being an inner join. And the inner join is what this syntax means, so it's ok. Returns one child:
With an explicit outer join in the HQL the parent filter gets included in the join condition. It returns all three children:
Here's a test case
Hi @Fabrizio Gennari, can you please provide a test case?
Tested with SQLite 3, but also affects MS SQL Server 2008.
Use this schema
classes
mapping
Run this program
The first list has 3 elements, as expected
Children are c of b,c of a,c of no one
One would expect that the second list has 2 elements, as only parent with name B is filtered out. Yet, it has 1 element: the child with a null parent is filtered out as well.
Children are c of a
This behaviour should be imho corrected