From: Lars-Erik Bjørk Date: January 30 2009 1:23pm Subject: NULLs and stuff List-Archive: http://lists.mysql.com/falcon/460 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="Boundary_(ID_xHYLJ5x1ieVb1KG/qSrH+w)" --Boundary_(ID_xHYLJ5x1ieVb1KG/qSrH+w) Content-type: text/plain; delsp=yes; format=flowed; charset=US-ASCII Content-transfer-encoding: 7BIT 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) Being in this area, I picked up http://bugs.mysql.com/bug.php?id=23692 again, which we have discussed on a previous meeting. The idea Kevin proposed here (if I remember correctly) was to append 0x20 to all upper-bound search keys ending in >= 0x20 (Kevin, please correct me if my memory is corrupt). This means passing an extra argument all the way from StorageTable::setIndexBounds down to Index::makeKey, because we don't want to store the extra byte in the regular index keys. How does the rest of you feel about these solutions? I get this hacky feeling:) We have to make sure there is always room for a 0x00 and a 0x20 in the key, meaning that the actual key can no longer be MAX_PHYSICAL_KEY_LENGTH long, but two less, as well as [prep,app]ending 0x00 and 0x20 being pretty magic. Is this something that you are comfortable with? I don't have any alternative solutions:) /Lars-Erik --Boundary_(ID_xHYLJ5x1ieVb1KG/qSrH+w)--