Обсуждение: Fail to search in array, produced by subquery - is it a bug?
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
On Tue, Apr 26, 2011 at 10:29 PM, Dmitry Fefelov <fozzy@ac-sw.com> wrote: > 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'\\') > )); well, if you *had* to use any you could rewrite that as: SELECT *FROM core.tag_links ctlWHERE (ctl.tag_id = ANY ( array ( SELECT ct.id FROM core.tags ct WHERE (LOWER(ct.tag)LIKE LOWER(('search tag')::text || '%') ESCAPE E'\\')) )); but you're far better off still using 'where in/'where exists' for this query. You could also expand an array with 'unnest' and use 'where in'. according to the documentation, 'any' only takes array expressions...this feels really awkward but i'm not sure if it's a bug. the semantics of 'any' suck and I prefer not to use it :(. this has come up a bunch of times in the archives (unfortunately, searching for 'any' isn't pleasant) and I think there's an explanation behind the current behavior. Couldn't find it though, so I'm not sure. merlin
> > well, if you *had* to use any you could rewrite that as: Using ANY I'll reduce number of subqueries, there are also some array overlappings, which works fine with same subquery - i.e. when " && " instead of " = ANY ". There is not full qi\uery in my first message of course. > SELECT * > FROM core.tag_links ctl > WHERE (ctl.tag_id = ANY ( array ( > SELECT ct.id > FROM core.tags ct > WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE > E'\\')) > )); > Okay, thank you for a hint. I asked because I thought that ANY and ALL should work too - when overlap ("&&") works.