BUG #18218: NOT LIKE ANY returns same result as LIKE ANY when array items are wrapped into E''
The following bug has been logged on the website:
Bug reference: 18218
Logged by: Baurzhan Sakhariyev
Email address: baurzhansahariev@gmail.com
PostgreSQL version: 15.0
Operating system: MacOS
Description:
https://www.db-fiddle.com/f/cpNjWRLmQT6UFb2wEo2LoG/0
select 'TextToMatch' like any (array[E'Te\%tch', E'nomatch']); -- true,
correct
select 'TextToMatch' NOT like any (array[E'Te\%tch', E'nomatch']); -- true
but must be false because it's a negated version of the expression above
Please note, that versions without E'' work as expected.
select 'TextToMatch' like any (array['Te\%tch', 'nomatch']); -- false
select 'TextToMatch' NOT like any (array['Te\%tch', 'nomatch']); -- true
Hi, Baurzhan!
I've checked the requests and here's what I think about it.
There is no mistake here.
Here's how I tested
1. select 'TextToMatch' like E'Te\%tch' returns true.
2. select 'TextToMatch' like E'nomatch' returns false.
3. select 'TextToMatch' not like E'Te\%tch' returns false.
4. select 'TextToMatch' not like E'nomatch' returns true.
LIKE ANY 1, 2 equals true.
LIKE NOT ANY 3, 4 equals true.
Maybe you need to use NOT ALL?
Show quoted text
The following bug has been logged on the website:
Bug reference: 18218
Logged by: Baurzhan Sakhariyev
Email address: baurzhansahariev@gmail.com
PostgreSQL version: 15.0
Operating system: MacOS
Description:
select 'TextToMatch' like any (array[E'Te\%tch', E'nomatch']); -- true, correct select 'TextToMatch' NOT like any (array[E'Te\%>tch', E'nomatch']); -- true but must be false because it's a negated version of the expression above
Please note, that versions without E'' work as expected.
select 'TextToMatch' like any (array['Te\%tch', 'nomatch']); -- false select 'TextToMatch' NOT like any (array['Te\%tch', >'nomatch']); -- true
PG Bug reporting form <noreply@postgresql.org> writes:
select 'TextToMatch' like any (array[E'Te\%tch', E'nomatch']); -- true,
correct
select 'TextToMatch' NOT like any (array[E'Te\%tch', E'nomatch']); -- true
but must be false because it's a negated version of the expression above
No, it is not. "NOT LIKE" is the operator to apply in this context,
so the second expression returns true if the test string is NOT LIKE
either of the array elements. You could write
NOT ('TextToMatch' like any (array[E'Te\%tch', E'nomatch']))
to get the behavior you are after. Or write NOT LIKE ALL,
as Orlov suggests.
regards, tom lane
Oh, you right, sorry for the noise and thanks for the clarification!
On Thu, Nov 30, 2023 at 2:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
PG Bug reporting form <noreply@postgresql.org> writes:
select 'TextToMatch' like any (array[E'Te\%tch', E'nomatch']); -- true,
correct
select 'TextToMatch' NOT like any (array[E'Te\%tch', E'nomatch']); --true
but must be false because it's a negated version of the expression above
No, it is not. "NOT LIKE" is the operator to apply in this context,
so the second expression returns true if the test string is NOT LIKE
either of the array elements. You could writeNOT ('TextToMatch' like any (array[E'Te\%tch', E'nomatch']))
to get the behavior you are after. Or write NOT LIKE ALL,
as Orlov suggests.regards, tom lane