Re: BUG #15129: Problem with UNION/UNION ALL type setting whenseveral NULL values before defining the proper type

Le 25/03/2018 à 10:50, Andrew Gierth a écrit :
>>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:
>   PG> While migrating a view from another RDBMS,I reached something that
>   PG> looks lie a bug in postgres.
>
> It's not a bug in the code, though perhaps you could point out a place
> where the documentation could be improved?
>
>   PG> -- This statement works as expected: the type of the result column is
>   PG> determined by the type of the first not null value
>   PG> SELECT NULL
>   PG>   UNION
>   PG> SELECT 1::INT;
>
>   PG> -- But with an additional NULL value, the statement fails with a message:
>   PG> "UNION types text and integer cannot be matched"
>   PG> SELECT NULL
>   PG>   UNION
>   PG> SELECT NULL
>   PG>   UNION
>   PG> SELECT 1::INT;
>
> This fails because the UNION is processed pairwise; that is, it's
> treated as if it were (select null union select null) union select 1::int
>
> For each union of two queries, the result types have to be resolved to
> some non-unknown type. If both types are unknown the result is assumed
> to be text (since the usual reason for unknown types is the use of a
> string literal); if one type is known and the other unknown, the known
> type is selected as the common type; otherwise unification via implicit
> casts is tried, otherwise the query fails.
>
> So in this case the (select null union select null) is resolved as
> having an output of one text column, and then trying to union against an
> integer column fails (since we don't implictly cast to text).
>
> (The SQL standard is of no particular help here since it does not allow
> NULL to appear "bare", except in contextually typed expressions.)
>
Thanks Andrew and Tom (sorry can't find the last mail of the thread in 
my mailbox :-()
So the postgres behaviour is clear, once the rules of the game are 
known, eventhough it is not very intuitive for end-user and is different 
from at least another RDBMS (namely SQL-Server).
So yes, an explanation in the documentation would be useful.
Thanks again. Philippe.