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.