Re: Sigh, LIKE indexing is *still* broken in foreign locales

On Thu, 08 Jun 2000 10:04:11 -0400 Tom Lane wrote:

> The bounds do not have to be perfectly tight, in the sense of being
> the least string >= or largest string <= the desired strings. It's
> OK if we scan a few extra tuples in some cases. But we have to have
> reasonably close bounds or we can't implement LIKE with an index.

Determining the bounding (sub-)strings looks like a very hard problem.

I think there is enough information in a POSIX locale to determine
what the rules for constructing such bounds would be ... but there is
no programatic interface to determine the rules a locale uses for
collation. (I have no idea what non-POSIX systems provide.)

(The localedef program can build a locale. strcoll() and strxfrm()
can use the collation information. That's all I see.)

In the absence of a way to do this "right" we need someone to see a
"good enough" hack that happens to work everywhere, or else give up
using indexes for LIKE which I doubt would please anyone. I suppose
the mismatch comes about because LIKE is about pattern matching and
not collation. :(

Regards,

Giles