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

LIKE on index bug

From: Vit Pavlik <vpavlik(at)securities(dot)cz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: LIKE on index bug
Date: 2000-11-29 17:55:07
Message-ID: 3A2542FB.C6AF975D@securities.cz (view raw or flat)
Thread:
Lists: pgsql-bugs
I have problem with LIKE operator when index scan is used. When I use
pattern with space just before '%' character (e.g. 'Line %'), I get bad results.

For example:

test=# create table test ( a int, b varchar(20));
CREATE
test=# create index test_b on test(b);
CREATE
test=# create function fill_table () returns int as '
test'# declare
test'#   i int4;
test'# begin
test'#   for i in 1..1000 loop
test'#     insert into test values( i, ''Line ''||i );
test'#   end loop;
test'#   return 1;
test'# end;
test'# ' language 'plpgsql';
CREATE
test=# select fill_table();
 fill_table 
------------
          1
(1 row)

test=# vacuum verbose analyze test;
NOTICE:  --Relation test--
NOTICE:  Pages 7: Changed 7, reaped 0, Empty 0, New 0; Tup 1000: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 46, MaxLen 49;
Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index test_b: Pages 7; Tuples 1000. CPU 0.00s/0.00u sec.
VACUUM
test=# select * from test where b like 'Line 99%';
  a  |    b     
-----+----------
  99 | Line 99
 990 | Line 990
 991 | Line 991
 992 | Line 992
 993 | Line 993
 994 | Line 994
 995 | Line 995
 996 | Line 996
 997 | Line 997
 998 | Line 998
 999 | Line 999
(11 rows)

test=# select * from test where b like 'Line %';
 a | b 
---+---
(0 rows)
*********** This is not correct output 
test=# 


I am running 7.0.3-2 on RedHat 7.0. You can use script attached to reproduce this problem.

Regards

Vit Pavlik
Database engineer
Internet securities
Prague

Attachment: likebug.sql
Description: text/plain (431 bytes)

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2000-11-29 22:02:05
Subject: Re: Both cross-named & compound foreign key constaints fail
Previous:From: Tom LaneDate: 2000-11-29 16:36:15
Subject: Re: Join Problem in Postgres 7.0.2

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