Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug in select with 'like' and index of two columns
Date: 2000-11-24 14:17:09
Message-ID: 200011241417.eAOEH9v67458@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Rani Pinchuk (rp(at)ockham(dot)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


Responses

pgsql-bugs by date

Next:From: Lou PiccianoDate: 2000-11-24 17:20:29
Subject: Bug in 'bigcheckt'
Previous:From: Tom LaneDate: 2000-11-24 01:52:20
Subject: Re: Function COPY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group