Re: weird GROUPING SETS and ORDER BY behaviour
| От | David G. Johnston |
|---|---|
| Тема | Re: weird GROUPING SETS and ORDER BY behaviour |
| Дата | |
| Msg-id | CAKFQuwbwGAK4WyhN1mT60=bYg8dSuoAjyyOUUGyYJcroxGD=4Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: weird GROUPING SETS and ORDER BY behaviour (Geoff Winkless <pgsqladmin@geoff.dj>) |
| Ответы |
Re: weird GROUPING SETS and ORDER BY behaviour
Re: weird GROUPING SETS and ORDER BY behaviour |
| Список | pgsql-hackers |
On Sat, Jan 6, 2024 at 8:38 AM Geoff Winkless <pgsqladmin@geoff.dj> wrote:
On Fri, 5 Jan 2024 at 18:34, Zhang Mingli <zmlpostgres@gmail.com> wrote:
>
> 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?
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:
ORDER BY
n nulls first , x nulls first
n nulls first , x nulls first
Where x is the assigned alias for the concatenation expression column.
David J.
В списке pgsql-hackers по дате отправления: