Hi,

At January 26, 2005, 08:18, Helen Borrie wrote:

> At 09:21 AM 26/01/2005 +0000, you wrote:


>>FB does not catch ambiguous column names in a sub-query. For example,
>>I want find all passengers who are boarding flights on a certain date
>>at a certain point on the route:
>>
>>select PersonnelNo
>>from Passenger P
>>inner join Flight F on P.FltSeqNo = F.FltSeqNo
>>where FltDate = current_date
>>and DepartFrom = ?
>>
>>There is a DepartFrom column in both tables so FB raises an exception.
>>However if I have the same SQL in a sub-query there is no objection:
>>
>>select ...
>>where PersonnelNo in
>>(select PersonnelNo
>>from Passenger P
>>inner join Flight F on P.FltSeqNo = F.FltSeqNo
>>where FltDate = current_date
>>and DepartFrom = ?)
>>
>>A small point but I am converting form InterBase and am getting
>>paranoid about ambiguous column names.

> Rightly so.

> Is this a dialect 1 or dialect 3 database? Fb 1.5.x issues exceptions on
> all ambiguities for dialect 3 database and warnings for some cases in
> dialect 1. Most tools intercept and swallow warnings. AFAIU, in Fb 2, the
> party is over and you will get spanked for all potentially ambiguous
> correlations.

> If you can set up a simple testcase demonstrating this problem on a dialect
> 3 database, it would be important to post it in the bug tracker without too
> much delay. There is probably going to be a Fb 1.5.3...

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

Helen, I'm sure you'll be able to confirm it.

Dave, you could submit the bug using the above query and by specifying
that it is reproducible on Firebird's EMPLOYEE.FDB dialect 3.

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