Re: Final Patch for GROUPING SETS

2014-08-26 2:45 GMT+02:00 Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>:

> >>>>> "Pavel" == Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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)
>