[DRILL-2053] Column names are case sensitive if column is coming from WITH clause

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Assignee: Jinfeng Ni
• Reporter: Victoria Markman
• Created: 21/Jan/15 23:31
• Updated: 28/Sep/15 19:10
• Resolved: 09/Jul/15 05:52

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)