Re: Why does this array query fail? - Mailing list pgsql-general

Ken Tanzer wrote
> Well I partially take back my last question.  In the error message, I
> missed the non-array / array part of "integer = text[]"
>
> But I'm still confused.  My subselect returns an array.  If I cast it to a
> text array, ANY is happy.  But if I don't do so, what exactly does
> Postgres
> think my subquery has yielded?  And the error message still doesn't seem
> to
> make sense...
>
>
>
> On Mon, Sep 16, 2013 at 6:59 PM, Ken Tanzer <

> ken.tanzer@

> > wrote:
>
>> OK I tried that and see it works with the cast.  But now I'm confused
>> about both what exactly is failing without the cast, and about the
>> resulting error message.
>>
>> Is the query failing because PG doesn't understand the subquery is
>> yielding an array?  Seems unlikely.  But if the problem is a type
>> mismatch
>> between 'test' (on the left) and my subquery, I'd expect the same error
>> message as if I try to compare an int to a text array:
>>
>> SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','fail']));
>> ERROR:  operator does not exist: integer = text[]
>> LINE 1: SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','f...
>>                                ^
>> HINT:  No operator matches the given name and argument type(s). You might
>> need to add explicit type casts.
>>
>> Instead of the error message I actually got:
>>
>> ERROR:  array value must start with "{" or dimension information
>>
>> Thanks.
>>
>> Ken
>>
>>
>>>>
>>> Your subquery can also be explicitly casted to make it work. Note the
>>> "::TEXT[]"
>>>
>>> SELECT 'found' WHERE 'test' = ANY( (SELECT
>>> ARRAY['test','pass','fail'])::TEXT[] );
>>>

Can we please follow list norms (or at least my example since I was the
first to respond) and bottom-post.

Sub-queries come in a few different flavors:

This:

ANY( (SELECT ARRAY[])::text[] )

works for the same reason this:

SELECT (SELECT col1 FROM (VALUES (ARRAY[4,5,6])) tbl (col1))::integer[]

works;

but this:

SELECT (SELECT col1 FROM (VALUES (ARRAY[1,2,3]),(ARRAY[4,5,6])) tbl
(col1))::integer[]

fails.

For this explanation I will simplify and state that there are two kinds of
sub-queries:

Scalar
Table-like (i.e., non-scalar)

A scalar sub-query must return, at most, one row and only a single column.
A scalar sub-query can be used wherever a literal value is needed.

A table-like sub-query can return as many rows as desired and either one or
an unlimited number of columns - context depending.  In the case
"ANY(sub-query)" context it may only return a single column (but still
multiple rows).  In this context PostgreSQL goes looking for an operator -
e.g., equals(=) - with the right-side argument being of the type of the
sub-query column.  Since equals needs to have a matching type on the
left-side PostgreSQL presumes that whatever is on the left side must be of
the same type.  In this example you supplied an unadorned literal ('test')
which has no type information.  Thus PostgreSQL attempts to cast the unknown
literal to the type it requires (text[]) and fails since an array literal
must begin with '{'.  The attempt to cast 'test' failed.

WHERE 1 = ANY(SELECT ARRAY[]:text[])

This gives a different error because PostgreSQL knows that the number "1" is
an integer and thus has a known type - no cast is necessary.  However, since
the type "integer" does not match the needed type "text[]" a type mis-match
error is thrown or in this case no operator equals(integer, text[]) was
located.

It is the context of the use of the sub-query; not the form of the query
itself, that determines whether a particular sub-query will be treated as
scalar or table-like.  It is because "ANY(...)" can accept either a literal
or a table-like sub-query that this ambiguity arises.

The example query just happens to only have a single row but nothing is
explicitly stopping it from generating more.  The work-around of casting the
sub-query to "text[]" works because the system knows that it must either get
a scalar result or the sub-query will throw an exception (as in my failing
example above).  The system cannot use the actual number of rows returned to
make the decision and so if you know that only one row (at most) can be
returned and you want a scalar interpretation you have to explicitly
indicate that in the query.

There is quite a bit more to this that can be gleaned by reading the
documentation for sub-queries.

Note that "(SELECT ARRAY[...])::text[]" is only a proper solution if you
cannot directly invoke the ARRAY[] syntax.  Where it comes in handy would be
something like:

WITH array_to_check (atc) AS ( VALUES (ARRAY[1,2,3]::integer[]) )
SELECT ...
FROM ...
WHERE 2 = ANY((SELECT atc FROM array_to_check)::integer[])

For the most part, however, forcing a non-scalar sub-query to become a
scalar sub-query is an indication that you are doing something wrong.

It may help to think of:

ANY(subquery) as meaning:

ANY(setof "whatever column type the subquery returns as its only column")

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771183.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.