Wrong SQL generated when finding null 1:1 fk relations

Paul Newport · **Posted:** Tue Feb 01, 2005 2:13 pm

I have an Application object that has a one-to-one relationship with a queue. The database is set up so that queue has the key of applciation on it as a foreign key.

I want to find a count of all applications without a queue.

My HQL is this:

select new net.targetgroup.broker.queue.QueueCount(count(*),sum(application.advance)) from net.targetgroup.broker.application.Application as application where application.queueEntry is null

but this translates to the following SQL:

select count(*) as x0_0_, sum(applicatio0_.APPADVANCE) as x1_0_ from APPLICAT applicatio0_ where (applicatio0_.APPID is null )

which is wrong, as APPID is the primary key for APPLICAT. The only way I can see the generated SQL working would be if it did a subselect back to queue, saying where APPID not in (Select appid from queue)

Am I doing something wrong, or is hibernate ?

Hibernate version:
2.1.7

Mapping documents:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>


<class name="net.targetgroup.broker.application.Application" table="APPLICAT">

<id name="identifier" type="int" column="APPID" unsaved-value="-1">
<generator class="native" />
</id>

<property name="marketingID" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="MARKID" length="30" />
<property name="userStamp" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="USERSTAMP" length="10" />
<property name="fisaNumber" type="java.math.BigDecimal" column="FISANO" length="9" />
<property name="timeStamp" type="java.sql.Timestamp" column="DATETIME" length="19" />
<property name="loanType" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="APPLOANTYP" length="40" />
<property name="insurancePremium" type="java.math.BigDecimal" column="APPINSPREM" length="11" />
<property name="advance" type="java.math.BigDecimal" column="APPADVANCE" length="11" />
<property name="repayment" type="java.math.BigDecimal" column="APPREPAY" length="11" />
<property name="insurancePayment" type="java.math.BigDecimal" column="APPINSPAYM" length="11" />
<property name="term" type="net.targetgroup.util.datatypes.CustomIntType" column="APPTERM" length="3" />
<property name="loanPurpose" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="APPPURP" length="40" />
<property name="monthlyRate" type="java.math.BigDecimal" column="MNTHLYRATE" length="5" />
<property name="apr" type="java.math.BigDecimal" column="APPAPR" length="5" />
<property name="score" type="java.math.BigDecimal" column="APPSCORE" length="5" />
<property name="status" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="STATUS" length="10" />
<property name="totalAdvance" type="java.math.BigDecimal" column="TOTALADVANCE" length="11" />
<property name="requestedAdvance" type="java.math.BigDecimal" column="REQADVANCE" length="11" />
<property name="totalInterest" type="java.math.BigDecimal" column="TOTALCHARGE" length="11" />
<property name="totalRepayment" type="java.math.BigDecimal" column="TOTALREPAYMENT" length="11" />
<property name="debtToIncome" type="java.math.BigDecimal" column="APPDTI" length="5" />
<property name="profit" type="java.math.BigDecimal" column="APPPROFIT" length="11" />
<property name="loanToValue" type="java.math.BigDecimal" column="APPLTV" length="5" />
<property name="takeOnUser" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="TAKEONUSER" length="10" />
<property name="requestedTerm" type="net.targetgroup.util.datatypes.CustomIntType" column="REQTERM" length="3" />
<property name="lenderID" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="LENDID" length="40" />
<property name="takeOnDate" type="java.sql.Timestamp" column="TAKEONDATE" length="19" />
<property name="purchasePrice" type="java.math.BigDecimal" column="PURCHASEPRICE" length="11" />
<property name="deposit" type="java.math.BigDecimal" column="DEPOSITAMOUNT" length="11" />
<property name="depositSource" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="DEPOSTITSOURCE" length="18" />
<property name="businessInvestment" type="java.math.BigDecimal" column="BUSINESSINVESTMENT" length="11" />
<property name="personalInvestment" type="java.math.BigDecimal" column="PERSONALINVESTMENT" length="11" />
<property name="fees" type="java.math.BigDecimal" column="FEES" length="11" />
<property name="homeImprovements" type="java.math.BigDecimal" column="HOMEIMPROVEMENTS" length="11" />
<property name="consolidation" type="java.math.BigDecimal" column="CONSOLIDATION" length="11" />
<property name="otherFunds" type="java.math.BigDecimal" column="OTHERFUNDS" length="11" />
<property name="subLoanType" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="SUBLOANTYPE" length="40" />
<property name="repaymentTypeFlag" type="java.lang.String" column="REPAYMENTTYPEFLAG" length="1" />
<property name="interestOnlyAmount" type="java.math.BigDecimal" column="INTERESTONLYAMOUNT" length="11" />
<property name="capitalRepaymentAmount" type="java.math.BigDecimal" column="CAPITALREPAYAMOUNT" length="11" />
<property name="interestOnlyRepaymentMethod" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="INTONLYREPAYMETHOD" length="10" />
<property name="mortgageAmount" type="java.math.BigDecimal" column="MORTGAGEAMOUNT" length="11" />
<property name="additionalBorrowings" type="java.math.BigDecimal" column="ADDBORROW" length="11" />
<property name="migAmount" type="java.math.BigDecimal" column="MIGAMOUNT" length="11" />
<property name="mig" type="net.targetgroup.util.datatypes.CustomBooleanType" column="MIGFLAG" length="1" />
<property name="searchAuthorised" type="net.targetgroup.util.datatypes.CustomBooleanType" column="SEARCHAUTHORITY" length="1" />
<property name="customerContactable" type="net.targetgroup.util.datatypes.CustomBooleanType" column="CUSTOMERCONTACT" length="1" />
<property name="type" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="TYPE" length="10" />
<property name="currency" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="CURRENCY" length="10" />
<property name="notes" type="java.lang.String" column="NOTES" length="2147483647" />
<property name="dpaAuthorisation" type="net.targetgroup.util.datatypes.CustomBooleanType" column="DPAAUTHORISATION" length="1" />




