Issue Details (XML | Word | Printable)

Key: CORE-4060
Type: Bug Bug
Status: Open Open
Priority: Critical Critical
Assignee: Unassigned
Reporter: Jean-Baptiste Simmen
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Bug with case insensitive and accent insensitive collation: group by, distinct and "=" work different, recreate PK "explode"

Created: 11/Mar/13 09:07 AM   Updated: 30/Mar/13 08:13 AM
Component/s: Charsets/Collation
Affects Version/s: 2.5.2
Fix Version/s: None

Environment: Windows 7 64 Bit


 Description  « Hide
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
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jean-Baptiste Simmen added a comment - 30/Mar/13 08:13 AM
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