Re: AW: [HACKERS] having and union in v7beta

Zeugswetter Andreas SB <ZeugswetterA(at)Wien(dot)Spardat(dot)at> writes:
>>> play=> explain select * from comuni union select * from comuni;

>> *However*, we have not fixed the bug that causes "select foo union
>> select foo" to be incorrectly simplified --- the UNION code is still
>> applying cnfify.

> But if the two queries are the same, the union CAN be simplified,
> since the union of two identical masses (I don't know the correct word here)
> is still that one mass.

> Thus 6.5 simplification is correct in this particular case.

No, it is NOT right, because we're dealing with multisets not sets
(I think "set" is the English word you were looking for).

The SQL spec specifies that UNION implies DISTINCT, ie, removal of
duplicate 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 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.

If query "select foo" would produce X, Y, Y, Z, then the correct result
of "select foo UNION select foo" is X, Y, Z. But that's not what 6.5
will give you.

I think it would be correct to simplify the union to "select DISTINCT foo"
but that requires all-new simplification code, as well as some thought
about how it'd interact with any DISTINCT or DISTINCT ON already present.

regards, tom lane