Grouping id not honouring group order

[sebastian]

@dandanlen writes on slack:

This difference between (1,2) and (2,1) breaks some of the assumptions of the grouping_id function, namely that the grouping order doesn't matter. Both of these combinations will have the same grouping_id despite not really being equivalent.)

In this context (1, 2) means GROUP BY 1, 2, and the point being made refers to us dropping columns right to left in GROUP BY order when trying to refine values.

[cristianberneanu]

I don't think we can fix the grouping_id function, since this is how it works on most backends and we need to be able to offload it. We probably need to add a new API that works properly for anonymized groups.

[dandanlen]

I don't think there's much to be done here to be honest, apart from mentioning it in the docs. I noticed because I wanted to group by grouping sets ((1,2), (2,1)) and then use the grouping_id to distinguish. There is a simple workaround which is to run two separate queries, one with group by (1,2), the other with group by (2,1).

Also, note the number of possible combinations for a group of size n is n! so (a) querying for an exhaustive list of combinations (group by grouping sets ((1,2,3,4), (1,2,4,3), ...)) is impractical for larger groups and (b) encoding the group order efficiently might be a challenge...