Can not calculate position of query exception

[GoogleCodeExporter]

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 reported on code.google.com by abi...@owasp.org on 25 Sep 2014 at 8:28

[GoogleCodeExporter]

I think, this is a problem with your comment-lines. remove these and it should 
work. See issue 56.

Original comment by pho...@gmx.de on 26 Sep 2014 at 2:59

• Changed state: Accepted

[GoogleCodeExporter]

The error occurs also without the comment lines. I'll look deeper in the code...

Original comment by pho...@gmx.de on 26 Sep 2014 at 3:32

• Changed state: Started

[GoogleCodeExporter]

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

Original comment by pho...@gmx.de on 26 Sep 2014 at 5:11

[GoogleCodeExporter]

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 can�t manually add that because there might be nested queries.
-A

Original comment by abi...@owasp.org on 26 Sep 2014 at 5:15

[GoogleCodeExporter]

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-re
servedwords-5-7.html

Original comment by pho...@gmx.de on 26 Sep 2014 at 5:25

[GoogleCodeExporter]

Shouldn�t it make a parse error instead of a position error?

Original comment by abi...@owasp.org on 26 Sep 2014 at 5:26

[GoogleCodeExporter]

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.

Original comment by pho...@gmx.de on 26 Sep 2014 at 5:56

[GoogleCodeExporter]

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.

Original comment by pho...@gmx.de on 29 Sep 2014 at 2:52