Обсуждение: BUG #16396: Parallel Union queries seem to treat NULL values differently
BUG #16396: Parallel Union queries seem to treat NULL values differently
От
PG Bug reporting form
Дата:
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
Re: BUG #16396: Parallel Union queries seem to treat NULL values differently
От
"David G. Johnston"
Дата:
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.
"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: https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1e7c4bb0049732ece651d993d03bb6772e5d281a 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