Bug in unique constraints and / or NUMERIC-SORT collation?
----------------------------------------------------------
Key: CORE-3999
URL: http://tracker.firebirdsql.org/browse/CORE-3999
Project: Firebird Core
Issue Type: Bug
Components: Charsets/Collation, Engine
Affects Versions: 2.5.2
Reporter: Patrick Marten
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Keep yourself connected to Go Parallel:
INSIGHTS What's next for parallel hardware, programming and related areas?
Interviews and blogs by thought leaders keep you ahead of the curve.
http://goparallel.sourceforge.net
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
