Bug in select with 'like' and index of two columns - Mailing list pgsql-bugs

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