Date: Sat, 10 Feb 2018 08:59:37 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jao@geophile.com, pgsql-docs@lists.postgresql.org
Subject: Re: Documentation of EXCEPT ALL may have a bug
Message-ID: <20180210115937.aqoolac4yk66ajeu@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <151819851166.9466.6740951677204542727@wrigleys.postgresql.org>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
Precedence: bulk

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