[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2898) Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table

[Nicolas De Cubber (JIRA)]

Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table
-----------------------------------------------------------------------------------------

                 Key: HHH-2898
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2898
             Project: Hibernate3
          Issue Type: Bug
          Components: core, query-hql
         Environment: Hibernate Tools 3.2.0.beta8
            Reporter: Nicolas De Cubber


I have the following mappings describing a hierarchy of events and a class that the events refer to:

<hibernate-mapping package="com.xyz">
    <class name="Event" table="event">
        <meta attribute="scope-class" inherit="false">public abstract</meta>
        <id name="Id" type="long" column="event_id"/>
        <discriminator column="event_type_id" type="integer" />
        <subclass name="EventPayer" discriminator-value="-3">
            <join table="event_payer">
                <key column="event_id" />
            </join>
        </subclass>
    </class>

<class name="Payer" table="payer">
        <id name="payerId" column="payer_id" type="java.lang.Long"/>
        <list name="eventPayers" cascade="save-update">
            <key column="payer_id"/>
            <list-index column="LISTINDEX"/>
            <one-to-many class="EventPayer"/>
        </set>
    </class>
</hibernate-mapping> 

when, in the Hibernate Console, i run the query : 
select 1 from Payer payer inner join payer.eventPayers

It generates the following sql-code :
select
   1 as col_0_0_
from
   Payer payer0_
inner join
   Event event1_
   on payer0_.payerId=event1_.payer_id
inner join
   EventPayer event1_1_
   on event1_.event_id=event1_1_event_id


Which is incorrect because the table Event do not have the colum payerId.
The correct query should be like:
select
   1 as col_0_0_
from
   Payer payer0_
inner join
   EventPayer event1_
   on payer0_.payerId=event1_.payer_id
inner join
   Event event1_1_
   on event1_.event_id=event1_1_event_id


I found a 'workaround' but it has performance consequences.
The workAround is to remove the discriminator and to use <joined-subclass> instead of [<subclass> <join>]
So if i rewrite the Event mapping like this:
<class name="Event" table="event">
        <meta attribute="scope-class" inherit="false">public abstract</meta>
        <id name="Id" type="long" column="event_id"/>
        <joined-subclass name="EventPayer"  table="event_payer">
                <key column="event_id" />
        </joined-subclass>
    </class>
the query is generated correctly :
select
   1 as col_0_0_
from
   Payer payer0_
inner join
   EventPayer event1_
   on payer0_.payerId=event1_.payer_id
inner join
   Event event1_1_
   on event1_.event_id=event1_1_event_id

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira