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

[Nicolas De Cubber (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2898?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28488 ] 

Nicolas De Cubber commented on HHH-2898:
----------------------------------------

Affects version 3.2.0.ga

> 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
>   Original Estimate: 4 hours
>  Remaining Estimate: 4 hours
>
> 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