[Hibernate-JIRA] Created: (HHH-2898) Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table
7:18 a.m.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:55 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2898) Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2898?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:20 a.m.
New subject: [Hibernate-JIRA] Resolved: (HHH-2898) Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2898?page=c...
]
Diego Pires Plentz resolved HHH-2898.
-------------------------------------
Assignee: Diego Pires Plentz
Resolution: Duplicate
If you found the same issue, why you had opened another one instead of just adding a
comment on the existing one?
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
Assignee: Diego Pires Plentz
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:24 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2898) Incorrect SQL generated when one-to-many foreign key is in a discriminated subclass table
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2898?page=c...
]
Nicolas De Cubber commented on HHH-2898:
----------------------------------------
As i explained on my first comment, I open this one because it is a bug, whereas the other
issue i found is marked as a new feature and is opened since octobre 2005.
And it is clearly a bug, because, if you read the documentation of hibernate, On the part
9.2 Limitations, you see that the 'Polymorphic one-to-many' must support the
'table per subclass' inheritance strategy.
So, i would like to know if this bug was fixed in an other version, or when you thinks to
fix it.
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
Assignee: Diego Pires Plentz
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6294
days inactive
6296
days old
hibernate-issues@lists.jboss.org
3 comments
2 participants
participants (2)
-
Diego Pires Plentz (JIRA) -
Nicolas De Cubber (JIRA)