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: