BUG #16396: Parallel Union queries seem to treat NULL values differently

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: stephan(at)teeuwendesign(dot)nl
Subject: BUG #16396: Parallel Union queries seem to treat NULL values differently
Date: 2020-04-27 15:26:59
Message-ID: 16396-083a432fe7ac8fb9@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16396
Logged by: Stephan Teeuwen
Email address: stephan(at)teeuwendesign(dot)nl
PostgreSQL version: 12.2
Operating system: Mac 10.15.3
Description:

We recently upgraded from PG 9.5 to PG 12.2 and encountered the following
error message: "ERROR: UNION types integer and text cannot be matched"

Even though docs state that if the left side of the join is defined. the
right side type should not have been defined:
https://www.postgresql.org/docs/12/typeconv-union-case.html

This query fits the documented on above url:
SELECT CAST(NULL AS INTEGER) AS hello_world
UNION
SELECT NULL AS hello_world

But as in our query the following happened:
SELECT hello_world
FROM (SELECT CAST(NULL AS INTEGER) AS hello_world) as a
UNION
SELECT hello_world
FROM (SELECT NULL AS hello_world) as b

It fails and is only fixable by defining the NULL in the right side of the
UNION as CAST(NULL AS INTEGER)

Which wasn't required on Postgres 9.5, but is required for sure on Postgres
12.2
I'm unsure which version introduced this different, but it would be good to
have it represented in the documentation mentioned above!

King regards,
Stephan Teeuwen

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2020-04-27 15:57:35 Re: BUG #16396: Parallel Union queries seem to treat NULL values differently
Previous Message PG Bug reporting form 2020-04-27 14:24:02 BUG #16395: error when selecting generated column in a foreign table