greenlion
Can not calculate position of query exception
From [email protected] on September 25, 2014 22:28:45
What steps will reproduce the problem? 1. Parse the following query with keyword positions true 2. Error happens What is the expected output? What do you see instead? PHP Fatal error: Uncaught exception 'UnableToCalculatePositionException' with message 'cannot calculate position of as dec within , cast(dec_30 as int)/30.0 -- now build mask grid. -- This is a separate query if no temp tables can be made ' in /Users/abiusx/Desktop/PHP-SQL-Parser/src/positions/PositionCalculator.php:199 What version of the product are you using? On what operating system? php-sql-parser-20140108 OS X Yosemite Please provide any additional information below. SQL Query: $sql1=<<<XXX SELECT cast((ra/cos(cast(dec_30 as int)/30.0))30 as int)/30.0 as raCosDec, cast(dec_30 as int)/30.0 as dec, count() as pop FROM Galaxy as G, fHTM_Cover('CONVEX J2000 6 175 -5 175 5 185 5 185 -5') as T WHERE htmID between T.HTMIDstart_ power(2,28)and T. HTMIDend_power(2,28) and ra between 175 and 185 and dec between -5 and 5 and u-g > 1 and r < 21.5 GROUP BY cast((ra/cos(cast(dec_30 as int)/30.0))_30 as int)/30.0, cast(dec_30 as int)/30.0 -- now build mask grid. -- This is a separate query if no temp tables can be made XXX;
Original issue: http://code.google.com/p/php-sql-parser/issues/detail?id=150
From [email protected] on September 26, 2014 07:59:21
I think, this is a problem with your comment-lines. remove these and it should work. See issue #56 .
Status: Accepted
Owner: [email protected]
From [email protected] on September 26, 2014 08:32:50
The error occurs also without the comment lines. I'll look deeper in the code...
Status: Started
From [email protected] on September 26, 2014 10:11:13
Ah, I have found it.
In the first "cast((ra/cos(cast(dec_30 as int)/30.0))_30 as int)/30.0 as raCosDec" you use a column name "dec", which is recognized as reserved keyword. The following "*" is a column-ref instead of an operator, so the PositionCalculator doesn't allow "30" after a column-ref and looks forward in the statement to find a better position. By a rare fluke it finds a similar string on position 115. So it calculates forward starting a wrong position. At the end of the string it runs into an exception because there is no more statement left.
So the problem is not the PositionCalculator, the reason is the Parser itself. I'll see, what I can do...
From [email protected] on September 26, 2014 10:15:53
can you also point me in the direction of how I can make the parser return unified arrays (instead of segmenting SELECT FROM WHERE clauses separately and giving no position information on those)? I just need position informations on them to mark in the strings. Unfortunately cant manually add that because there might be nested queries. -A
From [email protected] on September 26, 2014 10:25:22
For the moment, you can try to change the column-name, because DEC is listed as reserved keyword on http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-7.html
From [email protected] on September 26, 2014 10:26:56
Shouldnt it make a parse error instead of a position error?
From [email protected] on September 26, 2014 10:56:08
No, because the parser doesn't validate the output. It gets a substring, try to find a meaning and stores it into the output. The parser doesn't not check, wether or not a reserved keyword is possible on this specific place in the statement.
It looks into the static array of the reserved keyword, and if there is a match, it will store the string as reserved word in the output. The next '*' is first recoginzed as operator, but there is a reserved word in front of it, so it will be a column-ref as in "SELECT * FROM...
So the only chance I have to differ you column name and the reserved keyword is to investigate the environment of the substring (which are the previous and next parts of the statement). With a little bit addition information I can decide, which type I'll return.
But the main problem is, that I don't know a statement, which uses DEC as keyword. Maybe one of the other users can help.
From [email protected] on September 29, 2014 07:52:07
DEC is a synonym of DECIMAL, so I could check, whether or not in front of DEC is an "AS". In this case, it is a reserved word, in all other cases it could be a column-ref.
cast(dec*30 as dec)
should return a column-ref first, then a reserved keyword.