Hi,

At January 27, 2005, 15:22, Leyne, Sean wrote:

> Daniel,

>> 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!

Then WHY Firebird doesn't generate an ambiguous field exception error.
Sorry for being blunt, but have you tried the example with FB 1.5x?
The header of the employee.fdb does show that it is a dialect 3
database. And, I can reproduce this bug with another dialect 3
database that I develop with. Also, if I take the sub-query and try to
run it by itself, I do get an ambiguous field exception error. As
Dave already mentioned, it seems that ambiguous fields are not checked
properly in sub-queries.

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

And, to show the bug, it is voluntary.

> 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

If an exception would be thrown, then a user(programmer) would most
likely revise the query and make the appropriate changes. But, in this
scenario, there's no exception that is generated.

> 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.

If it can be confirmed fixed in FB 2.0, then maybe enter the bug
report and then mark it as fixed in FB 2.0(just for documentation).
But, if it hasn't been fixed in FB 2.0, then it should be entered as a
bug that needs to be fixed, either FB 1.5.3 and/or FB 2.0.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)