Why does Apache Drill select column with the same name of a different table

Question

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'

tobi6 tobi6 · Accepted Answer · 2017-09-01T14:54:49

This seems to be because you use

select dept.name, [...]

But you have never assigned an alias for the table department (department AS dept). Hence

select department.name, [...]

should yield the value you are looking for.

Why does Apache Drill select column with the same name of a different table

1 Answers