Re: (PATCH) Adding CORRESPONDING (NULL error)

On Thu, Oct 27, 2011 at 23:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> Kerem Kat <keremkat(at)gmail(dot)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