You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Aaron Molitor <am...@splicemachine.com> on 2016/08/19 17:17:27 UTC
Cannot select data from a system table
Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is this a known bug?
0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYP |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
| | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | false | | | |
| | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | false | | | |
| | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | false | | | |
| | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | false | | | |
| | TPCH | CUSTOMER | TABLE | | | | | | false | null | false | | | |
| | TPCH | LINEITEM | TABLE | | | | | | false | null | false | | | |
| | TPCH | NATION | TABLE | | | | | | false | null | false | | | |
| | TPCH | ORDERS | TABLE | | | | | | false | null | false | | | |
| | TPCH | PART | TABLE | | | | | | false | null | false | | | |
| | TPCH | PARTSUPP | TABLE | | | | | | false | null | false | | | |
| | TPCH | REGION | TABLE | | | | | | false | null | false | | | |
| | TPCH | SUPPLIER | TABLE | | | | | | false | null | false | | | |
+------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from SYSTEM.FUNCTION;
Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1, column 22.
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:1280)
at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1363)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1434)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:807)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)
Caused by: MismatchedTokenException(65!=99)
at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:360)
at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(PhoenixSQLParser.java:9969)
at org.apache.phoenix.parse.PhoenixSQLParser.identifier(PhoenixSQLParser.java:9936)
at org.apache.phoenix.parse.PhoenixSQLParser.from_table_name(PhoenixSQLParser.java:9589)
at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(PhoenixSQLParser.java:6244)
at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixSQLParser.java:6066)
at org.apache.phoenix.parse.PhoenixSQLParser.table_list(PhoenixSQLParser.java:6002)
at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixSQLParser.java:5967)
at org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4595)
at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(PhoenixSQLParser.java:4697)
at org.apache.phoenix.parse.PhoenixSQLParser.select_node(PhoenixSQLParser.java:4763)
at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:789)
at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:508)
at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
... 9 more
Re: Cannot select data from a system table
Posted by Ted Yu <yu...@gmail.com>.
Thanks for confirmation, Ankit.
> On Aug 31, 2016, at 3:36 AM, Ankit Singhal <an...@gmail.com> wrote:
>
> bq. Is this documented somewhere ?
> not as such, https://phoenix.apache.org/language/index.html#quoted_name is generally for case sensitive identifier(and to allow some special characters) and same can be used for keywords.
>
> bq. Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would give us good idea.
> Yes Ted, you are right . Phoenix keywords are the tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g
>
>
>
>> On Sun, Aug 21, 2016 at 8:33 PM, Ted Yu <yu...@gmail.com> wrote:
>> Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would give us good idea.
>>
>> Experts please correct me if I am wrong.
>>
>>> On Sun, Aug 21, 2016 at 7:21 AM, Aaron Molitor <am...@splicemachine.com> wrote:
>>> Thanks, Ankit, that worked.
>>>
>>> And on the heels of Ted's question... Are the reserved words documented (even if just a list) somewhere, I've been looking at this page: http://phoenix.apache.org/language/index.html -- it feels like where I should find a list like that, but I don't see it explicitly called out.
>>>
>>> -Aaron
>>>> On Aug 21, 2016, at 09:04, Ted Yu <yu...@gmail.com> wrote:
>>>>
>>>> Ankit:
>>>> Is this documented somewhere ?
>>>>
>>>> Thanks
>>>>
>>>>> On Sun, Aug 21, 2016 at 6:07 AM, Ankit Singhal <an...@gmail.com> wrote:
>>>>> Aaron,
>>>>>
>>>>> you can escape check for reserved keyword with double quotes ""
>>>>>
>>>>> SELECT * FROM SYSTEM."FUNCTION"
>>>>>
>>>>> Regards,
>>>>> Ankit Singhal
>>>>>
>>>>>> On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <am...@splicemachine.com> wrote:
>>>>>> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is this a known bug?
>>>>>>
>>>>>>
>>>>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
>>>>>> +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
>>>>>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYP |
>>>>>> +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
>>>>>> | | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | false | | | |
>>>>>> | | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | false | | | |
>>>>>> | | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | false | | | |
>>>>>> | | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | CUSTOMER | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | LINEITEM | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | NATION | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | ORDERS | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | PART | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | PARTSUPP | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | REGION | TABLE | | | | | | false | null | false | | | |
>>>>>> | | TPCH | SUPPLIER | TABLE | | | | | | false | null | false | | | |
>>>>>> +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
>>>>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from SYSTEM.FUNCTION;
>>>>>> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
>>>>>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1, column 22.
>>>>>> 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:1280)
>>>>>> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1363)
>>>>>> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1434)
>>>>>> at sqlline.Commands.execute(Commands.java:822)
>>>>>> at sqlline.Commands.sql(Commands.java:732)
>>>>>> at sqlline.SqlLine.dispatch(SqlLine.java:807)
>>>>>> at sqlline.SqlLine.begin(SqlLine.java:681)
>>>>>> at sqlline.SqlLine.start(SqlLine.java:398)
>>>>>> at sqlline.SqlLine.main(SqlLine.java:292)
>>>>>> Caused by: MismatchedTokenException(65!=99)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:360)
>>>>>> at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(PhoenixSQLParser.java:9969)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.identifier(PhoenixSQLParser.java:9936)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.from_table_name(PhoenixSQLParser.java:9589)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(PhoenixSQLParser.java:6244)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixSQLParser.java:6066)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(PhoenixSQLParser.java:6002)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixSQLParser.java:5967)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4595)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(PhoenixSQLParser.java:4697)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(PhoenixSQLParser.java:4763)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:789)
>>>>>> at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:508)
>>>>>> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
>>>>>> ... 9 more
>
Re: Cannot select data from a system table
Posted by Ankit Singhal <an...@gmail.com>.
bq. Is this documented somewhere ?
not as such, https://phoenix.apache.org/language/index.html#quoted_name is
generally for case sensitive identifier(and to allow some special
characters) and same can be used for keywords.
bq. Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would
give us good idea.
Yes Ted, you are right . Phoenix keywords are the tokens in
phoenix-core/src/main/antlr3/PhoenixSQL.g
On Sun, Aug 21, 2016 at 8:33 PM, Ted Yu <yu...@gmail.com> wrote:
> Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would give
> us good idea.
>
> Experts please correct me if I am wrong.
>
> On Sun, Aug 21, 2016 at 7:21 AM, Aaron Molitor <amolitor@splicemachine.com
> > wrote:
>
>> Thanks, Ankit, that worked.
>>
>> And on the heels of Ted's question... Are the reserved words documented
>> (even if just a list) somewhere, I've been looking at this page:
>> http://phoenix.apache.org/language/index.html -- it feels like where I
>> should find a list like that, but I don't see it explicitly called out.
>>
>> -Aaron
>>
>> On Aug 21, 2016, at 09:04, Ted Yu <yu...@gmail.com> wrote:
>>
>> Ankit:
>> Is this documented somewhere ?
>>
>> Thanks
>>
>> On Sun, Aug 21, 2016 at 6:07 AM, Ankit Singhal <an...@gmail.com>
>> wrote:
>>
>>> Aaron,
>>>
>>> you can escape check for reserved keyword with double quotes ""
>>>
>>> SELECT * FROM SYSTEM."FUNCTION"
>>>
>>> Regards,
>>> Ankit Singhal
>>>
>>> On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <
>>> amolitor@splicemachine.com> wrote:
>>>
>>>> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is
>>>> this a known bug?
>>>>
>>>>
>>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
>>>> +------------+--------------+-------------+---------------+-
>>>> ---------+------------+----------------------------+--------
>>>> ---------+--------------+-----------------+---------------+-
>>>> --------------+-----------------+------------+-----------+
>>>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS |
>>>> TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE |
>>>> IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT |
>>>> VIEW_TYPE | INDEX_TYP |
>>>> +------------+--------------+-------------+---------------+-
>>>> ---------+------------+----------------------------+--------
>>>> ---------+--------------+-----------------+---------------+-
>>>> --------------+-----------------+------------+-----------+
>>>> | | SYSTEM | CATALOG | SYSTEM TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | SYSTEM | FUNCTION | SYSTEM TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | SYSTEM | SEQUENCE | SYSTEM TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | SYSTEM | STATS | SYSTEM TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | CUSTOMER | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | LINEITEM | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | NATION | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | ORDERS | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | PART | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | PARTSUPP | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | REGION | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> | | TPCH | SUPPLIER | TABLE | |
>>>> | | | |
>>>> false | null | false | |
>>>> | |
>>>> +------------+--------------+-------------+---------------+-
>>>> ---------+------------+----------------------------+--------
>>>> ---------+--------------+-----------------+---------------+-
>>>> --------------+-----------------+------------+-----------+
>>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from
>>>> SYSTEM.FUNCTION;
>>>> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
>>>> "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
>>>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604
>>>> (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION"
>>>> 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:1280)
>>>> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(Phoe
>>>> nixStatement.java:1363)
>>>> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStat
>>>> ement.java:1434)
>>>> at sqlline.Commands.execute(Commands.java:822)
>>>> at sqlline.Commands.sql(Commands.java:732)
>>>> at sqlline.SqlLine.dispatch(SqlLine.java:807)
>>>> at sqlline.SqlLine.begin(SqlLine.java:681)
>>>> at sqlline.SqlLine.start(SqlLine.java:398)
>>>> at sqlline.SqlLine.main(SqlLine.java:292)
>>>> Caused by: MismatchedTokenException(65!=99)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatc
>>>> hedToken(PhoenixSQLParser.java:360)
>>>> at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.m
>>>> atch(BaseRecognizer.java:115)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(Ph
>>>> oenixSQLParser.java:9969)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.identifier(Phoenix
>>>> SQLParser.java:9936)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.from_table_name(Ph
>>>> oenixSQLParser.java:9589)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(Phoen
>>>> ixSQLParser.java:6244)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixS
>>>> QLParser.java:6066)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(Phoenix
>>>> SQLParser.java:6002)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixS
>>>> QLParser.java:5967)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.single_select(Phoe
>>>> nixSQLParser.java:4595)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(Ph
>>>> oenixSQLParser.java:4697)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(Phoeni
>>>> xSQLParser.java:4763)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(Phoen
>>>> ixSQLParser.java:789)
>>>> at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixS
>>>> QLParser.java:508)
>>>> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.
>>>> java:108)
>>>> ... 9 more
>>>
>>>
>>>
>>
>>
>
Re: Cannot select data from a system table
Posted by Ted Yu <yu...@gmail.com>.
Looks like tokens in phoenix-core/src/main/antlr3/PhoenixSQL.g would give
us good idea.
Experts please correct me if I am wrong.
On Sun, Aug 21, 2016 at 7:21 AM, Aaron Molitor <am...@splicemachine.com>
wrote:
> Thanks, Ankit, that worked.
>
> And on the heels of Ted's question... Are the reserved words documented
> (even if just a list) somewhere, I've been looking at this page:
> http://phoenix.apache.org/language/index.html -- it feels like where I
> should find a list like that, but I don't see it explicitly called out.
>
> -Aaron
>
> On Aug 21, 2016, at 09:04, Ted Yu <yu...@gmail.com> wrote:
>
> Ankit:
> Is this documented somewhere ?
>
> Thanks
>
> On Sun, Aug 21, 2016 at 6:07 AM, Ankit Singhal <an...@gmail.com>
> wrote:
>
>> Aaron,
>>
>> you can escape check for reserved keyword with double quotes ""
>>
>> SELECT * FROM SYSTEM."FUNCTION"
>>
>> Regards,
>> Ankit Singhal
>>
>> On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <
>> amolitor@splicemachine.com> wrote:
>>
>>> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is
>>> this a known bug?
>>>
>>>
>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
>>> +------------+--------------+-------------+---------------+-
>>> ---------+------------+----------------------------+--------
>>> ---------+--------------+-----------------+---------------+-
>>> --------------+-----------------+------------+-----------+
>>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS |
>>> TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE |
>>> IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT |
>>> VIEW_TYPE | INDEX_TYP |
>>> +------------+--------------+-------------+---------------+-
>>> ---------+------------+----------------------------+--------
>>> ---------+--------------+-----------------+---------------+-
>>> --------------+-----------------+------------+-----------+
>>> | | SYSTEM | CATALOG | SYSTEM TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | SYSTEM | FUNCTION | SYSTEM TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | SYSTEM | SEQUENCE | SYSTEM TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | SYSTEM | STATS | SYSTEM TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | CUSTOMER | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | LINEITEM | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | NATION | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | ORDERS | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | PART | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | PARTSUPP | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | REGION | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> | | TPCH | SUPPLIER | TABLE | |
>>> | | | |
>>> false | null | false | |
>>> | |
>>> +------------+--------------+-------------+---------------+-
>>> ---------+------------+----------------------------+--------
>>> ---------+--------------+-----------------+---------------+-
>>> --------------+-----------------+------------+-----------+
>>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from
>>> SYSTEM.FUNCTION;
>>> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
>>> "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
>>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
>>> Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" 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:1280)
>>> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(Phoe
>>> nixStatement.java:1363)
>>> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStat
>>> ement.java:1434)
>>> at sqlline.Commands.execute(Commands.java:822)
>>> at sqlline.Commands.sql(Commands.java:732)
>>> at sqlline.SqlLine.dispatch(SqlLine.java:807)
>>> at sqlline.SqlLine.begin(SqlLine.java:681)
>>> at sqlline.SqlLine.start(SqlLine.java:398)
>>> at sqlline.SqlLine.main(SqlLine.java:292)
>>> Caused by: MismatchedTokenException(65!=99)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatc
>>> hedToken(PhoenixSQLParser.java:360)
>>> at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.m
>>> atch(BaseRecognizer.java:115)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(Ph
>>> oenixSQLParser.java:9969)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.identifier(Phoenix
>>> SQLParser.java:9936)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.from_table_name(Ph
>>> oenixSQLParser.java:9589)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(Phoen
>>> ixSQLParser.java:6244)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixS
>>> QLParser.java:6066)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(Phoenix
>>> SQLParser.java:6002)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixS
>>> QLParser.java:5967)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.single_select(Phoe
>>> nixSQLParser.java:4595)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(Ph
>>> oenixSQLParser.java:4697)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(Phoeni
>>> xSQLParser.java:4763)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(Phoen
>>> ixSQLParser.java:789)
>>> at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixS
>>> QLParser.java:508)
>>> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.
>>> java:108)
>>> ... 9 more
>>
>>
>>
>
>
Re: Cannot select data from a system table
Posted by Aaron Molitor <am...@splicemachine.com>.
Thanks, Ankit, that worked.
And on the heels of Ted's question... Are the reserved words documented (even if just a list) somewhere, I've been looking at this page: http://phoenix.apache.org/language/index.html -- it feels like where I should find a list like that, but I don't see it explicitly called out.
-Aaron
> On Aug 21, 2016, at 09:04, Ted Yu <yu...@gmail.com> wrote:
>
> Ankit:
> Is this documented somewhere ?
>
> Thanks
>
> On Sun, Aug 21, 2016 at 6:07 AM, Ankit Singhal <ankitsinghal59@gmail.com <ma...@gmail.com>> wrote:
> Aaron,
>
> you can escape check for reserved keyword with double quotes ""
>
> SELECT * FROM SYSTEM."FUNCTION"
>
> Regards,
> Ankit Singhal
>
> On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <amolitor@splicemachine.com <ma...@splicemachine.com>> wrote:
> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is this a known bug?
>
>
> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
> +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT | VIEW_TYPE | INDEX_TYP |
> +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
> | | SYSTEM | CATALOG | SYSTEM TABLE | | | | | | false | null | false | | | |
> | | SYSTEM | FUNCTION | SYSTEM TABLE | | | | | | false | null | false | | | |
> | | SYSTEM | SEQUENCE | SYSTEM TABLE | | | | | | false | null | false | | | |
> | | SYSTEM | STATS | SYSTEM TABLE | | | | | | false | null | false | | | |
> | | TPCH | CUSTOMER | TABLE | | | | | | false | null | false | | | |
> | | TPCH | LINEITEM | TABLE | | | | | | false | null | false | | | |
> | | TPCH | NATION | TABLE | | | | | | false | null | false | | | |
> | | TPCH | ORDERS | TABLE | | | | | | false | null | false | | | |
> | | TPCH | PART | TABLE | | | | | | false | null | false | | | |
> | | TPCH | PARTSUPP | TABLE | | | | | | false | null | false | | | |
> | | TPCH | REGION | TABLE | | | | | | false | null | false | | | |
> | | TPCH | SUPPLIER | TABLE | | | | | | false | null | false | | | |
> +------------+--------------+-------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-----------+
> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from SYSTEM.FUNCTION;
> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1, column 22.
> 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:1280)
> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1363)
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1434)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> Caused by: MismatchedTokenException(65!=99)
> at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:360)
> at org.apache.phoenix.shaded.org <http://org.apache.phoenix.shaded.org/>.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
> at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(PhoenixSQLParser.java:9969)
> at org.apache.phoenix.parse.PhoenixSQLParser.identifier(PhoenixSQLParser.java:9936)
> at org.apache.phoenix.parse.PhoenixSQLParser.from_table_name(PhoenixSQLParser.java:9589)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(PhoenixSQLParser.java:6244)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixSQLParser.java:6066)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(PhoenixSQLParser.java:6002)
> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixSQLParser.java:5967)
> at org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4595)
> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(PhoenixSQLParser.java:4697)
> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(PhoenixSQLParser.java:4763)
> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:789)
> at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:508)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
> ... 9 more
>
>
Re: Cannot select data from a system table
Posted by Ted Yu <yu...@gmail.com>.
Ankit:
Is this documented somewhere ?
Thanks
On Sun, Aug 21, 2016 at 6:07 AM, Ankit Singhal <an...@gmail.com>
wrote:
> Aaron,
>
> you can escape check for reserved keyword with double quotes ""
>
> SELECT * FROM SYSTEM."FUNCTION"
>
> Regards,
> Ankit Singhal
>
> On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <
> amolitor@splicemachine.com> wrote:
>
>> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is
>> this a known bug?
>>
>>
>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
>> +------------+--------------+-------------+---------------+-
>> ---------+------------+----------------------------+--------
>> ---------+--------------+-----------------+---------------+-
>> --------------+-----------------+------------+-----------+
>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS |
>> TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE |
>> IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT |
>> VIEW_TYPE | INDEX_TYP |
>> +------------+--------------+-------------+---------------+-
>> ---------+------------+----------------------------+--------
>> ---------+--------------+-----------------+---------------+-
>> --------------+-----------------+------------+-----------+
>> | | SYSTEM | CATALOG | SYSTEM TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | SYSTEM | FUNCTION | SYSTEM TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | SYSTEM | SEQUENCE | SYSTEM TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | SYSTEM | STATS | SYSTEM TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | CUSTOMER | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | LINEITEM | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | NATION | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | ORDERS | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | PART | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | PARTSUPP | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | REGION | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> | | TPCH | SUPPLIER | TABLE | |
>> | | | |
>> false | null | false | |
>> | |
>> +------------+--------------+-------------+---------------+-
>> ---------+------------+----------------------------+--------
>> ---------+--------------+-----------------+---------------+-
>> --------------+-----------------+------------+-----------+
>> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from
>> SYSTEM.FUNCTION;
>> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
>> "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
>> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
>> Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" 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:1280)
>> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(Phoe
>> nixStatement.java:1363)
>> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStat
>> ement.java:1434)
>> at sqlline.Commands.execute(Commands.java:822)
>> at sqlline.Commands.sql(Commands.java:732)
>> at sqlline.SqlLine.dispatch(SqlLine.java:807)
>> at sqlline.SqlLine.begin(SqlLine.java:681)
>> at sqlline.SqlLine.start(SqlLine.java:398)
>> at sqlline.SqlLine.main(SqlLine.java:292)
>> Caused by: MismatchedTokenException(65!=99)
>> at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatc
>> hedToken(PhoenixSQLParser.java:360)
>> at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.m
>> atch(BaseRecognizer.java:115)
>> at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(Ph
>> oenixSQLParser.java:9969)
>> at org.apache.phoenix.parse.PhoenixSQLParser.identifier(Phoenix
>> SQLParser.java:9936)
>> at org.apache.phoenix.parse.PhoenixSQLParser.from_table_name(
>> PhoenixSQLParser.java:9589)
>> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(Phoen
>> ixSQLParser.java:6244)
>> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(PhoenixS
>> QLParser.java:6066)
>> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(Phoenix
>> SQLParser.java:6002)
>> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(PhoenixS
>> QLParser.java:5967)
>> at org.apache.phoenix.parse.PhoenixSQLParser.single_select(
>> PhoenixSQLParser.java:4595)
>> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(
>> PhoenixSQLParser.java:4697)
>> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(Phoeni
>> xSQLParser.java:4763)
>> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(Phoen
>> ixSQLParser.java:789)
>> at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixS
>> QLParser.java:508)
>> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.
>> java:108)
>> ... 9 more
>
>
>
Re: Cannot select data from a system table
Posted by Ankit Singhal <an...@gmail.com>.
Aaron,
you can escape check for reserved keyword with double quotes ""
SELECT * FROM SYSTEM."FUNCTION"
Regards,
Ankit Singhal
On Fri, Aug 19, 2016 at 10:47 PM, Aaron Molitor <am...@splicemachine.com>
wrote:
> Looks like the SYSTEM.FUNCTION table is names with a reserved word. Is
> this a known bug?
>
>
> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> !tables
> +------------+--------------+-------------+---------------+-
> ---------+------------+----------------------------+--------
> ---------+--------------+-----------------+---------------+-
> --------------+-----------------+------------+-----------+
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS |
> TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE |
> IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT | VIEW_STATEMENT |
> VIEW_TYPE | INDEX_TYP |
> +------------+--------------+-------------+---------------+-
> ---------+------------+----------------------------+--------
> ---------+--------------+-----------------+---------------+-
> --------------+-----------------+------------+-----------+
> | | SYSTEM | CATALOG | SYSTEM TABLE | |
> | | | |
> false | null | false | |
> | |
> | | SYSTEM | FUNCTION | SYSTEM TABLE | |
> | | | |
> false | null | false | |
> | |
> | | SYSTEM | SEQUENCE | SYSTEM TABLE | |
> | | | |
> false | null | false | |
> | |
> | | SYSTEM | STATS | SYSTEM TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | CUSTOMER | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | LINEITEM | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | NATION | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | ORDERS | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | PART | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | PARTSUPP | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | REGION | TABLE | |
> | | | |
> false | null | false | |
> | |
> | | TPCH | SUPPLIER | TABLE | |
> | | | |
> false | null | false | |
> | |
> +------------+--------------+-------------+---------------+-
> ---------+------------+----------------------------+--------
> ---------+--------------+-----------------+---------------+-
> --------------+-----------------+------------+-----------+
> 0: jdbc:phoenix:stl-colo-srv073.splicemachine> select * from
> SYSTEM.FUNCTION;
> Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting
> "NAME", got "FUNCTION" at line 1, column 22. (state=42P00,code=604)
> org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00):
> Syntax error. Mismatched input. Expecting "NAME", got "FUNCTION" at line 1,
> column 22.
> 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:1280)
> at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(
> PhoenixStatement.java:1363)
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(
> PhoenixStatement.java:1434)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> Caused by: MismatchedTokenException(65!=99)
> at org.apache.phoenix.parse.PhoenixSQLParser.
> recoverFromMismatchedToken(PhoenixSQLParser.java:360)
> at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.
> match(BaseRecognizer.java:115)
> at org.apache.phoenix.parse.PhoenixSQLParser.parseNoReserved(
> PhoenixSQLParser.java:9969)
> at org.apache.phoenix.parse.PhoenixSQLParser.identifier(
> PhoenixSQLParser.java:9936)
> at org.apache.phoenix.parse.PhoenixSQLParser.from_table_
> name(PhoenixSQLParser.java:9589)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_factor(
> PhoenixSQLParser.java:6244)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_ref(
> PhoenixSQLParser.java:6066)
> at org.apache.phoenix.parse.PhoenixSQLParser.table_list(
> PhoenixSQLParser.java:6002)
> at org.apache.phoenix.parse.PhoenixSQLParser.parseFrom(
> PhoenixSQLParser.java:5967)
> at org.apache.phoenix.parse.PhoenixSQLParser.single_
> select(PhoenixSQLParser.java:4595)
> at org.apache.phoenix.parse.PhoenixSQLParser.unioned_
> selects(PhoenixSQLParser.java:4697)
> at org.apache.phoenix.parse.PhoenixSQLParser.select_node(
> PhoenixSQLParser.java:4763)
> at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(
> PhoenixSQLParser.java:789)
> at org.apache.phoenix.parse.PhoenixSQLParser.statement(
> PhoenixSQLParser.java:508)
> at org.apache.phoenix.parse.SQLParser.parseStatement(
> SQLParser.java:108)
> ... 9 more