MySQL Bugs: #26803: unique constraints on VARCHAR columns behave different in MyISAM and Falcon

Bug #26803	unique constraints on VARCHAR columns behave different in MyISAM and Falcon
Submitted:	2 Mar 2007 19:15	Modified:	13 Apr 2007 9:04
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
Testcase derived from varchar.inc
---------------------------------

create table t1_myisam (a varchar(10), unique idx (a)) ENGINE=MyISAM;
create table t1_falcon (a varchar(10), unique idx (a)) ENGINE=Falcon;
insert into t1_myisam values ('a   ');
insert into t1_falcon values ('a   ');

insert into t1_myisam values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 'idx'
insert into t1_falcon values ('a ');
    Why do I get here success ?

InnoDB shows the same behaviour like MyISAM.

The manual says in
  http://dev.mysql.com/doc/refman/5.1/en/char.html
For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.

My environment:
- MySQL-5.1-falcon last ChangeSet@1.2461, 2007-02-27
  (compile-pentium-max)
- Linux openSUSE 10.2 (X86-64), Core2Duo (x86 64 Bit)

How to repeat:
Please use my attached testscript ml_falcon1.test
copy it to mysql-test/t
echo "Dummy" > r/.ml_falcon1.result # Produce a dummy file with 
                                    # expected results
./mysql-test-run ml_falcon1

test script

Attachment: ml_falcon1.test (application/octet-stream, text), 638 bytes.

Subsequently, if I update to get rid of the trailing spaces,
I end up with two rows that are exactly identifical,
but there's still no error. And searches fail.

mysql> create table tg (s1 varchar(5),s2 varchar(5)) engine=falcon;
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index itg on tg (s1,s2);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tg values ('y','y'),('y','y');
ERROR 1582 (23000): Duplicate entry 'y-y' for key 'itg'
mysql> insert into tg values ('y ','y'),('y','y');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update tg set s1='y',s2='y';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select hex(s1),hex(s2) from tg;
+---------+---------+
| hex(s1) | hex(s2) |
+---------+---------+
| 79      | 79      |
| 79      | 79      |
+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from tg where s1 = 'y' and s2 = 'y';
+------+------+
| s1   | s2   |
+------+------+
| y    | y    |
+------+------+
1 row in set (0.01 sec)

Falcon is providing charset->coll->strnncoll with two strings that are not equally padded with space to the same length.

MySQLCollation::compare() is used for two Value objects, which are values from records that have not gone through MySQLCollation::makeKey().  If either value has trailing spaces, they need to be stripped in order to make sure that duplicate values are identified.  This is NOT to be done if the collation is a binary collation.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24021

ChangeSet@1.2569, 2007-04-07 16:54:31+02:00, hakank@lu0011.wdf.sap.corp +2 -0
  Test for fixed BUG#26803.

Confirming fix. Added test case falcon_bug_26803.test.

Regards,

Hakan

A note has been added to the 5.2.4 changelog.

This bug report entry has been moved to the 6.0.0 Falcon changelog.