Обсуждение: LIKE pattern matching

I've updated the LIKE code to make it more SQL9x compliant. I've left in
the "permanent backslash" escape character, but I would like to remove
it now.

Here's why:

Usually, we would want to preserve the backward compatibility for a
release or so. But in this case, we have to choose backward
compatibility or SQL9x compliance. I'd rather move toward compliance and
(in this case) a richer feature set. If I leave in the backslash, then
you can't use SQL9x syntax to specify a pattern match which has a
literal backslash in it. So the "one release grace period" means that we
have one more release which does not support the full SQL92 syntax for
this feature.

If I remove the backslash feature, then instead of matching a literal
percent sign ("%") like this:
 ... 'hi%there' LIKE 'hi\%there' ...

you would write
 ... 'hi%there' LIKE 'hi\%there' ESCAPE '\' ...

or of course you could specify another escape character. afaik there is
no default explicit escape character in SQL99.

Comments?
                      - Thomas

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> you would write

>   ... 'hi%there' LIKE 'hi\%there' ESCAPE '\' ...

> or of course you could specify another escape character. afaik there is
> no default explicit escape character in SQL99.

I thought the agreement was to assume default ESCAPE '\' (or really
ESCAPE '\\', unless you are proposing to break ALL Postgres applications
rather than just all the ones that use LIKE?).

Two points here:

1. I do not think it's acceptable to drop the backslash-quoting behavior
with no notice.

2. It's not clear to me that the SQL default of "no quote character" is
superior to having a default quote character, and therefore I'd actually
argue that we should NEVER go to 100% SQL-and-nothing-but semantics on
this point.
        regards, tom lane

Tom Lane wrote:
> >   ... 'hi%there' LIKE 'hi\%there' ESCAPE '\' ...
> > or of course you could specify another escape character. afaik there is
> > no default explicit escape character in SQL99.
> I thought the agreement was to assume default ESCAPE '\' (or really
> ESCAPE '\\', unless you are proposing to break ALL Postgres applications
> rather than just all the ones that use LIKE?).

No, my proposal *only* affects the internal workings of the LIKE support
code, *not* the other backslashing which happens at the parser. That is
another can of worms as you point out. But...

> 1. I do not think it's acceptable to drop the backslash-quoting behavior
> with no notice.

Not all quoting behavior, as noted above.

> 2. It's not clear to me that the SQL default of "no quote character" is
> superior to having a default quote character, and therefore I'd actually
> argue that we should NEVER go to 100% SQL-and-nothing-but semantics on
> this point.

For the LIKE constructs, this isn't true. And you point out something
interesting which I hadn't noticed: to get the backslash quoting
behavior which was implemented in the LIKE code you actually had to use
*two* backslashes. Yuck.

Anyway, the point is that the effects of this proposed change are
limited to internal LIKE behavior only, *and* will give us richer and
more consistant features. istm that this is to be preferred over some
"halfway there" implementation which isn't exactly backward compatible
and isn't completely standards compliant.

That said, it is trivial to clean up the internal code as I propose but
to *also* support the default backslash (not SQL9x compliant, but what
the heck ;) by simply passing the right parameter to the new "two
argument" like() support routines. That parameter could be set back to
NULL after the next release to get us back to SQL9x compliance.

Oh, and I seem to have not committed the new strings regression test
output, but will do so soon.
                    - Thomas

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> That said, it is trivial to clean up the internal code as I propose but
> to *also* support the default backslash (not SQL9x compliant, but what
> the heck ;) by simply passing the right parameter to the new "two
> argument" like() support routines. That parameter could be set back to
> NULL after the next release to get us back to SQL9x compliance.

Sure.  I'm merely arguing that the default behavior needs to be to treat
backslash as escape by default for at least one more release.  You need
to give people warning and time to update their applications to say
"LIKE ... ESCAPE '\\'", if that's the behavior they want to have going
forward.
        regards, tom lane

> > That said, it is trivial to clean up the internal code as I propose but
> > to *also* support the default backslash (not SQL9x compliant, but what
> > the heck ;) by simply passing the right parameter to the new "two
> > argument" like() support routines. That parameter could be set back to
> > NULL after the next release to get us back to SQL9x compliance.
> Sure.  I'm merely arguing that the default behavior needs to be to treat
> backslash as escape by default for at least one more release.  You need
> to give people warning and time to update their applications to say
> "LIKE ... ESCAPE '\\'", if that's the behavior they want to have going
> forward.

OK. I was worried that leaving in the explicit "escape code" in the
routines will lead to bad behavior wrt both old releases *and* SQL9x.
But providing the default argument while still cleaning up the internal
code probably does The Right Thing.
                    - Thomas