[
https://issues.apache.org/jira/browse/DRILL-4757?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Till Haug updated DRILL-4757:
-----------------------------
Priority: Critical (was: Major)
> Column Name Clash experienced with JDBC
> ---------------------------------------
>
> Key: DRILL-4757
> URL: https://issues.apache.org/jira/browse/DRILL-4757
> Project: Apache Drill
> Issue Type: Bug
> Components: Storage - JDBC
> Affects Versions: 1.6.0, 1.7.0
> Reporter: Till Haug
> Priority: Critical
>
> If there are two columns with the same name in two different tables, there
> seems to be a conflict.
> Example 1:
> select t.emp_no as col_one, d.emp_no as col_two
> from mysqlaws.employees.titles as t, mysqlaws.employees.dept_manager as d
> where t.emp_no = d.emp_no
> Result 1:
> emp_no emp_no0
> 110022 null
> 110022 null
> 110039 null
> ...
> Expected Result 1:
> col_one col_two
> 110022 110022
> 110022 110022
> 110039 110039
> ...
> EXPLAIN PLAN FOR Example 1:
> +------+------+
> | text | json |
> +------+------+
> | 00-00 Screen
> 00-01 Project(col_one=[$0], col_two=[$1])
> 00-02 Project(col_one=[$2], col_two=[$6])
> 00-03 Jdbc(sql=[SELECT *
> FROM `employees`.`titles`
> INNER JOIN `employees`.`dept_manager` ON `titles`.`emp_no` =
> `dept_manager`.`emp_no`])
> | {
> "head" : {
> "version" : 1,
> "generator" : {
> "type" : "ExplainHandler",
> "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
> },
> "graph" : [ {
> "pop" : "jdbc-scan",
> "@id" : 3,
> "sql" : "SELECT *\nFROM `employees`.`titles`\nINNER JOIN
> `employees`.`dept_manager` ON `titles`.`emp_no` = `dept_manager`.`emp_no`",
> "config" : {
> "type" : "jdbc",
> "driver" : "com.mysql.jdbc.Driver",
> "url" :
> "jdbc:mysql://vz-test.cbnbj0e1vrwg.eu-central-1.rds.amazonaws.com:8008",
> "username" : "vz_master",
> "password" : "vzpassword",
> "enabled" : true
> },
> "userName" : "",
> "cost" : 100.0
> }, {
> "pop" : "project",
> "@id" : 2,
> "exprs" : [ {
> "ref" : "`col_one`",
> "expr" : "`from_date`"
> }, {
> "ref" : "`col_two`",
> "expr" : "`from_date0`"
> } ],
> "child" : 3,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 100.0
> }, {
> "pop" : "project",
> "@id" : 1,
> "exprs" : [ {
> "ref" : "`col_one`",
> "expr" : "`col_one`"
> }, {
> "ref" : "`col_two`",
> "expr" : "`col_two`"
> } ],
> "child" : 2,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 100.0
> }, {
> "pop" : "screen",
> "@id" : 0,
> "child" : 1,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 100.0
> } ]
> }
> Example 2:
> select t.from_date as col_one, d.from_date as col_two
> from mysqlaws.employees.titles as t, mysqlaws.employees.dept_manager as d
> where t.emp_no = d.emp_no
> Result 2:
> col_one col_two
> 1985-01-01 null
> 1985-01-01 null
> 1991-10-01 null
> ...
> Expected Result 2:
> col_one col_two
> 1985-01-01 1985-01-01
> 1991-10-01 1985-01-01
> 1991-10-01 1991-10-01
> ...
> In Example 1 there is no rename happening and the col_two is all nulls.
> In Example 2 the rename is happening, but the col_two is still all nulls.
> When we run these queries directly against the databases (both mssql and
> mysql) they work as expected.
> If you'd like to directly reproduce it, feel free to use our server we set up
> with the following storage plugin
> {
> "type": "jdbc",
> "driver": "com.mysql.jdbc.Driver",
> "url":
> "jdbc:mysql://vz-test.cbnbj0e1vrwg.eu-central-1.rds.amazonaws.com:8008",
> "username": "vz_master",
> "password": "vzpassword",
> "enabled": false
> }
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
