Problem description:

I have the two business entities Request and Exam. A Request can have a 0..1 association with an Exam, but the Exam table contains the foreign key to the Request table. Since the association is bi-directional, an Exam also has a property for accessing the corresponding Request. Also, each Request is linked to an Educator.

The query I'm attempting to make is that I want to get a list of all Requests linked to Educator 4550Z2 (through the TakenBy path) and which either don't have an Exam or have an Exam which DateAndTime lays in the future. In this particular case, the database contains 18 Request records for the Educator with Code 4550Z2. One of these Requests has an Exam linked to it.

The result of the HQL query below returns only 1 record, the one which does have an Exam. But I also expected the other ones, which don't have an Exam yet. Somehow, NHibernate seems to generate the incorrect SQL query. If I remove the part that uses the Exam association, everything is fine.

Although I have done something wrong, this seems to look like a bug in NHibernate.

Thanks

Hibernate version:
1.2.0.4000

Mapping documents:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Cbr.Dyktion.BusinessEntities" assembly="Cbr.Dyktion.BusinessEntities">
<class name="Request" table="DTN_AANVRAGEN" mutable="false">
<id name="Id">
<column name="ID" not-null="true"/>
<generator class="native">
<param name="sequence">DTN_SEQ_PK</param>
</generator>
</id>
<property name="ReceivedDate" column="ONTVANGST_DATUM" not-null="true"/>
<property name="BringsInterpreter" column="IND_EIGEN_TOLK" type="Cbr.Framework.NHibernate.JaNeeType, Cbr.Framework.Services" not-null="true"/>
<property name="DeclinedReasonPrivate" column="REDEN_NIET_GEACCEPTEERD_RIT" not-null="false"/>
<property name="DeclinedReasonPublic" column="REDEN_NIET_GEACCEPTEERD_TOP" not-null="false"/>
<property name="IsAccepted" column="IND_GEACCEPTEERD" type="Cbr.Framework.NHibernate.JaNeeType, Cbr.Framework.Services" not-null="true"/>
<property name="IsReleased" column="IND_VRIJGEGEVEN_CBR_MEDEWERKER" type="Cbr.Framework.NHibernate.JaNeeType, Cbr.Framework.Services" not-null="true"/>
<property name="ReasonSpecialApplication" column="REDEN_BIJZONDERE_AANVRAAG" not-null="false"/>
<property name="SendToEducator" column="IND_GEGEVENS_NAAR_OPLEIDER" type="Cbr.Framework.NHibernate.JaNeeType, Cbr.Framework.Services" not-null="false"/>
<property name="Version" column="VERSIENUMMER" />
<property name="Code" column="CODE" />
<property name="VehicleNumber" column="VOERTUIGNUMMER" />
<property name="ExternalRequestCode" column="EXTERNE_AANVRAAGCODE" />
<property name="IsPostponed" column="IND_UITGESTELD" type="Cbr.Framework.NHibernate.JaNeeType, Cbr.Framework.Services" />
<many-to-one name="Product" class="Product" column="PRODUCT_ID" />
<many-to-one name="Location" class="Location" column="LOCATIE_ID" />
<many-to-one name="Statement" class="PersonalStatement" column="EIGEN_VERKLARING_ID" />
<many-to-one name="Candidate" class="Candidate" column="PERSOON_ID" />
<many-to-one name="Language" class="Language" column="TAAL_ID" />
<many-to-one name="BusinessUnit" class="BusinessUnit" column="ORGANISATIE_ONDERDEEL_ID" />
<many-to-one name="Instructor" class="Instructor" column="INSTRUCTEUR_ID" />
<many-to-one name="TakenBy" class="Educator" column="OPLEIDER_ID_AFGENOMEN" />
<many-to-one name="RequestedBy" class="Educator" column="OPLEIDER_ID_INGEDIEND" />
<one-to-one name="Exam" class="Exam" property-ref="Request"/>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Cbr.Dyktion.BusinessEntities" assembly="Cbr.Dyktion.BusinessEntities">
<class name="Exam" table="DTN_EXAMENS" mutable="false">
<id name="Id">
<column name="ID" not-null="true"/>
<generator class="native">
<param name="sequence">DTN_SEQ_PK</param>
</generator>
</id>
<many-to-one name="Request" class="Request" column="AANVRAAG_ID" />
<property name="DateAndTime" column="DATUM_TIJD"/>
<property name="Version" column="VERSIENUMMER" />
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

session.CreateQuery(
"from Request req where req.TakenBy.Code = :code and (req.Exam is null or req.Exam.DateAndTime > :data)").
SetString("code", educatorCode).
SetDateTime("data", DateTime.Now).List<Request>()

The generated SQL (show_sql=true):

