SQL Parser bug

[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?

[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

[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

[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

[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

[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

[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

[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

[shannah]

Try updating the entire SQL directory. That query works fine in my copy.

[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

[shannah]

Actually, you should run them with:
php SQL/tests/test_parser.php

[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

[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.

[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