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 4e67ff47-6832-46b3-bd47-b186651778fe@Spark
Whole thread Raw
In response to weird GROUPING SETS and ORDER BY behaviour  (Geoff Winkless <pgsqladmin@geoff.dj>)
Responses Re: weird GROUPING SETS and ORDER BY behaviour
List pgsql-hackers
Hi, 


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








pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: psql not responding to SIGINT upon db reconnection
Next
From: Robert Haas
Date:
Subject: Re: Oversight in reparameterize_path_by_child leading to executor crash