public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alvaro Herrera <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Documentation of EXCEPT ALL may have a bug
Date: Sat, 10 Feb 2018 08:59:37 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
PG Doc comments form wrote:
> create table t(x int);
> create table u(x int);
>
> insert into t values (1), (2), (2), (3), (3);
> insert into u values (1), (2);
>
> select * from t except all select * from u;
> x
> ---
> 3
> 3
> 2
> (3 rows)
I find this pretty odd behavior. Is this not an outright bug? I
haven't read the SQL standard on this matter[*], but if they define
EXCEPT ALL to work like this, then it seems pretty useless. (Maybe they
just didn't intend EXCEPT ALL to be useful?) If this is indeed the
case, maybe we should amend the docs not only to explain this behavior
but also to warn against the construct.
[*] I didn't try terribly hard, but couldn't actually find where the
behavior is defined. What I have on hand is a draft of SQL:2011 where
this appears to be defined in 7.13 <query expression> but I was unable
to find the rules for set operations. It refers to 9.12 Grouping
operations but that defines conformance rules only.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
view thread (5+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Documentation of EXCEPT ALL may have a bug
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox