[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1657) hql update generate wrong sql with joined subclass hierarcy

[John Baker (JIRA)]

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

John Baker commented on HHH-1657:
---------------------------------

Can I add some more thought to this discussion?

I have a simple class hierarchy:

class Event
class ISSO extends Event
class Login extends Event
class BasicLogin extends Login
class IWALogin extends Login

And it's mapped using the <joined-subclass> syntax.

The database's cascade functionality will delete rows in the relevant tables when a row is deleted from Event.  If I issue the HQL:

delete from Event where time < 10

then instead of simply executing this, Hibernate issues statements for each type of subclass.  While probably not incorrect, it's not very efficient given it only needs to issue one delete statement.  This is what I see in the logs:

Hibernate: insert into HT_BasicLogin select basiclogin0_.eventId as eventId from BasicLogin basiclogin0_ inner join Login basiclogin0_1_ on basiclogin0_.eventId=basiclogin0_1_.eventId inner join Event basiclogin0_2_ on basiclogin0_.eventId=basiclogin0_2_.eventId where eventId in (select basiclogin1_.eventId from BasicLogin basiclogin1_ inner join Login basiclogin1_1_ on basiclogin1_.eventId=basiclogin1_1_.eventId inner join Event basiclogin1_2_ on basiclogin1_.eventId=basiclogin1_2_.eventId where basiclogin1_2_.time<?)
Hibernate: delete from BasicLogin where (eventId) IN (select eventId from HT_BasicLogin)
Hibernate: delete from Login where (eventId) IN (select eventId from HT_BasicLogin)
Hibernate: delete from Event where (eventId) IN (select eventId from HT_BasicLogin)
Hibernate: delete from HT_BasicLogin
Hibernate: insert into HT_IWALogin select iwalogin0_.eventId as eventId from IWALogin iwalogin0_ inner join Login iwalogin0_1_ on iwalogin0_.eventId=iwalogin0_1_.eventId inner join Event iwalogin0_2_ on iwalogin0_.eventId=iwalogin0_2_.eventId where eventId in (select iwalogin1_.eventId from IWALogin iwalogin1_ inner join Login iwalogin1_1_ on iwalogin1_.eventId=iwalogin1_1_.eventId inner join Event iwalogin1_2_ on iwalogin1_.eventId=iwalogin1_2_.eventId where iwalogin1_2_.time<?)
Hibernate: delete from IWALogin where (eventId) IN (select eventId from HT_IWALogin)
Hibernate: delete from Login where (eventId) IN (select eventId from HT_IWALogin)
Hibernate: delete from Event where (eventId) IN (select eventId from HT_IWALogin)
Hibernate: delete from HT_IWALogin
Hibernate: insert into HT_ISSO select isso0_.eventId as eventId from ISSO isso0_ inner join Event isso0_1_ on isso0_.eventId=isso0_1_.eventId where eventId in (select isso1_.eventId from ISSO isso1_ inner join Event isso1_1_ on isso1_.eventId=isso1_1_.eventId where isso1_1_.time<?)
11-07 14:44:46,295  WARN JDBCExceptionReporter.logExceptions:77 - SQL Error: 918, SQLState: 42000
11-07 14:44:46,295 ERROR JDBCExceptionReporter.logExceptions:78 - ORA-00918: column ambiguously defined

The last insert statement fails because eventId hasn't been qualified it's immediately after the first where statement).  While this bug does need fixing, perhaps someone on the Hibernate team can optimise the DELETE HQL functionality?







> hql update generate wrong sql with joined subclass hierarcy
> -----------------------------------------------------------
>
>                 Key: HHH-1657
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1657
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-hql
>         Environment: Hibernate 3.2.0cr1, Hibernate 3.1.3
>            Reporter: Alexey Romanchuk
>            Assignee: Gail Badner
>            Priority: Critical
>             Fix For: 3.2.x, 3.3.x
>
>
> Let suppose that we have two joined subclass entities: Parent (id PK) and Child (id PK) that mapped with joined subclass method.
> When I try to update Child by id with hql:
> update Child c set c.field = 'value' where c.id = 1234
> hibernate generates joined tables like 
> insert into HT_parent select child0_.id as id from child child0_ inner join parent child0_1_ on child0_.id=child0_1_.id  wher id in = 1234
> look at last condition. hibernate use id WITH OUT tables alias that cause sql exception: column reference "id" is ambiguous

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