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

From: Martin Swiech <martin(dot)swiech(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Postgres 10 problem with UNION ALL of null value in "subselect"
Date: 2018-04-16 10:40:54
Message-ID: CAGtQ-WpqM=BPiNZQK66wtHaxvFxYdPZ68-baKiS26UZDg5HxQA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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
```

It worked on PostgreSQL 9.6.

Query without wrapping subselects (t1 and t2) works on both versions of
PostgreSQL (9.6 and 10) well:
```
select 1::integer as a
union all
select null as a;
```

Is there some new optimization of query processing in PostgreSQL 10, which
needs some "early type determination", but named subselects (t1 and t2)
shades the type from first query?

Or could it be some regression bug?

Thanks for answer.

Martin Swiech

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Emre Hasegeli 2018-04-16 10:45:23 Re: Prefix operator for text and spgist support
Previous Message Pavel Stehule 2018-04-16 09:34:11 very slow queries when max_parallel_workers_per_gather is higher than zero