Обсуждение: BUG #8696: Type-checking seems to fail on UNIONs with arrays
The following bug has been logged on the website: Bug reference: 8696 Logged by: Jacques-Pascal Deplaix Email address: jp.deplaix@gmail.com PostgreSQL version: 9.3.2 Operating system: Linux Description: Hi, I'm wondering why the following examples works: (SELECT NULL AS test) UNION (SELECT array_agg(t.name) AS test FROM foo AS t) ; (SELECT NULL AS test) UNION (SELECT array_agg(t.name) AS test FROM foo AS t) UNION (SELECT NULL AS test) ; but this one: (SELECT NULL AS test) UNION (SELECT NULL AS test) UNION (SELECT array_agg(t.name) AS test FROM foo AS t) ; fails with: ERROR: UNION types text and text[] cannot be matched Is it (as I suppose) a bug or a well known limitation ?
On 12/23/2013 01:47 AM, jp.deplaix@gmail.com wrote: > (SELECT NULL AS test) > UNION > (SELECT NULL AS test) > UNION > (SELECT array_agg(t.name) AS test FROM foo AS t) > ; > > > fails with: > ERROR: UNION types text and text[] cannot be matched > > > Is it (as I suppose) a bug or a well known limitation ? It's a well known limitation (I knew what was coming before I read it), but I can't seem to find the right keywords to dig up a reference about it at the moment. It has nothing to do with arrays, but the fact that you have *two* unknown types before a known one. To wit: vik=# select null union select null union select 1; ERROR: UNION types text and integer cannot be matched LINE 1: select null union select null union select 1; The workaround is to provide the type in either the first or second union-ed query: vik=# select null::integer union select null union select 1; int4 ---- 1 (2 rows) vik=# select null union select null::integer union select 1; ?column? -------- 1 (2 rows) PS: Interesting that the column name changed with those two queries... -- Vik
On Mon, Dec 23, 2013 at 10:21 AM, Vik Fearing <vik.fearing@dalibo.com> wrote: > On 12/23/2013 01:47 AM, jp.deplaix@gmail.com wrote: >> (SELECT NULL AS test) >> UNION >> (SELECT NULL AS test) >> UNION >> (SELECT array_agg(t.name) AS test FROM foo AS t) >> ; >> >> >> fails with: >> ERROR: UNION types text and text[] cannot be matched >> >> >> Is it (as I suppose) a bug or a well known limitation ? > > It's a well known limitation (I knew what was coming before I read it), > but I can't seem to find the right keywords to dig up a reference about > it at the moment. > > It has nothing to do with arrays, but the fact that you have *two* > unknown types before a known one. > > To wit: > > vik=# select null union select null union select 1; > ERROR: UNION types text and integer cannot be matched > LINE 1: select null union select null union select 1; > > The workaround is to provide the type in either the first or second > union-ed query: > > vik=# select null::integer union select null union select 1; > int4 > ---- > > 1 > (2 rows) > > vik=# select null union select null::integer union select 1; > ?column? > -------- > > 1 > (2 rows) > > PS: Interesting that the column name changed with those two queries... Yep, this is because a cast simply calls a dedicated function for the conversion, and this function name is used: http://www.postgresql.org/docs/devel/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS Regards, -- Michael