[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2074) MySQL GROUP BY issue (with parenthesis)

[Eric Sauve (JIRA)]

MySQL GROUP BY issue (with parenthesis)
---------------------------------------

         Key: HHH-2074
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2074
     Project: Hibernate3
        Type: Bug

  Components: query-hql  
    Versions: 3.0.5    
 Environment: Win2K
    Reporter: Eric Sauve


a query goes from

[demo] 10:21:13 DEBUG XXXReportingDAOHibernate.doInHibernate(96) | select entreprise as entreprise, businessUnit as BU, devise as devise, sum(balance.balanceAmount) from Balance balance join balance.account.currency devise join balance.account.company entreprise join balance.account.users utilisateur join entreprise.bus businessUnit where balance.balanceDate = :date and utilisateur.userId = :iduser group by  entreprise.idCompany, entreprise.country, entreprise.groups, entreprise.companyName, entreprise.contactName, entreprise.contactMail, entreprise.contactPhone, entreprise.address, businessUnit.idBu, businessUnit.buName, businessUnit.buDescription, businessUnit.groups, devise.idCurrency, devise.currencyName, devise.currencyDesc 

to:

[demo] 10:21:13 WARN DispatcherServlet.processHandlerException(883) | Handler execution resulted in exception - forwarding to resolved error view
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select company4_.ID_COMPANY as col_0_0_, businessun9_.ID_BU as col_1_0_, currency2_.ID_CURRENCY as col_2_0_, sum(balance0_.BALANCE_AMOUNT) as col_3_0_, company4_.ID_COMPANY as ID1_0_, businessun9_.ID_BU as ID1_1_, currency2_.ID_CURRENCY as ID1_2_, company4_.ID_COUNTRY as ID2_6_0_, company4_.ID_GROUP as ID3_6_0_, company4_.COMPANY_NAME as COMPANY4_6_0_, company4_.address as address6_0_, company4_.city as city6_0_, company4_.province as province6_0_, company4_.country as country6_0_, company4_.postal_code as postal9_6_0_, company4_.COMPANY_CONTACT_MAIL as COMPANY10_6_0_, company4_.COMPANY_CONTACT_NAME as COMPANY11_6_0_, company4_.COMPANY_CONTACT_PHONE as COMPANY12_6_0_, businessun9_.ID_GROUP as ID2_23_1_, businessun9_.BU_DESCRIPTION as BU3_23_1_, businessun9_.BU_NAME as BU4_23_1_, currency2_.CURRENCY_DESC as CURRENCY2_12_2_, currency2_.CURRENCY_NAME as CURRENCY3_12_2_ from BALANCE balance0_ inner join ACCOUNT account1_ on balance0_.ID_ACCOUNT=account1_.ID_ACCOUNT inner join CURRENCY currency2_ on account1_.ID_CURRENCY=currency2_.ID_CURRENCY inner join COMPANY company4_ on account1_.ID_COMPANY=company4_.ID_COMPANY inner join LIST_BU_COMPANY bus8_ on company4_.ID_COMPANY=bus8_.ID_COMPANY inner join BUSINESS_UNIT businessun9_ on bus8_.ID_BU=businessun9_.ID_BU inner join RIGHT_ACCOUNT users6_ on account1_.ID_ACCOUNT=users6_.ID_ACCOUNT inner join app_user user7_ on users6_.ID_USER=user7_.ID_USER where balance0_.BALANCE_DATE=? and user7_.ID_USER=? group by company4_.ID_COMPANY , company4_.ID_COUNTRY , company4_.ID_GROUP , company4_.COMPANY_NAME , company4_.COMPANY_CONTACT_NAME , company4_.COMPANY_CONTACT_MAIL , company4_.COMPANY_CONTACT_PHONE , (company4_.address, company4_.city, company4_.province, company4_.country, company4_.postal_code) , businessun9_.ID_BU , businessun9_.BU_NAME , businessun9_.BU_DESCRIPTION , businessun9_.ID_GROUP , currency2_.ID_CURRENCY , currency2_.CURRENCY_NAME , currency2_.CURRENCY_DESC]; SQL state [21000]; error code [1241]; Operand should contain 1 column(s); nested exception is java.sql.SQLException: Operand should contain 1 column(s)
java.sql.SQLException: Operand should contain 1 column(s)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3004)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1128)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
	at org.hibernate.loader.Loader.doQuery(Loader.java:391)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
	at org.hibernate.loader.Loader.doList(Loader.java:1593)
	at org.hibernate.loader.Loader.list(Loader.java:1577)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
	at com.planaxis.swifter.dao.hibernate.XXXReportingDAOHibernate$1.doInHibernate(XXXReportingDAOHibernate.java:98)
	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:314)
	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:287)
	at com.XXX.swifter.dao.hibernate.XXXReportingDAOHibernate.getReporting01(XXXReportingDAOHibernate.java:114)

is caused by the additional parenthesis in the GROUP BY CLAUSE
, (company4_.address, company4_.city, company4_.province, company4_.country, company4_.postal_code) ,

is this a dev/configuration issue or a bug ?

Regards


-- 
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
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira