List:Falcon Storage Engine« Previous MessageNext Message »
From:Vladislav Vaintroub Date:January 21 2009 5:47pm
Subject:RE: search for null values in indexed columns
View as plain text  
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


Thread
Problems with record visibility and how it is computedOlav Sandstaa21 Jan
  • search for null values in indexed columnsVladislav Vaintroub21 Jan
    • Re: search for null values in indexed columnsLars-Erik Bjørk21 Jan
    • Re: search for null values in indexed columnsKevin Lewis21 Jan
      • RE: search for null values in indexed columnsVladislav Vaintroub21 Jan
        • Re: search for null values in indexed columnsKevin Lewis21 Jan
      • Re: search for null values in indexed columnsJames Day23 Jan
  • Re: Problems with record visibility and how it is computedOlav Sandstaa23 Jan
    • Re: Problems with record visibility and how it is computedJim Starkey23 Jan
      • Re: Problems with record visibility and how it is computedKevin Lewis28 Jan
        • Re: Problems with record visibility and how it is computedJim Starkey28 Jan
          • Re: Problems with record visibility and how it is computedKevin Lewis28 Jan
            • Cycle Locking (was Problems with record visibility and how it iscomputed)Jim Starkey28 Jan
            • Re: Problems with record visibility and how it is computedAnn W. Harrison28 Jan
              • Re: Problems with record visibility and how it is computedJim Starkey28 Jan
                • Re: Problems with record visibility and how it is computedAnn W. Harrison28 Jan
                  • Re: Problems with record visibility and how it is computedOlav Sandstaa26 Feb
                    • New Transaction State object (Was: Problems with record visibility andhow it is computed)Olav Sandstaa16 Mar
                      • Re: New Transaction State object (Was: Problems with record visibilityand how it is computed)Kevin Lewis16 Mar
                      • Re: New Transaction State object (Was: Problems with record visibilityand how it is computed)Jim Starkey16 Mar
                        • Re: New Transaction State object (Was: Problems with record visibilityand how it is computed)Olav Sandstaa19 Mar
                          • Re: New Transaction State object (Was: Problems with record visibilityand how it is computed)Jim Starkey19 Mar
                            • RE: New Transaction State object (Was: Problems with record visibilityand how it is computed)Vladislav Vaintroub20 Mar
                            • Re: New Transaction State object (Was: Problems with record visibilityand how it is computed)Olav Sandstaa20 Mar
                              • Re: New Transaction State object (Was: Problems with record visibilityand how it is computed)Jim Starkey20 Mar
          • Re: Problems with record visibility and how it is computedOlav Sandstaa28 Jan
            • Re: Problems with record visibility and how it is computedJim Starkey28 Jan
              • Another Idea for Transaction Lifetime ControlJim Starkey28 Jan
                • Re: Another Idea for Transaction Lifetime ControlJim Starkey29 Jan
              • RE: Problems with record visibility and how it is computedXuekun Hu4 Feb
          • Re: Problems with record visibility and how it is computedAnn W. Harrison28 Jan
  • Quick question on row countsKeith Murphy24 Jan
    • Re: Quick question on row countsJim Starkey25 Jan
      • Re: Quick question on row countsKeith Murphy25 Jan