From: | Oleg Broytmann <phd(at)sun(dot)med(dot)ru> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] Oops, I seem to have changed UNION's behavior |
Date: | 1999-02-08 08:45:16 |
Message-ID: | Pine.SOL2.3.96.SK.990208114124.29949B-100000@sun.med.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello!
On Sun, 7 Feb 1999, Tom Lane wrote:
> The equal() updates I installed yesterday (to fix the "don't know
> whether nodes of type 600 are equal" problem) have had an unintended
> side effect.
>
> Am I right in thinking that UNION (without ALL) is defined to do a
> DISTINCT on its result, so that duplicates are removed even if the
> duplicates both came from the same source table? That's what 6.4.2
> does, but I do not know if it's strictly kosher according to the SQL
> spec.
Yes, this is standard. My books (primary, Gruber) say UNION should work
this way - UNION without ALL implies DISTINCT.
> If so, the code is now busted, because with the equal() extension in
> place, cnfify() is able to recognize and remove duplicate select
> clauses. That is, "SELECT xxx UNION SELECT xxx" will be folded to
> just "SELECT xxx" ... and that doesn't mean the same thing.
>
> An actual example: given the data
>
> play=> select a from tt;
> a
> -
> 1
> 1
> 2
> 3
> (4 rows)
>
> Under 6.4.2 I get:
>
> play=> select a from tt union select a from tt;
> a
> -
> 1
> 2
> 3
> (3 rows)
>
> Note lack of duplicate "1". Under current sources I get:
>
> ttest=> select a from tt union select a from tt;
> a
> -
> 1
> 1
> 2
> 3
> (4 rows)
>
> since the query is effectively reduced to just "select a from tt".
I am sure my books did not consider such case as UNION that could be
otimized this way. Not sure what is Right Thing here...
Oleg.
----
Oleg Broytmann National Research Surgery Centre http://sun.med.ru/~phd/
Programmers don't die, they just GOSUB without RETURN.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Meskes | 1999-02-08 10:28:31 | Embedded SQL question |
Previous Message | Michael Meskes | 1999-02-08 07:47:33 | Re: [HACKERS] v6.4.3 ? |