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

From: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
To: ashutosh(dot)bapat(at)enterprisedb(dot)com
Cc: martin(dot)swiech(at)gmail(dot)com, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Postgres 10 problem with UNION ALL of null value in "subselect"
Date: 2018-04-19 03:01:45
Message-ID: 20180419.120145.135221417.horiguchi.kyotaro@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
fix_unknown_resolve_behavior_in_union.patch text/x-patch 1.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2018-04-19 03:05:50 Re: Excessive PostmasterIsAlive calls slow down WAL redo
Previous Message Peter Geoghegan 2018-04-19 02:58:15 Re: Adding an LWLockHeldByMe()-like function that reports if any buffer content lock is held