Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ekUoN-0002Yy-Us for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 13:03:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ekUoM-00052f-Ci for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 13:03:22 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ekUoM-00052W-19 for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 13:03:22 +0000 Received: from mail-io0-x22c.google.com ([2607:f8b0:4001:c06::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ekUoE-0002sF-SX for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 13:03:20 +0000 Received: by mail-io0-x22c.google.com with SMTP id 72so12701581iom.10 for ; Sat, 10 Feb 2018 05:03:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=EdS9pr70ZiwFORQu4z9nR/LLwb8qsDflf+j4fmIMqzs=; b=T2kq+HjvZeWzdTsF0EYbhIdHKRR+t7grqFbA/kc4kck9E+jHM30x9RP0kpUKh5FiqH e7D4eKDwU9MEHAg9afJRHKjsFINyCPyNYuS5DR0+DMeHWHvOper6/LGdS0X8dpntvE1H bxcNgbFUCtjJr3gId2eDF9pc6Y1lrjjno4jmSVeBFsE6tEtatOAjXAvzqjiqPxpVULir J80ZYml0D54Xj7UzQj53wNXbwAiCBEMNVWu7V0h+TaqMlycUNpz8hSYZN5VokH+qPqAK OQ4cmFTR8z4eURLxrLEnl5H8jqz0K9NLdN9tE/IJti8WQhdTNJczOjDDeAKGhiThZXAf wa3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=EdS9pr70ZiwFORQu4z9nR/LLwb8qsDflf+j4fmIMqzs=; b=UbYa3qBhmPUBgbf+LNKaFhrQCOoAgD+LSkRi6o1eJM99i1rjkYUEIHZ2q41f0vICwM OtgmXTEpsUQITC0o10epIDzXkDoAYRAuv5O/f5bCEzyuW5Xv7jmKD8osq66kUWpHLZDQ aiPjZl9XgjIX6TDua/R97HcO1l8WuC5QU4Fk3QeoBwKJfj+grIGH4q0XMuWlUvYJVWmb +blS5Doxq7O9RnT5lRdQI0ja+lShI1lVP1LeGy6YWxktgAMgpeK49ZCmmv6aXkP3gTYb 8vSyCITTevuPe18AgUUe2dXQ8cyfwxt1IVxH5GN2OVoxLS1JwBpd925IJSCSIntHil2r wpvg== X-Gm-Message-State: APf1xPCTdseA1LL4bcSfUlvDCC+8eBkOPbdN1mIExpS/OwUtKXutMigH NkOjgTIhG41hdp0XAgxmyuRE2Xu4fAUAn0flSbg= X-Google-Smtp-Source: AH8x224qQSYJH3P2tiRDf2NqSgg7SLF29WMl24dkkjG96ZD4SjcQu3fIWsRh1xxl1ZZbaMzL/SzUptkIbNOV3Gv7sOU= X-Received: by 10.107.156.203 with SMTP id f194mr2779174ioe.23.1518267793833; Sat, 10 Feb 2018 05:03:13 -0800 (PST) MIME-Version: 1.0 Received: by 10.2.67.65 with HTTP; Sat, 10 Feb 2018 05:02:53 -0800 (PST) In-Reply-To: <20180210115937.aqoolac4yk66ajeu@alvherre.pgsql> References: <151819851166.9466.6740951677204542727@wrigleys.postgresql.org> <20180210115937.aqoolac4yk66ajeu@alvherre.pgsql> From: Pantelis Theodosiou Date: Sat, 10 Feb 2018 13:02:53 +0000 Message-ID: Subject: Re: Documentation of EXCEPT ALL may have a bug To: Alvaro Herrera Cc: jao@geophile.com, pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="001a1140cd008684f80564db4484" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --001a1140cd008684f80564db4484 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Feb 10, 2018 at 11:59 AM, Alvaro Herrera wrote: > 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 think that's the way it is defined (but I agree, I don't remember ever seeing a use for it) > > [*] 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 but I was unable > to find the rules for set operations. It refers to 9.12 Grouping > operations but that defines conformance rules only. > > -- > I may have a different version but I see: iii) T contains the following rows: 1) Let R be a row that is a duplicate of some row in ET1 or of some row in ET2 or both. Let m be the number of duplicates of R in ET1 and let n be the number of duplicates of R in ET2, where m =E2=89=A5 0 and n =E2=89=A5 0. 2) If DISTINCT is specified or implicit, then .... ... 3) If ALL is specified, then Case: A) If UNION is specified, then the number of duplicates of R that T contains is (m + n). B) If EXCEPT is specified, then the number of duplicates of R that T contains is the maximum of (m =E2=80=93 n) and 0 (zero). C) If INTERSECT is specified, then the number of duplicates of R that T contains is the minimum of m and n. --001a1140cd008684f80564db4484 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Sat, Feb 10, 2018 at 11:59 AM, Alvaro Herrera <= alvherre@alvh.= no-ip.org> wrote:
PG Doc comments form wrote:

>=C2=A0 =C2=A0 =C2=A0create table t(x int);
>=C2=A0 =C2=A0 =C2=A0create table u(x int);
>
>=C2=A0 =C2=A0 =C2=A0insert into t values (1), (2), (2), (3), (3);
>=C2=A0 =C2=A0 =C2=A0insert into u values (1), (2);
>
>=C2=A0 =C2=A0 =C2=A0select * from t excep= t all select * from u;

>=C2=A0 =C2=A0 =C2=A0 x
>=C2=A0 =C2=A0 =C2=A0---
>=C2=A0 =C2=A0 =C2=A0 3
>=C2=A0 =C2=A0 =C2=A0 3
>=C2=A0 =C2=A0 =C2=A0 2
>=C2=A0 =C2=A0 =C2=A0(3 rows)

I find this pretty odd behavior.=C2=A0 Is this not an outright bug?= =C2=A0 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.=C2=A0 (Maybe th= ey
just didn't intend EXCEPT ALL to be useful?)=C2=A0 If this is indeed th= e
case, maybe we should amend the docs not only to explain this behavior
but also to warn against the construct.

I think that's the way it is defined (but I agree, I don't remembe= r ever seeing a use for it)
=C2=A0

[*] I didn't try terribly hard, but couldn't actually find where th= e
behavior is defined.=C2=A0 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 unabl= e
to find the rules for set operations.=C2=A0 It refers to 9.12 Grouping
operations but that defines conformance rules only.

--

I may have a different= version but I see:

iii)
T contains the following rows:
1) Let= R be a row that is a duplicate of some row in ET1 or of some row in ET2 or= both. Let
m be the number of duplicates of R in ET1 and let n be the nu= mber of duplicates of R in
ET2, where m =E2=89=A5 0 and n =E2=89=A5 0.2) If DISTINCT is specified or implicit, then ....
...

3) If AL= L is specified, then
Case:
A) If UNION is specified, then the number = of duplicates of R that T contains is (m + n).
B) If EXCEPT is specified= , then the number of duplicates of R that T contains is the
maximum of (= m =E2=80=93 n) and 0 (zero).
C) If INTERSECT is specified, then the numb= er of duplicates of R that T contains is the
minimum of m and n.

--001a1140cd008684f80564db4484--