[PHOENIX-3305] Parser doesn't accept UNION ALL with LIMIT

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 4.7.0
• Assignee: Unassigned
• Reporter: Nick Dimiduk
• Created: 21/Sep/16 16:15
• Updated: 21/Sep/16 20:23
• Resolved: 21/Sep/16 16:35

Description

UNION ALL and LIMIT appear to not work in tandem.

> create table test1(a integer, b varchar, constraint pk primary key (a));
> create table test2(a integer, b varchar, constraint pk primary key (a));
> upsert into test1 values(1, 'one');
> upsert into test1 values(2, 'two');
> upsert into test2 values(3, 'three');
> upsert into test2 values(4, 'four');
> select a, b from test1 limit 1 union all select a, b from test2 limit 1;
Error: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column 32. (state=42P00,code=602)
org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column 32.
        at org.apache.phoenix.exception.PhoenixParserException.newException(PhoenixParserException.java:33)
        at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
        at org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementParser.parseStatement(PhoenixStatement.java:1185)
        at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1268)
        at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1339)
        at sqlline.Commands.execute(Commands.java:822)
        at sqlline.Commands.sql(Commands.java:732)
        at sqlline.SqlLine.dispatch(SqlLine.java:808)
        at sqlline.SqlLine.begin(SqlLine.java:681)
        at sqlline.SqlLine.start(SqlLine.java:398)
        at sqlline.SqlLine.main(SqlLine.java:292)
Caused by: MissingTokenException(inserted [@-1,0:0='<missing EOF>',<-1>,1:31] at union)
        at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:350)
        at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
        at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:510)
        at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
        ... 9 more

A workaround using subselect is required

> select a, b from (select a, b from test1 limit 1) union all select a, b from (select a, b from test2 limit 1);
+----+--------+
| A  |   B    |
+----+--------+
| 1  | one    |
| 3  | three  |
+----+--------+