Daniel

OK, I'll submit a bug report.

Dave

--- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...> wrote:
> 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)