On Thu, Oct 27, 2011 at 23:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Kerem Kat <keremkat@gmail.com> writes:
>>> Union with NULL error persists without the corresponding patch. Here
>>> is the output from postgres without the patch:
>
>>> SELECT a FROM (SELECT 1 a) foo
>>> UNION
>>> SELECT a FROM (SELECT NULL a) foo2;
>
>>> ERROR: failed to find conversion function from unknown to integer
>
>> Yeah, this is a longstanding issue that is not simple to fix without
>> introducing other unpleasantnesses. It is not something you should
>> try to deal with at the same time as implementing CORRESPONDING.
>
> BTW, just to clarify: although that case fails, the case Erik was
> complaining of does work in unmodified Postgres:
>
> regression=# select 1 a , 2 b
> union all
> select null a, 4 b ;
> a | b
> ---+---
> 1 | 2
> | 4
> (2 rows)
>
> and I agree with him that it should still work with CORRESPONDING.
> Even though the behavior of unlabeled NULLs is less than perfect,
> we definitely don't want to break cases that work now. I suspect
> the failure means that you tried to postpone too much work to plan
> time. You do have to match up the columns honestly at parse time
> and do the necessary type coercions on them then.
>
> regards, tom lane
>
That is by design, because CORRESPONDING is implemented as subqueries:
select 1 a , 2 b
union all
corresponding select null a, 4 b ;
is equivalent to
SELECT a, b FROM ( SELECT 1 a, 2 b ) foo
UNION ALL
SELECT a, b FROM ( SELECT null a, 4 b ) foo2;
which gives the same error in unpatched postgres.
Regards,
Kerem KAT
