Re: [HACKERS] Bug in LIKE ?

Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)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 conditions
b >= '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