From: Kevin Lewis Date: October 21 2008 2:56pm Subject: bug#23692 Solutions List-Archive: http://lists.mysql.com/falcon/65 Message-Id: <48FDEDAF.1020304@sun.com> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 Content-Transfer-Encoding: 7BIT >Lars-Erik wrote; > --------------------------------------------------------- > 2. What do you plan to accomplish by next time we meet? > --------------------------------------------------------- > * Continue to work on, and hopefully have a fix for bug#34479 and bug#23692 > - This includes chatting/mailing with Kevin about the possible solutions Lars-Erik, Jim, Ann, Last Friday in our meeting, Jim mentioned that we should compare char and varchar key values as if they have an imaginary trailing blank after them. This will work only if it happens when adding keys to the index in addition to searching the index. Also, it must happen when sorting and searching in DeferredIndexes. For example, if these values are found in the index in the order of what is actually stored, they would be (and currently are) like this; notation [this means the character is trimmed for the index] 0x 43 0x 43 [20] 0x 43 09 0x 43 43 Consider; SELECT hex(a) FROM t1 WHERE a > 0x43; If we implicitly add a 0x20 after the search key and the indexed key, then we might find the 0x43 values, but the next key value will be 43 09, which is less than the current position. In other words, it will seem like the index is out of order. But if we sort the keysin the index as if they contained that trailing space, then they would look like this; notation {this means the character is assumed but missing} 0x 43 09 0x 43 {20} 0x 43 {[20]} 0x 43 43 Now the index keys are in SQL order and can correctly return both SELECT hex(a) FROM t1 WHERE a > 0x43; and SELECT hex(a) FROM t1 WHERE a < 0x43; In Falcon, key comparison is done on a byte by byte manner independent of what type of key it is. That is because all key values, whether number or string, are converted into a form that can be compared byte-wise left to right. This is a great advantage when comparing multi-column indexes of various types because the code is much simpler. The strict field type comparisons happen later when we are searching each record. The solution Jim suggested Friday would necessitate that we determine where in a multicolumn key a char or varchar field starts and ends in order to know where to implicitly add a space character. Unless someone can correct my thinking here, I do not think that implied but not stored spaces in indexes is going to work for us. The only solution to this dilema is to actually add the trailing space character to the index. notation (this means the character is added for correct sorting) 0x 43 09 0x 43 (20) 0x 43 20 0x 43 43 Note that the trainiling space is added only to the index values. Not the record. If we do this, I think it must be done as a selectable feature that can be turned off for 'better' performance. And we need to see what that performance difference is. Kevin