Обсуждение: weird GROUPING SETS and ORDER BY behaviour

We have some (generated) SQL that uses grouping sets to give us the
same data grouped in multiple ways (with sets of groups configurable
by the user), with the ordering of the rows the same as the grouping
set. This generally works fine, except for when one of the grouping
sets contains part of another grouping set joined against a subquery
(at least, I think that's the trigger).

Minimal example here:

SELECT seq, CONCAT('n', seq) AS n INTO TEMP TABLE test1 FROM
generate_series(1,5) AS seq;
SELECT seq, CONCAT('x', 6-seq) AS x INTO TEMP TABLE test2 FROM
generate_series(1,5) AS seq;

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)))
)
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;
 gp_n | gp_conc | n  | concat
------+---------+----+--------
    1 |       0 |    | n5x1
    1 |       0 |    | n4x2
    1 |       0 |    | n3x3
    1 |       0 |    | n2x4
    1 |       0 |    | n1x5
    0 |       1 | n1 |
    0 |       1 | n2 |
    0 |       1 | n3 |
    0 |       1 | n4 |
    0 |       1 | n5 |


Am I missing some reason why the first set isn't sorted as I'd hoped?
Is the subquery value in the ORDER BY not the same as the value in the
main query? That seems... frustrating. I'd like to be able to say
"order by column (n)" but I don't think I can?

On Centos7, with the latest pg12 from the pg repo:
PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit

Thanks

Geoff

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.

> 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
assignedwith the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding
expressionis included in the grouping criteria of the grouping set generating the current result row, and 1 if it is
notincluded 
>
> I guess you misunderstand it?

I don't think I did. I pass GROUPING(something) and if the current set
is being grouped by (something) then the return value will be 0.

> 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)


With respect, I've no idea why you think that's any clearer.

Geoff

On Sat, 6 Jan 2024 at 16:22, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Sat, Jan 6, 2024 at 8:38 AM Geoff Winkless <pgsqladmin@geoff.dj> wrote:
>> 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.

That's disappointing.

> 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

Ahh, well, yes, that's fine in this instance which (as you may
remember) was a minimal example of the behaviour, but wouldn't be
useful in the real-world situation, where we can have many
potentially-conflicting grouping sets, each set needing to be ordered
consistently internally.

Geoff

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Something does seem off here with the interaction between grouping sets and
> order by.

Yeah.  I think Geoff is correct to identify the use of subqueries in
the grouping sets as the triggering factor.  We can get some insight
by explicitly printing the ordering values:

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)),
  CASE WHEN GROUPING(test1.n)=0 THEN test1.n ELSE NULL END as o1,
  CASE WHEN GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)))=0 THEN concat(test1.n, (SELECT x FROM
test2WHERE seq=test1.seq)) ELSE NULL END as o2 
FROM test1
GROUP BY
GROUPING SETS(
  (test1.n),
  (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)))
)
ORDER BY o1 NULLS FIRST, o2 NULLS FIRST;

which produces

 gp_n | gp_conc | n  | concat | o1 | o2
------+---------+----+--------+----+----
    1 |       0 |    | n5x1   |    | x1
    1 |       0 |    | n4x2   |    | x2
    1 |       0 |    | n3x3   |    | x3
    1 |       0 |    | n2x4   |    | x4
    1 |       0 |    | n1x5   |    | x5
    0 |       1 | n1 |        | n1 |
    0 |       1 | n2 |        | n2 |
    0 |       1 | n3 |        | n3 |
    0 |       1 | n4 |        | n4 |
    0 |       1 | n5 |        | n5 |
(10 rows)

Those columns appear correctly sorted, so it's not the sort that
is misbehaving.  But how come the values don't match the "concat"
column where they should?  EXPLAIN VERBOSE gives a further clue:


                      QUERY PLAN
                                                       

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=53622.76..53623.76 rows=400 width=136)
   Output: (GROUPING(test1.n)), (GROUPING(concat(test1.n, (SubPlan 1)))), test1.n, (concat(test1.n, (SubPlan 2))),
(CASEWHEN (GROUPING(test1.n) = 0) THEN test1.n ELSE NULL::text END), (CASE WHEN (GROUPING(concat(test1.n, (SubPlan 3)))
=0) THEN concat(test1.n, (SubPlan 4)) ELSE NULL::text END) 
   Sort Key: (CASE WHEN (GROUPING(test1.n) = 0) THEN test1.n ELSE NULL::text END) NULLS FIRST, (CASE WHEN
(GROUPING(concat(test1.n,(SubPlan 3))) = 0) THEN concat(test1.n, (SubPlan 4)) ELSE NULL::text END) NULLS FIRST 
   ->  HashAggregate  (cost=32890.30..53605.48 rows=400 width=136)
         Output: GROUPING(test1.n), GROUPING(concat(test1.n, (SubPlan 1))), test1.n, (concat(test1.n, (SubPlan 2))),
CASEWHEN (GROUPING(test1.n) = 0) THEN test1.n ELSE NULL::text END, CASE WHEN (GROUPING(concat(test1.n, (SubPlan 3))) =
0)THEN concat(test1.n, (SubPlan 4)) ELSE NULL::text END 
         Hash Key: test1.n
         Hash Key: concat(test1.n, (SubPlan 2))
         ->  Seq Scan on public.test1  (cost=0.00..32887.12 rows=1270 width=68)
               Output: test1.n, concat(test1.n, (SubPlan 2)), test1.seq
               SubPlan 2
                 ->  Seq Scan on public.test2  (cost=0.00..25.88 rows=6 width=32)
                       Output: test2.x
                       Filter: (test2.seq = test1.seq)
         SubPlan 4
           ->  Seq Scan on public.test2 test2_1  (cost=0.00..25.88 rows=6 width=32)
                 Output: test2_1.x
                 Filter: (test2_1.seq = test1.seq)
