These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
Daverz
 Post subject: Entities in group by
PostPosted: Tue Sep 07, 2004 5:49 am 
Newbie

Joined: Tue Sep 07, 2004 4:58 am
Posts: 2
In the Hibernate docs at
http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html#queryhql-aggregation
they show a query like this

Code:
select cat, count( elements(cat.kittens) )
from eg.Cat cat group by cat

However, when I try a query like this (I'm using the hibern8ide to test queries):

Code:
select composer, count(elements(composer.children)) from Composer as composer group by composer

I get an error. Here composer has a one-to-many relationship with composer.children. Relevant bits of my mappings are:

Code:
   <set name="children" inverse="true" cascade="all" lazy="true">
      <key column="composer_id"/>
      <one-to-many class="org.davidcook.classicollect.Work"/>
    </set>

and

Code:
    <many-to-one name="composer" column="composer_id" class="org.davidcook.classicollect.Composer" not-null="true"/>

And the error is:

Code:
1 errors occurred while listing (and calling getPathNames).
net.sf.hibernate.JDBCException: Could not execute query
java.sql.SQLException: Not in aggregate function or group by clause:
COLUMN COMPOSER0_.NAME in statement [select composer0_.COMPOSER_ID as COMPOSER1_, composer0_.NAME as NAME, composer0_.visible as visible, composer0_.COMPOSER_ID as x0_0_, count(children1_.WORK_ID) as x1_0_ from COMPOSER composer0_, WORK children1_ where composer0_.COMPOSER_ID=children1_.composer_id group by  composer0_.COMPOSER_ID]

But this query, using the id instead of the full entity, works fine:

Code:
select composer.id, count(elements(composer.children)) from Composer as composer group by composer.id

Is there a problem with my mapping docs, is it something hsqldb doesn't support, or is the documenation wrong?

This isn't a show stopper since I can just retrieve the objects by their IDs, but I want to make sure it isn't a problem with my mapping or some other misunderstanding.

Hibernate version: 2.1.6

Mapping documents:

[dmcook@localhost dmcook]$ cat Projects/hibernate/test/src/org/davidcook/classicollect/Composer.hbm.xml
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>
                                                                               
  <class name="org.davidcook.classicollect.Composer" table="COMPOSER">
    <meta attribute="class-description">
      Represents a composer in the music database.
      @author Dave Cook (with help from Hibernate)
    </meta>
                                                                               
    <id name="id" type="int" column="COMPOSER_ID">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>
                                                                               
    <property name="name" type="string">
      <meta attribute="use-in-tostring">true</meta>
      <column name="NAME" not-null="true" index="COMPOSER_NAME"/>
    </property>
                                                                               
    <property name="visible" type="boolean">
      <meta attribute="field-description">Whether the row is visible in UI selection fields</meta>
    </property>
                                                                               

    <set name="children" inverse="true" cascade="all" lazy="true">
      <key column="composer_id"/>
      <one-to-many class="org.davidcook.classicollect.Work"/>
    </set>

  </class>

  <query name="org.davidcook.classicollect.composerByName">
    <![CDATA[
        from org.davidcook.classicollect.Composer as composer
        where composer.name == :name
      ]]>
  </query>

  <query name="org.davidcook.classicollect.idWithNChildren">
    <![CDATA[
select composer.id, composer.name, count(children) from Composer as composer left join composer.children as children group by composer.id, composer.name order by lower(composer.name)]]>
  </query>

</hibernate-mapping>

[dmcook@localhost dmcook]$ cat Projects/hibernate/test/src/org/davidcook/classicollect/Work.hbm.xml
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>

  <class name="org.davidcook.classicollect.Work" table="WORK">
    <meta attribute="class-description">
      Represents a work associated with a composer.
      @author Dave Cook (with help from Hibernate)
    </meta>

    <id name="id" type="int" column="WORK_ID">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

    <many-to-one name="composer" column="composer_id" class="org.davidcook.classicollect.Composer" not-null="true"/>

    <property name="title" type="string">
      <meta attribute="use-in-tostring">true</meta>
      <column name="TITLE" not-null="true" index="WORK_NAME"/>
    </property>

    <property name="visible" type="boolean">
      <meta attribute="field-description">Whether the row is visible in UI selection fields</meta>
    </property>

  </class>

  <query name="org.davidcook.classicollect.workByComposerNameAndTitle">
    <![CDATA[
        from org.davidcook.classicollect.Work as work
        where work.composer.name = :composer_name and work.title = :title
      ]]>
  </query>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close(): N/A

Full stack trace of any exception that occurs:

[java] 02:24:07,864 WARN JDBCExceptionReporter:38 - SQL Error: -67, SQLState: 37000
[java] 02:24:07,865 ERROR JDBCExceptionReporter:46 - Not in aggregate function or group by clause:
[java] COLUMN COMPOSER0_.NAME in statement [select composer0_.COMPOSER_ID as COMPOSER1_, composer0_.NAME as NAME, composer0_.visible as visible, composer0_.COMPOSER_ID as x0_0_, count(children1_.WORK_ID) as x1_0_ from COMPOSER composer0_, WORK children1_ where composer0_.COMPOSER_ID=children1_.composer_id group by composer0_.COMPOSER_ID]
[java] 02:24:07,867 WARN JDBCExceptionReporter:38 - SQL Error: -67, SQLState: 37000
[java] 02:24:07,868 ERROR JDBCExceptionReporter:46 - Not in aggregate function or group by clause:
[java] COLUMN COMPOSER0_.NAME in statement [select composer0_.COMPOSER_ID as COMPOSER1_, composer0_.NAME as NAME, composer0_.visible as visible, composer0_.COMPOSER_ID as x0_0_, count(children1_.WORK_ID) as x1_0_ from COMPOSER composer0_, WORK children1_ where composer0_.COMPOSER_ID=children1_.composer_id group by composer0_.COMPOSER_ID]
[java] 02:24:07,869 ERROR JDBCExceptionReporter:38 - Could not execute query
[java] java.sql.SQLException: Not in aggregate function or group by clause:
[java] COLUMN COMPOSER0_.NAME in statement [select composer0_.COMPOSER_ID as COMPOSER1_, composer0_.NAME as NAME, composer0_.visible as visible, composer0_.COMPOSER_ID as x0_0_, count(children1_.WORK_ID) as x1_0_ from COMPOSER composer0_, WORK children1_ where composer0_.COMPOSER_ID=children1_.composer_id group by composer0_.COMPOSER_ID]
[java] at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
[java] at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
[java] at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:257)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
[java] at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
[java] at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544) [java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at net.sf.hibern8ide.HQLQueryPage.getList(HQLQueryPage.java:26)
[java] at net.sf.hibern8ide.Hibern8IDE$9.contentsChanged(Hibern8IDE.java:781)
[java] at net.sf.hibern8ide.Hibern8IDE$9.intervalAdded(Hibern8IDE.java:757)
[java] at javax.swing.AbstractListModel.fireIntervalAdded(AbstractListModel.java:130)
[java] at net.sf.hibern8ide.QueryPageModel.add(QueryPageModel.java:51)
[java] at net.sf.hibern8ide.Hibern8IDE$ExecuteQuery.actionPerformed(Hibern8IDE.java:989)
[java] at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
[java] at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
[java] at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
[java] at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
[java] at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
[java] at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231)
[java] at java.awt.Component.processMouseEvent(Component.java:5100)
[java] at java.awt.Component.processEvent(Component.java:4897)
[java] at java.awt.Container.processEvent(Container.java:1569)
[java] at java.awt.Component.dispatchEventImpl(Component.java:3615)
[java] at java.awt.Container.dispatchEventImpl(Container.java:1627)
[java] at java.awt.Component.dispatchEvent(Component.java:3477)
[java] at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483)
[java] at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
[java] at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
[java] at java.awt.Container.dispatchEventImpl(Container.java:1613)
[java] at java.awt.Window.dispatchEventImpl(Window.java:1606)
[java] at java.awt.Component.dispatchEvent(Component.java:3477)
[java] at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
[java] at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
[java] at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
[java] at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
[java] at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
[java] at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)


Name and version of the database you are using:
hsqldb 1.7.2

Debug level Hibernate log excerpt:
Top
 Profile  
 
Daverz
 Post subject:
PostPosted: Tue Sep 07, 2004 10:01 am 
Newbie

Joined: Tue Sep 07, 2004 4:58 am
Posts: 2
Paying attention to that errror message, I found that this also works

Code:
select composer, count(elements(composer.children)) from Composer composer group by composer, composer.name, composer.visible


But it seems to me that hibernate should put those extra fields in the SQL group by clause if HSQLDB is so picky, and I shouldn't have to specify them explicity.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.