From: Jim Starkey
Date: February 2 2009 4:13pm
Subject: Re: NULLs and stuff
List-Archive: http://lists.mysql.com/falcon/480
Message-Id: <49871BC5.9000301@nimbusdb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Vladislav Vaintroub wrote:
>   
>> -----Original Message-----
>> From: Jim Starkey [mailto:jstarkey@stripped]
>> Sent: Monday, February 02, 2009 3:33 PM
>> To: Lars-Erik Bjørk; FalconDev
>> Subject: Re: NULLs and stuff
>>
>> Lars-Erik Bjørk wrote:
>>     
>>> On Sat, 2009-01-31 at 18:31 -0500, Jim Starkey wrote:
>>>
>>>       
>>>> Lars-Erik Bjørk wrote:
>>>>
>>>>         
>>>>> Hi all!
>>>>>
>>>>> This week I have been looking at
>>>>> http://bugs.mysql.com/bug.php?id=42208 (Falcon's ORDER BY .. LIMIT
>>>>> gives wrong/inconsistent results on NULL values).
>>>>>
>>>>> The problem is that NULL values sort together with numeric zero. I
>>>>> have talked this issue over with Vlad, and have tried several
>>>>> different solutions to make it work for both single-field and
>>>>> multi-field indexes. What seems to work, is for
>>>>>           
>> Index::makeKey(Field
>>     
>>>>> *field, Value *value, int segment, IndexKey *indexKey) to return a
>>>>> zero length key for NULL values, and to prepend 0x00 to all keys
>>>>> starting with 0x00 (to make NULL sort before the empty (string),
>>>>>           
>> and
>>     
>>>>> to make the empty string, now 0x00, sort before the previous 0x00,
>>>>>           
>> now
>>     
>>>>> 0x0000, etc, etc)
>>>>>
>>>>>
>>>>>           
>>>> I think it's a lot simpler than this.  Remember that we do
>>>>         
>> significant
>>     
>>>> violence to numbers while turning them into keys -- turn them into
>>>> double precision and then zapping the sign bit (if positive) or
>>>> complimenting the whole thing (if negative) then truncating trailing
>>>> zeros.  The upshot of this all is that a binary zero is actually
>>>>         
>> stored
>>     
>>>> as 0x80.
>>>>
>>>> The solution is to represent nulls (string and numeric) as one byte
>>>>         
>> of
>>     
>>>> 0x00.  This will collate below everything (though equal to some 56
>>>>         
>> bit
>>     
>>>> negative integer).
>>>>
>>>>
>>>>         
>>> Hmmm, won't NULL still sort strange with strings that are empty or
>>>       
>> 0x00,
>>     
>>> that are not subject to the same well deserved violence?:)
>>>
>>>
>>>       
>> Yes,  null strings and empty strings will be intermingled.  I think we
>> can live with that.
>>     
>
> Hmm, in this case order by (limit?) will probably still return wrong result with respect to NULLs, if empty string  are involved.
>
>   
Vlad, can you construct a scenario where someone needs to do a limit 
with an order on a field with both nulls and empty strings, and cares 
about the actual ordering of null strings and empty strings?  Even 
constructing a scenario where someone cares about the difference between 
null string and empty strings is hard...


-- 
Jim Starkey
President, NimbusDB, Inc.
978 526-1376