SELECT DISTINCT not working

Question

Why does this query produce an "Duplicate entry" error?

TRUNCATE parim_firm_tag_names;
INSERT INTO parim_firm_tag_names (firm_tag_name_value) 
    SELECT DISTINCT sona 
    FROM parim_marksona;

Error message:

SQL Error (1062): Duplicate entry '1-??????? ??????' for key 'firm_tag_name_value'

As you can see, firm_tag_name_value has an unique index, I use DISTINCT select and I'm truncating all existing data from tag_names.

What could produce this error?

Answer 1

This could be happening because of different collations defined on both tables parim_firm_tag_names and parim_marksona as string comparisons using distinct may results in different values on case sensitive and case insensitive collation values.

You can check collation of columns using this query:

SHOW FULL COLUMNS FROM parim_marksona;
SHOW FULL COLUMNS FROM parim_firm_tag_names;

To avoid this error, you can convert collation of column sona to the collation of column firm_tag_name_value using COLLATE, while selecting the distinct values from table parim_marksona.

Assuming collation of column firm_tag_name_value as latin1_swedish_cs:

TRUNCATE parim_firm_tag_names;

INSERT INTO parim_firm_tag_names (firm_tag_name_value)
    SELECT DISTINCT sona COLLATE latin1_swedish_cs
    FROM parim_marksona;

This should work without errors.

For more details refer manual Column Character Set and Collation.