<many-to-one name="source" class="net.targetgroup.broker.source.Source" not-null="true">
<column name="SOURCEID" />
</many-to-one>


<set name="consolidations" lazy="false" inverse="true" cascade="all">
<key>
<column name="APPID" />
</key>
<one-to-many class="net.targetgroup.broker.application.Consolidation" />
</set>

<set name="securitySet" lazy="false" inverse="true" cascade="all" where="type = 'SECURED'">
<key>
<column name="APPID" />
</key>
<one-to-many class="net.targetgroup.broker.application.Property" />
</set>

<one-to-one name="queueEntry" class="net.targetgroup.broker.queue.QueueEntry" outer-join="true" cascade="all" />
<one-to-one name="mortgageInsurance" class="net.targetgroup.broker.application.MortgageInsurance" constrained="false" outer-join="auto" cascade="all" />

<set name="applicants" lazy="false" cascade="all-delete-orphan" order-by="CUSTID">
<key>
<column name="APPID" />
</key>
<one-to-many class="net.targetgroup.broker.customer.Applicant" />
</set>
<set name="applicationFees" lazy="false" inverse="true" cascade="all-delete-orphan">
<key>
<column name="APPID" />
</key>
<one-to-many class="net.targetgroup.broker.application.ApplicationFee" />
</set>

<set name="disclosureAnswers" lazy="false" inverse="true" cascade="all-delete-orphan">
<key>
<column name="APPID" />
</key>
<one-to-many class="net.targetgroup.broker.application.Disclosure" />
</set>
<set name="proposals" lazy="false" inverse="true" cascade="all" order-by="propid">
<key>
<column name="APPID" />
</key>
<one-to-many class="net.targetgroup.broker.product.Proposal" />
</set>

</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>


<class name="net.targetgroup.broker.queue.QueueEntry" table="QUEUE">

<id name="identifier" type="int" column="APPID">
<generator class="foreign">
<param name="property">application</param>
</generator>

</id>

<timestamp name="timeStamp" column="DATETIME" />

<property name="name" type="net.targetgroup.util.datatypes.CustomStringTrimType" column="QUEUE" length="30" />
<property name="reviewDate" type="java.sql.Timestamp" column="REVIEWDATE" length="26" />
<property name="priority" type="java.lang.String" column="PRIORITY" length="10" />
<property name="userStamp" type="java.lang.String" column="USERSTAMP" length="10" />

<one-to-one name="application" class="net.targetgroup.broker.application.Application" constrained="true" outer-join="auto" cascade="none" />

</class>
</hibernate-mapping>

Full stack trace of any exception that occurs:
N/A
Name and version of the database you are using:

Db2 UDB for iSeries, version 5.1

The generated SQL (show_sql=true):

2005-02-01 16:05:50,886 [Servlet.Engine.Transports : 0] DEBUG net.sf.hibernate.hql.QueryTranslator - HQL: select new net.targetgroup.broker.queue.QueueCount(count(*),sum(application.advance)) from net.targetgroup.broker.application.Application as application where application.queueEntry is null
2005-02-01 16:05:50,886 [Servlet.Engine.Transports : 0] DEBUG net.sf.hibernate.hql.QueryTranslator - SQL: select count(*) as x0_0_, sum(applicatio0_.APPADVANCE) as x1_0_ from APPLICAT applicatio0_ where (applicatio0_.APPID is null )
2005-02-01 16:05:50,886 [Servlet.Engine.Transports : 0] DEBUG net.sf.hibernate.impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
2005-02-01 16:05:50,886 [Servlet.Engine.Transports : 0] DEBUG net.sf.hibernate.SQL - select count(*) as x0_0_, sum(applicatio0_.APPADVANCE) as x1_0_ from APPLICAT applicatio0_ where (applicatio0_.APPID is null )