Bug #43216 Trailing values between 0x00 and the pad char sort incorrectly when using LIMIT

• Submitted: 26 Feb 2009 9:13
• Modified: 26 May 2010 17:51
• Reporter: Lars-Erik Bjørk
• Status: Unsupported
• Category: MySQL Server: Falcon storage engine
• Severity: S3 (Non-critical)
• Version: 6.0.10
• OS: Any
• Assigned to: Lars-Erik Bjørk
• CPU Architecture: Any
• Tags: F_ENCODING, limit

[26 Feb 2009 9:13] Lars-Erik Bjørk

Description:
This bug is related to bug#23692, but happens when using LIMIT.

In a multi segment key in Falcon, each segment is padded to the RUN length with 0x00. When sorting on a field with trailing values between 0x00 and the pad character, this will give the wrong order when applying the LIMIT keyword.

How to repeat:
Create a Falcon table:

create table t1 (f1 CHAR(5), f2 VARCHAR(5), f3 char(20), key(f1), key(f2), key(f1,f2,f3)) engine=falcon;

Insert some values into the table:

insert into t1 values ('B', 'B', 'B');
insert into t1 values ('A', 'A', 'A');
insert into t1 values (0x00000240, 0x00000240, 'oh-oh-two-A');
insert into t1 values (0x00000202, 0x00000202, 'oh-oh-two-two');
insert into t1 values (0x000002, 0x000002, 'oh-oh-two-space');
insert into t1 values (0x0041, 0x0041, 'oh-B');
insert into t1 values (0x0002, 0x0002, 'oh-two');
insert into t1 values (0x0001, 0x0001, 'oh-one');
insert into t1 values (0x00, 0x00, 'oh-space');
insert into t1 values ('', '', 'none');

Select and let the server do the ordering, this is the correct behavior:
select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1; 

Select  and let Falcon do the ordering:
select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1 limit 9; 
Here we can see that row is incorrectly sorted

Another example:
select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1,f2,f3 limit 9 ; 

Suggested fix:
The only solution to this may be to add a pad character to the end of every char and varchar string created by makeKey, in case the string also exists with an appended character < pad character.

[5 Mar 2009 10:57] Kevin Lewis

create table t1 (f1 CHAR(5), f2 VARCHAR(5), f3 char(20), key(f1), key(f2), key(f1,f2,f3)) engine=falcon;
insert into t1 values ('B', 'B', 'B');
insert into t1 values ('A', 'A', 'A');
insert into t1 values (0x00000240, 0x00000240, 'oh-oh-two-A');
insert into t1 values (0x00000202, 0x00000202, 'oh-oh-two-two');
insert into t1 values (0x000002, 0x000002, 'oh-oh-two-space');
insert into t1 values (0x0041, 0x0041, 'oh-B');
insert into t1 values (0x0002, 0x0002, 'oh-two');
insert into t1 values (0x0001, 0x0001, 'oh-one');
insert into t1 values (0x00, 0x00, 'oh-space');
insert into t1 values ('', '', 'none');

mysql> select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1;
+------+----------+------+----------+-----------------+
| f1   | hex(f1)  | f2   | hex(f2)  | f3              |
+------+----------+------+----------+-----------------+
|   ☻☻ | 00000202 |   ☻☻ | 00000202 | oh-oh-two-two   |
|   ☻  | 000002   |   ☻  | 000002   | oh-oh-two-space |
|   ☻@ | 00000240 |   ☻@ | 00000240 | oh-oh-two-A     |
|  ☺   | 0001     |  ☺   | 0001     | oh-one          |
|  ☻   | 0002     |  ☻   | 0002     | oh-two          |
|      | 00       |      | 00       | oh-space        |
|  A   | 0041     |  A   | 0041     | oh-B            |
|      |          |      |          | none            |
| A    | 41       | A    | 41       | A               |
| B    | 42       | B    | 42       | B               |
+------+----------+------+----------+-----------------+
10 rows in set (0.00 sec)

mysql> select f1, hex(f1), f2, hex(f2), f3 from t1 order by f1 limit 9;
+------+----------+------+----------+-----------------+
| f1   | hex(f1)  | f2   | hex(f2)  | f3              |
+------+----------+------+----------+-----------------+
|      | 00       |      | 00       | oh-space        |
|      |          |      |          | none            |
|   ☻  | 000002   |   ☻  | 000002   | oh-oh-two-space |
|   ☻☻ | 00000202 |   ☻☻ | 00000202 | oh-oh-two-two   |
|   ☻@ | 00000240 |   ☻@ | 00000240 | oh-oh-two-A     |
|  ☺   | 0001     |  ☺   | 0001     | oh-one          |
|  ☻   | 0002     |  ☻   | 0002     | oh-two          |
|  A   | 0041     |  A   | 0041     | oh-B            |
| A    | 41       | A    | 41       | A               |
+------+----------+------+----------+-----------------+
9 rows in set (0.01 sec)

[13 Mar 2009 18:40] Kevin Lewis

Needs to be re-triaged. Probably P3, I4

[11 May 2009 18:20] Lars-Erik Bjørk

Should this bug really be a beta bug? It seems like a rare condition. The fix for this will also cause a performance impact for Falcon.