"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
containingthe 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
bethe number of duplicates of R in T1 and let n be the number of duplicates of R in T2,
wherem >= 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
