[
https://issues.apache.org/jira/browse/DRILL-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14619357#comment-14619357
]
Aman Sinha commented on DRILL-2053:
-----------------------------------
Patch looks good. +1 .
Regarding the ambiguous column issue...I would think that since Calcite follows
the MySql convention by default, it would be different from Postgres. My main
concern is that if there is no error, then we need to make sure the result is
correct - i.e the column 'a' is not getting swapped with column 'A'.
> 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)
