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&s...
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/