HQL vs SQL - returning different results

perja · **Joined:** Tue Nov 18, 2003 11:56 am **Posts:** 16

I've problems with understanding what goes on here. When I set the showsql=true in the configuration file, I get the following SQL from Hibernate:

Code:

select substan0_.substans_id as substans1_, substan0_.navn as navn, substan0_.beskrivelse as beskrive3_, substan0_.aktivsubstans as aktivsub4_, substan0_.analyse_id as analyse_id 
from substans substan0_ inner join preparatsubstans preparats1_ on substan0_.substans_id=preparats1_.substans_id inner join preparat preparat2_ on preparats1_.preparat_id=preparat2_.preparat_id 
where (preparat2_.navn like 'Paracet Weifa' ) group by  substan0_.substans_id

Hibernate returns 71 rows from the database (MySQL 4.1).

But if I copy this sql and runs it directly with the MySQL client it only returns 1 row (which is what I expect).

Isn't the sql printed by Hibernate actaully sent to the database? How can I make Hibernate return 1 result instead of 71?

Info:

Hibernate version 2.1.3

HQL:

Code:

select s from Substan as s inner join s.preparats as p where p.navn like :substansorpreparat group by s.id, s.navn, s.beskrivelse, s.aktivsubstans

Mapping:

Code:

<class 
    name="no.nr.lmportal.model.Preparat" 
    table="preparat"
>

    <id
        name="preparatId"
        type="int"
        column="preparat_id"
    >
        <generator class="native" />
    </id>

    <property
        name="navn"
        type="java.lang.String"
        column="navn"
        length="255"
    />

    <!-- associations -->
    <!-- bi-directional many-to-many association to Substan -->
    <set
        name="substans"
        lazy="true"
        table="preparatsubstans"
    >
        <key>
            <column name="preparat_id" />
        </key>
        <many-to-many
            class="no.nr.lmportal.model.Substan"
        >
            <column name="substans_id" />
        </many-to-many>
    </set>

</class>

Code:

<class 
    name="no.nr.lmportal.model.Substan" 
    table="substans"
>

    <id
        name="substansId"
        type="int"
        column="substans_id"
    >
        <generator class="native" />
    </id>

    <property
        name="navn"
        type="java.lang.String"
        column="navn"
        length="255"
    />
    <property
        name="beskrivelse"
        type="java.lang.String"
        column="beskrivelse"
        not-null="true"
        length="255"
    />
    <property
        name="aktivsubstans"
        type="byte"
        column="aktivsubstans"
        length="4"
    />

    <!-- associations -->
    <!-- bi-directional many-to-one association to Analyse -->
    <many-to-one
        name="analyse"
        class="no.nr.lmportal.model.Analyse"
        not-null="true"
    >
        <column name="analyse_id" />
    </many-to-one>
    <!-- bi-directional many-to-many association to Preparat -->
    <set
        name="preparats"
        lazy="true"
        table="preparatsubstans"
    >
        <key>
            <column name="substans_id" />
        </key>
        <many-to-many
            class="no.nr.lmportal.model.Preparat"
        >
            <column name="preparat_id" />
        </many-to-many>
    </set>
    <!-- bi-directional many-to-many association to Bivirkning -->
    <set
        name="bivirknings"
        lazy="true"
        table="substansbivirkning"
    >
        <key>
            <column name="substans_id" />
        </key>
        <many-to-many
            class="no.nr.lmportal.model.Bivirkning"
        >
            <column name="bivirkning_id" />
        </many-to-many>
    </set>

</class>

david · **Posted:** Thu May 13, 2004 9:05 pm

You are not using the same argument as being passed the the query, eg, to the like, as when running it manually. It is the only way this could be happening.

perja · **Joined:** Tue Nov 18, 2003 11:56 am **Posts:** 16

Thanks, I solved it! I was blaming Hibernate, but Hibernate works great. It was the jdbc driver.

It turned out that for some reason we're using MySQL Connector/J 3.1 that is a alpha release. Switching to 3.0 (production release) solved the problem.

BTW: If you use proxool as the connection cache and you set the proxool.trace=true flag in hibernate.cfg.xml, you can see the actual SQL with all parameters bound...