Thread

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

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-24T14:17:09Z

   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
   
   
   

2. Re: Bug in select with 'like' and index of two columns

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-24T17:40:27Z

   pgsql-bugs@postgresql.org writes:
   > As you will see below - when I use index on two columns in the case
   > below, a select with "like" doesn't work correctly.
   
   This looks like a variant of the known problems with LIKE index
   optimization in non-ASCII locales.  What locale settings (environment
   variables) are you running the postmaster with?
   
   It would be useful to see the output of EXPLAIN VERBOSE for the
   failing query, also.
   
   			regards, tom lane