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 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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/ _______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users _______________________________________________ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
