Hi Kunal/Hielke/Zelaine, I can confirm reproducing a similar case and have described it in DRILL-5713<https://issues.apache.org/jira/browse/DRILL-5713> and DRILL-4211<https://issues.apache.org/jira/browse/DRILL-4211> and have provided code to reproduce. Looks similar to DRILL-4374<https://issues.apache.org/jira/browse/DRILL-4374> .The issue turned out to be two pronged:
1. Drill does not handle ambiguous column names correctly in some cases as Zelaine said DRILL-5713<https://issues.apache.org/jira/browse/DRILL-5713> 2. The Jdbc storage plugin does not correctly push your select statement down to your Jdbc database in some cases. It breaks it up into pieces and executes some parts in your Jdbc store and other parts in Drill incorrectly. DRILL-4211<https://issues.apache.org/jira/browse/DRILL-4211> - Fixing DRILL-5713<https://issues.apache.org/jira/browse/DRILL-5713> is tough and would require major changes to Drill based on my discussions with Paul and Jinfeng. - Fixing DRILL-4211<https://issues.apache.org/jira/browse/DRILL-4211> is easier and would be sufficient for resolving your issue. Current status of the fix is that I tried and failed to fix DRILL-4211<https://issues.apache.org/jira/browse/DRILL-4211>. I tabled it and it's currently prioritized for me to work on a few weeks from now. I'm happy to hand it off if someone is available to work on it sooner though :). Thanks, Tim ________________________________ From: Kunal Khatua <[email protected]> Sent: Monday, August 21, 2017 3:22:29 PM To: [email protected] Subject: RE: Drill selects column with the same name of a different table 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. A quick way to validate this would be to have the similar data as 2 text/parquet tables and have Drill read from that. If we don't see an issue, then it is most likely a storage plugin bug, for which you should file a JIRA. -----Original Message----- From: Hielke Hoeve [mailto:[email protected]] Sent: Monday, August 21, 2017 7:05 AM To: [email protected] Subject: Drill selects column with the same name of a different table 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'
