Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: tobias(dot)wendorff(at)tu-dortmund(dot)de, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
Date: 2021-06-24 06:23:34
Message-ID: 20210624062334.GA3131@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Wed, Jun 23, 2021 at 03:20:08PM -0400, Tom Lane wrote:
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > SELECT
> > '2021-01-01'::date AS month
> > GROUP BY
> > rollup(month)
> > ORDER BY
> > month NULLS FIRST;
> > [ produces unsorted output ]
>
> Hm, that's certainly a bug, but so far as I can tell it's specific to the
> case of a constant value being used as the GROUP BY/ORDER BY target.
> Which doesn't seem very likely to be interesting in practice. Do you have
> a non-toy example where things go wrong?
>
> The issue here is that ORDER BY a constant is normally deemed to be a
> no-op. Our parse representation fails to make it clear that in this
> situation, the "constant" column isn't so constant after the GROUP BY has
> been applied. There's been some discussion of changing that, but it's a
> large task and isn't likely to happen overnight (much less be a plausible
> candidate for back-patching). So I'm wondering if this was reduced from
> a more realistic example that we might be able to fix in some other way.

Well, the problem happens only when there is only one row, so I'd say
all examples are kinda toy-examples.

But - it can be reproduced using:

$ select relkind, count(*) from pg_class where relkind = 'r' group by rollup(relkind) order by relkind nulls first;
relkind │ count
─────────┼───────
r │ 68
[null] │ 68
(2 rows)

if there are more rows, nulls got to proper position:

$ select relkind, count(*) from pg_class where relkind in( 'r', 'i') group by rollup(relkind) order by relkind nulls first;
relkind │ count
─────────┼───────
[null] │ 227
i │ 159
r │ 68
(3 rows)

Best regards,

depesz

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Vladimir Shvartsgor 2021-06-24 06:29:40 Re: Example in "42.8. Transaction Management" doesn't work for PostgreSQL v 12.7
Previous Message Sudheer H R 2021-06-24 01:53:37 Re: Found a buffer-overflow defect in asynchronous database connection API PQconnectPoll