Thread: like operator bug

Your name       :   Jeremy Faith
Your email address  :jfaith@cemsys.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)     :Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.0.36

  PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-6.5.1

  Compiler used (example:  gcc 2.8.0)       : gcc 2.7.2.3


Please enter a FULL description of your problem:
------------------------------------------------
The percent charater(%) is not functioning correctly in arguments to the
like(~~) operator. Many matches are being missed.
Also the bug tracking system at
  http://www.PostgreSQL.ORG/bugs/visitor.php3
is not functioning(I reported this like bug about 2 weeks ago slip 72 I
think).
The output I get now starts with
  Warning: PostgresSQL query failed: ERROR: status: Permission denied.
in ./postgres.php3 on line 40.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
create table tst(addr char(4));
insert into tst values('1000');
insert into tst values('1010');
insert into tst values('1120');
select * from tst where addr like '1%0';

produces

addr
----
1120(1 row)

where it should return all of the example rows.


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
In the file ./src/backend/utils/adt/like.c

The function DoMatch appears to have been optimised to return LIKE_ABORT
when
the pattern cannot be matched. However in the above instance it
incorrectly
aborts causing the like function to return FALSE. I think this could be
fixed
by changing the recursuve call from
  (matched=DoMatch(text,p))!=LIKE_FALSE
to
  (matched=DoMatch(text,p))==LIKE_TRUE
at the expense of some speed, there may be a more efficient solution,
but it
needs a fix.
It may be that the aborting concept is flawed.

jfaith <jfaith@cemsys.com> writes:
> create table tst(addr char(4));
> insert into tst values('1000');
> insert into tst values('1010');
> insert into tst values('1120');
> select * from tst where addr like '1%0';
> produces
> addr
> ----
> 1120(1 row)
> where it should return all of the example rows.

You are right.  I think that's my fault :-(.  Will fix it.

> Also the bug tracking system at
>   http://www.PostgreSQL.ORG/bugs/visitor.php3
> is not functioning(I reported this like bug about 2 weeks ago slip 72 I
> think).

We have concluded that reporting bugs via the bugs email list is a
better approach than having people make direct entries into the bug
tracking system.  Theoretically the bug tracking system is for letting
people know what the status of known bugs is ... but I'm not sure if
anyone is maintaining it very diligently :-(.  The website needs to be
updated to explain that you should still submit bugs via pgsql-bugs.

            regards, tom lane

jfaith <jfaith@cemsys.com> writes:
> create table tst(addr char(4));
> insert into tst values('1000');
> insert into tst values('1010');
> insert into tst values('1120');
> select * from tst where addr like '1%0';
> produces
> addr
> ----
> 1120(1 row)
> where it should return all of the example rows.

I believe I have fixed this.  If you need the fix before 6.5.2 is out,
line 188 in src/backend/utils/adt/like.c should return LIKE_FALSE not
LIKE_ABORT:

      if (*text !='\0')
!         return LIKE_ABORT;
      else

      if (*text !='\0')
!         return LIKE_FALSE;
      else

            regards, tom lane