Re: [HACKERS] Bug in LIKE ?

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> But we have code in DoMatching that does %% to % already.

No, we don't --- take another look at what it's doing.

If we did make %% mean a literal %, it would be new behavior as far as
DoMatch is concerned.  I have been playing with this issue using 6.4.2,
and find that its behavior is extremely inconsistent (ie buggy):

Given

play=> select * from a;
b
-------
foo
bar
foobar
foobar2
foo%bar
fooxbar
foo.bar
(7 rows)

6.4.2 produces

play=> select * from a where b like 'foo%%bar';
b
-------
foo%bar
(1 row)

which sure looks like it is treating %% as literal %, doesn't it?  But
the selectivity comes from the parser's inserted conditionsb >= 'foo%bar' AND b <= 'foo%bar\377'
which eliminate things that DoMatch would take.  With a little more
poking we find

play=> select * from a where b not like 'foo%%bar';
b
-------
foo
bar
foobar2
(3 rows)

and

play=> select * from a where b like 'foo%%';
b
-------
foo%bar
(1 row)

and

play=> create table pat (p text);
CREATE
play=> insert into pat values ('foo%%bar');
INSERT 1194153 1
play=> select * from a, pat where b like p;
b      |p
-------+--------
foobar |foo%%bar
foo%bar|foo%%bar
fooxbar|foo%%bar
foo.bar|foo%%bar
(4 rows)

In these cases, the parser's range conditions don't mask the underlying
behavior of DoMatch.

Since 6.4.2's behavior with %% is clearly broken and in need of some
kind of fix, I think we should make it work like the standard says,
rather than paint ourselves into a corner we'll want to get out of
someday.  If %% actually worked reliably, people would start relying
on it.  Bad enough that we'll have to keep defaulting to ESCAPE \
for backwards-compatibility reasons; let's not add another deviation
from the spec.

BTW, this is not to discourage you from adding ESCAPE in 6.6 ;-)
        regards, tom lane