On Monday, April 27, 2020, PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16396
Logged by: Stephan Teeuwen
Email address: stephan@teeuwendesign.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.