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

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