Till Haug created DRILL-4757: -------------------------------- Summary: 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
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)