Search code examples
mysqlhbaseapache-phoenix

Error when using sql query containing IFNULL and serial number column in Phoenix

I have a SQL query as follows: 

Select 
    @sl:=@sl+1 as slNum,
    IFNULL(startTime,'-'),
from 
    race,
    (Select @s1:=0) x ;

I get the desired result when i run the above query in mysql, but I want to execute the same query on Phoenix for the table stored in Hbase. I get the following error for @sl:

org.apache.phoenix.exception.PhoenixParserException: ERROR 601 (42P00): Syntax error. Encountered "@" at line 'x', column 'y'.
Caused by: NoViableAltException(15@[])

And when I comment out the serial number, i get this:

org.apache.phoenix.exception.PhoenixParserException: ERROR 603 (42P00): Syntax error. Unexpected input. Expecting "LPAREN", got "IFNULL" at line x2, column y2.
Caused by: UnwantedTokenException(found=IFNULL, expected 91)

Does Phoenix not support them or is there some other way of doing this? Any suggestions will be helpful.

Solution

  • I don't know whether or not Phoenix supports dynamic SQL, but there is a way to generate row number without dynamic SQL:

    SELECT
    COALESCE(r1.startTime, '-') AS startTime
    (SELECT COUNT(*) FROM race r2 WHERE r2.startTime <= r1.startTime) slNum
FROM race r1;

    I assume that the start time provides the ordering. Your current logic is not well defined using row number without an ordering. You may replace startTime with another column in the WHERE clause of my subquery.