NH-3506 - ICriteria/QueryOver create incorrect left join condition when table-per-hierarchy is used with filters

[nhibernate-bot]

Patrick Froede created an issue — 5th August 2013, 14:43:41:

Querying using ICriteria or QueryOver creates joins with conditions in the main WHERE clause instead of the JOIN clause.

This breaks left joins when using table-per-hierarchy discriminators as the condition in the WHERE clause effectively turns the left join into an inner join.

This bug only appears when any filter is enabled, whether the filter is used or not.

It also does not appear to affect queries written in HQL.

This mapping demonstrates the problem:

<class name="BaseClass">
	<id name="Id">
		<generator class="guid.comb"/>
	</id>
  <discriminator column="discriminator" type="String"/>

  <subclass name="Employee" discriminator-value="Emp">
    <many-to-one name="Department" class="Department"/>
  </subclass>

  <subclass name="Department" discriminator-value="Dep">
  </subclass>
</class>

Querying using this HQL query works fine:

select e from Employee e left join e.Department d

This QueryOver query fails:

session.QueryOver<Employee>().JoinQueryOver(x => x.Department, JoinType.LeftOuterJoin)

SQL generated for HQL query:

select employee0_.Id         as Id0_,
       employee0_.Department as Department0_
from   BaseClass employee0_
       left outer join BaseClass department1_
         on employee0_.Department = department1_.Id
            and department1_.discriminator = 'Dep'
where  employee0_.discriminator = 'Emp'

SQL generated for QueryOver query:

SELECT this_.Id         as Id0_1_,
       this_.Department as Department0_1_,
       department1_.Id  as Id0_0_
FROM   BaseClass this_
       left outer join BaseClass department1_
         on this_.Department = department1_.Id
WHERE  department1_.discriminator = 'Dep'
       and this_.discriminator = 'Emp'

A test case will follow shortly.

I have also provided a patch containing a fix that appears to solve this issue. All unit tests still pass after this fix.

---

Patrick Froede added a comment — 6th November 2013, 9:52:12:

Are there any plans to integrate this? This issue is becoming quite important for us to fix.

Is there anything I can do to help with this issue?

---

Patrick Froede added a comment — 10th June 2014, 14:02:15:

Created pull request: #268

---

Oskar Berggren added a comment — 27th July 2014, 16:46:45:

See some notes on NH-3461 and the other linked issues.

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, where many believe that filters on many-to-one where a bad idea from the start. Note that you can disable filters for many-to-one by setting use-many-to-one to false on the filter-def. Which should prevent the issue with the discriminator being in the wrong place.

I'm not going to apply the pull request at the moment. All the behavior around filters on many-to-one seems poorly defined and documented and so I would prefer not to change anything at the moment. Will the workaround of setting use-many-to-one work for you?

---

greenmoose added a comment — 11th March 2015, 8:42:37:

We have the same issue where a "References" association used in a left join in a table-per-hiearchy design causes the discriminator value to be used in the global where thus resulting in no rows if the left join does not produce any rows.

The only workaround I see is creating query only property that, instead of referencing the concrete type, references the common base type and then manually insert the discriminator as a restriction in the left join itself.
Am I missing something or is there a better workaround for above scenario?

---

Patrick Froede added a comment — 11th March 2015, 8:56:31:

Disabling filters on many-to-one by setting use-many-to-one to false on the filter-def worked in my case.

This was fine for us as we didn't actually want the filters applying in a many-to-one scenarios.

The bit would still like changed is that enabling any filter affects discriminators in an unexpected way even if the filters do not apply to the class.

---

greenmoose added a comment — 11th March 2015, 9:28:09:

Thanks, got it. We do have filter on the session to filter out "deleted" entities, and we don't really want to disable this since then the behavior will change so I guess going with ugly use-query-only-when-left-joining alternative is the way to go.

---

Radim.Kohler added a comment — 9th July 2015, 14:37:32:

Please, are there any plans to apply that patch? Seems to be working. And the issue without fix still remains? Any comments?
Thanks
Radim Köhler