Subject RE: [firebird-support] FB 1.5.2 Ambiguous Column Name
Author Leyne, Sean

> I can reproduce it very easily in FB 1.5.2 with a Dialect 3
> database(EMPLOYEE.FDB).
> Here's a query to reproduce it:
> select emp_no
> from employee
> where emp_no in (select emp_no
> from employee_project
> join employee
> on (employee.emp_no=employee_project.emp_no)
> where emp_no=138)
> order by emp_no

In dialect 3 this IS considered an ambiguous query!

There are 2 un-aliases references to the employee table, accordingly,
there is ambiguity.

The correct syntax would be

select e1.emp_no
> from employee e1
> where e1.emp_no in (select e2.emp_no
> from employee_project
> join employee e2
> on (e2.emp_no=employee_project.emp_no)
> where e2.emp_no=138)
> order by e1.emp_no

I am 98-99% certain that the parser/lexser logic has been improved in
v2.0 to resolve this, but on the face of it, the example is ambiguous.