[xwiki-users] MySQL Error "invalid use of group function" in Statistics application

[Sergiu Dumitriu]

BOUSQUET Jeremie wrote:
> Sergiu,
> I must admit that your solution is the more efficient and clean, even if I personally don't like it (you'd be surprised to know how painful it will be to ask my infra team to upgrade mysql version ...).
> For me it's clearly a MySQL bug not an hibernate one, let's say at least a break of compatibility in MySql not managed by hibernate. Typically what you would like an abstraction layer to take care of ... :/
> 
> Thanks for help,
> Jeremie
>  

I know how hard it is to convince sysadmins to upgrade. I barely 
convinced one to upgrade from 4.0 in order to have UTF-8 support in the 
database. It took many days and a signature from the university rector. 
Finally he decided to install a new mysql on another machine, so that 
the other apps won't be in danger...

> 
> -----Original Message-----
> From: users-bounces at xwiki.org [mailto:users-bounces at xwiki.org] On Behalf Of Sergiu Dumitriu
> Sent: mardi 19 février 2008 11:42
> To: XWiki Users
> Subject: Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
> 
> Marius Dumitru Florea wrote:
>> A fourth option (someway similar to the second one) is to use the Native
>> SQL Query feature of Hibernate. Instead of calling
>> session.createQuery(hql) you can call session.createSQLQuery(sql). It
>> works, using aliases. However, I tested it only on MySql and HSQLDB.
>>
>> WDYT? Is this a convenient solution. I would have to switch many of the
>> hql queries from the Statistics Application to sql queries.
>>
> 
> Generally, we should stay away from pure SQL as much as possible. If the 
> existing query works in all DBMSs except mysql 4.1, then leave it as it is.
> 
>> Marius
>>
>>> Right.. but the error persists with the correction made. Not at the same
>>> level though.
>>>
>>> WARN  util.JDBCExceptionReporter - SQL Error: 1054, SQLState: 42S22
>>> ERROR util.JDBCExceptionReporter - Unknown column 'pageViewsCount' in
>>> 'order clause'
>>>
>>> The problem is with Hibernate. There is a bug ("missing feature", for
>>> them) in the translation from HQL to SQL. The aliases from the Select
>>> clause are not resolved correctly in other clauses, including Order By. In
>>> this case they "recommend" us to duplicate the expressions from the Select
>>> clause, hoping that the RDBMS will take care of the optimization.
>>>
>>> See for yourself:
>>>
>>> http://opensource.atlassian.com/projects/hibernate/browse/HHH-892
>>> http://opensource.atlassian.com/projects/hibernate/browse/HHH-1902
>>> http://opensource.atlassian.com/projects/hibernate/browse/HHH-2390
>>> http://forum.hibernate.org/viewtopic.php?t=966075&postdays=0&postorder=asc&start=0
>>>
>>> So, using aliases breaks Hibernate and using expressions in the Order By
>>> clause is not supported by MySql 4.1.20. I don't see any clean solution
>>> for this. Some options are:
>>>
>>> * drop the Order By clause and sort on the Java side - No way, I don't
>>> think this is feasible.
>>> * skip Hibernate layer and use JDBC directly - Doable, the SQL query being
>>> not too complicated, but with cross DB issues.
>>> * tweak the HQL query - How?
>>>
>>> Any other options Sergiu?
>>>
>>> Marius
>>>
>>>> Marius Dumitru Florea wrote:
>>>>> Hi Jeremie,
>>>>>
>>>>> The sql query you suggested is cleaner and at least as efficient as the
>>>>> current one. It was my first option but unfortunately it's not
>>>>> supported
>>>>> by the newer versions of mysql. I've no idea why..
>>>>>
>>>>> mysql> select xws_name, sum(xws_page_views) as pageviewcount from
>>>>> xwikistatsdoc where xws_action='view' and xws_name like '%.%' group by
>>>>> xws_name order by pageviewscount desc;
>>>>> ERROR 1054 (42S22): Unknown column 'pageviewscount' in 'order clause'
>>>>>
>>>> Note that there's a typo: pageviewcount <> pageview-S-count
>>>>
>>>>> Anyway, hibernate should be responsible for the correct translation
>>>>> from
>>>>> hql to a specific sql dialect. A solution would be to use the (idea
>>>>> from
>>>>> the) above sql but configure the dialect to MySQL5Dialect (in my case).
>>>>> I
>>>>> have to test this. I'll give you feedback soon.
>>>>>
>>>>> Best Regards,
>>>>> Marius.
>>>>>
> 
> 


-- 
Sergiu Dumitriu
http://purl.org/net/sergiu/