Rani Pinchuk (rp@ockham.be) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Bug in select with 'like' and index of two columns
Long Description
As you will see below - when I use index on two columns in the case below, a select with "like" doesn't work correctly.
I checked it on two Linux machines. Postgres was installed there with rpms: On one machine it was:
postgresql-7.0.3-2.i386.rpmand on the other machine it was postgresql-7.0.2-2.i386.rpm.
Both machines are Redhat 6.1 with kernels 2.2.12-20 and 2.2.14.
If you need more information - I will be happy to give it immediately.
So here is the bug:
cat postgres.bug gives:
create table tree ( tree_name varchar(30), path_name varchar(200), id numeric(10,0), sequence_number numeric(10,0)) ;
create index tree_tree_name_path_name on tree (tree_name, path_name) ;
insert into tree(tree_name, path_name, id, sequence_number) values('Demo', '/root', 0, -1) ;
insert into tree(tree_name, path_name, id, sequence_number) values('Demo', '/root/test1', 1, 0) ;
select path_name, id, sequence_number from tree where path_name like '/root/%' and not path_name like '/root/%/%' and
tree_name= 'Dem
o';
select path_name, id, sequence_number from tree where path_name like '/root/%' and not path_name like '/root/%/%';
drop index tree_tree_name_path_name;
select path_name, id, sequence_number from tree where path_name like '/root/%' and not path_name like '/root/%/%' and
tree_name= 'Dem
o';
mikush:/disk1/home/postgres$ createdb bug
CREATE DATABASE
mikush:/disk1/home/postgres$ psql -e bug < postgres.bug
create table tree ( tree_name varchar(30), path_name varchar(200), id numeric(10,0), sequence_number numeric(10,0)) ;
CREATE
create index tree_tree_name_path_name on tree (tree_name, path_name) ;
CREATE
insert into tree(tree_name, path_name, id, sequence_number) values('Demo', '/root', 0, -1) ;
INSERT 71983 1
insert into tree(tree_name, path_name, id, sequence_number) values('Demo', '/root/test1', 1, 0) ;
INSERT 71984 1
select path_name, id, sequence_number from tree where path_name like '/root/%' and not path_name like '/root/%/%' and
tree_name= 'Demo';
path_name | id | sequence_number
-----------+----+-----------------
(0 rows)
select path_name, id, sequence_number from tree where path_name like '/root/%' and not path_name like '/root/%/%';
path_name | id | sequence_number
-------------+----+-----------------
/root/test1 | 1 | 0
(1 row)
drop index tree_tree_name_path_name;
DROP
select path_name, id, sequence_number from tree where path_name like '/root/%' and not path_name like '/root/%/%' and
tree_name= 'Demo';
path_name | id | sequence_number
-------------+----+-----------------
/root/test1 | 1 | 0
(1 row)
If you will look carefully, there first select result is wrong. When I delete the index and run the same select I get
otherresult (the correct one)!
Please your comments.
Thanks a lot.
Rani.
Sample Code
No file was uploaded with this report