Skip site navigation (1) Skip section navigation (2)

bug in string comparison

From: "kopljan" <kopljan(at)beotel(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: bug in string comparison
Date: 2010-02-27 00:20:24
Message-ID: 4b886548.10a.734e.1809531464@beotel.net (view raw or flat)
Thread:
Lists: pgsql-bugs
Logged by: Michael Kopljan
email: admin(at)navigator-info(dot)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?



Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-02-27 01:33:23
Subject: Re: bug in string comparison
Previous:From: AdministratorDate: 2010-02-27 00:12:10
Subject:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group