Re: UNION result

On Wed, 15 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > Hmm, I think (but am not sure) that the spec bit
> > in SQL92 that addresses this is 9.3
> > Set operation result data types based on the
> > text in 7.10 query expression. It seems
> > to say to me that should always be an
> > approximate numeric (if 1.0 is an approximate
> > numeric). Am I reading that right?
>
> Yeah, the existing algorithm for determining CASE/UNION result datatype
> does not have any smarts about preferring numeric over integer, which is
> what's missing to handle this case per-spec.
>
> There has been some speculation about junking the existing code (which
> is mostly driven by a hardwired notion of "preferred types") in favor of
> something driven by the contents of pg_cast. (At least I recall a
> message or two about it, but I can't find it in the archives at the
> moment.)

It seems to me that the spec has a fairly hardwired notion of what types
should come out given the sql types. The biggest problems that I can
see are that it doesn't extend well to an extensible type system and that
in alot of cases it doesn't seem to allow conversions (for example
select CAST(1 as float) union select '1' - if you were to allow
conversions the rules seem to be ambiguous)