Re: weird GROUPING SETS and ORDER BY behaviour

On Fri, 5 Jan 2024 at 18:34, Zhang Mingli <zmlpostgres(at)gmail(dot)com> wrote:
>
> On Jan 6, 2024 at 01:38 +0800, Geoff Winkless <pgsqladmin(at)geoff(dot)dj>, wrote:
>
>
> Am I missing some reason why the first set isn't sorted as I'd hoped?
>
>
> Woo, it’s a complex order by, I try to understand your example.
> And I think the order is right, what’s your expected order result?

I was hoping to see

gp_n | gp_conc | n | concat
------+---------+------+--------
1 | 0 | NULL | n1x5
1 | 0 | NULL | n2x4
1 | 0 | NULL | n3x3
1 | 0 | NULL | n4x2
1 | 0 | NULL | n5x1
0 | 1 | n1 | NULL
0 | 1 | n2 | NULL
0 | 1 | n3 | NULL
0 | 1 | n4 | NULL
0 | 1 | n5 | NULL

because when gp_conc is 0, it should be ordering by the concat() value.

> https://www.postgresql.org/docs/current/functions-aggregate.html
> GROUPING ( group_by_expression(s) ) → integer
> Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included
>
> I guess you misunderstand it?

I don't think I did. I pass GROUPING(something) and if the current set
is being grouped by (something) then the return value will be 0.

> And your GROUPING target entry seems misleading, I modify it to:
>
> SELECT GROUPING(test1.n, (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))))::bit(2),
>
> test1.n, CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))
> FROM test1
> …skip
>
>
> To show the grouping condition:
>
> grouping | n | concat
> ----------+------+--------
> 10 | NULL | n5x1
> 10 | NULL | n4x2
> 10 | NULL | n3x3
> 10 | NULL | n2x4
> 10 | NULL | n1x5
> 01 | n1 | NULL
> 01 | n2 | NULL
> 01 | n3 | NULL
> 01 | n4 | NULL
> 01 | n5 | NULL
> (10 rows)

With respect, I've no idea why you think that's any clearer.

Geoff