From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | jao(at)geophile(dot)com, pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Documentation of EXCEPT ALL may have a bug |
Date: | 2018-02-10 19:06:00 |
Message-ID: | 5573.1518289560@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> 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?
It's exactly what the standard says to do: if there are M occurrences
of a row value in the LHS, and N occurrences in the RHS, emit
max(M-N, 0) copies of the row. To my mind that's a reasonable definition
of EXCEPT if you suppose that nominally-identical rows are matched and
discarded one by one, rather than with de-duplication occurring
beforehand.
> [*] I didn't try terribly hard, but couldn't actually find where the
> behavior is defined.
In SQL:2011, it's 7.13 <query expression> general rule 3) b) iii) 3) B),
on page 420 in the draft version I have.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-02-10 20:54:46 | Re: Functions in sort order - undocumented restriction |
Previous Message | Tom Lane | 2018-02-10 18:56:37 | Re: Functions in sort order - undocumented restriction |