Thread

1. AW: [HACKERS] having and union in v7beta

   Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2000-03-02T09:03:14Z

   >> 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.  (Which it probably shouldn't, but I haven't wanted
   > to touch that code until I have the time to rewrite it completely.)
   > The reason 7.0beta1 generates the "right" answer is that it has a
   > recently-introduced bug in the comparison routines that causes it to
   > think the two select subqueries aren't the same.
   
   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.
   
   Andreas
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-03-02T15:12:21Z

   Zeugswetter Andreas SB <ZeugswetterA@Wien.Spardat.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
   
   

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

   Peter Eisentraut <e99re41@docs.uu.se> — 2000-03-02T16:23:35Z

   On Thu, 2 Mar 2000, Zeugswetter Andreas SB wrote:
   
   > 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.
   
   "set" :)
   
   > 
   > Thus 6.5 simplification is correct in this particular case.
   
   The issue here seems to be that the queries could have side-effects, such
   as
   
   select nextval('sequence1')
     union
   select nextval('sequence1')
   
   which should arguably return two distinct rows. I gotta reread SQL's
   opinion on this, but I'm sure Tom has already done that. From a
   mathematical point of view, I believe your assumption "lexically equal
   queries yield mathematically equal sets" is wrong.
   
   -- 
   Peter Eisentraut                  Sernanders väg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden