HQL: strange select result

johnskiller · **Joined:** Fri Oct 14, 2005 8:56 am **Posts:** 6

Code:

from Test t where t.kname = 'SIZE'

returns 2 test objects which is correct but

Code:

from Test t where t.kname = 'SIZE' and t.Conecos.imp = 4

returns 3 objects with duplicated.

any idea?

Hibernate version:
3.0.5
Mapping documents:

Code:

<class name="data.Test" table="test" schema="public">
        <id name="id" type="integer">
            <column name="id" />
            <generator class="assigned" />
        </id>
        <property name="kname" type="string">
            <column name="kname" length="20" />
        </property>
        <property name="name" type="string">
            <column name="name" length="60" />
        </property>
        <set name="Conecos" inverse="true">
            <key>
                <column name="tid" not-null="true" />
            </key>
         
            <one-to-many class="data.Coneco" />
        </set>

    </class>

    <class name="data.Coneco" table="coneco" schema="public">
        <id name="id" type="integer">
            <column name="id" />
            <generator class="assigned" />
        </id>
        <property name="imp" type="short">
            <column name="imp" />
      </property>
        
         <many-to-one name="test" class="data.Test" update="false" insert="false">
            <column name="tid" not-null="true" />
         </many-to-one>

    </class>

Data:
Test table:
id, kname, name
1,GASO,GASO
2,GASO,GASO
3,SIZE,SIZE
4,SIZE,FULL SIZE
7,WEIGHT, WEIGHT

PK is id

Coneco Table:
id, tid, imp
2,3,4
4,4,4
1,3,4
id is the PK
tid is the FK ref to Test.id

Name and version of the database you are using:
postgreSQL 8

Code:

kochcp · **Posted:** Fri Oct 14, 2005 10:15 am

the log file and generated sql would definitely show whats happening

johnskiller · **Joined:** Fri Oct 14, 2005 8:56 am **Posts:** 6

the sql of first query is :

Code:

select test0_.id as id, test0_.kname as kname0_, test0_.name as name0_ from public.test test0_ where test0_.kname='SIZE'

i run it in sql client and get 2 rows

the second query get

Code:

select test0_.id as id, test0_.kname as kname0_, test0_.name as name0_ from public.test test0_, public.coneco conecos1_ where test0_.id=conecos1_.tid and test0_.kname='SIZE' and conecos1_.imp=4

which output 3 rows

it seems that hibernate put too many columns in select, wrong sql generation or wrong mapping file?

johnskiller · **Joined:** Fri Oct 14, 2005 8:56 am **Posts:** 6

ok, the generated sql should return 3 rows.

the relation between test and coneco is 1:n, so for 1 test there are several coneco. i want to select some test but filt out coneco at the same time. e.g. test A has 3 coneco, but only 2 of them have imp = 4 so if i want get test A with that 2 coneco, what can i do? can i do it using HQL or must i use filter function of session?

steve · **Posted:** Fri Oct 14, 2005 12:33 pm

The easiest is to explicitly list the returns:

select t from Test t where t.kname = 'SIZE' and t.Conecos.imp = 4

I'll leave checking the documentation as to why as a user excercise ;)

dma_k · **Joined:** Sat Sep 17, 2005 6:50 am **Posts:** 23

Hello, johnskiller!

There is nothing strange in the 2ng query you've supplied: the first query shows only "data.Test" objects, the second -- "data.Test" and "data.Coneco" objects. If the query is confusing, try to put <set ... outer-join="false"> -- the final result will be the same anyway, as hibernate correctly processes the collections for joins.

johnskiller · **Joined:** Fri Oct 14, 2005 8:56 am **Posts:** 6

hi dma_k

it is not because of outer-join, i've add the outer-join="false" but got the same result and from genered sql i see no outer join.

hi steve

thanks for response

you HQL is the same as my 2nd query which is

from Test t where t.kname = 'SIZE' and t.Conecos.imp = 4

except the "select Test t"

i test your HQL but get the same result, here is the output:

Code:

Hibernate: select test0_.id as id, test0_.kname as kname0_, test0_.name as name0_ from public.test test0_, public.coneco conecos1_ where test0_.id=conecos1_.tid and test0_.kname='SIZE' and conecos1_.imp=4
Hibernate: select conecos0_.tid as tid1_, conecos0_.id as id1_, conecos0_.id as id0_, conecos0_.imp as imp1_0_, conecos0_.name as name1_0_, conecos0_.tid as tid1_0_ from public.coneco conecos0_ where conecos0_.tid=?
4 SIZE 
    4 Beta [4]
Hibernate: select conecos0_.tid as tid1_, conecos0_.id as id1_, conecos0_.id as id0_, conecos0_.imp as imp1_0_, conecos0_.name as name1_0_, conecos0_.tid as tid1_0_ from public.coneco conecos0_ where conecos0_.tid=?
3 SIZE 
    2 Alfa [4]    1 charli [3]    3 Delta [4]
3 SIZE 
    2 Alfa [4]    1 charli [3]    3 Delta [4]

i and a "name" column to the coneco table.

i noticed the generated sql which retrieve coneco(lazy loading) did not use imp = ? at all.

i use session filter to get what i need as following:
Test.hbm.xml:

Code:

       <set name="Conecos" inverse="true">
            <key>
                <column name="tid" not-null="true" />
            </key>
         
            <one-to-many class="data.Coneco" />
       <filter name="imp" condition=":imp = imp"/>
        </set>

   <filter-def name="imp">
      <filter-param name="imp" type="int"/>
   </filter-def>

and in java source:

Code:

   session.enableFilter("imp").setParameter("imp", new Integer(4));
   List list =session.createQuery("select t from Test t where t.kname = 'SIZE'").list();
      

   for (Iterator iter = list.iterator(); iter.hasNext();) {
         Test t = (Test) iter.next();
         System.out.println(t);
      }

with the filter on , the output of
select t from Test t where t.kname = 'SIZE'
is

Code:

Hibernate: select test0_.id as id, test0_.kname as kname0_, test0_.name as name0_ from public.test test0_ where test0_.kname='SIZE'
Hibernate: select conecos0_.tid as tid1_, conecos0_.id as id1_, conecos0_.id as id0_, conecos0_.imp as imp1_0_, conecos0_.name as name1_0_, conecos0_.tid as tid1_0_ from public.coneco conecos0_ where  ? = conecos0_.imp and conecos0_.tid=?
3 SIZE 
    2 Alfa [4]    3 Delta [4]
Hibernate: select conecos0_.tid as tid1_, conecos0_.id as id1_, conecos0_.id as id0_, conecos0_.imp as imp1_0_, conecos0_.name as name1_0_, conecos0_.tid as tid1_0_ from public.coneco conecos0_ where  ? = conecos0_.imp and conecos0_.tid=?
4 SIZE 
    4 Beta [4]

hibernate use ? = conecos0_.imp when retrieve Coneco so it got the right result.

since my team members come from strong sql background so they perfer to do things in HQL than java source.

so is there any way to do it in HQL?

thanks in advance

Jason Zhicheng Li · **Joined:** Tue Aug 16, 2005 11:06 pm **Posts:** 46

Can you try this:

from Test t
join t.Conecos as conecos
where t.kname = 'SIZE'
and conecos.imp = 4

johnskiller · **Joined:** Fri Oct 14, 2005 8:56 am **Posts:** 6

hi, Li

i tried your HQL in HQL Scachpad, it still return 3 rows and each row contain to Object: the Test and the Coneco, so when i put your HQL in java source it cause a ClassCastException

from my unerstand of HQL, your query return a list with each element as an object array, the array has to elements: Test and Coneco, am i right?