Обсуждение: 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