From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Silvio Scarpati <silvio(dot)scarpati(at)virgilio(dot)it> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: UNION discards indentical rows in postgres 7.3.3 |
Date: | 2003-08-07 22:10:00 |
Message-ID: | 20030807150316.K41658-100000@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ruslan A Dautkhanov | 2003-08-08 05:17:32 | upcoming 7.4 relaese: absent recode() function ? |
Previous Message | Rod Taylor | 2003-08-07 21:15:19 | Re: UNION discards indentical rows in postgres 7.3.3 |