On Sat, Feb 10, 2018 at 1:02 PM, Pantelis Theodosiou <yperc...@gmail.com> wrote:
> > > On Sat, Feb 10, 2018 at 11:59 AM, Alvaro Herrera <alvhe...@alvh.no-ip.org> > 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 <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. >> >> -- >> > > I may have a different version but I see: > That is in: 7.13 <query expression> 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 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 ≥ 0 and n ≥ 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 – 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. > >
