Possible bug in 2.1.3 (group by)!

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

I'm using Hibernate 2.1.3.
By looking at the generated sql from the method code below we can see that it's wrong. The "planoprodu0_.equip_prod_fk" in the "GROUP BY" clause doesn't match the "equipprod1_.id as x0_0_" in the "SELECT" clause.
If I take out the "GROUP BY" clause it works,repeats the results which I don't want.

Has this been reported?
Please, need to know.

Thanks in advance.

------------------
Method code
------------------

Code:

( ... )
List res = session.find("select ppd.equipProd.id, ppd.equipProd.custoHorario from vo.PlanoProducaoDia ppd where ppd.op = ? group by ppd.equipProd.id, ppd.equipProd.custoHorario", op_id, Hibernate.LONG);
( ... )

------------------------
Generated sql code
------------------------

Code:

select
equipprod1_.id as x0_0_, 
equipprod1_.custo_horario as x1_0_ 
from plano_producao_dia planoprodu0_, equip_prod equipprod1_ 
where planoprodu0_.equip_prod_fk=equipprod1_.id and ((planoprodu0_.op_fk=? )) 
group by  planoprodu0_.equip_prod_fk , equipprod1_.custo_horario

It's giving me the following error:

Code:

WARN : SQL Error: 0, SQLState: 42803
ERROR: ERROR: column "equipprod1_.id" must appear in the GROUP BY clause or be used in an aggregate function

WARN : SQL Error: 0, SQLState: 42803
ERROR: ERROR: column "equipprod1_.id" must appear in the GROUP BY clause or be used in an aggregate function

ERROR: Could not execute query
org.postgresql.util.PSQLException: ERROR: column "equipprod1_.id" must appear in the GROUP BY clause or be used in an aggregate function
( ... )

------------------
Mapping file
------------------
PlanoProducaoDia

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="vo.PlanoProducaoDia" 
    table="plano_producao_dia"
>

    <id
        name="id"
        type="long"
        column="id"
    >
        <generator class="sequence">
           <param name="sequence">plano_producao_dia_seq</param>
        </generator>
    </id>

    <property
        name="ano"
        type="int"
        column="ano"
        not-null="true"
        length="4"
    />

    <!-- associations -->
    <!-- bi-directional many-to-one association to EquipProd -->
    <many-to-one
        name="equipProd"
        class="vo.EquipProd"
        not-null="true"
    >
        <column name="equip_prod_fk" />
    </many-to-one>
    <!-- bi-directional many-to-one association to Op -->
    <many-to-one
        name="op"
        class="vo.Op"
        not-null="true"
    >
        <column name="op_fk" />
    </many-to-one>

</class>
</hibernate-mapping>

christian · **Posted:** Wed Jun 02, 2004 7:42 am

Try this:

select ep.id, ep.custoHorario from vo.PlanoProducaoDia ppd join ppd.equipProd ep where ppd.op = ? group by ep.id, ep.custoHorario

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

christian wrote:

Try this:

select ep.id, ep.custoHorario from vo.PlanoProducaoDia ppd join ppd.equipProd ep where ppd.op = ? group by ep.id, ep.custoHorario

Thanks for the reply.
Your suggestion gives no result.

I'm using a workaround like the one below:

Code:

( ... )
List res_key = new ArrayList();
List res_final = new ArrayList();

res = session.find("select ppd.equipProd.id, ppd.equipProd.custoHorario from vo.PlanoProducaoDia ppd where ppd.op = ?", op_id, Hibernate.LONG);   
         
for(Iterator it = res.iterator(); it.hasNext();) {
   Object[] obj = (Object[])it.next();
   if(!res_chaves.contains(obj[0])) {
      res_key.add(obj[0]);
      res_final.add(obj);
   }
}
( ... )
return(res_final);
( ... )

With the workaround I'm getting the desired result:

Code:

EQUIP_ID: 4 CUSTO: 12.10
EQUIP_ID: 5 CUSTO: 56.11

Any idea?

This way I'm removing repeated "equipProd"'s.

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

Sorry...

There's an ArrayList res_chaves that is in fact res_key in the prevous post. Just as a note.

christian · **Posted:** Wed Jun 02, 2004 8:35 am

Ehm, yes. I didn't the that: Your first query doesn't make sense, because you have no aggregation in the select clause, therefore, you don't have to group by.

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

christian wrote:

Ehm, yes. I didn't the that: Your first query doesn't make sense, because you have no aggregation in the select clause, therefore, you don't have to group by.

With the following code (first post):

Code:

( ... )
List res = session.find("select ppd.equipProd.id, ppd.equipProd.custoHorario from vo.PlanoProducaoDia ppd where ppd.op = ? group by ppd.equipProd.id, ppd.equipProd.custoHorario", op_id, Hibernate.LONG);
( ... ) 

Hibernate does the aggregation internally. That's why I thought that Hibernate could pick up the "GROUP BY" clause and generate the sql accordingly.