Skip site navigation (1) Skip section navigation (2)

Re: BUG #1453: NULLs in UNION query

From: Dirk(dot)Lutzebaeck(at)t-online(dot)de (Dirk Lutzebaeck)
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: m(dot)woehling(at)barthauer(dot)de, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1453: NULLs in UNION query
Date: 2005-02-05 16:45:53
Message-ID: 4204F841.6050306@aeccom.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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(dot)woehling(at)barthauer(dot)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
>
>  
>


In response to

pgsql-bugs by date

Next:From: Rolf SponselDate: 2005-02-05 17:45:48
Subject: Re: [Feed-back] Installing PostgreSQL 8.0.0 on SPARC/Solaris.
Previous:From: Rolf SponselDate: 2005-02-04 22:26:28
Subject: Re: [Feed-back] Installing PostgreSQL 8.0.0 on SPARC/Solaris.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group