Re: Why does this array query fail?

Ken Tanzer wrote
> 1) On what exactly does PG base its decision to interpret the ANY as
> scalar or not? Or are you saying a sub-query will always be treated as
> non-scalar, unless it is explicitly cast to an array?

Correct. With respect to a sub-query inside ANY(...) it will be treated as
non-scalar. You can explicitly make it scalar by casting it to an array -
understanding that the query will fail if the sub-query does not actually
conform.

> 2) Regarding:
>
>> 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.
>
>
> Why? In this case you have ANY (varchar[]), which as I understand it "="
> needs to compare with a varchar. So why is it looking for an array? If
> it
> was just varchar = varchar[], I'd get that, but in this case it's
> ANY(varchar[]), so does PG extract the type of the argument to ANY without
> paying attention to the ANY itself?

No. The sub-query version is basically:

varchar = ANY (setof varchar[]) ... which is wrong

varchar[] = ANY (setof varchar[]) ... is correct

The "setof" is the difference; its not trying to look inside the array but
rather looking for an entire array that matches one of the arrays the
sub-query generates.

ARRAY[1,2,3] = ANY ( SELECT col1 FROM (VALUES (ARRAY[2,3,4]::int[]),
(ARRAY[1,2,3])) src (col1) )

In the above ANY has to decide whether {2,3,4} or {1,2,3} is equal to the
input; which must be an array. It does not mean "does the number 1 exist in
any of the supplied arrays". Again, it becomes more clear if you understand
ANY(subquery) can return more than one row.

> Is there another page I should be looking at?

Not that I can think of offhand.

> Note that "(SELECT ARRAY[...])::text[]" is only a proper solution if...
>
>
> Yeah, I tried to boil down my example, but this is closer to what I was
> really trying to do:
>
> CREATE TEMP TABLE mytable ( codes varchar[] );
> INSERT INTO mytable VALUES ( array[ 'pass','fail'] );
> INSERT INTO mytable VALUES ( array[ 'found'] );
> SELECT 'found' WHERE 'found' =ANY(
> (SELECT array_agg(code) FROM (SELECT unnest(codes) AS code FROM
> mytable) foo
> )
> );
>
>
> And for immediate purposes, found this worked just as well (as a
> non-scalar
> subquery, I guess):
>
> SELECT 'found' WHERE 'found' =ANY(
> (SELECT unnest(codes) AS code FROM mytable)
> );

Yes, un-nesting can make the problem go away though it too is unusual. For
the most part either use relations/sets or use arrays (for a specific
component of the schema). Your example mixes the two which makes using that
part of the schema difficult.

David J.

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