Issue Details (XML | Word | Printable)

Key: CORE-3999
Type: Bug Bug
Status: Closed Closed
Resolution: Duplicate
Priority: Major Major
Assignee: Unassigned
Reporter: Patrick Marten
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Bug in unique constraints and / or NUMERIC-SORT collation?

Created: 28/Nov/12 10:26 AM   Updated: 28/Nov/12 12:26 PM
Component/s: Charsets/Collation, Engine
Affects Version/s: 2.5.2
Fix Version/s: None

Time Tracking:
Not Specified

Issue Links:
Duplicate
 

Planning Status: Unspecified


 Description  « Hide
Hello,

I'm pumping data from a FB 2.1.4 database into a FB 2.5.2 database. During the process I've discovered something, which looks like a bug to me.

There is a table PRODUCTS, one of the columns is PRODUCTNO defined as VARCHAR(100) COLLATE UNICODE_NUM_CI_AI

The collation gets created as follows:
CREATE COLLATION UNICODE_NUM_CI_AI FOR UTF8 FROM UNICODE_CI_AI 'NUMERIC-SORT=1';

The table has an unique constraint:
ALTER TABLE PRODUCTS ADD CONSTRAINT UK_PRODUCTS UNIQUE (PRODUCTNO);

When pumping data, it fails at some point because of the violation of PRIMARY or UNIQUE KEY constraint.

The table does not have any duplicate values, I've checked the FB 2.1.4 database several times now.

As a test I've dropped the unique constraint now. When I look into the "new" table, several records are recognized as duplicates.

A query like

select * from PRODUCTS where PRODUCTNO = 'S01'

returns two records and the values of the column PRODUCTNO are "S01" and "S1".


A query like

select * from PRODUCTS where PRODUCTNO = 'W0008017480'

returns two records and the values of the column PRODUCTNO are "W0008017480" and "W008017480".

And so on... the first zero seems to get ignored or something like that...

It's probably because of the numeric-sort collation. Is this a bug or is this collations supposed to work like that?

Best regards,
Patrick

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 28/Nov/12 10:32 AM
Yes, this collation is supposed to work this way.

Adriano dos Santos Fernandes added a comment - 28/Nov/12 10:35 AM
No, this collation is not supposed to work in this way.

But this is a duplicate bug of CORE-3947. It's fixed for v3 only.

Patrick Marten added a comment - 28/Nov/12 11:00 AM - edited
Sorry, I did a search before posting, but somehow I didn't find the linked issue...

Good to hear that it's not supposed to work that way and is fixed already. Bummer, that it's fixed for v3 only... I was happy to finally have a way to sort such columns in a proper way... Was surprised that it had such effects on select statements and unique constraints as I would expect it to have effects on sorting only...

Thanks for the info!