Re: UNION Types Mismatch

"CN" <cnliou9(at)fastmail(dot)fm> writes:
> SELECT c1 AS a,NULL AS b,NULL AS c FROM ta
> UNION ALL
> SELECT NULL,c1,NULL FROM tb
> UNION ALL
> SELECT NULL,NULL,c1 FROM tc;

> ERROR: UNION types 'text' and 'smallint' not matched

Yeah. The problem basically is that you have
(null UNION null) UNION smallint
and the inner union type gets resolved to text by default.

I have a TODO note about this that questions whether we can improve
the behavior without violating spec. Consider
(numeric UNION numeric) UNION float8
Presently this will be performed as a numeric UNION, then cast the
output to float8 and do a float8 UNION. The datatype is a critical
part of the semantics because it determines what will be considered
duplicate values. If we chose a common datatype across all three
union arms to start with, it'd be float8, and we'd possibly get a
different result from the query.

It may be that the spec will let us get away with doing it anyway,
but some close study is needed.

regards, tom lane