Thread: BUG #1453: NULLs in UNION query

The following bug has been logged online:

Bug reference:      1453
Logged by:
Email address:      m.woehling@barthauer.de
PostgreSQL version: 8.0.0
Operating system:   Windows 2000
Description:        NULLs in UNION query
Details:

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

"" <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:

>"" <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
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>