I was using fb1.5.0, select Codpedido from linalbar returns null value and
not null values. Return Linalbar.CodPedido that exist in cabpedido.codigo



I´ve tested the same sentence on FB1.5.0 and FB 2.0 and the results are
different



Thanks



_____

De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] En nombre de Paul Vinkenoog
Enviado el: miércoles, 10 de enero de 2007 14:18
Para: firebird-support@yahoogroups.com
Asunto: 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]





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