Félix González wrote:

> I'm using FB2.0 I've a select like this:
>
> select *
> from cabpedido
> where
> codigo not in (select Codpedido from linalbar)
>
> this select doesn't return any value because the second select returns null
> 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. In FB 1.5 the first format work's fine

I just tested

SELECT * from rdb$database
where rdb$relation_id not in (cast(NULL as integer))

on Firebird 1.5.2 and it didn't return any record. I would believe this
to be the same result as you describe for your Firebird 2.0 query.

What would be the correct result?

Well, NULL means unknown and anything compared to unknown is unknown. Is
rdb$relation_id in (NULL)?

Well, I don't know. Is

rdb$relation_id not in (NULL)?

I still don't know.

Unknown is generally not considered as matching anything in Firebird, so
I think the way you report Firebird to behave to be correct and I
believe Firebird 1.5.2 behaves the same way. If there is a difference
between Firebird 1.5 and Firebird 2.0 in this regard, I would at least
expect it mentioned in the Firebird 2.0 documentation, but is it
different? What version of Firebird 1.5 did you test against? Maybe it
was an early fix that makes your Firebird 1.5 behave differently from my
Firebird 1.5? Do you get any result if you try the exact same query as
I've written above in your 1.5 (I don't have any cabpedido table, but
all databases have an rdb$database table)?

To get the result you want, use NOT EXISTS rather than NOT IN or specify
not null in your where part as you already do.

Set