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

2018-04-19 5:01 GMT+02:00 Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp
>:

> 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.
>
>
+1

Pavel

> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>