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
Symbols ignored for ES_ES_CI_AI collation [CORE1172] #679
Comments
Commented by: Jorge Andres Brugger (jbrugger) INSERT INTO TABLE_A (FIELD_A) VALUES ('A/B'); Seems es_es_ci_ai only considers A-Z and 0-9 as individual characters. |
Modified by: Jorge Andres Brugger (jbrugger)Version: 2.0.0 [ 10091 ] |
Modified by: @dyemanovassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: Sean Leyne (seanleyne) Helen Borrie commented in the Dev List: "What's to fix? It looks like correct behaviour for a case-insensitive COLLATE attribute. If all of the variations of "A" are mapped to "A" then all of the variations will be seen by the parser as "A" regardless of accent, case, etc. Therefore your real problem arises because you constrained the field to be unique. Of course ES_ES "works fine": it is case-sensitive. Store the field using the ES_ES collation, then apply COLLATE ES_ES_CI_AI to any search or ordering clauses where you want a case-insensitive match." |
Commented by: Jorge Andres Brugger (jbrugger) Sean: Please, read what I wrote. I know it?s bad english, sorry for that. Equivalent for es_es_ci_ai, and correct: Equivalent strings for actual es_es_ci_ai, but not correct: Symbols and spaces should not be considered "the same"! Try my sample case, please. |
Commented by: @asfernandes V2.1 has one collation attribute that fix your case, though it will cause somewhat different order. create collation es_es_ci_ai2 for iso8859_1 from es_es_ci_ai 'SPECIALS-FIRST=1'; And use es_es_ci_ai2. |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1 [ 10041 ] |
Commented by: Jorge Andres Brugger (jbrugger) Adriano: CREATE TABLE TABLE_A (FIELD_A VARCHAR(10) COLLATE PT_BR); INSERT INTO TABLE_A (FIELD_A) VALUES ('a-a'); COMMIT WORK; An then try: CREATE TABLE TABLE_B (FIELD_B VARCHAR(10) COLLATE ES_ES_CI_AI); INSERT INTO TABLE_B (FIELD_B) VALUES ('a-a'); COMMIT WORK; Can you see my point? Firebird 2.0 es_es_ci_ai should work as pt_br, or SQL Server es_es_ci_ai |
Commented by: @asfernandes I know this is not expected. But it's not a bug, it's as designed. CI_AI considers only the first level (greater precedence / base letter). PT_BR is a totally different (new) collation that don't inherit this old bad (IMHO) design decisons. |
Commented by: Jorge Andres Brugger (jbrugger) Ok, I now understand you, even when I cant imagine why someone "defines" such buggy collation. |
Commented by: @pcisar "Fix" confirmed for 2.1 Alpha 1. Test added. However, this "fix" have to be properly documented in Release Notes, so I'm going to create a task for you to include a section with potential problems with collations and new possibilities to fix them in 2.1 into documentation for INTL. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @pcisar Reopened to update ticket information. |
Modified by: @pcisarWorkflow: jira [ 11700 ] => Firebird [ 15527 ] |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: Done successfully Test Details: See also other tickets related to collation ES_ES_CI_AI: CORE1384, CORE2361, CORE1174, CORE1178 |
Submitted by: Jorge Andres Brugger (jbrugger)
Is related to CORE1221
Is related to QA74
CREATE TABLE TABLE_A (
FIELD_A VARCHAR(10) COLLATE ES_ES_CI_AI
);
ALTER TABLE TABLE_A ADD CONSTRAINT UNQ1_TABLE_A UNIQUE (FIELD_A);
INSERT INTO TABLE_A (FIELD_A) VALUES ('A');
Next sentences give all "Invalid insert or update value(s): object columns are
constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "UNQ1_TABLE_A" on table "TABLE_A".
INSERT INTO TABLE_A (FIELD_A) VALUES ('A.');
INSERT INTO TABLE_A (FIELD_A) VALUES ('A-');
INSERT INTO TABLE_A (FIELD_A) VALUES ('-A');
INSERT INTO TABLE_A (FIELD_A) VALUES ('(A)');
All symbols are ignored.
es_es collation works fine
====== Test Details ======
See also other tickets related to collation ES_ES_CI_AI: CORE1384, CORE2361, CORE1174, CORE1178
The text was updated successfully, but these errors were encountered: