Thread: BUG #8934: value != ANY (uuid[]) AND expr does not work in all cases

The following bug has been logged on the website:

Bug reference:      8934
Logged by:          Marcus Overheu
Email address:      marcusoverheu@gmail.com
PostgreSQL version: 9.2.4
Operating system:   ubuntu 12.04.3
Description:

if you have a table ala.
 create table uuidtest( aint integer, auuid uuid);

 insert into uuidtest VALUES (1, 'ab15ea27-8768-4cbe-a1fb-02a2a949a34d'),
(1, '1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2');

then
 select * from uuidtest where auuid = ANY
(ARRAY['1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2','ab15ea27-8768-4cbe-a1fb-02a2a949a34d']::uuid[])
and aint = 1;
return 2 result
which would mean that if using not in

select * from uuidtest where auuid != ANY
(ARRAY['1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2','ab15ea27-8768-4cbe-a1fb-02a2a949a34d']::uuid[])
and aint = 1;

would return zero, but it stills returns 2 results.

marcusoverheu@gmail.com escribió:

>  insert into uuidtest VALUES (1, 'ab15ea27-8768-4cbe-a1fb-02a2a949a34d'),
> (1, '1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2');
>
> then
>  select * from uuidtest where auuid = ANY
> (ARRAY['1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2','ab15ea27-8768-4cbe-a1fb-02a2a949a34d']::uuid[])
> and aint = 1;
> return 2 result
> which would mean that if using not in
>
> select * from uuidtest where auuid != ANY
> (ARRAY['1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2','ab15ea27-8768-4cbe-a1fb-02a2a949a34d']::uuid[])
> and aint = 1;

Presumably you want auuid != ALL (array[ .. ]) here.  In the first case,
each value is = to the value that it equals to, so both are returned.
In the second case, each value is != to the other value, so both are
returned too.

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

Yeah i noticed a bit too late, i apologize for not reading the
documentation properly.


2014/1/23 Alvaro Herrera <alvherre@2ndquadrant.com>

> marcusoverheu@gmail.com escribi=F3:
>
> >  insert into uuidtest VALUES (1, 'ab15ea27-8768-4cbe-a1fb-02a2a949a34d'=
),
> > (1, '1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2');
> >
> > then
> >  select * from uuidtest where auuid =3D ANY
> >
> (ARRAY['1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2','ab15ea27-8768-4cbe-a1fb-02=
a2a949a34d']::uuid[])
> > and aint =3D 1;
> > return 2 result
> > which would mean that if using not in
> >
> > select * from uuidtest where auuid !=3D ANY
> >
> (ARRAY['1c9e9ce5-d4d8-4a14-9eaf-929f27e09ba2','ab15ea27-8768-4cbe-a1fb-02=
a2a949a34d']::uuid[])
> > and aint =3D 1;
>
> Presumably you want auuid !=3D ALL (array[ .. ]) here.  In the first case=
,
> each value is =3D to the value that it equals to, so both are returned.
> In the second case, each value is !=3D to the other value, so both are
> returned too.
>
> --
> =C1lvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>