Re: weird GROUPING SETS and ORDER BY behaviour

On Sat, Jan 6, 2024 at 8:38 AM Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> 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.
>
>
Something does seem off here with the interaction between grouping sets and
order by. I'm inclined to believe that using grouping in the order by
simply is an unsupported concept we fail to prohibit. The discussion
around union all equivalency and grouping happening well before order by
lead me to this conclusion.

You can get the desired result with a much less convoluted order by clause
- so long as you understand where your nulls are coming from - with:

https://dbfiddle.uk/Uk22nPIZ

ORDER BY
n nulls first , x nulls first

Where x is the assigned alias for the concatenation expression column.

David J.