BUG #16396: Parallel Union queries seem to treat NULL values differently
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)
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
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.htmlThis query fits the documented on above url:
SELECT CAST(NULL AS INTEGER) AS hello_world
UNION
SELECT NULL AS hello_worldBut 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 bIt 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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Monday, April 27, 2020, PG Bug reporting form <noreply@postgresql.org>
wrote: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.
Yeah. I believe this changed in v10, at this commit:
Probably that should have been called out as an incompatibility in the
v10 release notes, but I don't immediately see anything there that
matches. In any case, it's intentional.
regards, tom lane