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