drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jinfeng Ni (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-2053) Column names are case sensitive if column is coming from WITH clause
Date Wed, 08 Jul 2015 00:56:04 GMT

    [ https://issues.apache.org/jira/browse/DRILL-2053?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14617761#comment-14617761
] 

Jinfeng Ni commented on DRILL-2053:
-----------------------------------

For the query you suggested,

{code}
select X.A From (SELECT * from (select n_nationkey as a, n_nationkey as A from cp.`tpch/nation.parquet`))
as X;
{code}

I think the right behavior is to raise Sql validation error, since column reference 'a' is
ambiguous. However, the current master branch also has the same behavior.
{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   |
.......
+-----+
25 rows selected (0.225 seconds)
0: jdbc:drill:zk=localhost:5181> select commit_id, commit_message, commit_time from sys.version;
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------------------+
|                 commit_id                 |                                            
  commit_message                                               |        commit_time      
  |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------------------+
| 2506ecf1551bdb9a7dc6dbf950ba2c5c565eb1f4  | DRILL-3450: Moved methods from AbstractStatusReporter
and NonRootStatusReporter to FragmentStatusReporter  | 07.07.2015 @ 17:40:53 UTC  |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+----------------------------+
{code}


Postgres will block similar query, which seems to be the right behavior.

{code}
select X.A From (SELECT * from (select empno as a, ename as A from emp) as Y) as X;
ERROR:  column reference "a" is ambiguous
{code}

Therefore,  it is a different issue in the code that this query is not blocked.



> 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)

Mime
View raw message