Thread: bug in string comparison

bug in string comparison

From
"kopljan"
Date:
Logged by: Michael Kopljan
email: admin@navigator-info.com
PostgreSQL version: 8.4.2
Operating system: Windows 2008 Server
Description: bug in string comparison
Details:

-- Drop
DROP TABLE IF EXISTS "public"."test" ;
-- Definition
CREATE TABLE "public"."test" (
  "id_robe" INTEGER NOT NULL,
  "sifra" VARCHAR(15) NOT NULL,
  "naziv" VARCHAR(50) NOT NULL,
  PRIMARY KEY("id_robe")
) WITH OIDS;

ALTER TABLE "public"."test"
  ALTER COLUMN "id_robe" SET STATISTICS 0;
ALTER TABLE "public"."test"
  ALTER COLUMN "sifra" SET STATISTICS 0;
ALTER TABLE "public"."test"
  ALTER COLUMN "naziv" SET STATISTICS 0;

COMMENT ON COLUMN "public"."test"."id_robe"
IS 'PK';

-- Data
INSERT INTO "public"."test" VALUES(1, 'PDTT18', 'POD TT18')
;
INSERT INTO "public"."test" VALUES(2, 'K-K8-8', 'kutija
840x410x145') ;
INSERT INTO "public"."test" VALUES(3, 'ZDTT25', 'ZID TT25')
;
INSERT INTO "public"."test" VALUES(4, 'ZDW18' , 'ZID W18') ;
INSERT INTO "public"."test" VALUES(5, 'ZDTT18', 'ZID TT18')
;
INSERT INTO "public"."test" VALUES(6, 'ZDOR18', 'ZID OR18')
;
INSERT INTO "public"."test" VALUES(7, 'ZDX18' , 'ZID X18') ;
INSERT INTO "public"."test" VALUES(8, 'ZDW25' , 'ZID W25') ;

-----------
-- QUERY --
-----------

--Query#1 (In PostgreSQL 8.2 returns all rows, but in 8.4
only 2 rows?
Select
  t1.*
From public.test t1
Where t1.sifra BETWEEN '%' AND 'z%' ;

--Query#2 (In PostgreSQL 8.2 returns 0 rows, but in 8.4 only
2 rows?
Select
  t1.*
From public.test t1
Where t1.sifra BETWEEN 'a%' AND 'z%' ;

--Query#3 (In PostgreSQL 8.2 returns all rows and in 8.4
returns all rows!
Select
  t1.*
From public.test t1
Where t1.sifra BETWEEN '%' AND 'zz%' ;

-- BUG
-- In field "sifra" data is stored in UPPER CASE, why
query#1 don't return
all data in 8.4?
-- In field "sifra" data is stored in UPPER CASE, why
query#2 return 2 rows
in 8.4?
-- Why, in query#3, must for second parameter of BETWEEN
operator use "zz%"
instead "z%" (that work on 8.2) to get all rows?

Re: bug in string comparison

From
Tom Lane
Date:
"kopljan" <kopljan@beotel.net> writes:
> Description: bug in string comparison

What this sounds like is you are using two different locale settings
in the 8.2 and 8.4 installations.  Postgres' string comparisons follow
whatever the rules of the locale are.  Try "show lc_collate" to see
what the setting is.

In 8.2 the locale is determined at initdb time, but in 8.4 you can
set it when creating a database.

            regards, tom lane

Re: bug in string comparison

From
"Kopljan Michael"
Date:
Yes, Tom, that is problem! Thanks a lot!



Michael Kopljan, Main developer
Development sector
Info Technologies DOO
Narodnih Heroja 7
11000 Beograd, Srbija
Tel: +381 (0) 62 270 834
http://www.navigator-info.com/

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 27, 2010 2:33 AM
To: kopljan@beotel.net
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] bug in string comparison

"kopljan" <kopljan@beotel.net> writes:
> Description: bug in string comparison

What this sounds like is you are using two different locale settings
in the 8.2 and 8.4 installations.  Postgres' string comparisons follow
whatever the rules of the locale are.  Try "show lc_collate" to see
what the setting is.

In 8.2 the locale is determined at initdb time, but in 8.4 you can
set it when creating a database.

            regards, tom lane

__________ Information from ESET Smart Security, version of virus signature
database 4902 (20100228) __________

The message was checked by ESET Smart Security.

http://www.eset.com