At 00:01 14.06.2003 +0000, you wrote:

>subquery version B:
>--------------------
>select DK.KOMPLET_ID from
>dokument_komplet DK join
>dokument D on
> DK.SYS_FILIA_ID = D.SYS_FILIA_ID AND
> DK.DOKUMENT_ID = D.DOKUMENT_ID AND
> D.DOKUMENT_TYP_ID in (4,3,11,1)
> where DK.SYS_FILIA_ID = 1 and dk.komplet_ID = 181016
>
>0 row(s) fetched (0 row(s) listed).
>Exec time: 0,01 s / Fetch time: 0,00 s
>
>now, when I use the above subqueries in the below query:
>
>select * from komplet where
>komplet_ID = 181016 and
>KOMPLET_ID not in (<subquery here>)
>
>... I'm getting following results:
>
>for subquery A: one record - correct
>for subquery B: no records - not correct
>for subquery C: one record - correct
>
>Why the inconsistency if the subquery in ALL cases returns no records
>if ran separately. Why the fact if I join by full PK/FK matters in
>this case - it should not influence anything except execution time.
>
>What am I missing?

In general, I try to avoid 'not in <subquery>'. Things are improving, so it
may be OK for version 1.5 (though I've never heard of errors relating to
this, just slow execution), but in general one can obtain the same by using
NOT EXISTS. Try changing to

select * from komplet K where
K.komplet_ID = 181016 and
not exists(select 1 from
dokument_komplet DK join
dokument D on
DK.SYS_FILIA_ID = D.SYS_FILIA_ID AND
DK.DOKUMENT_ID = D.DOKUMENT_ID AND
D.DOKUMENT_TYP_ID in (4,3,11,1)
where DK.SYS_FILIA_ID = 1 and dk.komplet_ID = 181016 and DK.KOMPLET_ID =
K.KOMPLET_ID)

Having said that, I do not understand how using NOT IN (<subquery returning
nothing>) could limit the result. It must be an error, either in Firebird,
your execution or my reading of your problem.

Set