BUG #8696: Type-checking seems to fail on UNIONs with arrays

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs