Page 1 of 1

SQL Parser bug

PostPosted: Fri May 06, 2011 3:40 am
by jasonds
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? :)

Re: SQL Parser bug

PostPosted: Fri May 06, 2011 10:21 am
by shannah
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 7:10 am
by ADobkin
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 7:58 am
by shannah
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 8:22 am
by ADobkin
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 9:22 am
by shannah
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 9:49 am
by ADobkin
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 9:56 am
by ADobkin
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

Re: SQL Parser bug

PostPosted: Fri Jul 08, 2011 10:55 am
by shannah
Try updating the entire SQL directory. That query works fine in my copy.

Re: SQL Parser bug

PostPosted: Sat Jul 09, 2011 5:05 pm
by ADobkin
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

Re: SQL Parser bug

PostPosted: Sat Jul 09, 2011 11:17 pm
by shannah
Actually, you should run them with:
php SQL/tests/test_parser.php

Re: SQL Parser bug

PostPosted: Tue Aug 02, 2011 9:54 am
by ADobkin
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

Re: SQL Parser bug

PostPosted: Tue Aug 02, 2011 11:22 am
by shannah
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.

Re: SQL Parser bug

PostPosted: Tue Aug 02, 2011 11:49 am
by ADobkin
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