Re: weird GROUPING SETS and ORDER BY behaviour - Mailing list pgsql-hackers

From Zhang Mingli
Subject Re: weird GROUPING SETS and ORDER BY behaviour
Date
Msg-id abda3d0f-0f7a-4365-9886-1c1beb03c068@Spark
Whole thread Raw
In response to Re: weird GROUPING SETS and ORDER BY behaviour  (Geoff Winkless <pgsqladmin@geoff.dj>)
List pgsql-hackers
Hi, 


Zhang Mingli
www.hashdata.xyz
On Jan 6, 2024 at 23:38 +0800, Geoff Winkless <pgsqladmin@geoff.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.




pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: introduce dynamic shared memory registry
Next
From: Joe Conway
Date:
Subject: Re: Password leakage avoidance