MySQL Bugs: #5520: Insert statement failing on duplicate key

Bug #5520	Insert statement failing on duplicate key
Submitted:	10 Sep 2004 18:33	Modified:	10 Sep 2004 20:58
Reporter:	Elena Tivey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.20-standard	OS:	pc-linux
Assigned to:		CPU Architecture:	Any

Description:
I have a simple table where Primary Key is one column type CHAR(2).

I'm trying to insert 2 records with the following values for the primary key:

'v@'
'V@'

It does not let me insert second record and the error is the following:

"ERROR 1062: Duplicate entry 'V@' for key 1"

When I convert the value into ascii, it gives me different values as it should:

select ascii('@')  - 64

select char(64) - @

select ascii('V') - 86

select ascii('v') - 118

But then when I try to insert both characters together as 'V@' or 'v@', it recognized them as the same.

I did the test and created a plain table with 2 columns and inserted 'V@' into column t1 and 'v@' into column t2

when I run the query:
   select * from test where t1 = t2

it selected back the row that I just inserted recognizing that both values are the same. 

The reason I need to get this to work is because we are converting certain data from Oracle to MySQL and this problem is not happening on Oracle side but when I bring the data over, I'm not able to insert the record that I need.

How to repeat:
Create table test with one field t1 and primary key on the field

Insert two records:

insert into test values('v@')

insert into test values ('V@') - the second record will fail on duplicate key

Suggested fix:
I'm not sure what would the be fix but it should not recognize V@ as being equal to v@

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Character fields are case insensitive unless you specify them as BINARY.

Create your column as 

  CHAR (2) BINARY PRIMARY KEY 

and all will be as you expecet