Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2053

Column names are case sensitive if column is coming from WITH clause

    Details

      Description

      test.json

      {"customerid":100,"customername":"AAA"}
      {"customerid":101,"customername":"BBB"}
      {"customerid":102,"customername":"CCC"}
      

      Wrong result:

      0: jdbc:drill:schema=dfs> with a as ( select * from `test.json` ) select * from a, `test.json` b where a.CUSTOMERID = b.CUSTOMERID;
      +------------+--------------+-------------+---------------+
      | customerid | customername | customerid0 | customername0 |
      +------------+--------------+-------------+---------------+
      +------------+--------------+-------------+---------------+
      No rows selected (0.202 seconds)
      

      Correct result, when column name matches the case of the column name in the json file:

      0: jdbc:drill:schema=dfs> with a as ( select * from `test.json` ) select * from a, `test.json` b where a.customerid = b.customerid;
      +------------+--------------+-------------+---------------+
      | customerid | customername | customerid0 | customername0 |
      +------------+--------------+-------------+---------------+
      | 100        | AAA          | 100         | AAA           |
      | 101        | BBB          | 101         | BBB           |
      | 102        | CCC          | 102         | CCC           |
      +------------+--------------+-------------+---------------+
      3 rows selected (0.204 seconds)
      

      Correct result when column does not match case, but is coming directly from the table:

      0: jdbc:drill:schema=dfs> with a as ( select * from `test.json` ) select * from a, `test.json` b where a.customerid = b.customerID;
      +------------+--------------+-------------+---------------+
      | customerid | customername | customerid0 | customername0 |
      +------------+--------------+-------------+---------------+
      | 100        | AAA          | 100         | AAA           |
      | 101        | BBB          | 101         | BBB           |
      | 102        | CCC          | 102         | CCC           |
      +------------+--------------+-------------+---------------+
      3 rows selected (0.197 seconds)
      

      If you change case of a column name that comes from subquery (WITH clause), this is where it goes all wrong:

      0: jdbc:drill:schema=dfs> with a as ( select * from `test.json` ) select * from a, `test.json` b where a.Customerid = b.customerid;
      +------------+--------------+-------------+---------------+
      | customerid | customername | customerid0 | customername0 |
      +------------+--------------+-------------+---------------+
      +------------+--------------+-------------+---------------+
      No rows selected (0.186 seconds)
      

        Attachments

          Activity

            People

            • Assignee:
              jni Jinfeng Ni
              Reporter:
              vicky Victoria Markman
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: