[ https://issues.apache.org/jira/browse/DRILL-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14619947#comment-14619947 ]
Jinfeng Ni commented on DRILL-2053: ----------------------------------- Calcite not only let a|A in case-insensitive parsing pass, but also let a|a pass. The root cause of problem seems to be that Calcite always tries to first resolve the naming conflicts (rename the second a into a0, etc). > Column names are case sensitive if column is coming from WITH clause > -------------------------------------------------------------------- > > Key: DRILL-2053 > URL: https://issues.apache.org/jira/browse/DRILL-2053 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 0.8.0 > Reporter: Victoria Markman > Assignee: Jinfeng Ni > Priority: Critical > Fix For: 1.2.0 > > Attachments: > 0003-DRILL-2053-Fix-incorrect-query-result-when-join-CTE-.patch > > > test.json > {code} > {"customerid":100,"customername":"AAA"} > {"customerid":101,"customername":"BBB"} > {"customerid":102,"customername":"CCC"} > {code} > Wrong result: > {code} > 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) > {code} > Correct result, when column name matches the case of the column name in the > json file: > {code} > 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) > {code} > Correct result when column does not match case, but is coming directly from > the table: > {code} > 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) > {code} > If you change case of a column name that comes from subquery (WITH clause), > this is where it goes all wrong: > {code} > 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) > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)