[
https://issues.apache.org/jira/browse/PHOENIX-3053?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kevin Liew updated PHOENIX-3053:
--------------------------------
Description:
bq. select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name *AND*
emp.first_name = 'Chuck'
should return the same results as
bq. select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name *WHERE*
emp.first_name = 'Chuck'
but the first query is more performant because it filters before joining.
The first query currently fails with:
{code}
8org.apache.calcite.avatica.proto.Responses$ErrorResponse„
Ãjava.lang.RuntimeException: java.sql.SQLException: ERROR 217 (22017):
Ambiguous or non-equi join condition specified. Consider using table list with
where clause.
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: java.sql.SQLException: ERROR 217 (22017): Ambiguous or non-equi join
condition specified. Consider using table list with where clause.
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:441)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at
org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.throwAmbiguousJoinConditionException(JoinCompiler.java:963)
at
org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:950)
at
org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:873)
at
org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:47)
at
org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
at org.apache.phoenix.parse.AndParseNode.accept(AndParseNode.java:47)
at
org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init>(JoinCompiler.java:459)
at
org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init>(JoinCompiler.java:442)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:197)
at
org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:171)
at org.apache.phoenix.parse.JoinTableNode.accept(JoinTableNode.java:81)
at
org.apache.phoenix.compile.JoinCompiler.compile(JoinCompiler.java:127)
at
org.apache.phoenix.compile.JoinCompiler.optimize(JoinCompiler.java:1161)
at
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:197)
at
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:404)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:378)
at
org.apache.phoenix.jdbc.PhoenixPreparedStatement.getMetaData(PhoenixPreparedStatement.java:223)
at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:701)
... 15 more
{code}
was:
bq. select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name *AND*
emp.first_name = 'Chuck'
should return the same results as
bq. select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name *WHERE*
emp.first_name = 'Chuck'
but the first query is more performant because it filters before joining.
> Allow literal comparison in JOIN...ON
> -------------------------------------
>
> Key: PHOENIX-3053
> URL: https://issues.apache.org/jira/browse/PHOENIX-3053
> Project: Phoenix
> Issue Type: Improvement
> Affects Versions: 4.7.0
> Reporter: Kevin Liew
> Priority: Minor
> Labels: filter, join, sql
>
> bq. select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name *AND*
> emp.first_name = 'Chuck'
> should return the same results as
> bq. select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name
> *WHERE* emp.first_name = 'Chuck'
> but the first query is more performant because it filters before joining.
> The first query currently fails with:
> {code}
> 8org.apache.calcite.avatica.proto.Responses$ErrorResponse„
> Ãjava.lang.RuntimeException: java.sql.SQLException: ERROR 217 (22017):
> Ambiguous or non-equi join condition specified. Consider using table list
> with where clause.
> 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: java.sql.SQLException: ERROR 217 (22017): Ambiguous or non-equi
> join condition specified. Consider using table list with where clause.
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:441)
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.throwAmbiguousJoinConditionException(JoinCompiler.java:963)
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:950)
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:873)
> at
> org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:47)
> at
> org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
> at org.apache.phoenix.parse.AndParseNode.accept(AndParseNode.java:47)
> at
> org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init>(JoinCompiler.java:459)
> at
> org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init>(JoinCompiler.java:442)
> at
> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:197)
> at
> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:171)
> at org.apache.phoenix.parse.JoinTableNode.accept(JoinTableNode.java:81)
> at
> org.apache.phoenix.compile.JoinCompiler.compile(JoinCompiler.java:127)
> at
> org.apache.phoenix.compile.JoinCompiler.optimize(JoinCompiler.java:1161)
> at
> org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:197)
> at
> org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:404)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:378)
> at
> org.apache.phoenix.jdbc.PhoenixPreparedStatement.getMetaData(PhoenixPreparedStatement.java:223)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:701)
> ... 15 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