select request0_.ID as ID36_, request0_.ONTVANGST_DATUM as ONTVANGST2_36_, request0_.IND_EIGEN_TOLK as IND3_36_, request0_.REDEN_NIET_GEACCEPTEERD_RIT as REDEN4_36_, request0_.REDEN_NIET_GEACCEPTEERD_TOP as REDEN5_36_, request0_.IND_GEACCEPTEERD as IND6_36_, request0_.IND_VRIJGEGEVEN_CBR_MEDEWERKER as IND7_36_, request0_.REDEN_BIJZONDERE_AANVRAAG as REDEN8_36_, request0_.IND_GEGEVENS_NAAR_OPLEIDER as IND9_36_, request0_.VERSIENUMMER as VERSIEN10_36_, request0_.CODE as CODE36_, request0_.VOERTUIGNUMMER as VOERTUI12_36_, request0_.EXTERNE_AANVRAAGCODE as EXTERNE13_36_, request0_.IND_UITGESTELD as IND14_36_, request0_.PRODUCT_ID as PRODUCT15_36_, request0_.LOCATIE_ID as LOCATIE16_36_, request0_.EIGEN_VERKLARING_ID as EIGEN17_36_, request0_.PERSOON_ID as PERSOON18_36_, request0_.TAAL_ID as TAAL19_36_, request0_.ORGANISATIE_ONDERDEEL_ID as ORGANIS20_36_, request0_.INSTRUCTEUR_ID as INSTRUC21_36_, request0_.OPLEIDER_ID_AFGENOMEN as OPLEIDER22_36_, request0_.OPLEIDER_ID_INGEDIEND as OPLEIDER23_36_ from DTN_AANVRAGEN request0_, DTN_OPLEIDERS educator1_, DTN_EXAMENS exam2_ where (educator1_.CODE=:p0 and request0_.OPLEIDER_ID_AFGENOMEN=educator1_.ID)and((request0_.ID is null )or(exam2_.DATUM_TIJD>:p1 and request0_.ID=exam2_.AANVRAAG_ID)); :p0 = '4550Z2', :p1 = '14-9-2007 7:29:07'

The expected SQL:

select request0_.ID as ID36_
, request0_.ONTVANGST_DATUM as ONTVANGST2_36_
, request0_.IND_EIGEN_TOLK as IND3_36_
, request0_.REDEN_NIET_GEACCEPTEERD_RIT as REDEN4_36_
, request0_.REDEN_NIET_GEACCEPTEERD_TOP as REDEN5_36_
, request0_.IND_GEACCEPTEERD as IND6_36_
, request0_.IND_VRIJGEGEVEN_CBR_MEDEWERKER as IND7_36_
, request0_.REDEN_BIJZONDERE_AANVRAAG as REDEN8_36_
, request0_.IND_GEGEVENS_NAAR_OPLEIDER as IND9_36_
, request0_.VERSIENUMMER as VERSIEN10_36_
, request0_.CODE as CODE36_
, request0_.VOERTUIGNUMMER as VOERTUI12_36_
, request0_.EXTERNE_AANVRAAGCODE as EXTERNE13_36_
, request0_.IND_UITGESTELD as IND14_36_
, request0_.PRODUCT_ID as PRODUCT15_36_
, request0_.LOCATIE_ID as LOCATIE16_36_
, request0_.EIGEN_VERKLARING_ID as EIGEN17_36_
, request0_.PERSOON_ID as PERSOON18_36_
, request0_.TAAL_ID as TAAL19_36_
, request0_.ORGANISATIE_ONDERDEEL_ID as ORGANIS20_36_
, request0_.INSTRUCTEUR_ID as INSTRUC21_36_
, request0_.OPLEIDER_ID_AFGENOMEN as OPLEIDER22_36_
, request0_.OPLEIDER_ID_INGEDIEND as OPLEIDER23_36_
from DTN_AANVRAGEN request0_
, DTN_OPLEIDERS educator1_
, DTN_EXAMENS exam2_
where ( educator1_.CODE='4550Z2'
and request0_.OPLEIDER_ID_AFGENOMEN=educator1_.ID)
and ( request0_.ID=exam2_.AANVRAAG_ID (+))
and ( exam2_.aanvraag_id is null
or exam2_.DATUM_TIJD > to_date('14-09-2007 07:29:07', 'dd-mm-yyyy HH24:MI:ss'))

Name and version of the database you are using:
Oracle 10g

The SQL looks fine except for (request0_.ID is null ). req.Exam = null is being translated into request0_.ID is null.
Have you tried setting fetch="join" on your one-to-one?

Exam is referencing Request using Request's ID. Request has direct reference to Exam (from DB standpoint) so the Exam/Request relationship is only valid when an Exam is present. Saying "where req.Exam is null" doesn't make sense to me without an outer join, since the req.Exam relationship is request's ID.

(The following has been edited because the first posting definitely did not include the more important practical solution! - HM)

You write:
>The query I'm attempting to make is that I want to get a list of all Requests linked to Educator 4550Z2 (through the TakenBy path) and which either don't have an Exam or have an Exam which DateAndTime lays in the future.

Somewhat more abstractly, you have class A and class B, with a nullable reference A-->B. You want to find all A where either no B exists; or some condition for B holds (e.g. B.DateAndTime > ...).

As far as I understand it, what we see here is one of the big "OR gaps": The logic of the disjunction (logical OR); one can argue that this is a Hibernate bug, but maybe it's also a feature: Hibernate (and many other OR mappings) will interpret a sub-expression A.B to mean that A should be INNER joined to B. At that point, all is lost: If there is no B for an A (e.g. if A.Association is NULL), the A is not joined in ...

* One possibility is to "multiply out" the OR and do two different queries.

* The other possibility is to use outer joins - then you, will also get As with missing Bs.

Regards
Harald M.

Hi Harald,

I'm not quite following you, but I can tell you that the problem has been solved with the latest build of NHibernate and using a LeftOuterJoin on the A->B association,