LIKE on index bug
От | Vit Pavlik |
---|---|
Тема | LIKE on index bug |
Дата | |
Msg-id | 3A2542FB.C6AF975D@securities.cz обсуждение исходный текст |
Список | 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, MaxLen49; 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 Praguecreate table test ( a int, b varchar(20)); create index test_b on test(b); -- -- create function fill_table () returns int as ' declare i int4; begin for i in 1..1000 loop insert into test values( i, ''Line ''||i ); end loop; return 1; end; ' language 'plpgsql'; -- -- select fill_table(); -- -- vacuum verbose analyze test; -- -- select * from test where b like 'Line 99%'; select * from test where b like 'Line %';
В списке pgsql-bugs по дате отправления: