MySQL Bugs: #33190: Falcon: DeferredIndex and IndexKey may sort slightly differently

Bug #33190	Falcon: DeferredIndex and IndexKey may sort slightly differently
Submitted:	12 Dec 2007 23:50	Modified:	15 May 2009 14:15
Reporter:	Kevin Lewis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4 team	OS:	Any
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_INDEX

Description:
Bug#33081 was an assert that prevented the gopher thread or the recover thread from processing the serial log because it found that the index keys were logged in the wrong order.  It was fixed by commenting out the assert.  Falcon would use findInsertionPoint to insert each record from the serial log into the falcon table so the order in the index page would be correct in any case.

This bug is for tracking this now hidden issue.  The symptom is now a debug message indicating that the index entries in the log were out of order.  T

How to repeat:
create table hoo (s3 char(5) character set ucs2) engine=falcon;
insert into hoo (s3) values ('a!'),(null);
create index k on hoo(s3(1));
alter table hoo modify column s3 varbinary(20);

See testcase for falcon_bug_33081.

Suggested fix:
Jim suggests that we make sure the varbinary key value of 0x00 is truncated to length 0 before adding it to the index.  This solution changes the index order in the index page. Needs investigation to determine if the key value of 0x00 should be put into the index page.  In other words, does a varbinary of 0x00 length 1 always need to sort after null values.

Thank you for the bug report. Repeatable on main tree source but not on
team tree source:

c:\dbs>6.0t\bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-team-tree-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database db5;
Query OK, 1 row affected (0.02 sec)

mysql> use db5
Database changed
mysql> create table hoo (s3 char(5) character set ucs2) engine=falcon;
Query OK, 0 rows affected (0.48 sec)

mysql> insert into hoo (s3) values ('a!'),(null);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index k on hoo(s3(1));
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table hoo modify column s3 varbinary(20);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table hoo\G
*************************** 1. row ***************************
       Table: hoo
Create Table: CREATE TABLE `hoo` (
  `s3` varbinary(20) DEFAULT NULL,
  KEY `k` (`s3`(1))
) ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.16 sec)

mysql>

--------------------------------------------------------------------
c:\dbs>6.0\bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database db5;
Query OK, 1 row affected (0.02 sec)

mysql> use db5
Database changed
mysql> create table hoo (s3 char(5) character set ucs2) engine=falcon;
Query OK, 0 rows affected (0.38 sec)

mysql> insert into hoo (s3) values ('a!'),(null);
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index k on hoo(s3(1));
Query OK, 2 rows affected (0.34 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table hoo modify column s3 varbinary(20);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

mysql>

CALL STACK

>	ntdll.dll!7c901230() 	
 	mysqld.exe!Error::error()  + 0x49	C++
 	mysqld.exe!Error::assertionFailed()  + 0x14	C++
 	mysqld.exe!IndexRootPage::indexMerge()  + 0x6ff	C++

Lars-Erik, Another character-set related bug.  This one is more involved but worth looking into.

This bug is similar to Bug#34478 in that the core problem is that DeferredIndex is not sorting key values exactly like Index Key values are sorted with regard to 0x00 characters.  

Lars-Erik, looks like I intended to assign this to you back in June, but only got the comment added.

This is fixed by the patch to bug#34478. The assert is uncommented, and the debug message is removed.

Pushed into 6.0.11

A note has been added to the 6.0.11 changelog: 

Searching for 0x00 in Falcon tables using the VARBINARY column type would fail to return the correct rows. In addition, a crash could be encountered when modifying a column to the VARBINARY type.