Such condition is just a simple ternary logic issue (http://en.wikipedia.org/wiki/Ternary_logic).
Note that "null" doesn't mean "nothing", it means "unknown"!
So there is no assurance that specified value (cabpedido.codigo) doesn't exist in set with _unknown_ values (set of exact values and _nulls_).
"in" operator can be rewriten as "var=value1 or var=value2 or...", so "not in" will be: "var<>value1 and var<>value2 and...". Hence if one of valueN will be null then result of logical expression will be "unknown" and nothing will be returned.
So FB2.0 is absolutely right. And I'm surprised if FB1.5 works different.

Sanya.






________________________________

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Paul Vinkenoog
Sent: Wednesday, January 10, 2007 4:18 PM
To: firebird-support@yahoogroups.com
Subject: Spam(BOD):Re: [firebird-support] it´s a bug?



Hello Félix,

> I´m using FB2.0 I´ve a select like this:
>
> select *
> from cabpedido
> where
> codigo not in (select Codpedido from linalbar)
>
> this select dosen´t return any value because se second select
> retrn´s null values

This is correct, if indeed no value in cabpedido.codigo also occurs in
linalbar.Codpedido

(from your text it's not entirely clear if the subselect returns
ONLY nulls, or nulls and also regular values)

> if I change the select by this
>
> select *
> from cabpedido
> where
> codigo not in (select Codpedido from linalbar where CodPedido is not null)
>
> work´s fine.

That is also correct. If the subselect doesn't return any nulls, the
engine can tell *for sure* if a value from cabpedido.codigo is not
present in the subselect result set.

> In FB 1.5 the first format work´s fine

These queries should work exactly the same in 1.5 and 2.0 -- and in my
experience they do.

Are you sure that the table contents are the same (including nulls
presence) both times? If yes, could you provide a testcase with a
small number of records that gives a different result in 1.5 and 2.0,
and tell me which 1.5 version you use ("plain" 1.5, 1.5.1, 1.5.2, etc.)

If there's a difference, I have to document it. If it's an unfixed
bug, it should be reported.

Greetings,
Paul Vinkenoog

[Non-text portions of this message have been removed]