Re: weird GROUPING SETS and ORDER BY behaviour

Hi,

Zhang Mingli
www.hashdata.xyz
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?

```
ORDER BY
 CASE WHEN GROUPING(test1.n)=0 THEN test1.n ELSE NULL END NULLS FIRST,
 CASE WHEN GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq)))=0 THEN concat(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq)) ELSE NULL END NULLS FIRST;
```
You want to Order by a, b where a is: CASE WHEN GROUPING(test1.n)=0 THEN test1.n ELSE NULL END NULLS FIRST.
GROUPING(test1.n)=0 means that your are within grouping set test1.n and the value is test1.n, so results of another grouping
set b is NULL, and you specific  NULL FIRST.

So your will first get the results of grouping set b while of course, column gp_n GROUPING(test1.n) is 1.
The result is very right.

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

NB: the Grouping bit is set to 1 when this column is not included.

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?

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)