Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3053

Allow literal comparison in JOIN...ON

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 4.7.0
    • None
    • None

    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
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            kliew Kevin Liew
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: