Re: BUG #5478: ILIKE operator returns wrong result

Markus wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5478
> Logged by:          Markus
> Email address:      markus.herven@outpost24.com
> PostgreSQL version: PostgreSQL 8.4.
> Operating system:   Ubuntu 10.04
> Description:        ILIKE operator returns wrong result
> Details:
>
> The following query
>
> select 'ba' ilike '%__%';
>
> return true as expected in 8.2 but false in 8.4.

I can confirm the odd behavior in current CVS:

    test=> select 'ba' ilike '%__%';
     ?column?
    ----------
     f
    (1 row)

    test=> select 'ba' like '__';
     ?column?
    ----------
     t
    (1 row)

    test=> select 'ba' like '__%';
     ?column?
    ----------
     t
    (1 row)

    test=> select 'ba' like '%_%';
     ?column?
    ----------
     t
    (1 row)

It seems to be the leading '%' it does not like.  Our docs clearly state
your syntax is recommended:

    LIKE pattern matching always covers the entire string. Therefore, to
    match a sequence anywhere within a string, the pattern must start and
    end with a percent sign.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com