[
https://issues.apache.org/jira/browse/DRILL-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14617722#comment-14617722
]
Jinfeng Ni commented on DRILL-2053:
-----------------------------------
[~amansinha100], the query you listed seems to produce the correct result:
{code}
select X.A From (SELECT * from (select n_nationkey as a, n_nationkey as A from
cp.`tpch/nation.parquet`)) as X;
+-----+
| A |
+-----+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
+-----+
25 rows selected (0.184 seconds)
{code}
Also, TestExamples.java has unit testcase for DRILL-1911 and DRILL-1943, which
runs successfully on this patch. I also tried the queries in DRILL-1911 and
DRILL-1943, and the results are expected.
{code}
select employee_id, Employee_id from cp.`employee.json` limit 2;
+--------------+---------------+
| employee_id | Employee_id0 |
+--------------+---------------+
| 1 | 1 |
| 2 | 2 |
+--------------+---------------+
select a1, A1 from `t.json`;
+------+------+
| a1 | A10 |
+------+------+
| aaa | aaa |
| bbb | bbb |
| bbb | bbb |
| eee | eee |
+------+------+
select a1, a1 from `t.json`;
+------+------+
| a1 | a10 |
+------+------+
| aaa | aaa |
| bbb | bbb |
| bbb | bbb |
| eee | eee |
+------+------+
create view myview as select a1, A1 from `t.json`;
Error: PARSE ERROR: Duplicate column name [A1]
{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)
