Hibernate/JPA + Derby - SELECT statement has too many items in GROUP BY, ORDER BY or select list

Question

I use Hibernate for JPA DB mapping with Derby DB. For a complex object structure I am getting "org.apache.derby.client.am.SqlException: SELECT statement has too many items in GROUP BY, ORDER BY or select list":

org.apache.derby.client.am.SqlException: SELECT statement has too many items in GROUP BY, ORDER BY or select list.
        org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
        org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
        org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
        org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
        org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
        org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
        org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
        org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
        org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
        org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
        org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
        org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
        org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
        org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
        org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
        org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
        org.hibernate.loader.Loader.doQuery(Loader.java:673)
        org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        org.hibernate.loader.Loader.loadEntity(Loader.java:1860)
        org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
        org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
        org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3044)
        org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:395)
        org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)
        org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)
        org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
        org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
        org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
        org.hibernate.impl.SessionImpl.get(SessionImpl.java:815)
        org.hibernate.event.def.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:229)
        org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:120)
        org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:53)
        org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:677)
        org.hibernate.impl.SessionImpl.merge(SessionImpl.java:661)
        org.hibernate.impl.SessionImpl.merge(SessionImpl.java:665)
        org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:228)
        sun.reflect.GeneratedMethodAccessor376.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365)
        $Proxy74.merge(Unknown Source)
        sun.reflect.GeneratedMethodAccessor376.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
        $Proxy41.merge(Unknown Source)

Has anyone run into this problem and if what was the solution? I am thinking of

• switching to MySQL
• lazily load some attributes (fetch=FetchType.LAZY)
• redesign DB schema (that might be difficult now, DB is generated using Hyperjaxb from XSD schemas and shemas would have to be changed)

For which solution would you go? Do you know what are the limitations of Derby for SQL statements?

Answer 1

Try debugging it with show_sql=true doc
Check the SQL generated and try debugging it .

Answer 2

I believe Derby allows approximately 32,000 columns in the GROUP BY clause. There are similar limits for ORDER BY, and for the columns named in the SELECT list.