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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 | Content-Type | Size |
---|---|---|
likebug.sql | text/plain | 431 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2000-11-29 22:02:05 | Re: Both cross-named & compound foreign key constaints fail |
Previous Message | Tom Lane | 2000-11-29 16:36:15 | Re: Join Problem in Postgres 7.0.2 |