From: Jim Starkey
Date: January 31 2009 11:31pm
Subject: Re: NULLs and stuff
List-Archive: http://lists.mysql.com/falcon/472
Message-Id: <4984DF51.8070406@NimbusDB.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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).

This will require kicking up INDEX_CURRENT_VERSION and putting 
appropriate runtime checks to handle new and old versions.  I regret 
that the indx version stuff is a little crocky, but, alas, it was an 
afterthought.

Anyway, this should be valuable experience in rolling upgrades.