[Hibernate-issues] [Hibernate-JIRA] Created: (HB-556) Wrong generated SQL statements for group by

[<leg...@at...>]

Message:

  A new issue has been created in JIRA.

---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-556


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-556
    Summary: Wrong generated SQL statements for group by
       Type: Bug

     Status: Unassigned
   Priority: Major

    Project: Hibernate2
   Versions:
             2.1

   Assignee: 
   Reporter: Joachim Schmid

    Created: Tue, 16 Dec 2003 4:31 AM
    Updated: Tue, 16 Dec 2003 4:31 AM
Environment: Microsoft Windows 2000. IBM DB2 V7.2

Description:
The HQL following statement 
----------------------------------------------
select 
  tl.teilProjekt, 
  sum(tl.stunden) 
from 
  de.bea.plato.domain.StundenEntry tl 
where 
  tl.day >= :dayFrom and tl.day <= :dayTo and tl.mitarbeiter=:mitarbeiter 
group by tl.teilProjekt
-----------------------------------------------

is translated to

----------------------------------------------
select 
  teilprojek1_.OID as OID, 
  teilprojek1_.ProjektOID as ProjektOID, 
  teilprojek1_.bezeichnung as bezeichn3_, 
  teilprojek1_.teilProjektArt as teilProj4_, 
  teilprojek1_.teilProjektKategorie as teilProj5_, 
  teilprojek1_.teilProjektInactive as teilProj6_, 
  teilprojek1_.budget as budget, 
  teilprojek1_.aufwand as aufwand, 
  teilprojek1_.OID as x0_0_, 
  sum(stundenent0_.stunden) as x1_0_ 
from 
  STUNDEN stundenent0_, 
  TEILPROJEKT teilprojek1_ 
where 
  stundenent0_.TeilProjektOID=teilprojek1_.OID and 
  ((stundenent0_.day>=? )and(stundenent0_.day<=? )and(stundenent0_.MitarbeiterOID=? )) 
group by  stundenent0_.TeilProjektOID
----------------------------------------------

The problem is that IBM DB2 complains about the group by attribute
"stundenent0_.TeilProjektOID" which is not part of the select part.
MySQL seems to be intelligent enough to detect that "stundenent0_.TeilProjektOID" is the same as "teilprojek1_.OID as OID". However, it seems that the generated SQL statement is wrong.


STUNDEN ist defined as:
	<class name="de.bea.plato.domain.StundenEntry" table="STUNDEN">
		<id column="OID" name="oid" length="25" type="java.lang.String" unsaved-value="null">
			<generator class="de.bea.plato.datastore.PlatoIdGenerator"/>
		</id>
		<many-to-one name="teilProjekt" column="TeilProjektOID" not-null="true"/>
		<many-to-one name="mitarbeiter" column="MitarbeiterOID" not-null="true"/>
		<property length="70" name="text" not-null="true"/>
		<property length="70" name="uhrzeitText" not-null="true"/>
		<property name="verrechnet" not-null="true"/>
		<property name="stunden" type="de.bea.plato.hibernate.TypeRationalNumberStunden" not-null="true"/>
		<property name="stundensatz" type="de.bea.plato.hibernate.TypeRationalNumberEUR" not-null="true"/>
		<property name="day" type="de.bea.plato.hibernate.TypeDay">
			<column name="day" index="StundenEntyDayIdx"/>
		</property>
	</class>


and TeilProjekt is defined as

	<class name="de.bea.plato.domain.TeilProjekt" table="TEILPROJEKT">
		<id column="OID" name="oid" length="25" type="java.lang.String" unsaved-value="null">
			<generator class="de.bea.plato.datastore.PlatoIdGenerator"/>
		</id>
		<many-to-one name="projekt" column="ProjektOID" not-null="true"/>
		<property length="70" name="bezeichnung" not-null="true"/>
		<property name="teilProjektArt" type="de.bea.plato.hibernate.TypeTeilProjektArt" not-null="true"/>
		<property name="teilProjektKategorie" type="de.bea.plato.hibernate.TypeTeilProjektKategorie" not-null="true"/>
		<property name="teilProjektInactive" not-null="false"/>
		<property name="budget" type="de.bea.plato.hibernate.TypeRationalNumberEUR" not-null="true"/>
		<property name="aufwand" type="de.bea.plato.hibernate.TypeRationalNumberStunden" not-null="true"/>
	</class>




---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira