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

On Monday, April 27, 2020, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

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

In the example the second null is untyped still when it gets fed to the
used and so can be implicitly cast to match the left side of the union. In
you real case the null appears in a subquery under the from clause and
needs to be made into an actual type before it can be passed out of the
subquery and appear in the main query. The resultant type here is text.
Then the union happens and integer and text cannot be melded together.

The fixing of null to a concrete type is documented somewhere else (not
sure where atm) as it applies generally. Adding that level of detail to
parts of the documentation is assumed to provide more distraction than
clarification.

I don’t know why it may have worked in the past but the existing behavior
is expected for the query you presented. I have my doubts about the
presented test case and version discrepancy observation but am not in a
position to explore it.

David J.