From: phb07 <phb07@apra.asso.fr>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15129: Problem with UNION/UNION ALL type setting when several NULL values before defining the proper type
Date: Wed, 28 Mar 2018 08:35:16 +0200
Message-ID: <59f39434-5a54-adbb-5e38-d4fcdc0c7f04@apra.asso.fr> (raw)
In-Reply-To: <878tago8m2.fsf@news-spur.riddles.org.uk>
References: <152196085023.32649.9916472370480121694@wrigleys.postgresql.org>
<878tago8m2.fsf@news-spur.riddles.org.uk>
Le 25/03/2018 à 10:50, Andrew Gierth a écrit :
>>>>>> "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.)
>
Thanks Andrew and Tom (sorry can't find the last mail of the thread in
my mailbox :-()
So the postgres behaviour is clear, once the rules of the game are
known, eventhough it is not very intuitive for end-user and is different
from at least another RDBMS (namely SQL-Server).
So yes, an explanation in the documentation would be useful.
Thanks again. Philippe.
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: phb07@apra.asso.fr, andrew@tao11.riddles.org.uk, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15129: Problem with UNION/UNION ALL type setting when several NULL values before defining the proper type
In-Reply-To: <59f39434-5a54-adbb-5e38-d4fcdc0c7f04@apra.asso.fr>
* 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