Thread: 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