To: "Aaron Bono" <postgresql@aranya.com>
cc: "Postgres SQL language list" <pgsql-sql@postgresql.org>
Subject: Re: Unions and Grouping 
In-reply-to: <bf05e51c0612151315s59e6647etc2dd45e940d41a2a@mail.gmail.com> 
References: <bf05e51c0612151315s59e6647etc2dd45e940d41a2a@mail.gmail.com>
Comments: In-reply-to "Aaron Bono" <postgresql@aranya.com>
	message dated "Fri, 15 Dec 2006 15:15:12 -0600"
Date: Fri, 15 Dec 2006 16:42:04 -0500
Message-ID: <21818.1166218924@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Aaron Bono" <postgresql@aranya.com> writes:
> My question, what do the SQL Specifications say should happen on a Union?
> Is it supposed to eliminate duplicates even WITHIN the individual queries
> that are being unioned?

Yes.  SQL92 7.10 saith:

            b) If a set operator is specified, then the result of applying
              the set operator is a table containing the following rows:

              i) Let R be a row that is a duplicate of some row in T1 or of
                 some row in T2 or both. Let m be the number of duplicates
                 of R in T1 and let n be the number of duplicates of R in
                 T2, where m >= 0 and n >= 0.

             ii) If ALL is not specified, then

                 Case:

                 1) If UNION is specified, then

                   Case:

                   A) If m > 0 or n > 0, then T contains exactly one dupli-
                      cate of R.

                   B) Otherwise, T contains no duplicate of R.


			regards, tom lane