RE: Progress report on locale safe LIKE indexing

Hiroshi Inoue writes:

> Hmm * string1 = string2 * doesn't imply * string1 LIKE string2 * ?

In the current implementation of LIKE, you're right. The SQL standard
allows for the possibility that "[d]epending on the collating sequence,
two strings may compare as equal even if they are of different lengths or
contain different sequences of characters." However, I doubt that this
can really happen in practice. For example, in some collating sequences
(such as en_US), characters with diacritic marks (accents) are "more
equal" than others, but in the end there's always a tie breaker. Or do
you know an example where this really happens?

> Otherwise the current criterion of LIKE matching unwittingly assumes
> that there's no locale that has the different definition of '=' from that of
> ASCII locale. I don't think the current implementation is strictly right.

Strictly speaking, it isn't. The SQL standard says that literal
characters in the pattern must be matched to the characters in the
supplied value according to the collating sequence. (See 8.5 GR 3. d) ii)
4).)

However, I strongly doubt that that would actually be a good idea.
Pattern matching generally doesn't work this way (cf. POSIX regexp), and
since locale-aware comparisons are context-sensitive in some cases I don't
even want to think about whether this could actually work when faced with
wildcards.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://funkturm.homeip.net/~peter