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