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