--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> Hi Paul!
>
> >Hi all,
> >
> >We're using Firebird 2.1.4 and I can't work out what's going on with the following queries.
>
> (deleted queries returning 42 rows)
>
> >Now the problem is that when I run the following I expect to get NO records returned (ignore
> >the fact the query is pointless in this example):
> >
> >select A.ID
> >from A
> >where not A.ID in (select A.ID from A)
> >and A.ID in
> >(
> > select B.ID
> > from B,C
> > where B.ID=C.BID;
> >)
> >
> >But it still returns 42 records?!?
> >
> >This worked fine in InterBase 7.1 Has anybody got any idea what's going on here?
>
> Not much of an idea what is wrong (excepting that the comma after C.BID isn't legal, but I guess you just copied and pasted rather than show us your real problem), I cannot see how your query could possibly return any rows (well, unless 'not A.ID' somehow manages to negate the ID and that negative ID actually exists, but I don't think Firebird does that). However, I suggest changing the query in two ways:
>
> select A1.ID
> from A A1
> where not (A1.ID in (select A2.ID from A A2))
> and A1.ID in
> (select B1.ID
> from B B1
> join C C1 on B1.ID=C1.BID)
>
> Actually, I'd recommend you to change the query quite a bit more (but the above should not return any rows):
>
> select A1.ID
> from A A1
> left join A A2 on A1.ID = A2.ID
> where A2.ID is null
> and exists (select * from B B1
> join C C1 on B1.ID=C1.BID
> where A1.ID = B1.ID)
>
> HTH,
> Set
>

Well we've figured it out. In InterBase 7.1 this query returned the results we were expecting but apparently the "not in" has changed in Firebird to make it more SQL compliant.

In other words this query's results depend on whether the joining table returned NULLs or not. This is a far better example of what I was talking about:

http://tracker.firebirdsql.org/browse/CORE-2951

The solution for us is to trawl through a few hundred different queries and change our "not in" queries for "not exists" (plus the extra join).

My example was poor and didn't actually show our problem.

Hope this helps clear up what I was trying to ask, and also the solution.

Paul.