You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Kevin Liew (JIRA)" <ji...@apache.org> on 2016/07/04 22:57:10 UTC

[jira] [Updated] (PHOENIX-3047) `UNION` column cannot be used as the input for an `IN` predicate

     [ https://issues.apache.org/jira/browse/PHOENIX-3047?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kevin Liew updated PHOENIX-3047:
--------------------------------
    Description: 
The following query uses a column-result-set as the input to an `IN` predicate:
{code}select * from emp where emp_id in (select emp_id from emp){code}
and returns results.

The following query uses a union of two column-result-sets as the input to an `IN` predicate:
{code}select * from emp where emp_id in (select emp_id from emp union select emp_id from emp){code}
and fails with an :
{code}
8org.apache.calcite.avatica.proto.Responses$ErrorResponse
Ãjava.lang.RuntimeException: org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 25.
	at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:681)
	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:707)
	at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:208)
	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1193)
	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1164)
	at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:95)
	at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
	at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:124)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
	at org.eclipse.jetty.server.Server.handle(Server.java:499)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
	at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 25.
	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.PhoenixPreparedStatement.<init>(PhoenixPreparedStatement.java:94)
	at org.apache.phoenix.jdbc.PhoenixConnection.prepareStatement(PhoenixConnection.java:723)
	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:689)
	... 15 more
Caused by: MissingTokenException(inserted [@-1,0:0='<missing LPAREN>',<91>,1:24] at emp_id)
	at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:358)
	at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
	at org.apache.phoenix.parse.PhoenixSQLParser.not_expression(PhoenixSQLParser.java:6857)
	at org.apache.phoenix.parse.PhoenixSQLParser.and_expression(PhoenixSQLParser.java:6677)
	at org.apache.phoenix.parse.PhoenixSQLParser.or_expression(PhoenixSQLParser.java:6614)
	at org.apache.phoenix.parse.PhoenixSQLParser.expression(PhoenixSQLParser.java:6579)
	at org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4615)
	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)
	... 20 more
{code}

  was:
The following query uses a column result-set as the input to an `IN` predicate:
{code}select * from emp where emp_id in (select emp_id from emp){code}
and returns results.

The following query uses a union of two result-sets as the input to an `IN` predicate:
{code}select * from emp where emp_id in (select emp_id from emp union select emp_id from emp){code}
and fails with an error:
{code}
8org.apache.calcite.avatica.proto.Responses$ErrorResponse
Ãjava.lang.RuntimeException: org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 25.
	at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:681)
	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:707)
	at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:208)
	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1193)
	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1164)
	at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:95)
	at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
	at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:124)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
	at org.eclipse.jetty.server.Server.handle(Server.java:499)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
	at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 25.
	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.PhoenixPreparedStatement.<init>(PhoenixPreparedStatement.java:94)
	at org.apache.phoenix.jdbc.PhoenixConnection.prepareStatement(PhoenixConnection.java:723)
	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:689)
	... 15 more
Caused by: MissingTokenException(inserted [@-1,0:0='<missing LPAREN>',<91>,1:24] at emp_id)
	at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:358)
	at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
	at org.apache.phoenix.parse.PhoenixSQLParser.not_expression(PhoenixSQLParser.java:6857)
	at org.apache.phoenix.parse.PhoenixSQLParser.and_expression(PhoenixSQLParser.java:6677)
	at org.apache.phoenix.parse.PhoenixSQLParser.or_expression(PhoenixSQLParser.java:6614)
	at org.apache.phoenix.parse.PhoenixSQLParser.expression(PhoenixSQLParser.java:6579)
	at org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4615)
	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)
	... 20 more
{code}


> `UNION` column cannot be used as the input for an `IN` predicate
> ----------------------------------------------------------------
>
>                 Key: PHOENIX-3047
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3047
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.7.0
>            Reporter: Kevin Liew
>            Priority: Minor
>              Labels: in, parser, parsing, union
>
> The following query uses a column-result-set as the input to an `IN` predicate:
> {code}select * from emp where emp_id in (select emp_id from emp){code}
> and returns results.
> The following query uses a union of two column-result-sets as the input to an `IN` predicate:
> {code}select * from emp where emp_id in (select emp_id from emp union select emp_id from emp){code}
> and fails with an :
> {code}
> 8org.apache.calcite.avatica.proto.Responses$ErrorResponse
> Ãjava.lang.RuntimeException: org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 25.
> 	at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:681)
> 	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:707)
> 	at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:208)
> 	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1193)
> 	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1164)
> 	at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:95)
> 	at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
> 	at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:124)
> 	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
> 	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
> 	at org.eclipse.jetty.server.Server.handle(Server.java:499)
> 	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
> 	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
> 	at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
> 	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
> 	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
> 	at java.lang.Thread.run(Thread.java:745)
> Caused by: org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 25.
> 	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.PhoenixPreparedStatement.<init>(PhoenixPreparedStatement.java:94)
> 	at org.apache.phoenix.jdbc.PhoenixConnection.prepareStatement(PhoenixConnection.java:723)
> 	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:689)
> 	... 15 more
> Caused by: MissingTokenException(inserted [@-1,0:0='<missing LPAREN>',<91>,1:24] at emp_id)
> 	at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:358)
> 	at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
> 	at org.apache.phoenix.parse.PhoenixSQLParser.not_expression(PhoenixSQLParser.java:6857)
> 	at org.apache.phoenix.parse.PhoenixSQLParser.and_expression(PhoenixSQLParser.java:6677)
> 	at org.apache.phoenix.parse.PhoenixSQLParser.or_expression(PhoenixSQLParser.java:6614)
> 	at org.apache.phoenix.parse.PhoenixSQLParser.expression(PhoenixSQLParser.java:6579)
> 	at org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4615)
> 	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)
> 	... 20 more
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)