Re: Postgres 10 problem with UNION ALL of null value in "subselect"

At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote in <CAFjFpRf6Q0B9m2qqsQjw9vTyh8r2S=FG2SUb360Mg3CbxQ1ciA(at)mail(dot)gmail(dot)com>
> On Mon, Apr 16, 2018 at 4:10 PM, Martin Swiech <martin(dot)swiech(at)gmail(dot)com> wrote:
> > Hi folks,
> >
> > I got some complex query which works on PostgreSQL 9.6 , but fails on
> > PostgreSQL 10.
> >
> > Version of PostgreSQL:
> > PostgreSQL 10.3 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version
> > 7.0.0 (clang-700.1.76), 64-bit
> >
> > Simplified core of the problematic query looks like this:
> > ```
> > select * from (
> > select 1::integer as a
> > ) t1
> > union all
> > select * from (
> > select null as a
> > ) t2;
> > ```
> >
> > It fails with this error message:
> > ```
> > ERROR: UNION types integer and text cannot be matched
> > LINE 5: select * from (
> > ^
> > SQL state: 42804
> > Character: 66
> > ```
> >
>
> The error disappears if we go one commit before
> 1e7c4bb0049732ece651d993d03bb6772e5d281a, the error disappears. But
> that's I think expected with that commit.
>
> We can work around this problem by casting null to integer like null::integer.

I think the wanted behavior is not resolving unknown for all FROM
clauses under union.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center