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:
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Kevin Lewis CPU Architecture:Any

[2 Mar 2007 19:15] Matthias Leich
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
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
- 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
[2 Mar 2007 19:23] Matthias Leich
test script

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

[26 Mar 2007 22:51] Peter Gulutzan
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)
[4 Apr 2007 15:33] Kevin Lewis
Falcon is providing charset->coll->strnncoll with two strings that are not equally padded with space to the same length.
[5 Apr 2007 3:57] Kevin Lewis
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.
[7 Apr 2007 14:54] Bugs System
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:


ChangeSet@1.2569, 2007-04-07 16:54:31+02:00, hakank@lu0011.wdf.sap.corp +2 -0
  Test for fixed BUG#26803.
[7 Apr 2007 14:55] Hakan Küçükyılmaz
Confirming fix. Added test case falcon_bug_26803.test.


[13 Apr 2007 9:04] MC Brown
A note has been added to the 5.2.4 changelog.
[10 Jul 2007 19:09] MC Brown
This bug report entry has been moved to the 6.0.0 Falcon changelog.