[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-3389) HQL generates wrong sql when using subquery on the same table

[Roman Makowski (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3389?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Roman Makowski updated HHH-3389:
--------------------------------

    Attachment: CodeReggTest.zip

Add hbm files to your mappings, and in the test get your hibernate session. Current placeholder returns null. If you want you can change package name in java and hbm files. I was running it on SQL Server 2005 and got following error: 

10:24:24,884 WARN  [JDBCExceptionReporter] SQL Error: -28, SQLState: S0022
10:24:24,884 ERROR [JDBCExceptionReporter] Column not found: REG_CODE.C_DIM_ID in statement [insert into REG_G ( C_SEQ, C_VALUE ) select (select cast(count(*)-1 as integer) from REG_CODE codee1_ where codee1_.C_DIM_ID=REG_CODE.C_DIM_ID and codee1_.C_ID<=REG_CODE.C_ID) as col_0_0_, codee0_.C_CODE as col_1_0_ from REG_CODE codee0_ where codee0_.C_DIM_ID=?]

> HQL generates wrong sql when using subquery on the same table
> -------------------------------------------------------------
>
>                 Key: HHH-3389
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3389
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.2.6
>            Reporter: Roman Makowski
>         Attachments: CodeReggTest.zip
>
>
> I've got following insert in my code:
> String queryString = "INSERT INTO Regg (index, code) "
>                 + "SELECT ( SELECT cast(count(*) - 1 as int) "
>                 + "      FROM Code c2 "
>                 + "      WHERE c2.parent.id = c1.parent.id AND c2.id <= c1.id), c1.code "
>                 + "FROM Code c1 "
>                 + "WHERE c1.parent.id = :DIM_ID";
> Query q = session.createQuery(queryString);
> q.setParameter(DIM_ID, dimId, Hibernate.LONG);
> q.executeUpdate();
> Unfortunately HQLQueryPlan returns following SQL query to the database:
> insert into REG_G ( C_SEQ, C_VALUE )
> select (
> select cast(count(*)-1 as int)
> from REG_CODE code1_
> where code1_.C_DIM_ID=REG_CODE.C_DIM_ID and code1_.C_ID<=REG_CODE.C_ID) as col_0_0_,
> code0_.C_CODE as col_1_0_
> from REG_CODE code0_
> where code0_.C_DIM_ID=?
> where REG_CODE.C_DIM_ID and REG_CODE.C_ID are not resolved to code0_.C_DIM_ID and code0_.C_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