Bug in select with 'like' and index of two columns

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.rpm and 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 other result (the correct one)!

Please your comments.

Thanks a lot.

Rani.

Sample Code

No file was uploaded with this report