(17 rows)

We have ended up with four distinct SubPlans (two of which seem to
have gotten dropped because they are inside GROUPING functions,
which never really evaluate their arguments).  What I think happened
here is that the parser let the concat() expressions in the targetlist
and ORDER BY through because they were syntactically identical to
GROUPING SET expresions --- but later on, the planner expanded each
of the sub-selects to a distinct SubPlan, and that meant that those
subexpressions were no longer identical, and so most of them didn't
get converted to references to the grouping key column output by the
HashAggregate node.  Because they didn't get converted, they fail to
do the right thing in rows where they should go to NULL because
they're from the wrong grouping set.  The "concat" targetlist element
did get converted, so it behaves correctly, and the GROUPING functions
don't actually care because they have a different method for
determining what they should output.  But you get the wrong answer for
the concat() inside the "o2" expression: it gets evaluated afresh
using the nulled value of test1.n.

I think this particular symptom might be new, but we've definitely
seen related trouble reports before.  I'm inclined to think that the
right fix will require making the parser actually replace such
expressions with Vars referencing a notional grouping output relation,
so that there's not multiple instances of the sub-query in the parser
output in the first place.  That's a fairly big job and nobody's
tackled it yet.

In the meantime, what I'd suggest as a workaround is to put those
subexpressions into a sub-select with an optimization fence (you
could use OFFSET 0 or a materialized CTE), so that the grouping
sets list in the outer query just has simple Vars as elements.

[Digression: the SQL spec *requires* grouping set elements to be
simple Vars, and I begin to see why when contemplating examples like
this.  It's a little weird that "concat(test1.n, ...)" can evaluate
with a non-null value of test1.n in a row where test1.n alone would
evaluate as null.  However, we've dug this hole for ourselves and now
we have to deal with the consequences.]

            regards, tom lane

On Sat, 6 Jan 2024 at 23:27, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

> Well yes. I assumed that since it's required that a group expression is in the query itself that
> the grouping values were taken from the result set, I have to admit to some surprise that
> they're calculated twice (three times?).

Seems there was a reason why I thought that: per the documentation:

"The arguments to the GROUPING function are not actually evaluated,
but they must exactly match expressions given in the GROUP BY clause
of the associated query level."

https://www.postgresql.org/docs/16/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE

Mildly interesting: you can pass column positions to GROUP BY and
ORDER BY but if you try to pass a position to GROUPING() (I wondered
if that would help the engine somehow) it fails:

SELECT
  test1.n,
  CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))
FROM test1
GROUP BY
GROUPING SETS(
  1,
  2
)
ORDER BY
  CASE WHEN GROUPING(1)=0 THEN 1 ELSE NULL END NULLS FIRST,
  CASE WHEN GROUPING(2)=0 THEN 2 ELSE NULL END NULLS FIRST;

ERROR:  arguments to GROUPING must be grouping expressions of the
associated query level

Geoff

On Mon, 8 Jan 2024 at 10:23, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

> Seems there was a reason why I thought that: per the documentation:
>
> "The arguments to the GROUPING function are not actually evaluated,
> but they must exactly match expressions given in the GROUP BY clause
> of the associated query level."
>
> https://www.postgresql.org/docs/16/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE

To throw a spanner in the works, it looks like it's not the test
itself that's failing: it's putting the ORDERing in a CASE at all that
fails.

... ORDER BY
  CASE WHEN GROUPING(test1.n) THEN 1 ELSE NULL END NULLS FIRST, CASE
WHEN true THEN 2 ELSE 2 END;
 n  | concat
----+--------
 n1 |
 n2 |
 n3 |
 n4 |
 n5 |
    | n3x3
    | n5x1
    | n2x4
    | n1x5
    | n4x2

but without the CASE works fine:

... ORDER BY
  CASE WHEN GROUPING(test1.n) THEN 1 ELSE NULL END NULLS FIRST, 2;
 n  | concat
----+--------
 n4 |
 n2 |
 n3 |
 n5 |
 n1 |
    | n1x5
    | n2x4
    | n3x3
    | n4x2
    | n5x1

What's even more of a head-scratcher is why fixing this this then
breaks the _first_ group's ORDERing.

It _looks_ like removing the CASE altogether and ordering by the
GROUPING value for all the grouping sets first:

ORDER BY
  GROUPING(test1.n,CONCAT(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq))), 1, 2;

actually works. I'm trying to figure out if that scales up or if it's
just dumb luck that it works for my example.

Geoff

On Mon, 8 Jan 2024 at 11:12, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> What's even more of a head-scratcher is why fixing this this then
> breaks the _first_ group's ORDERing.

Ignore that. Finger slippage - looking back I realised I forgot the
"=0" test after the GROUPING() call.

It looks like I'm going to go with

ORDER BY GROUPING(test1.n), test1.n, GROUPING(CONCAT(....)), CONCAT(...)

because it's easier to build the query sequentially that way than
putting all the GROUPING tests into a single ORDER, and it does seem
to work OK.

Geoff

Mildly interesting: you can pass column positions to GROUP BY and
ORDER BY but if you try to pass a position to GROUPING() (I wondered
if that would help the engine somehow) it fails: