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,
