Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1222

DatabaseMetaData.getColumnLabel returns null when query has ORDER BY

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.8.0
    • Component/s: None
    • Labels:
      None

      Description

      Background from the thread here: http://mail-archives.apache.org/mod_mbox/calcite-dev/201604.mbox/%3CCAJU9nmgxyR%2BCXPXXjYkSKRL4%3DUvbN4nD1JMCtiFzAofRrT1Pug%40mail.gmail.com%3E

      Kevin Risden wrote:

      I've been integrating Calcite/Avatica into Solr and ran into a case where
      adding an order by or a limit changes the output of
      DatabaseMetaData.getColumnName and getColumnLabel.

      An example is below:

      Behavior 1 (which I think is correct from reading the JDBC DatabaseMetaData
      spec):

      • select e."name" as full_name from "hr"."emps" as e;
      • getColumnName(1) -> name
      • getColumnLabel(1) -> FULL_NAME

      Behavior 2:

      • select e."name" as full_name from "hr"."emps" as e limit 10;
      • getColumnName(1) -> FULL_NAME
      • getColumnLabel(1) -> FULL_NAME

      I was able to reproduce the behavior with the JdbcExample.java file in the
      master branch of Calcite since I wasn't sure if it was my adapter
      originally.

      I started looking at the SqlParserTest and added this test case which
      doesn't error out.

      /**

      • "LIMIT ... OFFSET ..." is the postgres equivalent of SQL:2008
      • "OFFSET ... FETCH". It all maps down to a parse tree that looks like
      • SQL:2008.
        */
        @Test public void testColumnAliasWithAsWithLimit() {
        check(
        "select a as abc from foo order by b, c limit 2 offset 1",
        "SELECT `A` AS `ABC`\n"
        + "FROM `FOO`\n"
        + "ORDER BY `B`, `C`\n"
        + "OFFSET 1 ROWS\n"
        + "FETCH NEXT 2 ROWS ONLY");
        check(
        "select a as abc from foo order by b, c limit 2",
        "SELECT `A` AS `ABC`\n"
        + "FROM `FOO`\n"
        + "ORDER BY `B`, `C`\n"
        + "FETCH NEXT 2 ROWS ONLY");
        check(
        "select a as abc from foo order by b, c offset 1",
        "SELECT `A` AS `ABC`\n"
        + "FROM `FOO`\n"
        + "ORDER BY `B`, `C`\n"
        + "OFFSET 1 ROWS");

      I'm not sure where to look next to try to find this to submit a patch. Let
      me know if you need more information.

      I think this potentially causes some problems down the line when the
      original name or alias is used for ordering and having, but I haven't been
      able to verify this for sure.

        Issue Links

          Activity

          Hide
          risdenk Kevin Risden added a comment -

          I'm not sure if order by and limit are the only two cases that can cause this. These are the two that I observed.

          Show
          risdenk Kevin Risden added a comment - I'm not sure if order by and limit are the only two cases that can cause this. These are the two that I observed.
          Hide
          julianhyde Julian Hyde added a comment -

          I have put a provisional fix here: https://github.com/julianhyde/calcite/tree/1222-order-by-label. Please test it, read the test case, and see if you can add some more tests and/or find some other kinds of queries that are broken.

          Show
          julianhyde Julian Hyde added a comment - I have put a provisional fix here: https://github.com/julianhyde/calcite/tree/1222-order-by-label . Please test it, read the test case, and see if you can add some more tests and/or find some other kinds of queries that are broken.
          Hide
          risdenk Kevin Risden added a comment -

          I pulled down the branch and it fixes the issue I described. The part that confuses me is that ORDER BY fixes LIMIT as well. Its not obvious how that is the case from the changes.

          Show
          risdenk Kevin Risden added a comment - I pulled down the branch and it fixes the issue I described. The part that confuses me is that ORDER BY fixes LIMIT as well. Its not obvious how that is the case from the changes.
          Hide
          julianhyde Julian Hyde added a comment -

          In Calcite, LIMIT and FETCH are just arguments to ORDER BY. Asking for the first N records doesn't make much sense if the records are not sorted. A LIMIT causes a SqlOrderBy over 0 key columns.

          Show
          julianhyde Julian Hyde added a comment - In Calcite, LIMIT and FETCH are just arguments to ORDER BY. Asking for the first N records doesn't make much sense if the records are not sorted. A LIMIT causes a SqlOrderBy over 0 key columns.
          Hide
          risdenk Kevin Risden added a comment -

          Ah that was the piece that I was missing. Thanks I'm going to keep playing and see if there are other queries that don't work.

          Show
          risdenk Kevin Risden added a comment - Ah that was the piece that I was missing. Thanks I'm going to keep playing and see if there are other queries that don't work.
          Hide
          risdenk Kevin Risden added a comment -

          I tested some more queries and no others stood out. I think this change is good.

          Show
          risdenk Kevin Risden added a comment - I tested some more queries and no others stood out. I think this change is good.
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/af315fbe .
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.8.0 (2016-06-13).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.8.0 (2016-06-13).

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              risdenk Kevin Risden
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development