[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6812) HQL query for map value leads to incorrect joins in SQL

[Neukomm (JIRA)]

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

Neukomm commented on HHH-6812:
------------------------------

This issue is still present in hibernate-core 4.0.0.Final
(I wasn't able to figure out how to change the 'Affects Version/s' field in this track...)

> HQL query for map value leads to incorrect joins in SQL
> -------------------------------------------------------
>
>                 Key: HHH-6812
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6812
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-hql, query-sql
>    Affects Versions: 3.6.7
>         Environment: Oracle Java 1.6.0_29, Hibernate 3.6.7-Final, Hsqldb 2.2.5
>            Reporter: Neukomm
>              Labels: HQL, query
>         Attachments: map_query_test.zip
>
>
> Whenever an expression like 'where p.map['key'] = :val' is used in HQL, the join between the entity table and the map value table is not done correctly. This leads to missing results.
> The attached test case uses HQL like this:
> from MapQueryEntity p where p.name = 'B' or p.map['key'] = 'value'
> the corresponding sql looks like this:
>     select
>         mapqueryen0_.id as id0_,
>         mapqueryen0_.name as name0_ 
>     from
>         MapQueryEntity mapqueryen0_ cross 
>     join
>         MapQueryEntity_map map1_ 
>     where
>         mapqueryen0_.id=map1_.ident 
>         and map1_.mkey = 'key' 
>         and (
>             mapqueryen0_.name='B'
>             or map1_.mvalue='value'
>         )
> This leads to all entities that don't have a map entry for key 'key' not to be in the result,
> even if they have name 'B'.
> SQL that would return the correct rows (at least on hsqldb) could e.g. look like this:
>     select
>         mapqueryen0_.id as id0_,
>         mapqueryen0_.name as name0_ 
>     from
>         MapQueryEntity mapqueryen0_
>     left outer join
>         MapQueryEntity_map map1_ 
> 	on mapqueryen0_.id=map1_.ident 
>     where
>       mapqueryen0_.name='B'
>       or (map1_.mkey = 'key' and map1_.mvalue='value')
> Looks like org.hibernate.hql.ast.tree.IndexNode (which seems to be responsible for the hql/sql translation if '[]' is used) doesn't work correctly for this case.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira