Обсуждение: BUG #13289: ANY() function produces a paradox
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.
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
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
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' !=3D any('{2,3}')) , ('2' =3D any('{2,3}'))
>
> When !=3D (or <>) operator is applied to any() , results are wrong. There=
by,
> you have a situation where x =3D y AND x !=3D y both evaluating as true.
>
=E2=80=8B=E2=80=8BAs Tom said.=E2=80=8B
=E2=80=8BAnd what is "y" in your argument?=E2=80=8B
This is shorthand for:
('2' !=3D '2' OR '2' !=3D '3')=E2=80=8B, ('2' =3D '2' OR '2' =3D '3')
=E2=80=8B=E2=80=8BIf you define "y" as the expression "any('{2,3}')" then i=
t 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' !=3D any('{2,3}')) , ('2' =3D any('{2,3}'))
> >
> > When !=3D (or <>) operator is applied to any() , results are wrong.
> Thereby,
> > you have a situation where x =3D y AND x !=3D 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 !=3D =
3
> therefore 2 !=3D any ('{2,3}') is true.
>
> Maybe you want !=3D ALL instead of !=3D ANY. With !=3D 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) =3D> NOT (A OR B) =3D (NOT A AND NOT B)=E2=80=8B
(=E2=80=8Bx =3D ANY) =3D> NOT(x =3D ANY) =3D (X !=3D ALL)=E2=80=8B
ANY =3D OR; ALL =3D 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 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' !=3D any('{2,3}')) , ('2' =3D any('{2,3}'))
>>
>> When !=3D (or <>) operator is applied to any() , results are wrong. Ther=
eby,
>> you have a situation where x =3D y AND x !=3D y both evaluating as true.
>>
>
> =E2=80=8B=E2=80=8BAs Tom said.=E2=80=8B
>
> =E2=80=8BAnd what is "y" in your argument?=E2=80=8B
>
> This is shorthand for:
>
> ('2' !=3D '2' OR '2' !=3D '3')=E2=80=8B, ('2' =3D '2' OR '2' =3D '3')
>
> =E2=80=8B=E2=80=8BIf 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 boole=
an
> expression (x op y[1] OR x op y[2] OR ...)
>
> David J.
>
>
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 n=
ot
> so clear to me that ANY / ALL aren't pure functions, more of a control
> structure .
>
=E2=80=8BA "pure" function would have to be able to be used by itself - but=
the
following gives a syntax error:
SELECT any('{1,2}'::int[])=E2=80=8B
=E2=80=8B
=E2=80=8BDavid J.=E2=80=8B