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: users-bounces(a)xwiki.org [mailto:users-bounces@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&postor…
>
> 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(a)xwiki.org
http://lists.xwiki.org/mailman/listinfo/users