NULLs are considered 'indeterminant' values, so;
1) You cannot say that one null is equal to another null.
2) You can return all values that 'are null' , and most databases allow
you to say '= null'.
3) You cannot say whether a null is > or < a real value. The ANSI spec
does not say, so most databases, by convention just put all null values
before the real values when sorted ascending and after them when sorting
4) NULL's should not be sorted with the zeros, as we seem to be doing
in our index and as they show up using the LIMIT clause.
Now here is an interesting question... If the first field in a
multi-segmented index is NULL, or has an indeterminant value, how can
you sort the rest of the fields? If you do, you are basically saying
that the NULLs are all equal to each other.
Vladislav Vaintroub wrote:
> I'm quite intrigued by this place in StorageDatabase::makeKey (this one
> translates MySQL key values to Falcon key values during the search).
> It appears that we stop building a key when encountering a null value and
> ignore all following ranges
> Foreach key segment
> if (nullFlag)
> That is, when NULL is given in the first segment, we produce zero-length
> Falcon key
> So how does searching for NULL works in practice?
> How does search range search in multisegment indexes
> lower bound (a = NULL, b = const1)
> upper bound (a = NULL ,b = const2)
> is supposed to work?
> Thanks for the answer!
> My first suspicion is that NULLs are excluded because NULL is encoded the
> same as numeric 0 (0x80 in index) and this lead to wrong answer. But I'd
> prefer to hear a qualified answer on how it was originally designed and
> supposed to work.