Hi Hielke, This is indeed a bug. It's captured by https://issues.apache.org/jira/browse/DRILL-5713 . You can try a workaround by changing your query to use a column alias like below. But that workaround probably will not work until this bug is fixed https://issues.apache.org/jira/browse/DRILL-4211.
select employee.name as employeename, employee.salary, department.name from employee inner join department on employee.dept = department.id where department.name = 'A' The ETA for fixing DRILL-4211 so that at least a work around is available is a few weeks (possibly longer), unless someone else is available to fix it sooner š Thanks, Tim ________________________________ From: Hielke Hoeve <[email protected]> Sent: Wednesday, August 30, 2017 5:08:24 AM To: [email protected] Subject: Re: Drill selects column with the same name of a different table Hi all, Apologies for the late response, I had some issues with subscribing to this mailinglist but I succeeded! Also thank you for your replies, I've put all responses to you questions below as I am unable to reply to each of them separately. > At times if during the join if the columns names are same the drill suffix > zero for second column name like in your case it could be name ,name0, > Can you please do a select * on join tables and check the column headers ? Running select employee.name, employee.salary, department.name from employee inner join department on employee.dept = department.id where department.name = 'A' returns | name | salary | name0 | |------|-------------|-----------| | A | 100 | null | | A | 75 | null | | A | 120 | null | Executing the second query gives me null in 'name' and the employees name in 'name0'. (which is still wrong but given this bug it is logical since the join is reversed.) > Could you share the profile ( *.sys.drill file or the > http://<hostname>:8047/profiles/<queryId>.json) ? > This might be a bug with the JDBC Storage plugin. I am unable to find this file or table I have no access to the .json file, the person who is able is on a 2 week holiday :( For now I have a workaround but it is a bit tedious). I am certain it is a JDBC plugin bug as I copied the data from the postgresql tables to parquet files and a query on the parquet files gives me the correct results !!! :-) | name | salary | name0 | |---------|----------|-----------| | U | 100 | A | | V | 75 | A | | W | 12 | A | Regards, Hielke > From: Hielke Hoeve <[email protected]> > Subject: Drill selects column with the same name of a different table > Date: 21 August 2017 at 16:04:30 GMT+2 > To: [email protected] > > Hi everyone. I just started using Drill/Zeppelin and Iām facing a strange > problem. I have a PostgreSQL database linked to a Drill instance. Whenever I > am trying to join 2 tables which both have a column name and whenever I want > to select this name Drill selects the wrong name column. What am I doing > wrong? > > Given the following 2 tables: > > Department > | id | name | > |----|------| > | 1 | A | > | 2 | B | > > Employee > | id | name | dept | salary | > |----|------|------|--------| > | 1 | U | 1 | 100 | > | 2 | V | 1 | 75 | > | 3 | W | 1 | 120 | > | 4 | X | 2 | 95 | > | 5 | Y | 2 | 140 | > | 6 | Z | 2 | 55 | > > Running > > select employee.name, employee.salary > from employee > inner join department on employee.dept = department.id > where department.name = 'A' > > returns > > | name | salary | > |------|--------| > | A | 100 | > | A | 75 | > | A | 120 | > > Running > > select dept.name, employee.salary > from employee > inner join department on employee.dept = department.id > where department.name = 'A' > > returns > > | name | salary | > |------|--------| > | null | 100 | > | null | 75 | > | null | 120 | > > > What does work, but seems very silly to me, is: > > select dept.name, employee.salary > from employee > inner join (select id, name as deptname from department) as department on > employee.dept = department.id > where department.deptname = 'A' > > >
