>>>>> "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. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers