From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: pgsql-bugs@lists.postgresql.org
To: phb07@apra.asso.fr
Subject: Re: BUG #15129: Problem with UNION/UNION ALL type setting when several NULL values before defining the proper type
Date: Sun, 25 Mar 2018 09:50:40 +0100
Message-ID: <878tago8m2.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <152196085023.32649.9916472370480121694@wrigleys.postgresql.org>
References: <152196085023.32649.9916472370480121694@wrigleys.postgresql.org>
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:
PG> While migrating a view from another RDBMS,I reached something that
PG> looks lie a bug in postgres.
It's not a bug in the code, though perhaps you could point out a place
where the documentation could be improved?
PG> -- This statement works as expected: the type of the result column is
PG> determined by the type of the first not null value
PG> SELECT NULL
PG> UNION
PG> SELECT 1::INT;
PG> -- But with an additional NULL value, the statement fails with a message:
PG> "UNION types text and integer cannot be matched"
PG> SELECT NULL
PG> UNION
PG> SELECT NULL
PG> UNION
PG> SELECT 1::INT;
This fails because the UNION is processed pairwise; that is, it's
treated as if it were (select null union select null) union select 1::int
For each union of two queries, the result types have to be resolved to
some non-unknown type. If both types are unknown the result is assumed
to be text (since the usual reason for unknown types is the use of a
string literal); if one type is known and the other unknown, the known
type is selected as the common type; otherwise unification via implicit
casts is tried, otherwise the query fails.
So in this case the (select null union select null) is resolved as
having an output of one text column, and then trying to union against an
integer column fails (since we don't implictly cast to text).
(The SQL standard is of no particular help here since it does not allow
NULL to appear "bare", except in contextually typed expressions.)
--
Andrew (irc:RhodiumToad)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: andrew@tao11.riddles.org.uk, pgsql-bugs@lists.postgresql.org, phb07@apra.asso.fr
Subject: Re: BUG #15129: Problem with UNION/UNION ALL type setting when several NULL values before defining the proper type
In-Reply-To: <878tago8m2.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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