Hibernate version: 1.2.1 GA

Description:
Given the example documents, if I run the following HQL:


The resulting SQL is:
select account1_.ACCOUNTID as x0_0_, account1_.ACCOUNT as x1_0_ from OPPORTUNITY opportunit0_, ACCOUNT account1_ where opportunit0_.ACCOUNTID=account1_.ACCOUNTID and ((opportunit0_.OPPORTUNITYID='OGHEA0002815' )) group by opportunit0_.ACCOUNTID , account1_.ACCOUNT

The problem is that as the group by is processed, it hits the opp.Account.id first, and short circuits the special 'id' property to the foreign key value in the 'root' entity. This resolves to OPPORTUNITY.ACCOUNTID, as it is not aware of any joins yet, therefore making an attempt to 'optimize' the query.
Next, when the select columns are processed (which are last), it is now aware of the join and generates a reference to the ACCOUNT.ACCOUNTID, causing the group by issue.
If I reorder the group by, as follows:


The resultant SQL is correct, as the opp.Account.AccountName forces a join and the opp.Account.id uses ACCOUNT.ACCOUNTID instead.


Mapping Documents:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Entities" namespace="Entities">
<class name="Account" table="ACCOUNT" dynamic-insert="true" dynamic-update="true" batch-size="10" lazy="true">
<!-- Primary Key -->
<id name="Id" column="ACCOUNTID" type="AnsiString" access="field.pascalcase-underscore">
<generator class="IdGenerator.Base36Generator, SomeAssembly">
<param name="batch_size">10</param>
</generator>
</id>
<!-- Properties -->
<property name="AccountName" column="ACCOUNT" type="AnsiString" access="field.pascalcase-underscore" length="128" />
<!-- Relationships -->
<set name="Opportunities" cascade="all" lazy="true" batch-size="10" inverse="true" access="Sage.Platform.Collections.GenericAccessor, Sage.Platform">
<key column="ACCOUNTID" />
<one-to-many class="Entities.Opportunity, Entities" />
</set>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Entities" namespace="Entities">
<class name="Opportunity" table="OPPORTUNITY" dynamic-insert="true" dynamic-update="true" batch-size="10" lazy="true">
<!-- Primary Key -->
<id name="Id" column="OPPORTUNITYID" type="AnsiString" access="field.pascalcase-underscore">
<generator class="IdGenerator.Base36Generator, SomeAssembly">
<param name="batch_size">10</param>
</generator>
</id>
<!-- Properties -->
<property name="ActualAmount" column="ACTUALAMOUNT" type="Decimal" access="field.pascalcase-underscore" />
<property name="ActualClose" column="ACTUALCLOSE" type="DateTime" access="field.pascalcase-underscore" />
<property name="Description" column="DESCRIPTION" type="AnsiString" access="field.pascalcase-underscore" length="64" />
<!-- Relationships -->
<many-to-one name="Account" class="Entities.Account, Entities" access="field.pascalcase-underscore" column="ACCOUNTID" not-null="true" />
</class>
</hibernate-mapping>
[/code]

I'm not sure, but I think your HQL query is wrong. "from Opportunity opp, Account a" would create a cartesian product of the two classes. The query only works since the join criteria is added because of the association and the group statement. But I assume, that the parser gets confused about that. Try



and I assume, account name is unique, then this should also work:

Thanks mate - I'll give that a try. I should have tried adding the join explicitly to the from, but I was just assuming the parser would figure it all out. I did step through the code and noticed it does everything in 1 pass, therefore it can only make assumptions of what joins it knows about at the time.

I'll let you know how it goes.

I can confirm that adding the join to opp.Account solves the issue, by 'hinting' to the HQL parser that the child table should be joined.

Cheers,

Stu