Thread: UNION discards indentical rows in postgres 7.3.3

Hi Tom,

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.

I think i's a bug.

Thank you.

Silvio Scarpati

> Wrong ! The query should return 4 rows. In other words i don't know
> why postgres performs the following query:

I think the syntax you're looking for is UNION ALL.

select a,b from t1 union all (select distinct a,b from t2);

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.

Hi Stephan,

Thanks a lot for the answer.

On Thu, 7 Aug 2003 15:10:00 -0700 (PDT), you wrote:

>> 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.

Right ! i forgot that :-) (blushing).
Sorry.


Thank you again,

Silvio Scarpati