Bug with case insensitive and accent insensitive collation: group by, distinct
and "=" work different, recreate PK "explode"
----------------------------------------------------------------------------------------------------------------------------
Key: CORE-4060
URL: http://tracker.firebirdsql.org/browse/CORE-4060
Project: Firebird Core
Issue Type: Bug
Components: Charsets/Collation
Affects Versions: 2.5.2
Environment: Windows 7 64 Bit
Reporter: Jean-Baptiste Simmen
Priority: Critical
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
[email protected]
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester
Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the
endpoint security space. For insight on selecting the right partner to
tackle endpoint security challenges, access the full report.
http://p.sf.net/sfu/symantec-dev2dev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
