SQL Parser bug

A place to discuss development of the Xataface core.

SQL Parser bug

Postby jasonds » Fri May 06, 2011 3:40 am

Using your SQL Parser when parsing an SQL statement with an IN statement in the WHERE causes an error, e.g..

Code: Select all
$sql = "SELECT * FROM articles WHERE article_format_id IN (1,2)";
$parser = new SQL_Parser(null, "MySQL");
print_r($parser->parse($sql));


Any chance of a fix? :)
jasonds
 
Posts: 1
Joined: Fri May 06, 2011 3:37 am

Re: SQL Parser bug

Postby shannah » Fri May 06, 2011 10:21 am

I just added your query to the unit tests for the SQL parser and it seemed to pass with flying colours. The Parser has recently had some improvements made to it, but nothing specifically for the IN clause so it is my guess that it should work in older versions too.

You can try updating the lib/SQL directory up to the latest from svn http://weblite.ca/svn/dataface/core/trunk/lib/SQL
Otherwise....

What error message does it give you?
What version of Xataface are you using?
What version of PHP/MySQL?

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

Re: SQL Parser bug

Postby ADobkin » Fri Jul 08, 2011 7:10 am

I am getting some errors with the SQL parser in 1.3rc3 when using various functions. I haven't tested these queries in previous versions, so I'm not sure if it is a new issue or not.

To start, I am trying to do a simple timediff like what is described in this post:

calculate datetime difference

Code: Select all
__sql__ = "SELECT p.*, TIMEDIFF(timeStop, timeStart) AS hours FROM pto p"


When I do this, I get the following error:

Fatal error: Failed parsing SQL query on select: SELECT p.*, TIMEDIFF(timeStop, timeStart) AS hours FROM pto p . The Error was Parse error: Unexpected token "as" on line 1 SELECT p.*, TIMEDIFF(timeStop, timeStart) AS hours FROM pto p ^ found: "AS" in /var/www/html/xataface-1.3rc3/lib/SQL/Parser.php on line 1752

If I remove the "AS hours" clause, then I get this error instead:

Error: Could not load current record: Unknown column 'pto.TIMEDIFF' in 'field list' ...

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL Parser bug

Postby shannah » Fri Jul 08, 2011 7:58 am

Timediff may not have been added to the list of allowed functions. I'll look into this today and get a suggested fix to you when I have it.

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

Re: SQL Parser bug

Postby ADobkin » Fri Jul 08, 2011 8:22 am

Great, thanks!

I have a couple of other related functions that I'd like to use in the SQL query as well if possible:

Code: Select all
DATEDIFF(ptoDate,CURDATE())


Code: Select all
FORMAT(TIME_TO_SEC(TIMEDIFF(timeStop,timeStart))/3600,2)


I've tried them already and received similar errors.

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL Parser bug

Postby shannah » Fri Jul 08, 2011 9:22 am

I have added support for the timediff function. The other two already worked in my version - so you may want to just update the relevant file to the latest. Here's the diff:
Code: Select all

macbook:xataface shannah$ svn diff -r 2429:2430 .
Index: lib/SQL/Dialect_MySQL.php
===================================================================
--- lib/SQL/Dialect_MySQL.php   (revision 2429)
+++ lib/SQL/Dialect_MySQL.php   (revision 2430)
@@ -182,6 +182,7 @@
         'system_user',
         'tan',
         'time',
+        'timediff',
         'time_format',
         'time_to_sec',
         'to_days',




Or just replace your copy of lib/SQL/Dialect_MySQL.php with the one from svn
http://weblite.ca/svn/dataface/core/tru ... _MySQL.php

Best regards

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

Re: SQL Parser bug

Postby ADobkin » Fri Jul 08, 2011 9:49 am

The timediff works perfectly now, thanks! I didn't realize the fix would be so simple. The datediff also works.

Maybe I can make it a bit more challenging.... :-)

This function works from the inside out (including the TIMEDIFF and the TIME_TO_SEC) until I add the /3600. Is there still a problem with arithmetic in the SQL parser in version 1.3? If so, I think I can use the fieldname__display function to do the final math there.

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL Parser bug

Postby ADobkin » Fri Jul 08, 2011 9:56 am

I forgot to include the function that is still giving an error:

Code: Select all
FORMAT(TIME_TO_SEC(TIMEDIFF(timeStop,timeStart))/3600,2)


I can take out the FORMAT to simplify it a little, but it still fails:

Code: Select all
TIME_TO_SEC(TIMEDIFF(timeStop,timeStart))/3600


Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL Parser bug

Postby shannah » Fri Jul 08, 2011 10:55 am

Try updating the entire SQL directory. That query works fine in my copy.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: SQL Parser bug

Postby ADobkin » Sat Jul 09, 2011 5:05 pm

It is working now. Thanks again for your help! I had it as part of a more complex query, so it took a bit of tweaking to get it right.

I noticed the tests in the SQL subdirectory. Should I use these if I have a similar problem in the future? I tried running the following from the lib directory and got no output.

Code: Select all
php SQL/tests/parser_cases.php


Does that mean it worked?

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL Parser bug

Postby shannah » Sat Jul 09, 2011 11:17 pm

Actually, you should run them with:
php SQL/tests/test_parser.php
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: SQL Parser bug

Postby ADobkin » Tue Aug 02, 2011 9:54 am

I ran into another possible SQL parser bug. The relevant portion of the query is:

Fatal error: Failed parsing SQL query on select: SELECT ... WHERE date BETWEEN SUBDATE(CURDATE(), INTERVAL 2 YEAR) AND ADDDATE(CURDATE(), INTERVAL 2 YEAR) . The Error was Parse error: Expected an expression and unit for the interval on line 8 SUBDATE(CURDATE(), INTERVAL 2 YEAR) ^ found: "YEAR" in /var/www/html/xataface-1.3rc4/lib/SQL/Parser.php on line 1752

I also tried the same query with MONTH as the interval unit and got a similar error.

I can rewrite the query using different date functions, but I am reporting the error in case someone wants to do something similar.

Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: SQL Parser bug

Postby shannah » Tue Aug 02, 2011 11:22 am

Thanks. it is likely that the parser doesn't support the "INTERVAL" keyword yet. Unfortunately adding support for this keyword is more involved than just adding support for individual function names because it will affect the structure of the parse tree. Not sure when I'll have a chance to open this bug.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: SQL Parser bug

Postby ADobkin » Tue Aug 02, 2011 11:49 am

Okay, no problem. I am just using the DATEDIFF() function instead for now. But there may be other cases where DATE_ADD(), DATE_SUB(), or their synonymous functions ADDDATE() or SUBDATE() are needed.

Incidentally, I did check Dialect_MySQL.php, and I noticed that interval is listed as a function and a reserved word, so I figured it must be more involved. I also noticed all of the date functions mentioned above are already defined in the file, but they won't work without the INTERVAL keyword.

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA


Return to Xataface Developers

Who is online

Users browsing this forum: No registered users and 1 guest

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