[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4956) Native Query returns wrong results

[Maillefer Jean-David (JIRA)]

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

Maillefer Jean-David commented on HHH-4956:
-------------------------------------------

The following example is maybe clearer (it uses postgresql's system table to avoid generating a db structure):

A simple select using native query which should return the 10 first index names and the related table names:

        EntityManager em = ...;
        String query = "select c.relname, c2.relname"
                + " from pg_class c"
                + " join pg_index i on c.oid = i.indexrelid"
                + " join pg_class c2 on i.indrelid = c2.oid"
                + " where c.relkind = 'i'"
                + " order by c.relname"
                + " limit 10";
        @SuppressWarnings("unchecked")
        List<Object[]> indexes = em.createNativeQuery(query).getResultList();
        for (Object[] objects : indexes) {
            String indexName = (String) objects[0];
            String relatedTableName = (String) objects[1];
            System.out.println(indexName + "\t" + relatedTableName);
        }
        System.out.println();

The output is twice the index name on each row. The related table name is overridden by the index name. I think it's due to the use of the same column name. Is hibernate using internally the column names instead of their indexes to fill the resulting Object[] ?

Adding an alias to the query is a workaround.


> Native Query returns wrong results
> ----------------------------------
>
>                 Key: HHH-4956
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4956
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.3.2
>         Environment: hibernate 3.3.2.GA , linux, oracle 10g, weblogic 10
>            Reporter: Akashdeep Saddi
>
> Issue: In-case running native queries vai hibernate we have two technical ID's in the select clause from join of two or more tables the value of all the id's is set to one value. 
>  
> Example
> A.id = 1
> B.id =2 
> select A.Id as A_ID, B.Id as B_ID from A, B where A.B_id = B.id will return 1,1 in the result instead of 1,2
>  
> Resolution: Use Alias in-case more than one technical keys are part of select clause. Above query works fine when changed as below
>  
> select A.Id , B.Id from A, B where A.B_id = B.id will return 1,2

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