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 1ekUwS-0003D7-H1 for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 13:11:44 +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 1ekUwR-0001Mo-DQ for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 13:11:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ekUwR-0001Me-4P for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 13:11:43 +0000 Received: from mail-it0-x22f.google.com ([2607:f8b0:4001:c0b::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ekUwO-000435-Nc for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 13:11:42 +0000 Received: by mail-it0-x22f.google.com with SMTP id u12so1807083ite.0 for ; Sat, 10 Feb 2018 05:11:40 -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=buAdu41s4iWNr7FnEsN2+8+rrrV19OaHNKlEAFgKJ+Q=; b=obDr/Veyj5AHthq4mvk8UNrkDAq+pJOMDw4gZcoHrJiDixNSYSRJe7R0MPxhWh5/Xe 5u2UrKDXlcWrysps/kt5QIcZgt07zAyf8B3NAgS7Z5/RIUjPUzj5TYyTaEjnBNh2aq1i avcSQtm4/kPQa1YIxGt6Df+Fxboin53nnQNtLBrxHknRlTOEY+buvSgC4LN3Q7uzdXKw /AjvPNeUjmDnszuIPyBnxfFapnqPc0fS4SpxQT+AmsxBsB/9zRC7ZECQjAZiN7tjTM7W RVW2dgEY9A4h6rvK+SjNGFH/oAQ3ndgQD+5B55mf4gDTfvEGChvwwWVIeWxDgchl49rz b70A== 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=buAdu41s4iWNr7FnEsN2+8+rrrV19OaHNKlEAFgKJ+Q=; b=CAOPsXgpSAIFViGlWWrFDFKvEHsUE9qwviKt3ewx6FpVFPUgfyPpVq+Vs7IKNyzfDx LCfPW7x+HHs4GMkAfiLzbuUfWBSS7CJAX3+Abz/yvIWJEqYmKTXUuS+ObxGL1FHRYxua mFkEUFBpdLwIWxD/oCQTQWJybKmnNi27W/JzyP5RdO8rNqTW/ihzg4yJHspTCRItlCfe PhlcNG7x0NIewhkndh7qCc2nEKRsyYQCB6GXsEjJ/KjdlR+gP/RIR5Y57FZ9VTdXhqoE 9nY5wx9TZ5lLnVZpGUAeBhUNJUrboo/wHYILnkJUXT6KblQSP9AJ6H0yOoYepmNK+PsK wWIQ== X-Gm-Message-State: APf1xPBJZXRRq5pBsvvb4Qmc1JimUUBq+fZTT3yb88DyJp/jHhvDB3Ke i77hHMEGt1XdeBRf0MN4MEGzZ+r8rM+CUjgJYqQ= X-Google-Smtp-Source: AH8x226cB7YVuNJ0p7UG8Kictb+JZu2UxyBkfBFhtFnAtIRAHnkvOqsc6IRv8aORdXItuMKOvrAGtFxHYu2IFoaMfJk= X-Received: by 10.36.133.137 with SMTP id r131mr899206itd.88.1518268297672; Sat, 10 Feb 2018 05:11:37 -0800 (PST) MIME-Version: 1.0 Received: by 10.2.67.65 with HTTP; Sat, 10 Feb 2018 05:11:17 -0800 (PST) In-Reply-To: References: <151819851166.9466.6740951677204542727@wrigleys.postgresql.org> <20180210115937.aqoolac4yk66ajeu@alvherre.pgsql> From: Pantelis Theodosiou Date: Sat, 10 Feb 2018 13:11:17 +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="94eb2c05b0228e7ce20564db6215" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --94eb2c05b0228e7ce20564db6215 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Feb 10, 2018 at 1:02 PM, Pantelis Theodosiou wrote: > > > 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: > That is in: 7.13 General Rules 3, Case b: > > 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 i= n > 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. > > --94eb2c05b0228e7ce20564db6215 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Sat, Feb 10, 2018 at 1:02 PM, Pantelis Theodosiou = <ypercube@gmail.= com> wrote:


On Sat, Feb 10, 2018 at 11:59 AM, Alvaro Herrera &l= t;alvherre@alv= h.no-ip.org> wrote:
PG Doc comments fo= rm 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 except 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 = remember 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 di= fferent version but I see:
That is in:
7.13 <query expression>
Gene= ral Rules 3, Case b:

iii)
T= contains the following rows:
1) Let R be a row that is a duplicate of s= ome row in ET1 or of some row in ET2 or both. Let
m be the number of dup= licates of R in ET1 and let n be the number of duplicates of R in
ET2, w= here m =E2=89=A5 0 and n =E2=89=A5 0.
2) If DISTINCT is specified or imp= licit, then ....
...

3) If ALL is specified, then
Case:
A) = If UNION is specified, then the number of duplicates of R that T contains i= s (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) I= f INTERSECT is specified, then the number of duplicates of R that T contain= s is the
minimum of m and n.


--94eb2c05b0228e7ce20564db6215--