MySQL Bugs: #36868: UNIQUE contraint reports violation when with VARCHAR field

Bug #36868	UNIQUE contraint reports violation when with VARCHAR field
Submitted:	22 May 2008 1:17	Modified:	23 May 2008 18:30
Reporter:	Jeff Mouroux	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.051b-community-nt	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I am creating a table as follows (the column names have been changed but the
structure is the same):

CREATE TABLE test(
  field1 INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  field2 INTEGER UNSIGNED NOT NULL,
  field3 VARCHAR(255) NOT NULL,
  UNIQUE (field2, field3)
);

When I try to insert data into the table, I generate a unique constraint violation if the text entered differs only by case:

INSERT INTO test VALUES (1, 1, 'group');  //works fine
INSERT INTO test VALUES (1, 1, 'Group');  //FAILS

I'm migrating data from a Sybase database (where this works properly and, in
many cases, the text data varies only by case) to MySQL. 

How to repeat:
Simply create the table using the SQL in the description.

Then, use the INSERT statements in the order specified in the description.
The first insert will proceed normally.  The second insert will generate
the following error:
Duplicate entry '1-Group' for key 2: Error number 1062

Correction on the second insert statement:
INSERT INTO test VALUES (2, 1, 'Group');

Thank you for a problem report. Please, send the results of:

show variables like 'coll%';

from the testing environment.

The output of the query requested is:

Variable_name, Value
'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'

Thank you for the feedback.

> 'collation_database', 'latin1_swedish_ci'

You use case insensitive collation, so it is expected what 'Group' is considered equal of 'group'.

So I close the report as "Not a Bug". Please use case ensitive (*_cs) or binary collation.