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

Bug with case insensitive and accent insensitive collation: group by, distinct and "=" work different, recreate PK "explode" [CORE4060] #4388

Open
firebird-automations opened this issue Mar 11, 2013 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Jean-Baptiste Simmen (jb.simmen)

Hello From Switzerland

Below a script to reproduce the problem.

Best regards and thanks for your work, we are using Firebird since 1995 and are realy happy :-)

JB Simmen

/**************************************

Jean-Baptiste Simmen
mailto:jb.simmen@cse.ch
11.03.2013
+0041 32 387 19 20

We change from WIN1252 collation to WIN1252_CI_AI and had massive problems with
our "city" Table (list of all cities).

Tested with Firebird-2.5.2.26539_0_x64.exe

***************************************/

/* Create Database */
SET SQL DIALECT 3;
CREATE DATABASE '127.0.0.1/3050:C:\cse\DBs\COLLATIONPROBLEM.FDB'
USER 'SYSDBA'
PASSWORD 'masterkey'
PAGE_SIZE = 4096
DEFAULT CHARACTER SET WIN1252;

/* Create new Collation */
create collation WIN1252_CI_AI
for WIN1252
From PXW_INTL
case insensitive
accent insensitive;

/* Set new Collation as Default */
alter character set WIN1252 set default collation WIN1252_CI_AI;

/* Create Table "CITY" */
--DROP TABLE ORT;
Create Table ORT(
LKZ VARCHAR(100) NOT NULL COLLATE WIN1252_CI_AI
,PLZ VARCHAR(100) NOT NULL COLLATE WIN1252_CI_AI
,ORT VARCHAR(100) NOT NULL COLLATE WIN1252_CI_AI
);

/* Add PK */
ALTER TABLE ORT ADD CONSTRAINT XPK_ORT PRIMARY KEY (LKZ, PLZ, ORT);

/* Insert the "same" city 2 times with other notation, all corrects */
Insert Into ORT VALUES('CH', '3053', 'MÜNCHENBUCHSEE'); -- Germany-Notation
Insert Into ORT VALUES('CH', '3053', 'MUNCHENBUCHSEE'); -- Switzerland-French Notation
Insert Into ORT VALUES('CH', '3053', 'MUENCHENBUCHSEE'); -- Germany- and Switzerland Notation (ü can be written ue, ä ae, ...

/* Show records */
Select * from ort;

/* Problem 1 : the three Insert above work, if you drop the PK on ORT an rebuild it: ERROR ! */
Alter Table ort Drop CONSTRAINT XPK_ORT;
ALTER TABLE ORT ADD CONSTRAINT XPK_ORT PRIMARY KEY (LKZ, PLZ, ORT); -- not OK, why 3 insert work and recreating PK "explode" ?

/* Problem 2 : Distinct return two instead one record */
select distinct ort from ort; -- not OK, only MÜNCHENBUCHSEE and MUENCHENBUCHSEE are merged

/* Problem 3 : Group By return three instead one record */
select ort from ort group by ort; -- select distinct return 2 record, group by 3

/* Problem 4 : comparaison with "=" do not work properly - should return 3 for count and not 1 */
select
ort
,(select count(*) from ort o2 where o1.ort=o2.ort) -- Should return 3 and not 1
from ort o1;

/* Problem 5 : MÜNCHENBUCHSEE and MUNCHENBUCHSEE is not treated as the same (probably the same as Problem 2 and 3) */
Delete From ORT Where ORT COLLATE WIN1252 like 'MUN%'; -- Delete MUNCHENBUCHSEE: OK
Select ort From ORT; -- Show the records not deleted
Select distinct ort From ORT; -- is correct, one Record returned
Select ort From ORT Group By ort; -- Should return the same as select distinct

@firebird-automations
Copy link
Collaborator Author

Commented by: Jean-Baptiste Simmen (jb.simmen)

Hello, this Problem is very important for applications using collation

the fact that INSERT give no error ans rebuild PK crash is problematic and it is realy difficult to find the records generate the error

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

No branches or pull requests

1 participant