pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16396: Parallel Union queries seem to treat NULL values differently
3+ messages / 3 participants
[nested] [flat]
* BUG #16396: Parallel Union queries seem to treat NULL values differently
@ 2020-04-27 15:26 PG Bug reporting form <noreply@postgresql.org>
2020-04-27 15:57 ` Re: BUG #16396: Parallel Union queries seem to treat NULL values differently David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2020-04-27 15:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: stephan@teeuwendesign.nl
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16396: Parallel Union queries seem to treat NULL values differently
2020-04-27 15:26 BUG #16396: Parallel Union queries seem to treat NULL values differently PG Bug reporting form <noreply@postgresql.org>
@ 2020-04-27 15:57 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-27 16:40 ` Re: BUG #16396: Parallel Union queries seem to treat NULL values differently Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2020-04-27 15:57 UTC (permalink / raw)
To: stephan@teeuwendesign.nl <stephan@teeuwendesign.nl>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16396: Parallel Union queries seem to treat NULL values differently
2020-04-27 15:26 BUG #16396: Parallel Union queries seem to treat NULL values differently PG Bug reporting form <noreply@postgresql.org>
2020-04-27 15:57 ` Re: BUG #16396: Parallel Union queries seem to treat NULL values differently David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-27 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2020-04-27 16:40 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: stephan@teeuwendesign.nl <stephan@teeuwendesign.nl>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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=1e7c4bb0049732ece651d993d...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-04-27 16:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-27 15:26 BUG #16396: Parallel Union queries seem to treat NULL values differently PG Bug reporting form <noreply@postgresql.org>
2020-04-27 15:57 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-27 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox