Fail to search in array, produced by subquery - is it a bug?

From: Dmitry Fefelov <fozzy(at)ac-sw(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Fail to search in array, produced by subquery - is it a bug?
Date: 2011-04-27 03:29:03
Message-ID: 201104271029.03301.fozzy@ac-sw.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

With Postgres 8.4 query like

SELECT *
FROM core.tag_links ctl
WHERE (ctl.tag_id = ANY (
SELECT array_agg(ct.id)
FROM core.tags ct
WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
E'\\')
));

produces error:

ERROR: operator does not exist: bigint = bigint[]
ROW 3: WHERE (ctl.tag_id = ANY (
^
HINT: No operator matches the given name and argument type(s). You might need
to add explicit type casts.

Bith ct.id and ctl.tag_id - of type BIGINT.

Result of

SELECT array_agg(ct.id)
FROM core.tags ct
WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
E'\\');

array_agg
-----------------------------------------------------------------------------------------------------------------------------
{54426,53600,54062,187207,187642,54395,53312,51912,128621,19203,6613,54462}
(1 row)

Should ANY (...) and ALL (...) work when "..." is a subquery, returning
single ARRAY field, or maybe I misunderstood something?

Regards,
Dmitry

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2011-04-27 03:36:32 Re: offline consistency check and info on attributes
Previous Message Greg Stark 2011-04-27 03:12:02 Re: "stored procedures" - use cases?