2014-08-26 2:45 GMT+02:00 Andrew Gierth <and...@tao11.riddles.org.uk>:
> >>>>> "Pavel" == Pavel Stehule <pavel.steh...@gmail.com> writes: > > Pavel> Hi > Pavel> I checked this patch, and it working very well > > Pavel> I found only two issue - I am not sure if it is issue > > Pavel> It duplicate rows > > Pavel> postgres=# explain select name, place, sum(count), grouping(name), > Pavel> grouping(place) from cars group by rollup(name, place), name; > Pavel> QUERY PLAN > Pavel> > ------------------------------------------------------------------------ > Pavel> GroupAggregate (cost=10000000001.14..10000000001.38 rows=18 > width=68) > Pavel> Grouping Sets: (name, place), (name), (name) > > I think I can safely claim from the spec that our version is correct. > Following the syntactic transformations given in 7.9 <group by clause> > of sql2008, we have: > > GROUP BY rollup(name,place), name; > > parses as GROUP BY <rollup list>, <ordinary grouping set> > > Syntax rule 13 replaces the <rollup list> giving: > > GROUP BY GROUPING SETS ((name,place), (name), ()), name; > > Syntax rule 16b gives: > > GROUP BY GROUPING SETS ((name,place), (name), ()), GROUPING SETS (name); > > Syntax rule 16c takes the cartesian product of the two sets: > > GROUP BY GROUPING SETS ((name,place,name), (name,name), (name)); > > Syntax rule 17 gives: > > SELECT ... GROUP BY name,place,name > UNION ALL > SELECT ... GROUP BY name,name > UNION ALL > SELECT ... GROUP BY name > > Obviously at this point the extra "name" columns become redundant so > we eliminate them (this doesn't correspond to a spec rule, but doesn't > change the semantics). So we're left with: > > SELECT ... GROUP BY name,place > UNION ALL > SELECT ... GROUP BY name > UNION ALL > SELECT ... GROUP BY name > > Running a quick test on sqlfiddle with Oracle 11 suggests that Oracle's > behavior agrees with my interpretation. > > Nothing in the spec that I can find licenses the elimination of > duplicate grouping sets except indirectly via feature T434 (GROUP BY > DISTINCT ...), which we did not attempt to implement. > > ok, I'll try to search in my memory to find some indices, so redundant columns should be reduced, Regards Pavel > -- > Andrew (irc:RhodiumToad) >
