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

>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)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.)

--
Andrew (irc:RhodiumToad)