BUG #1453: NULLs in UNION query

"" <m.woehling@barthauer.de> writes:

The following query should not raise an error ("ERROR: UNION types text and
integer cannot be matched"):

SELECT NULL AS Test
UNION ALL SELECT NULL
UNION ALL SELECT 0

Hmm ... it works if you do

SELECT NULL AS Test
UNION ALL (SELECT NULL
UNION ALL SELECT 0)

The problem is that transformSetOperationTree() resolves the column
datatypes one UNION pair at a time, and so the two NULLs default to
"text" before we ever look at the zero.

It's probably possible to rejigger it so that the common type is chosen
considering all the set-operation arms in parallel, but it doesn't seem
like a trivial change. (Translation: there will not be an immediate
fix.)

As a workaround, perhaps you could cast one or all of the nulls to int
explicitly:

SELECT NULL::int AS Test
UNION ALL SELECT NULL
UNION ALL SELECT 0

regards, tom lane

I've also come across this in 7.4. You could also use:

SELECT NULL AS Test
UNION ALL SELECT NULL::int
UNION ALL SELECT 0

Dirk

Tom Lane wrote: