Re: UNION result

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.

regression=# select 1 union select 1.2;
?column?
----------
1
1.2
(2 rows)

The OP may have been fooled by this behavior:

regression=# select 1 union select 1.0;
?column?
----------
1
(1 row)

which happens because '1' and '1.0' are considered equal numeric values,
even though they print differently.

I'm not convinced that the UNION algorithm is right yet, but surely it's
better than it was before.

regards, tom lane