[PHOENIX-3053] Allow literal comparison in JOIN...ON

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 4.7.0
• Labels: filter join sql
• Assignee: Unassigned
• Reporter: Kevin Liew
• Created: 06/Jul/16 20:09
• Updated: 06/Jul/16 20:13

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