Re: like/ilike improvements - Mailing list pgsql-hackers

From Tom Lane
Subject Re: like/ilike improvements
Date
Msg-id 16336.1179860672@sss.pgh.pa.us
Whole thread Raw
In response to Re: like/ilike improvements  (Andrew - Supernews <andrew+nonews@supernews.com>)
Responses Re: like/ilike improvements  (Andrew Dunstan <andrew@dunslane.net>)
Re: like/ilike improvements  (Andrew Dunstan <andrew@dunslane.net>)
List pgsql-hackers
Andrew - Supernews <andrew+nonews@supernews.com> writes:
> On 2007-05-22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If "%" advances by bytes then this will find a spurious match.  The
>> only thing that prevents it is if "B" can't be both a leading and a
>> trailing byte of validly-encoded MB characters.

> Which is (by design) true in UTF8, but is not true of most other
> multibyte charsets.

> The %_ case is also trivially handled in UTF8 by simply ensuring that
> _ doesn't match a non-initial octet. This allows % to advance by bytes
> without danger of losing sync.

Yeah.  It seems we need three comparison functions after all:

1. Single-byte character set: needs NextByte and ByteEq only.

2. Generic multi-byte character set: both % and _ must advance by
characters to ensure we never try an out-of-alignment character
comparison.  But simple character comparison works bytewise given
that.  So primitives are NextChar, NextByte, ByteEq.

3. UTF8: % can advance bytewise.  _ must check it is on a first byte
(else return match failure) and if so do NextChar.  So primitives
are NextChar, NextByte, ByteEq, IsFirstByte.

In no case do we need CharEq.  I'd be inclined to drop ByteEq as a
macro and just use "==", too.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Andrew - Supernews
Date:
Subject: Re: like/ilike improvements
Next
From: mark@mark.mielke.cc
Date:
Subject: Re: like/ilike improvements