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

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.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company