Re: BUG #1847: Error in some kind of UNION query.

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> You probably mean
> SELECT NULL
> UNION
> SELECT NULL
> UNION
> SELECT 1

This has come up a couple of times before. It'd probably be possible to
resolve the type across all the union'd selects at once, using the same
rules as we do for CASE. The thing that bothers me is that I think
doing so would violate the SQL spec. The spec defines UNION as a
two-at-a-time operation; it is perfectly clear that the above must mean
(SELECT NULL UNION SELECT NULL) UNION SELECT 1
and in turn that means that a CASE-like approach would cause the "SELECT
1" to change the semantics of the other parenthesized operation. Which
is surely wrong.

For UNION ALL this might not matter a lot, because the end result
datatype would be the same anyway. But for UNION it matters a whole
lot, because the assigned datatype determines what "equality" means
and therefore which rows are going to be eliminated as duplicates.
Here is an example:

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

By default, the literals are resolved as type TEXT, and so they are
considered different. But try this:

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

The unquoted 1.0 is numeric, and so that choice propagates to the
untyped literals, and we end up with just one row because 1 == 1.0.
This example proves that the parenthesization of a nest of UNIONs
does make a difference.

So the short answer is: I don't think we can change this without
breaking spec. Better put some explicit casts on your nulls, eg

SELECT NULL::INTEGER UNION SELECT NULL::INTEGER UNION SELECT 1

(BTW, I'm fairly sure that "SELECT NULL" is illegal per the spec,
anyway. You'd have to CAST it to some specific type to be valid SQL.)

regards, tom lane