David,

Please review my reply before posting a bug report.


Sean

> -----Original Message-----
> From: davidalbiston [mailto:dalbiston@...]
> Sent: Thursday, January 27, 2005 10:45 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: FB 1.5.2 Ambiguous Column Name
>
>
>
> 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)
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>