Re: UNION result

On Wed, 15 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > 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)
>
> Agreed, we can't make use of the spec's rules as anything much better
> than "spiritual guidance". But it'd be nice if the rules we use match
> what the spec says for the cases covered by the spec. In particular,
> I think it's intuitively correct that numeric union int should yield
> numeric no matter which order you write them in.
>
> Actually, now that I look at the code, 7.3 does in fact get this case
> right, because we did add a check on pg_cast: it will prefer a type over
> another if there is an implicit cast in only one direction.

> The OP may have been fooled by this behavior:
>
> regression=# select 1 union select 1.0;

And I was fooled by
select '1' union select 1;

because I'd forgotten that '1' isn't exactly a character string
constant. select '1'::text union select 1; properly errors.