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.
