New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Bug in unique constraints and / or NUMERIC-SORT collation? [CORE3999] #4331
Comments
Commented by: @aafemt Yes, this collation is supposed to work this way. |
Commented by: @asfernandes No, this collation is not supposed to work in this way. But this is a duplicate bug of CORE3947. It's fixed for v3 only. |
Modified by: @asfernandes |
Commented by: Patrick Marten (patrick marten) 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! |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: No test => Done successfully |
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
The text was updated successfully, but these errors were encountered: