Skip to content
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

Closed
firebird-automations opened this issue Mar 16, 2007 · 22 comments
Closed

Symbols ignored for ES_ES_CI_AI collation [CORE1172] #679

firebird-automations opened this issue Mar 16, 2007 · 22 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Jorge Andres Brugger (jbrugger)

INSERT INTO TABLE_A (FIELD_A) VALUES ('A/B');
INSERT INTO TABLE_A (FIELD_A) VALUES ('A B'); <- doesn't work (spaces are all threated as symbols)

Seems es_es_ci_ai only considers A-Z and 0-9 as individual characters.

@firebird-automations
Copy link
Collaborator Author

Modified by: Jorge Andres Brugger (jbrugger)

Version: 2.0.0 [ 10091 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

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."

@firebird-automations
Copy link
Collaborator Author

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:
Aa
AA
A?
??

Equivalent strings for actual es_es_ci_ai, but not correct:
a-a
a.a
a/a
/a/a/

Symbols and spaces should not be considered "the same"!

Try my sample case, please.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Jorge Andres Brugger (jbrugger)

Adriano:
I still think it?s a bug for es_es_ci_ai and needs to be fixed. Same example for pt_br (pt "ci_ai" collation) works fine! es_es_ci_ai should be as pt_br. Try:

CREATE TABLE TABLE_A (FIELD_A VARCHAR(10) COLLATE PT_BR);
ALTER TABLE TABLE_A ADD CONSTRAINT UNQ1_TABLE_A UNIQUE (FIELD_A);

INSERT INTO TABLE_A (FIELD_A) VALUES ('a-a');
INSERT INTO TABLE_A (FIELD_A) VALUES ('aa');
INSERT INTO TABLE_A (FIELD_A) VALUES ('a/a');
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);
ALTER TABLE TABLE_B ADD CONSTRAINT UNQ1_TABLE_B UNIQUE (FIELD_B);

INSERT INTO TABLE_B (FIELD_B) VALUES ('a-a');
INSERT INTO TABLE_B (FIELD_B) VALUES ('aa');
INSERT INTO TABLE_B (FIELD_B) VALUES ('a/a');
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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I know this is not expected.
It' one reason to create the new attribute.

But it's not a bug, it's as designed.
Specials character in your collation are considered in the last level (lesser precedence).

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Jorge Andres Brugger (jbrugger)

Ok, I now understand you, even when I cant imagine why someone "defines" such buggy collation.
Could I suggest to include a new default collation in FB 2.1, named "ES_AR", and having the behaviour of pt_br? ( I know I can define it at FB 2.1, but right now es_es_ci_ai es totally useless and buggy - by design - when using symbols, so there is a need to have a real es_es_ci_ai collation, wich could be named es_ar)

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue block progress on CORE1221 [ CORE1221 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue block progress on CORE1221 [ CORE1221 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to CORE1221 [ CORE1221 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Reopened to update ticket information.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Fix Version: 2.1 Alpha 1 [ 10150 ]

Fix Version: 2.1.0 [ 10041 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Reopened [ 4 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA74 [ QA74 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11700 ] => Firebird [ 15527 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: 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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment