GROUP_CONCAT query parser problem

A place for users and developers of the Xataface to discuss and receive support.

GROUP_CONCAT query parser problem

Postby Gwynnbleid1 » Thu Sep 20, 2012 3:08 pm

Hi

I receive this error with query in fields.ini file:

Code: Select all
Fatal error: Failed parsing SQL query on select: SELECT a.*, GROUP_CONCAT(c.Description SEPARATOR ', ') AS Descript FROM RaceAnalysisLapInformation a LEFT JOIN RaceAnalysisLapInformationEvents b ON a.Id=b.Lap LEFT JOIN LapEventType c ON b.Event=c.Id GROUP BY a.Id . The Error was Parse error: Unexpected token "as" on line 1 SELECT a.*, GROUP_CONCAT(c.Description SEPARATOR ', ') AS Descript FROM RaceAnalysisLapInformation a ^ found: "AS" in /gpro/xataface/lib/SQL/Parser.php on line 1752


Here is sql entry in question:
Code: Select all
__sql__ = "SELECT a.*, GROUP_CONCAT(c.Description SEPARATOR ', ') AS Descript FROM RaceAnalysisLapInformation a
LEFT JOIN RaceAnalysisLapInformationEvents b ON a.Id=b.Lap
LEFT JOIN LapEventType c ON b.Event=c.Id GROUP BY a.Id"


I found a topic with similar question and I remade my query to be similar as one in that topic but error keeps showing up.
This query works in phpmyadmin.
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: GROUP_CONCAT query parser problem

Postby shannah » Mon Sep 24, 2012 9:49 am

Thanks for reporting this. I have filed it as a bug
http://bugs.weblite.ca/view.php?id=1157

It is the SEPARATOR directive that the parser doesn't handle properly right now. A workaround would be to omit the separator directive. The default separator is ','. You could wrap your group_concat call with some sort of string replace function to replace the separator. This isn't feasible in all situations, but will probably work in your case.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: GROUP_CONCAT query parser problem

Postby Gwynnbleid1 » Thu Oct 11, 2012 12:55 am

Hi Steve
Thanks for suggestion, but problem is still not solved. I modified query into this:
Code: Select all
SELECT a.*, GROUP_CONCAT(c.Description) FROM RaceAnalysisLapInformation a LEFT JOIN RaceAnalysisLapInformationEvents b ON a.Id=b.Lap LEFT JOIN LapEventType c ON b.Event=c.Id GROUP BY a.Id


and got this error:
Code: Select all
Fatal error: Unknown column 'RaceAnalysisLapInformation.Description' in 'field list'Error loading related records for relationship 'RaceAnalysisLapInformation' in table 'RaceAnalysis'. There was a problem performing the sql query 'select `RaceAnalysisLapInformation`.`Id`, `RaceAnalysisLapInformation`.`Description`, `RaceAnalysisLapInformation`.`GROUP_CONCAT`, `RaceAnalysisLapInformation`.`RaceAnalysis`, `RaceAnalysisLapInformation`.`LapNo`, `RaceAnalysisLapInformation`.`LapTime`, `RaceAnalysisLapInformation`.`Pos`, `RaceAnalysisLapInformation`.`Tyres`, `RaceAnalysisLapInformation`.`Weather`, `RaceAnalysisLapInformation`.`Temp`, `RaceAnalysisLapInformation`.`Hum`, `RaceAnalysisLapInformation`.`Source` from (SELECT a.*, GROUP_CONCAT(c.Description) FROM RaceAnalysisLapInformation a LEFT JOIN RaceAnalysisLapInformationEvents b ON a.Id=b.Lap LEFT JOIN LapEventType c ON b.Event=c.Id GROUP BY a.Id) as `RaceAnalysisLapInformation` where `RaceAnalysisLapInformation`.`RaceAnalysis` = '41' LIMIT 0,30'. The Mysql error returned was in /gpro/xataface/Dataface/Record.php on line 503
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: GROUP_CONCAT query parser problem

Postby shannah » Thu Oct 11, 2012 6:27 am

What version of Xataface are you using?
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: GROUP_CONCAT query parser problem

Postby Gwynnbleid1 » Sun Oct 21, 2012 12:44 pm

shannah wrote:What version of Xataface are you using?

1.3.2 3355 - taken from version.ini file
Gwynnbleid1
 
Posts: 31
Joined: Thu Sep 20, 2012 3:02 pm

Re: GROUP_CONCAT query parser problem

Postby shannah » Fri Oct 26, 2012 8:16 am

This may be a bug.. but it's not clear to me yet whether it is a problem with the parser, or with the relationship handling. Can you make up a minimal working example application with associated SQL file to build the db so we can create a test to verify/fix this issue?

I'll open a bug with it.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 27 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved