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

Cannot resolve columns aliased to its own name

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.4.0
    • Fix Version/s: 4.9.0
    • Labels:

      Description

      Tableau generates queries that alias a fully-qualified column name to its shortened name.

      Similar to:

      create table test (pk integer primary key);
      select test.pk as pk from test group by pk;
      

      Phoenix reports:

      8org.apache.calcite.avatica.proto.Responses$ErrorResponse¨
      ðjava.lang.RuntimeException: org.apache.phoenix.schema.AmbiguousColumnException: ERROR 502 (42702): Column reference ambiguous or duplicate names. columnName=PK
      	at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:671)
      	at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:695)
      	at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:208)
      	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1157)
      	at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1131)
      	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:127)
      	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.schema.AmbiguousColumnException: ERROR 502 (42702): Column reference ambiguous or duplicate names. columnName=PK
      	at org.apache.phoenix.parse.ParseNodeRewriter.visit(ParseNodeRewriter.java:406)
      	at org.apache.phoenix.compile.StatementNormalizer.visit(StatementNormalizer.java:177)
      	at org.apache.phoenix.compile.StatementNormalizer.visit(StatementNormalizer.java:58)
      	at org.apache.phoenix.parse.ColumnParseNode.accept(ColumnParseNode.java:56)
      	at org.apache.phoenix.parse.ParseNodeRewriter.rewrite(ParseNodeRewriter.java:111)
      	at org.apache.phoenix.compile.StatementNormalizer.normalize(StatementNormalizer.java:107)
      	at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:398)
      	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:689)
      	... 15 more
      
      1. PHOENIX-2930.2.patch
        5 kB
        Maryann Xue
      2. PHOENIX-2930.patch
        6 kB
        Ankit Singhal

        Activity

        Hide
        elserj Josh Elser added a comment -

        Thanks for filing, Kevin.

        Moved this over to Phoenix. The error is coming directly out of Phoenix, not Avatica. Not sure if it's a limitation in Phoenix's parser or a bug in the vendor's version that you're using. That will need to be investigated.

        Show
        elserj Josh Elser added a comment - Thanks for filing, Kevin. Moved this over to Phoenix. The error is coming directly out of Phoenix, not Avatica. Not sure if it's a limitation in Phoenix's parser or a bug in the vendor's version that you're using. That will need to be investigated.
        Hide
        hellstorm Ian Hellstrom added a comment -

        I can confirm this bug on Hortonworks HDP 2.3.2.0 with Phoenix 4.4.0. BI tools such as Tableau often auto-alias columns, so a fix for this would be important. We've tried using the Simba Phoenix ODBC driver (beta), but the same queries also crash in Phoenix (without the driver in-between).

        Show
        hellstorm Ian Hellstrom added a comment - I can confirm this bug on Hortonworks HDP 2.3.2.0 with Phoenix 4.4.0. BI tools such as Tableau often auto-alias columns, so a fix for this would be important. We've tried using the Simba Phoenix ODBC driver (beta), but the same queries also crash in Phoenix (without the driver in-between).
        Hide
        jamestaylor James Taylor added a comment -

        Maryann Xue - would it be possible for you to take a look? I tried this in 4.7.0 and it's still a problem.

        Show
        jamestaylor James Taylor added a comment - Maryann Xue - would it be possible for you to take a look? I tried this in 4.7.0 and it's still a problem.
        Hide
        ankit@apache.org Ankit Singhal added a comment -

        James Taylor, please review(not sure if this is the right way).

        Show
        ankit@apache.org Ankit Singhal added a comment - James Taylor , please review(not sure if this is the right way).
        Hide
        jamestaylor James Taylor added a comment -

        Maryann Xue - would you mind reviewing and committing this? This doesn't look like the right way to do it, but not sure if you have some ideas.

        Show
        jamestaylor James Taylor added a comment - Maryann Xue - would you mind reviewing and committing this? This doesn't look like the right way to do it, but not sure if you have some ideas.
        Hide
        ankit@apache.org Ankit Singhal added a comment -

        Maryann Xue, any idea to fix it in a better way?

        Show
        ankit@apache.org Ankit Singhal added a comment - Maryann Xue , any idea to fix it in a better way?
        Hide
        maryannxue Maryann Xue added a comment -

        Sorry, Ankit Singhal, I somehow missed James Taylor's ping for this issue. I think the fix should be in the resolver instead of in the ParseNodeRewriter or anywhere else. You were getting an AmbiguousColumnException and you wanted the alias to take precedence over the original column name. So looks like we need to build that logic into the ColumnResolver. I'll also take a look to see how to implement it.

        Show
        maryannxue Maryann Xue added a comment - Sorry, Ankit Singhal , I somehow missed James Taylor 's ping for this issue. I think the fix should be in the resolver instead of in the ParseNodeRewriter or anywhere else. You were getting an AmbiguousColumnException and you wanted the alias to take precedence over the original column name. So looks like we need to build that logic into the ColumnResolver. I'll also take a look to see how to implement it.
        Hide
        maryannxue Maryann Xue added a comment -

        Came up with a slightly different patch. Instead of making up a new ColumnParseNode and test equality between the two nodes, it might be better to test if they can be resolved as the same column reference. I'll go ahead and commit it if this patch passes all tests.

        Show
        maryannxue Maryann Xue added a comment - Came up with a slightly different patch. Instead of making up a new ColumnParseNode and test equality between the two nodes, it might be better to test if they can be resolved as the same column reference. I'll go ahead and commit it if this patch passes all tests.
        Hide
        hudson Hudson added a comment -

        FAILURE: Integrated in Jenkins build Phoenix-master #1413 (See https://builds.apache.org/job/Phoenix-master/1413/)
        PHOENIX-2930 Cannot resolve columns aliased to its own name (maryannxue: rev 2aa2d1f5b6cc3c2e71e73982bea58380f24d3f87)

        • (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
        • (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java
        Show
        hudson Hudson added a comment - FAILURE: Integrated in Jenkins build Phoenix-master #1413 (See https://builds.apache.org/job/Phoenix-master/1413/ ) PHOENIX-2930 Cannot resolve columns aliased to its own name (maryannxue: rev 2aa2d1f5b6cc3c2e71e73982bea58380f24d3f87) (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/GroupByCaseIT.java

          People

          • Assignee:
            ankit@apache.org Ankit Singhal
            Reporter:
            kliew Kevin Liew
          • Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development