UNNEST is only supported in our calcite branch currently. There's been no
release yet with support for it. If you're interested, I'd encourage you to
get involved in the Phoenix/Calcite integration work or implement support
for it in our 4.x branches.
Thanks,
James
On Sun, Mar 19, 2017 at 4:59 PM, Bernard Quizon <
bernard.qui...@stellarloyalty.com> wrote:
> Hi,
>
> I've tried using phoenix-4.6.0-Hbase-1.1 and phoenix-4.7.0-Hbase-1.1 to
> test if UNNEST is now available but it returns a syntax error.
>
> Both are resulting to this error
>
> sqlline version 1.1.8
> 0: jdbc:phoenix:localhost> SELECT t.k FROM UNNEST((SELECT k FROM a)) AS
> t(k);
> Error: ERROR 601 (42P00): Syntax error. Encountered "(" at line 1, column
> 24. (state=42P00,code=601)
> org.apache.phoenix.exception.PhoenixParserException: ERROR 601 (42P00):
> Syntax error. Encountered "(" at line 1, column 24.
> at org.apache.phoenix.exception.PhoenixParserException.newExcep
> tion(PhoenixParserException.java:33)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
> at org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementPar
> ser.parseStatement(PhoenixStatement.java:1285)
> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(Phoe
> nixStatement.java:1366)
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStat
> ement.java:1429)
> 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: NoViableAltException(90@[])
> at org.apache.phoenix.parse.PhoenixSQLParser.column_name(Phoeni
> xSQLParser.java:2397)
> at org.apache.phoenix.parse.PhoenixSQLParser.dyn_column_def(Pho
> enixSQLParser.java:3992)
> at org.apache.phoenix.parse.PhoenixSQLParser.dyn_column_defs(Ph
> oenixSQLParser.java:3925)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(Phoen
> ixSQLParser.java:5900)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixS
> QLParser.java:5672)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(Phoenix
> SQLParser.java:5608)
> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixS
> QLParser.java:5573)
> at org.apache.phoenix.parse.PhoenixSQLParser.single_select(Phoe
> nixSQLParser.java:4368)
> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(Ph
> oenixSQLParser.java:4470)
> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(Phoeni
> xSQLParser.java:4535)
> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(Phoen
> ixSQLParser.java:766)
> at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixS
> QLParser.java:500)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
>
> Even this simple one is resulting to syntax error:
>
> 0: jdbc:phoenix:localhost> SELECT * FROM UNNEST((ARRAY[1,2,3]));
> Error: ERROR 601 (42P00): Syntax error. Encountered "(" at line 1, column
> 22. (state=42P00,code=601)
> org.apache.phoenix.exception.PhoenixParserException: ERROR 601 (42P00):
> Syntax error. Encountered "(" at line 1, column 22.
> at org.apache.phoenix.exception.PhoenixParserException.newExcep
> tion(PhoenixParserException.java:33)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
> at org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementPar
> ser.parseStatement(PhoenixStatement.java:1185)
> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(Phoe
> nixStatement.java:1268)
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStat
> ement.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: NoViableAltException(90@[])
>
> Am I missing something? Or UNNEST is really not available yet on
> phoenix-4.7.0-Hbase-1.1 and phoenix-4.6.0-Hbase-1.1.
> BTW, I used this file as reference for the query syntax:
> https://github.com/apache/phoenix/blob/4.7.0-HBase-1
> .1/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnnestArrayIT.java
>
> Also, I have verified that I'm running phoenix-4.7.0-Hbase-1.1 and
> phoenix-4.6.0-Hbase-1.1.
>
>
> Thanks!
>
