Re: weird GROUPING SETS and ORDER BY behaviour

Hi,

Zhang Mingli
www.hashdata.xyz
On Jan 6, 2024 at 23:38 +0800, Geoff Winkless <pgsqladmin(at)geoff(dot)dj>, wrote:
>
> 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.
Hi, I misunderstand and thought you want to see the rows of gp_n = 0 first.
So you’re not satisfied with the second key of Order By.
I simply the SQL to show that the difference exists:

SELECT GROUPING(test1.n) AS gp_n,
GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) AS gp_conc,
 test1.n,
 CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))
 FROM test1
GROUP BY GROUPING SETS( (test1.n), (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) )
HAVING n is NULL
ORDER BY concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) NULLS FIRST;
 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
(5 rows)

This is what you want, right?

And if there is a CASE WHEN, the order changed:

SELECT GROUPING(test1.n) AS gp_n,
GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) AS gp_conc,
 test1.n,
 CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))
 FROM test1
GROUP BY GROUPING SETS( (test1.n), (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) )
HAVING n is NULL
ORDER BY CASE WHEN true THEN concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) END NULLS FIRST;
 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
(5 rows)

I haven’t dinged into this and it seems sth related with CASE WHEN.
A case when true will change the order.