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

[David (JIRA)]

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

David commented on HHH-1657:
----------------------------

I have a similar but different bug to report. (If this is different someone place repost.)

The problem is the update SQL comment doesn't use aliases so my inner query ends up referencing the wrong table.

The following EJB QL query...
UPDATE Provider p SET p.avgRating = (SELECT AVG(r.score) FROM Rating r WHERE r.providerFor = p) WHERE p.id = 44;
becomes...
update Provider set avgRating=(select AVG(rating1_.score) from Rating rating1_ where rating1_.providerFor_id=id) where id=44;
This query isn't correct. The 'id' at the end of the where of the inner select does not refer to the id of the Provider as it should, instead the id ends up referencing Rating which obviously isn't what i want.

This EJB QL query really should be  generating the following SQL query which uses aliases:
update Provider p set p.avgRating=(select AVG(rating1_.score) from Rating rating1_ where rating1_.providerFor_id=p.id) where p.id=44;

(I don't know a way around this so for the time being I am resorting to using a native query.)


> 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
>            Priority: Critical
>
> 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