Thread: weird GROUPING SETS and ORDER BY behaviour

weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:
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



Re: weird GROUPING SETS and ORDER BY behaviour

From
Zhang Mingli
Date:
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)








Re: weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:
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



Re: weird GROUPING SETS and ORDER BY behaviour

From
"David G. Johnston"
Date:
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

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

David J.

Re: weird GROUPING SETS and ORDER BY behaviour

From
Zhang Mingli
Date:
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.




Re: weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:
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



Re: weird GROUPING SETS and ORDER BY behaviour

From
Tom Lane
Date:
"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



Re: weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:

On Sat, 6 Jan 2024, 19:49 Tom Lane, <tgl@sss.pgh.pa.us> wrote:
"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.  
[snip]
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. 

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

That's a fairly big job and nobody's
tackled it yet.

For what it's worth, as a user if we could reference a column alias in the GROUP and ORDER sections, rather than having to respecify the expression each time, that would be a far more friendly solution. Not sure it makes the work any less difficult though.

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.

Not possible in our case, sadly - at least not without a complete redesign of our SQL-generating code. It would be (much) easier to add a sort to the output stage, tbh, and stop lazily relying on the output being sorted for us; I guess that's the route we'll have to take.

Thanks all for taking the time to look at it.

Geoff

Re: weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:
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



Re: weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:
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



Re: weird GROUPING SETS and ORDER BY behaviour

From
Geoff Winkless
Date:
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



Re: weird GROUPING SETS and ORDER BY behaviour

From
"David G. Johnston"
Date:
On Monday, January 8, 2024, Geoff Winkless <pgsqladmin@geoff.dj> wrote

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:

The symbol 1 is ambigious - it can be the number or a column reference.  In a compound expression it is always the number, not the column reference.

David J.