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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lou Picciano | 2000-11-24 17:20:29 | Bug in 'bigcheckt' |
Previous Message | Tom Lane | 2000-11-24 01:52:20 | Re: Function COPY |