Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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: [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
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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
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
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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/
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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.
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
>
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
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' 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. > To be complete, this same error occurs for all the Statistics panels ... > > > -Original Message- > From: BOUSQUET Jeremie > Sent: lundi 18 février 2008 15:50 > To: 'XWiki Users' > Subject: RE: [xwiki-users] MySQL Error "invalid use of group function" in > Statistics application > > > Hi Marius and thanks, > > Your SQL request returns the same error "invalid use of group function" in > my mysql version ... > > BTW I could have it work by explicitly naming the sum, and using it in the > order by : > > 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 > > I believe it's a difference in behaviour between MySQL 1.4 and 1.5 ... Do > you want me to create a JIRA issue for this ? > > Best Regards, > > Jeremie > > > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Marius Dumitru Florea > Sent: lundi 18 février 2008 15:19 > To: users@xwiki.org > Subject: Re: [xwiki-users] MySQL Error "invalid use of group function" in > Statistics application > > Hi Jeremie, > > Sorry for the inconvenience. Your tables' description seems fine. I > attached the description of my tables so you can check it for yourself. I > think it all resumes to the following SQL query: > > select xws_name, sum(xws_page_views) from xwikistatsdoc where > xws_action='view' and xws_name like '%.%' group by xws_name order by > sum(xws_page_views) desc > > This query is very similar to the one used for retrieving the Most Viewed > Pages. It works fine under: > > mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (x86_64) using readline > 5.2 > > It would be great if you could run this query and tell me if it works in > your case. > > Best Regards, > Marius. > ___ > 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
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
To be complete, this same error occurs for all the Statistics panels ... -Original Message- From: BOUSQUET Jeremie Sent: lundi 18 février 2008 15:50 To: 'XWiki Users' Subject: RE: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application Hi Marius and thanks, Your SQL request returns the same error "invalid use of group function" in my mysql version ... BTW I could have it work by explicitly naming the sum, and using it in the order by : 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 I believe it's a difference in behaviour between MySQL 1.4 and 1.5 ... Do you want me to create a JIRA issue for this ? Best Regards, Jeremie -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marius Dumitru Florea Sent: lundi 18 février 2008 15:19 To: users@xwiki.org Subject: Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application Hi Jeremie, Sorry for the inconvenience. Your tables' description seems fine. I attached the description of my tables so you can check it for yourself. I think it all resumes to the following SQL query: select xws_name, sum(xws_page_views) from xwikistatsdoc where xws_action='view' and xws_name like '%.%' group by xws_name order by sum(xws_page_views) desc This query is very similar to the one used for retrieving the Most Viewed Pages. It works fine under: mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (x86_64) using readline 5.2 It would be great if you could run this query and tell me if it works in your case. Best Regards, Marius. ___ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
Hi Marius and thanks, Your SQL request returns the same error "invalid use of group function" in my mysql version ... BTW I could have it work by explicitly naming the sum, and using it in the order by : 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 I believe it's a difference in behaviour between MySQL 1.4 and 1.5 ... Do you want me to create a JIRA issue for this ? Best Regards, Jeremie -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marius Dumitru Florea Sent: lundi 18 février 2008 15:19 To: users@xwiki.org Subject: Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application Hi Jeremie, Sorry for the inconvenience. Your tables' description seems fine. I attached the description of my tables so you can check it for yourself. I think it all resumes to the following SQL query: select xws_name, sum(xws_page_views) from xwikistatsdoc where xws_action='view' and xws_name like '%.%' group by xws_name order by sum(xws_page_views) desc This query is very similar to the one used for retrieving the Most Viewed Pages. It works fine under: mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (x86_64) using readline 5.2 It would be great if you could run this query and tell me if it works in your case. Best Regards, Marius. ___ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] MySQL Error "invalid use of group function" in Statistics application
Hi Jeremie, Sorry for the inconvenience. Your tables' description seems fine. I attached the description of my tables so you can check it for yourself. I think it all resumes to the following SQL query: select xws_name, sum(xws_page_views) from xwikistatsdoc where xws_action='view' and xws_name like '%.%' group by xws_name order by sum(xws_page_views) desc This query is very similar to the one used for retrieving the Most Viewed Pages. It works fine under: mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (x86_64) using readline 5.2 It would be great if you could run this query and tell me if it works in your case. Best Regards, Marius.mysql> desc xwikistatsdoc; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | XWS_ID | int(11) | NO | PRI | | | | XWS_NUMBER | int(11) | YES | | NULL| | | XWS_NAME| varchar(255) | NO | | | | | XWS_CLASSNAME | varchar(255) | NO | | | | | XWS_ACTION | varchar(255) | NO | | | | | XWS_PAGE_VIEWS | int(11) | YES | | NULL| | | XWS_UNIQUE_VISITORS | int(11) | YES | | NULL| | | XWS_PERIOD | int(11) | YES | | NULL| | | XWS_VISITS | int(11) | YES | | NULL| | +-+--+--+-+-+---+ mysql> desc xwikistatsreferer; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | XWR_ID | int(11) | NO | PRI | | | | XWR_NUMBER | int(11) | YES | | NULL| | | XWR_NAME | varchar(255) | NO | | | | | XWR_CLASSNAME | varchar(255) | NO | | | | | XWR_REFERER| varchar(255) | NO | | | | | XWR_PAGE_VIEWS | int(11) | YES | | NULL| | | XWR_PERIOD | int(11) | YES | | NULL| | ++--+--+-+-+---+ mysql> desc xwikistatsvisit; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | XWV_ID | int(11) | NO | PRI | | | | XWV_NUMBER | int(11) | YES | | NULL| | | XWV_NAME | varchar(255) | NO | | | | | XWV_CLASSNAME | varchar(255) | NO | | | | | XWV_IP | varchar(32) | NO | | | | | XWV_USER_AGENT | varchar(255) | NO | | | | | XWV_COOKIE | varchar(255) | NO | | | | | XWV_UNIQUE_ID | varchar(255) | NO | | | | | XWV_PAGE_VIEWS | int(11) | YES | | NULL| | | XWV_PAGE_SAVES | int(11) | YES | | NULL| | | XWV_DOWNLOADS | int(11) | YES | | NULL| | | XWV_START_DATE | datetime | YES | | NULL| | | XWV_END_DATE | datetime | YES | | NULL| | ++--+--+-+-+---+ ___ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
