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

[Sergiu Dumitriu]

Marius Dumitru Florea wrote:
> 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?
> 

Yep. Say that mysql <5 is not supported.

Frankly, I don't understand why people still use ancient versions. 
Version 4.1 has been EOL-d for some time, and version 7 is on its way 
already.

> 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/
>>

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