Skip site navigation (1) Skip section navigation (2)

Re: BUG #5478: ILIKE operator returns wrong result

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Markus <markus(dot)herven(at)outpost24(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5478: ILIKE operator returns wrong result
Date: 2010-05-28 14:06:29
Message-ID: 201005281406.o4SE6T208457@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Markus wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:      5478
> Logged by:          Markus
> Email address:      markus(dot)herven(at)outpost24(dot)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(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-05-28 14:13:19
Subject: Re: BUG #5480: Autovacuum interferes with operations (e.g. truncate) on very large databases
Previous:From: Sakari MaaranenDate: 2010-05-28 13:02:57
Subject: BUG #5480: Autovacuum interferes with operations (e.g. truncate) on very large databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group