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', saysing
that values not returned from this sub-select should be select in the
main one... Then having this records with or without NULL should not
change the result of the sub select, since no record was expected to
be returned due to the 'where' used.
And why is the result different when the index is removed from tab3 ?
I have fixed this problem by adding a ' and date_maj not null' in the
sub-select, but that does not sound logic that for 2 sub-select
returning the same number of records (0), the main select displays 2
different results.
I have designed the BUD DB very quickly : i wanted to use a simple
one to demonstrate the case (my one are too big and make no sense for
a test).But i will follow your recommendation... and read about keys !

Thanks for your support

Best regards

Olivier

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 10:29 PM 17/02/2005 +0000, Olivier Lucaes wrote:
>
> >I not sure if this is really a bug or a logic error from my side.
>
> It is a logic error from your side. To be more exact, it's a gap

in your

> understanding of NULL, i.e. your assumption that a test for "<

aValue" will

> return the row if aValue is null. It doesn't.
>
> The [theoretical] "Firebird Newbie T-shirt" is imprinted with the

legend

> "NULL is a state, not a value." :-)
>
> When *anything* is compared with NULL, the result returned will be
> False. So, it is by design that your query does not return rows

where the

> search rule finds null.
>
> The value in a column can have two states: NULL or NOT NULL. The

test for

> state is "WHERE....IS NULL" (or "WHERE ....IS NOT NULL").
>
> Your query expresses interest only in rows that are in the NOT NULL
> state. You will never get a match between your search rule and a
> null. Even "null = null" returns False. Two nulls are never

equal. They

> are "two values in an unknown state".
>
> If you want to get those null rows, add an extra search rule to

your WHERE

> clause "OR AVALUE IS NULL".
>
> Looking at your metadata, I see another source of misapprehension.

You are

> using Paradox-style hierarchical keys, presumably in the belief

that, like

> Paradox, Firebird implements referential integrity by maintaining
> hierarchical indexes. It doesn't. Research the referential

constraint

> FOREIGN KEY in any of the SQL tutorials that you can Google on the

Web.

>
> Hierarchical indexes in Firebird can actually cause problems for

the query

> optimizer, resulting in poorly-performing queries. The issues

involved in

> the switch from Paradox to Firebird are too many to deal with in a

single

> thread, but it's really important to part from that synaptic path

that has

> given rise to the instinct to implement relationships with

hierarchical keys.

>
> ./heLen