[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3314) "in elements" generated subquery table name not corrected translated on "insert into ... select" queries

[Bruno Mayerle Leite (JIRA)]

"in elements" generated subquery table name not corrected translated on "insert into ... select" queries
--------------------------------------------------------------------------------------------------------

                 Key: HHH-3314
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3314
             Project: Hibernate3
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.2.6, 3.2.5
         Environment: Windows XP SP2
latest Java 1.4
XML mapping
Derby 10.4.1.3
            Reporter: Bruno Mayerle Leite
            Priority: Blocker
         Attachments: bug_hibernate.zip

The following hql:
--------------------------------------------------------------------------------------------------------------------		
		sb.append(		" insert into ValorIndicador ( indicador," );
		sb.append(		" 							   tipoValor ) " );
		sb.append(		" select vi.indicador, " );
		sb.append(		" 		 vi.tipoValor ");
		sb.append(		" from ValorIndicador vi, PerfilIndicador pi " );
		sb.append(		" where 1 = 1 " );
		sb.append(		" 	and vi.indicador = pi.indicador " );
		sb.append(		" 	and vi.tipoValor in elements( pi.cenarios ) " );
-------------------------------------------------------------------------------------------------------------------

after translation generates the following WRONG sql:
-------------------------------------------------------------------------------------------------------------------
insert 
    into
        VALORINDICADOR
        ( indicador, tipovalor ) select
            valorindic0_.indicador as col_0_0_,
            valorindic0_.tipovalor as col_1_0_ 
        from
            VALORINDICADOR valorindic0_,
            PERFILINDICADOR perfilindi1_ 
        where
            1=1 
            and valorindic0_.indicador=perfilindi1_.indicador 
            and (
                valorindic0_.tipovalor in (
                    select
                        cenarios2_.cenario 
                    from
                        cenarios cenarios2_ 
                    where
                        PERFILINDICADOR.id=cenarios2_.perfilindicador_id
                )
            )
-------------------------------------------------------------------------------------------------------------------

while without the "insert into" statement :
-------------------------------------------------------------------------------------------------------------------
sb.append(		" select vi.indicador, " );
		sb.append(		" 		 vi.tipoValor ");
		sb.append(		" from ValorIndicador vi, PerfilIndicador pi " );
		sb.append(		" where 1 = 1 " );
		sb.append(		" 	and vi.indicador = pi.indicador " );
		sb.append(		" 	and vi.tipoValor in elements( pi.cenarios ) " );
-------------------------------------------------------------------------------------------------------------------

it translates to the CORRECT sql:
-------------------------------------------------------------------------------------------------------------------
select
        valorindic0_.indicador as col_0_0_,
        valorindic0_.tipovalor as col_1_0_ 
    from
        VALORINDICADOR valorindic0_,
        PERFILINDICADOR perfilindi1_ 
    where
        1=1 
        and valorindic0_.indicador=perfilindi1_.indicador 
        and (
            valorindic0_.tipovalor in (
                select
                    cenarios2_.cenario 
                from
                    cenarios cenarios2_ 
                where
                    perfilindi1_.id=cenarios2_.perfilindicador_id
            )
        )
-------------------------------------------------------------------------------------------------------------------

Note that in the first generated sql, the table instance name of  the "in elements" subquery is wrong. The entity name (PERFILINDICADOR) is used instead of the instance name (perfilindi1_). 

In the second generated sql this problem doesn't occurs.

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