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

[Krasimir Chobantonov (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=31806#action_31806 ] 

Krasimir Chobantonov commented on HHH-1015:
-------------------------------------------

Finally I had some time to inverstigate this issue.

It seems that there are 2 problems:

1) The collectionTable is not correctly set on the collection - e.g. instead of using the secondary table it is using the base table ( this is because persistentClass.getTable() on SINGLE_TABLE inheritance will return the base class). 
The issue can be exposed if the Hibernate schema validation is turned on and the join column is on the secondary table (using @SecondaryTable annotation) but not in the base table. The problem can be fixed in 

HbmBinder.bindCollectionSecondPass(Element node, Collection collection,
			java.util.Map persistentClasses, Mappings mappings, java.util.Map inheritedMetas)
			throws MappingException;

method.

The line that needs to be changed is on line 2402  (follow the code snippet)

     collection.setCollectionTable( persistentClass.getTable() ) 

The new code need to iterate over the "Element node" method parameter and get the key/column/@name (all columns) and then try to find in the persistentClass the property that is mapped to the same column(s) and property.getPersistentClass().equals(persistentClass).

Once the property is found then (assumming that the property is saved in "property" variable)

the line 
     collection.setCollectionTable( persistentClass.getTable() ) 

needs to be changed to 
     collection.setCollectionTable( property.getValue().getTable() ) 


With this change the schema validation will pass.

2) The second issue is with the query itself as pointed in the begining of the JIRA issue. The problem here is in the 

OneToManyJoinWalker.initStatementString(final OuterJoinLoadable elementPersister,
		final String alias,
		final int batchSize,
		final String subquery)
	throws MappingException;

Two things needs to be fixed here (at least)
     -  whereString generatation (line 122) should be prepared that the getKeyColumNames() may return columns that are mapped in the SecondaryTable instead of the base table.
The alias method parameter is actually pointing always to the base table.
Note the "elementPersister.fromTableFragment(alias)" in the fromClause generation
    -   oneToManyPersister.selectFragment on line 124 also needs to be prepared to handle this case 

The alias in those two cases need to be changed to use the alias of the subclass table (the secondary table) and not the base table.


I'm not sure if this issue can be observed in the order by clause because I did not have the time to test that case. 
The bottom line is that whenever we refer to the keyColumnNames then we need to not assume that those columns are always in the base table in case of SINGLE_TABLE inheritance because of the @SecondaryTable - the actual alias needs to be resolved and then to pass the new alias to the methods that will use the keyColumnNames.


Note: the exact lines of the code are obtained from the Hibernate version 3.3.1.GA source jar file.

> Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table
> -----------------------------------------------------------------------------------------
>
>                 Key: HHH-1015
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1015
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.1 beta 2
>         Environment: Hibernate versions 3.1 beta 3 and 3.0.5
>            Reporter: Steven Grimm
>            Priority: Minor
>
> 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" discriminator-value="-1">
>         <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" />
>                 <many-to-one name="payer" column="payer_id" class="Payer" />
>             </join>
>             <subclass name="EventPayerCreated" discriminator-value="1" />
>         </subclass>
>     </class>
>     <class name="Payer" table="payer">
>         <id name="payerId" column="payer_id" type="java.lang.Long"/>
>         <set name="eventPayers" inverse="true" cascade="save-update">
>             <key column="payer_id"/>
>             <one-to-many class="EventPayer"/>
>         </set>
>     </class>
> </hibernate-mapping>
> When I fetch the Payer.eventPayers collection, Hibernate generates this SQL:
> select eventpayer0_.payer_id as payer7_1_,
>     eventpayer0_.event_id as event1_1_,
>     eventpayer0_.event_id as event1_5_0_,
>     eventpayer0_1_.payer_id as payer2_6_0_,
>     eventpayer0_.event_type_id as event2_5_0_
> from event eventpayer0_
> inner join event_payer eventpayer0_1_
>     on eventpayer0_.event_id=eventpayer0_1_.event_id
> where eventpayer0_.payer_id=?
> The problem is that there is no event.payer_id column; payer_id is in the child table, not the parent. It appears that specifying a discriminated subclass in <one-to-many> is the same as specifying the superclass, or that Hibernate is ignoring the subclass's <join> element. As far as I can tell, this leaves no way to resolve bidirectional associations where one end of the association is in a discriminated subclass, which seems like a perfectly reasonable thing to want to do.
> I also tried changing <key column="payer_id"/> to <key property-ref="payer"/> in the Payer class's <set> element, but got similar behavior in the form of a "property not found" error: Hibernate is either looking in the superclass's properties rather than the subclass's or is ignoring the list of properties in the <join> element.

-- 
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