Re: UNION discards indentical rows in postgres 7.3.3

On Thu, 7 Aug 2003, Silvio Scarpati wrote:

> this seems a serious bug:
>
> testdb=>
> testdb=> create table t1(a int, b text);
> CREATE TABLE
> testdb=> create table t2(a int, b text);
> CREATE TABLE
> testdb=> insert into t1 values(1,'pippo');
> INSERT 7591667 1
> testdb=> insert into t1 values(2,'pluto');
> INSERT 7591668 1
> testdb=> insert into t2 values(3,'paperino');
> INSERT 7591669 1
> testdb=> insert into t2 values(3,'paperino');
> INSERT 7591670 1
>
> select a,b from t1 union (select a,b from t2);
>  a |    b
> ---+----------
>  1 | pippo
>  2 | pluto
>  3 | paperino
> (3 rows)
>
> Wrong ! The query should return 4 rows. In other words i don't know
> why postgres performs the following query:
>
> select a,b from t1 union (select DISTINCT a,b from t2);
>
> instead of the required one.

That is the required resultset.  Union is required to do return only
one copy of a row when there are duplicates of a row. Union all returns
a number of copies equal to the number of duplicates.