[
https://issues.apache.org/jira/browse/DRILL-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14617978#comment-14617978
]
Jinfeng Ni edited comment on DRILL-2053 at 7/8/15 5:05 AM:
-----------------------------------------------------------
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}
query1:
select * from (select n_nationkey as a, n_nationkey as a from T1);
query2:
Select X.A from ( select * from (select n_nationkey as a, n_nationkey as a from
T1)) X;
{code}
That is, any ambiguous column reference, either from * (query1), or indirectly
from * (query2) should be blocked.
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}
was (Author: jni):
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)
