[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3045) Duplicated column aliases in scalar query

[Tobias Troesch (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37319#action_37319 ] 

Tobias Troesch commented on HHH-3045:
-------------------------------------

It is easy to reproduce this problem. In Eclipse you can use the HQL-Editor. You need a join fetch e.g. over an one-to-one Mapping and a count over a set. The SQL which is produce have mutiple column aliases, this breaks the paginated query. Paging with Hibernate-Functions over such a Query is not possible with Hibernate 3.3.2 GA and 3.5.2. Okay Paging over an one-to-many mapping is done in memory but in our case it is a one-to-one mapping. 

> Duplicated column aliases in scalar query
> -----------------------------------------
>
>                 Key: HHH-3045
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3045
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.5
>         Environment: Hibernate 3.2.4SP1, 3.2.5GA
> Oracle 10g
>            Reporter: Anderson Souza
>            Priority: Critical
>         Attachments: ddl-tables.sql, dominio.zip
>
>
> The HQL bellow works fine, but when I add a call to a funcion in the select clause the SQL generated has duplicated aliases and it generate error on query execution, because the query is paginated.
> {code}
> select	a
> from 	Area a 
> 	inner join fetch a.horarioPadrao
> 	left outer join fetch a.horarioTemporario as hrtemp
> {code}
> The SQL generated for this query is:
> {code}
> select * from (
>   select  area0_.cod_local as cod1_15_0_, 
>           horarioare1_.cod_area as cod1_6_1_, 
>           horarioare2_.cod_area as cod1_7_2_, 
>           area0_.des_local as des2_15_0_, 
>           area0_.sig_local as sig3_15_0_, 
>           area0_.flg_extinto as flg4_15_0_, 
>           horarioare1_.hr_hora_ini as hr2_6_1_, 
>           horarioare1_.hr_hora_fim as hr3_6_1_, 
>           horarioare1_.flg_fim_semana as flg4_6_1_, 
>           horarioare2_.hr_hora_ini as hr2_7_2_, 
>           horarioare2_.hr_hora_fim as hr3_7_2_, 
>           horarioare2_.flg_fim_semana as flg4_7_2_, 
>           horarioare2_.dat_ini_vigencia as dat5_7_2_, 
>           horarioare2_.dat_fim_vigencia as dat6_7_2_ 
>   from    esq_fp.tb_local area0_ 
>           inner join tb_horario_local_padrao horarioare1_ on area0_.cod_local=horarioare1_.cod_area 
>           left outer join tb_horario_local_temporario horarioare2_ on area0_.cod_local=horarioare2_.cod_area 
>   where   1=1 
>   order by area0_.sig_local ) 
> where rownum <= ?
> {code}
> The scalar HQL query that generate duplicated column names is:
> {code}
> select	a, count(*)
> from 	Area a 
> 	inner join fetch a.horarioPadrao
> 	left outer join fetch a.horarioTemporario as hrtemp
> {code}
> The SQL generated is:
> {code}
> select * from (
>   select  area0_.cod_local as col_0_0_, 
>           count(*) as col_1_0_, 
>           horarioare1_.cod_area as cod1_6_1_, 
>           horarioare2_.cod_area as cod1_7_2_, 
>           area0_.cod_local as cod1_15_0_, 
>           horarioare1_.cod_area as cod1_6_1_, 
>           horarioare2_.cod_area as cod1_7_2_, 
>           area0_.des_local as des2_15_0_, 
>           area0_.sig_local as sig3_15_0_, 
>           area0_.flg_extinto as flg4_15_0_, 
>           horarioare1_.hr_hora_ini as hr2_6_1_, 
>           horarioare1_.hr_hora_fim as hr3_6_1_, 
>           horarioare1_.flg_fim_semana as flg4_6_1_, 
>           horarioare2_.hr_hora_ini as hr2_7_2_, 
>           horarioare2_.hr_hora_fim as hr3_7_2_, 
>           horarioare2_.flg_fim_semana as flg4_7_2_, 
>           horarioare2_.dat_ini_vigencia as dat5_7_2_, 
>           horarioare2_.dat_fim_vigencia as dat6_7_2_ 
>   from    esq_fp.tb_local area0_ 
>           inner join tb_horario_local_padrao horarioare1_ on area0_.cod_local=horarioare1_.cod_area 
>           left outer join tb_horario_local_temporario horarioare2_ on area0_.cod_local=horarioare2_.cod_area 
>   where 1=1 
>   order by area0_.sig_local )
> where rownum <= ?
> {code}
> As you can see the aliases cod1_6_1_ and  cod1_7_2_ are repeated and this repetition breaks the paginated query, beacause the main query appears in the from clause.
> The HBM's and classes are attached.

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