Kevin, nulls are used by optimizer, and they are not *that* undeterministic.
In a query like
select * from t where a is NULL;
if a is indexed or and if you have multisegment index where a is the first
member, optimizer will ask you for NULL.
Also this condition (a = 1 and b < 0 and c=1), that seemingly has nothing
to do with NULLs, will be translated into range query lower=(1,NULL,1)
upper=(1,0,1) by optimizer , if there is (a,b,c) index.
We have to sort/index them less than any other value and we seem not to do
it. Or I'm missing something.
> -----Original Message-----
> From: Kevin.Lewis@stripped [mailto:Kevin.Lewis@stripped]
> Sent: Wednesday, January 21, 2009 6:31 PM
> To: Vladislav Vaintroub
> Cc: 'FalconDev'
> Subject: Re: search for null values in indexed columns
>
> Vlad,
>
> 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
> descending.
> 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.
>
> Comments, Ann?
>
>
>
> Vladislav Vaintroub wrote:
> > Hi,
> > 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)
> > {
> > <blah>
> > break;
> > }
> > Endforeach
> >
> > 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.
> >
> >
> >
>
> --
> Falcon Storage Engine Mailing List
> For list archives: http://lists.mysql.com/falcon
> To unsubscribe: http://lists.mysql.com/falcon?unsub=1
