[ https://issues.apache.org/jira/browse/DRILL-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14617978#comment-14617978 ]
Jinfeng Ni commented on DRILL-2053: ----------------------------------- The ambiguous column issue probably is caused some bug in Drill's forked Calcite, where it's handling the resolution of * column. If we want to blocked the ambiguous column, then both the following queries should be blocked. {code} select * from (select n_nationkey as a, n_nationkey as a from T1); Select X.A from ( select * from (select n_nationkey as a, n_nationkey as a from T1)) X; {code} The two queries you listed are not valid, since the column reference X.A is ambiguous. Postgres has the same behavior: {code} select X.A From (select empno as a, ename as A from emp) X; ERROR: column reference "a" is ambiguous select X.A From (select empno as a, ename as a from emp) X; ERROR: column reference "a" is ambiguous select X.A From (select empno as a, empno as a from emp) X; ERROR: column reference "a" is ambiguous {code} > 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: Aman Sinha > 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)