Thread: SELECT 1 = ANY (SELECT ARRAY[1,2,3]) -> ERROR: operator does not exist: integer = integer[] ?

2013/3/13 Thor Michael Støre <thormichael@gmail.com>:
> Hello,
>
> Could someone make sense of this for me?
>
> $ /Library/PostgreSQL/9.2/bin/psql -d postgres postgres
> psql (9.2.3)
> Type "help" for help.
>
> postgres=# select 1 = ANY (ARRAY[1,2,3]);
>  ?column?
> ----------
>  t
> (1 row)
>
> postgres=# select 1 = ANY (SELECT ARRAY[1,2,3]);
> ERROR:  operator does not exist: integer = integer[]
> LINE 1: select 1 = ANY (SELECT ARRAY[1,2,3]);
>                  ^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> postgres=# select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]);
>  ?column?
> ----------
>  t
> (1 row)
>
> Why do I have to add an explicit cast to int array on something that is an
> int array to begin with? Based on the error message containing "integer =
> integer[]" I'd say PostgreSQL manages to figure out the right type anyhow,
> and ::int[] shouldn't change anything, but I still get a message that
> doesn't make sense when I have an ANY there.

A bit tricky to explain...

 select 1 = ANY (ARRAY[1,2,3])

-> "Is the integer value 1 contained in the specified array of integers?" (YES)

 select 1 = ANY (SELECT ARRAY[1,2,3])

-> "Is the integer value 1 contained in the specified result set,
which happens to be an array (which is not comparable with an
integer)?" (NO)

select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]);
-> "Is the value one contained in an array of integers which is
derived by converting a result set into an array?" (YES)

Note:

testdb=> SELECT array[1,2,3] = ANY (SELECT ARRAY[1,2,3]);
 ?column?
----------
 t
(1 row)

I hope that makes some kind of sense...

Ian Barwick

Ian Lawrence Barwick <barwick@gmail.com> writes:
> 2013/3/13 Thor Michael Støre <thormichael@gmail.com>:
>> Could someone make sense of this for me?

>> postgres=# select 1 = ANY (SELECT ARRAY[1,2,3]);
>> ERROR:  operator does not exist: integer = integer[]

> A bit tricky to explain...

Yeah.  The short answer is that "foo = ANY (SELECT ...)" is mandated by
standard to mean "compare foo to each value in the rowset returned by
the sub-SELECT".  So the above is invalid, and the error message is
giving a perfectly clear reason why.

Postgres has abused the ANY notation by saying that, if the right-hand
side is not a SELECT at the top level (which would be invalid on its
face per spec), then it must be an expression delivering an array value,
and we compare foo to each array element.  This isn't terribly
consistent but it's about the only easy way to shoehorn such an
operation into the language at all.

            regards, tom lane

On 13. mars 2013, at 15:35, Ian Lawrence Barwick <barwick@gmail.com> wrote:
> -> "Is the integer value 1 contained in the specified result set,
> which happens to be an array (which is not comparable with an
> integer)?" (NO)
>
> select 1 = ANY ((SELECT ARRAY[1,2,3])::int[]);
> -> "Is the value one contained in an array of integers which is
> derived by converting a result set into an array?" (YES)
>
> Note:
>
> testdb=> SELECT array[1,2,3] = ANY (SELECT ARRAY[1,2,3]);
> ?column?
> ----------
> t
> (1 row)
>
> I hope that makes some kind of sense…


Right, "= ANY" could either match an array or a result set. I thought of it as only matching arrays, I've never used it
forsubqueries where I've always used IN, that's what had me confused. For most other operators a select enclosed in
parenthesiswill be converted to a single type, array or not, but in this case it's evaluated as a result set. That
clearsit up. 

Thanks,
Michael