[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3203) DerbyDialect Bug

DerbyDialect Bug ---------------- Key: HHH-3203 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3203 Project: Hibernate Core Issue Type: Bug Environment: Hibernate 3.2.6 GA, Vista, Current ApacheDerby, BEA Jrockit Reporter: Bernd Farka When i execute following Query select sum(t.ammount) from Transaction t where t.bookingDate >= :from and t.bookingDate <= :until and t.account = :account" Hibernate throws this Exception (on Mysql it works!) 2008-03-25 20:07:21,460 [Main Thread] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [select transactio0_.category_id as col_0_0_, sum(transactio0_.ammount) as col_1_0_, category1_.categoryId as categoryId2_, category1_.categroyName as categroy2_2_ from tbl_transactions transactio0_ inner join tbl_category category1_ on transactio0_.category_id=category1_.categoryId where transactio0_.bookingDate>=? and transactio0_.bookingDate<=? and transactio0_.account_id=? group by transactio0_.category_id] java.sql.SQLException: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547) at org.hibernate.loader.Loader.doQuery(Loader.java:673) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2213) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.farkarich.bankmanager.MainFrame.updateData(MainFrame.java:317) at org.farkarich.bankmanager.MainFrame.<init>(MainFrame.java:342) at org.farkarich.bankmanager.StartClass.main(StartClass.java:27) Caused by: ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.VerifyAggregateExpressionsVisitor.visit(Unknown Source) at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown Source) at org.apache.derby.impl.sql.compile.ResultColumn.accept(Unknown Source) at org.apache.derby.impl.sql.compile.QueryTreeNodeVector.accept(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 23 more 2008-03-25 20:07:21,462 [Main Thread] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 20000, SQLState: 42Y30 2008-03-25 20:07:21,462 [Main Thread] ERROR org.hibernate.util.JDBCExceptionReporter - The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions. 2008-03-25 20:07:21,465 [Main Thread] DEBUG org.hibernate.jdbc.JDBCContext - after autocommit 2008-03-25 20:07:21,465 [Main Thread] DEBUG org.hibernate.jdbc.ConnectionManager - aggressively releasing JDBC connection 2008-03-25 20:07:21,465 [Main Thread] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 1, globally: 1) (open ResultSets: 0, globally: 0)] 2008-03-25 20:07:21,465 [Main Thread] DEBUG org.hibernate.connection.DriverManagerConnectionProvider - returning connection to pool, pool size: 1 Exception in thread "Main Thread" org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2216) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.farkarich.bankmanager.MainFrame.updateData(MainFrame.java:317) at org.farkarich.bankmanager.MainFrame.<init>(MainFrame.java:342) at org.farkarich.bankmanager.StartClass.main(StartClass.java:27) Caused by: java.sql.SQLException: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547) at org.hibernate.loader.Loader.doQuery(Loader.java:673) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2213) ... 10 more Caused by: ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.VerifyAggregateExpressionsVisitor.visit(Unknown Source) at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown Source) at org.apache.derby.impl.sql.compile.ResultColumn.accept(Unknown Source) at org.apache.derby.impl.sql.compile.QueryTreeNodeVector.accept(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 23 more