Submitted by: Patrick Marten (patrick marten)

Duplicates CORE3947

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