public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Documentation of EXCEPT ALL may have a bug
Date: Sat, 10 Feb 2018 14:06:00 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Alvaro Herrera <[email protected]> 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

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Documentation of EXCEPT ALL may have a bug
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox