Hi Helen!

What you write is very true, but still I consider the main point of
his report to be that the select he has written returns three records
when he has an index on TB3, whereas the very same query returns five
records if there is no index active. Regardless of how good or bad the
SQL might be, the creation of indexes should only affect performance,
and never change which rows are returned? I think he's actually
encountered a bug (provided the results are reproducible, I haven't
tried)...

Set

--- In firebird-support@yahoogroups.com, Helen Borrie wrote:
> At 06:36 AM 18/02/2005 +0000, you wrote:
> >Hello Helen,
> >Thanks for your answer.
> >I agree with your explanation about NULL state. The only thing i
> >don't understand is that i have used the clause 'NOT IN',
>
> Don't try to use "NOT IN...". The potential logic errors could
> curdle the milk!!
>
> Replace this predicate with "WHERE NOT EXISTS(SELECT 1 FROM...".
> Not only will the logic (and any potential illogic) be more obvious,
> but the subquery will use an index if one is available.
>
> Also, be very clear that the logic you dish out in your SQL is
> actually exactly what you plan. There are usually better ways to
> arrive at at the excluded set than to NOT its (apparent) opposite.
> Not is not always not! Only True can be counted on to be true;
> False can be either false or unknown.
>
> Just a request to you, please. Don't use our bug tracker for your
> support questions. That is NOT what the bug tracker is for. This
> is an abuse of the project facilities that makes work for the
> maintainers and a lot of automated traffic in the technical lists.
>
> You should report a bug only after you have received confirmation
> that a bug is a possibility. The route for that is (usually)
> support list first; then, if recommended here, post the bug
> description in Firebird-devel. If you get confirmation there that
> you might be seeing a bug, THEN add it- along with a full test case,
> including data - to the bug tracker.