Description
select * FROM emp INNER JOIN addr ON emp.last_name = addr.last_name AND emp.first_name = 'Chuck'
should return the same results as
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:
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