BUG #13289: ANY() function produces a paradox

udtelco@gmail.com writes:

select ('2' != any('{2,3}')) , ('2' = any('{2,3}'))

When != (or <>) operator is applied to any() , results are wrong. Thereby,
you have a situation where x = y AND x != y both evaluating as true.

Looks fine to me. '2' is equal to at least one of the array elements
(namely '2'), and it's also not equal to at least one of the array
elements (namely '3').

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

udtelco@gmail.com wrote:

The following bug has been logged on the website:

Bug reference: 13289
Logged by: Anton
Email address: udtelco@gmail.com
PostgreSQL version: 9.3.2
Operating system: Solaris 11.2
Description:

select ('2' != any('{2,3}')) , ('2' = any('{2,3}'))

When != (or <>) operator is applied to any() , results are wrong. Thereby,
you have a situation where x = y AND x != y both evaluating as true.

There is no paradox here. When you specify "op ANY array", the operator is
applied to each element in the array and the whole construct returns
true if the operator yields true with any of the array elements. 2 != 3
therefore 2 != any ('{2,3}') is true.

Maybe you want != ALL instead of != ANY. With != ALL, the element would
be compared to every element and the whole construct would yield true
only if all the operations yield true.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Thu, May 14, 2015 at 10:12 AM, <udtelco@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 13289
Logged by: Anton
Email address: udtelco@gmail.com
PostgreSQL version: 9.3.2
Operating system: Solaris 11.2
Description:

select ('2' != any('{2,3}')) , ('2' = any('{2,3}'))

When != (or <>) operator is applied to any() , results are wrong. Thereby,
you have a situation where x = y AND x != y both evaluating as true.

​​As Tom said.​

​And what is "y" in your argument?​

This is shorthand for:

('2' != '2' OR '2' != '3')​, ('2' = '2' OR '2' = '3')

​​If you define "y" as the expression "any('{2,3}')" then it is not even of
the same type as the scalar '2' so a literal comparison is undefined.
Therefore, PostgreSQL treats it as syntactic sugar for the compound boolean
expression (x op y[1] OR x op y[2] OR ...)

David J.

On Thu, May 14, 2015 at 10:01 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

udtelco@gmail.com wrote:

The following bug has been logged on the website:

Bug reference: 13289
Logged by: Anton
Email address: udtelco@gmail.com
PostgreSQL version: 9.3.2
Operating system: Solaris 11.2
Description:

select ('2' != any('{2,3}')) , ('2' = any('{2,3}'))

When != (or <>) operator is applied to any() , results are wrong.

Thereby,

you have a situation where x = y AND x != y both evaluating as true.

There is no paradox here. When you specify "op ANY array", the operator is
applied to each element in the array and the whole construct returns
true if the operator yields true with any of the array elements. 2 != 3
therefore 2 != any ('{2,3}') is true.

Maybe you want != ALL instead of != ANY. With != ALL, the element would
be compared to every element and the whole construct would yield true
only if all the operations yield true.

Using de Morgan::

http://en.wikipedia.org/wiki/De_Morgan%27s_laws

(A OR B) => NOT (A OR B) = (NOT A AND NOT B)​
(​x = ANY) => NOT(x = ANY) = (X != ALL)​

ANY = OR; ALL = AND

David J.

I understand now - thank you. It still rubs me the wrong way that both a OP
b and a not OP b can be equal - STL would go berserk. I guess it was not so
clear to me that ANY / ALL aren't pure functions, more of a control
structure .

Thank you,
Anton.

On Fri, May 15, 2015 at 1:02 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Fri, May 15, 2015 at 11:06 AM, Anton Pavlov <udtelco@gmail.com> wrote:

I understand now - thank you. It still rubs me the wrong way that both a
OP b and a not OP b can be equal - STL would go berserk. I guess it was not
so clear to me that ANY / ALL aren't pure functions, more of a control
structure .

​A "pure" function would have to be able to be used by itself - but the
following gives a syntax error:

SELECT any('{1,2}'::int[])​

​
​David J.​