Re: UNION with more than 2 branches

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Maybe we should just ignore those qualms and do it anyway --- I must
>> admit that I'm hard-pressed to come up with a situation where anyone
>> would really want different datatypes used in the inner union than
>> the outer.

> Does it even matter except in the case of nulls? I mean, if the inner pair
> uses integer and then the outer pair uses bigint it'll still work correctly,
> no?

Oh, it absolutely matters: you can get different answers. Consider

(select '1' union select ' 1') union all select 1;

Ignoring the point that we have no implicit integer/text cast, this
would yield three rows if the inner union is treated as text, vs
two rows if it's treated as integer. Likewise, '1.0' is different from
'1' according to some datatypes and not others.

The urgency of this objection decreases greatly if we get rid of all the
implicit cross-type-category casts, I think. Offhand the only trouble
case I can come up with without using a cross-category conversion is
trailing blanks in char vs text/varchar.

> What would happen if the inner pair defaulted null to "unknown" instead of
> text?

You're missing the point, which is that the inner UNION needs to decide
what its uniqueness semantics are, independently of what might happen to
its result later. Or that's how I read the spec anyway.

regards, tom lane