Re: textlike under the LIKE operator for char(n)

2016-05-06 23:17 GMT+09:00 Kevin Grittner <kgrittn(at)gmail(dot)com>:
> On Fri, May 6, 2016 at 8:58 AM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>
>> postgres=# select 'abcd'::char(20) LIKE 'ab%cd';
>> ?column?
>> ----------
>> f
>> (1 row)
>>
>> postgres=# select 'abcd'::char(4) LIKE 'ab%cd';
>> ?column?
>> ----------
>> t
>> (1 row)
>>
>> LIKE operator (that is eventually processed by textlike) considers the
>> padding space of char(n) data type as a part of string.
>
> The SQL standard generally requires this for CHAR(n) columns.
>
>> On the other hands, equal operator ignores the padding space when it
>> compares two strings.
>>
>> postgres=# select 'abcd'::char(20) = 'abcd';
>> ?column?
>> ----------
>> t
>> (1 row)
>>
>> postgres=# select 'abcd'::char(4) = 'abcd';
>> ?column?
>> ----------
>> t
>> (1 row)
>
> The SQL standard specifically requires this exception to the
> general rule.
>
>> Is this behavior as expected? or, bug?
>
> This has been discussed on community lists multiple times in the
> past; you might want to search the archives. I'm not inclined to
> dig through the standard for details on this point again right now,
> but in general the behaviors we provide for CHAR(n) are mandated by
> standard. It would not entirely shock me if there are some corner
> cases where different behavior could be allowed or even more
> correct, but my recollection is that what you have shown is all
> required to work that way.
>
Thanks, I couldn't find out the reason of the behavior shortly.
Requirement by SQL standard is a clear guidance even if it looks
a bit mysterious.

> Generally, I recommend avoiding CHAR(n) columns like the plague.
>
Yep, I agree. I found this matter when I port LIKE operator on GPU,
not a time when some real-life query tried to use char(n).

Best regards,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>