Re: BUG #18560: Inconsistent Behavior of PostgreSQL 'LIKE' Operator

On Wed, Jul 31, 2024 at 5:20 PM Aleksander Alekseev
<aleksander(at)timescale(dot)com> wrote:
>
> Hi,
>
> > In sql1, the result of the (" like ") query returns true. In sql2 (c1 like
> > c1) the return value is false when c1 is ".
> > I think this is contradictory and there may be some bug between the 'like'
> > and the query column.
>
> Actually the result is consistent:
>
> ```
> SELECT c1, c1 = ' ' FROM t0;
> c1 | ?column?
> ----+----------
> 1 | f
> | t
> (2 rows)
>
> select ' '::char(1) like ' '::char(1);
> ?column?
> ----------
> f
> (1 row)
> ```
>
> Although I find it puzzling too that LIKE operators for CHAR(1) and
> TEXT work differently. Not 100% sure if this is intended.
>
> Thoughts?

The reason for *select ' '::char(1) like ' '::char(1)* returns false is
that the pattern got trimmed(see rtrim1) to length 0, and the first ' '::char(1)
has length 1, so it doesn't match.

But for *select ' '::text like ' '::text*, the pattern is not trimmed.

The rtrim1 function is in a file named oracle_compat.c, can anybody
verify how oracle behaves in this case?

>
> --
> Best regards,
> Aleksander Alekseev
>
>

--
Regards
Junwang Zhao