pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
5+ messages / 4 participants
[nested] [flat]
* BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
@ 2021-06-23 13:05 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2021-06-23 13:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: tobias.wendorff@tu-dortmund.de
The following bug has been logged on the website:
Bug reference: 17071
Logged by: Tobias Wendorff
Email address: tobias.wendorff@tu-dortmund.de
PostgreSQL version: 12.6
Operating system: Debian Buster
Description:
If the result set of a query has only one row, but an additional row gets
added by GROUP BY rollup(), a subsequent ORDER BY is ignored by the
planner.
test case:
https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=e467725a3fae6df51f7ad8e89052de98
query:
SELECT
'2021-01-01'::date AS month
GROUP BY
rollup(month)
ORDER BY
month NULLS FIRST;
query plan:
GroupAggregate (cost=0.00..0.03 rows=2 width=0) (actual time=0.003..0.003
rows=2 loops=1)
Group Key: '2021-01-01'::date
Group Key: ()
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000
rows=1 loops=1)
Planning time: 0.017 ms
Execution time: 0.027 ms
workaround:
CTE or subquery with subsequent sorting
Tested on dbfiddle.uk using versions 9.5, 9.6, 10, 11, 12 and 13.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
@ 2021-06-23 19:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2021-06-23 19:20 UTC (permalink / raw)
To: tobias.wendorff@tu-dortmund.de; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.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.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
@ 2021-06-23 19:40 Tobias Wendorff <tobias.wendorff@tu-dortmund.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Tobias Wendorff @ 2021-06-23 19:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Dear Tom,
On 23/06/2021 21:20 Tom Lane wrote:
> 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?
I'm a little bit surprised by your message. It is actually common to
make an example reproducible, but as small as possible. Please check
out the "KISS principle". It is therefore pretty unprofessional that
you devalue it as a "toy example".
Even this shorter example would have effectively shown the error:
SELECT 1 GROUP BY rollup(1) ORDER BY 1 NULLS FIRST;
To take some of the wind out of your criticism: Of course there is
a use case for the case. The whole thing stems from a complex query
and kept a colleague busy for a long time today because it led
tocompletely illogical results in some cases.
> So I'm wondering if this was reduced from a more realistic example
> that we might be able to fix in some other way.
To keep a long discussion short: you classify it as a wontfix.
Since I've already supplied a workaround (using a subquery or CTE),
let's close it.
Thanks,
Tobias
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
@ 2021-06-23 21:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tobias Wendorff <tobias.wendorff@tu-dortmund.de>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2021-06-23 21:48 UTC (permalink / raw)
To: Tobias Wendorff <tobias.wendorff@tu-dortmund.de>; +Cc: pgsql-bugs@lists.postgresql.org
Tobias Wendorff <tobias.wendorff@tu-dortmund.de> writes:
> To keep a long discussion short: you classify it as a wontfix.
> Since I've already supplied a workaround (using a subquery or CTE),
> let's close it.
[ shrug... ] If you intend to take such a totally uncooperative
approach, then I can't help you.
I did *not* say this is a "wontfix". I said it's not easy to fix.
There is a large difference. What I was wondering about was whether
we could find an easier change that would help your actual use-case.
But, since you're not seeing fit to provide further detail, you'll
just have to wait till we can create a fix for the case as presented.
That's likely to be a year or two down the pike.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup()
@ 2021-06-24 06:23 hubert depesz lubaczewski <depesz@depesz.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2021-06-24 06:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: tobias.wendorff@tu-dortmund.de; pgsql-bugs@lists.postgresql.org
On Wed, Jun 23, 2021 at 03:20:08PM -0400, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-06-24 06:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-23 13:05 BUG #17071: ORDER BY gets ignored when result set has only one row, but another one gets added by rollup() PG Bug reporting form <noreply@postgresql.org>
2021-06-23 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-23 19:40 ` Tobias Wendorff <tobias.wendorff@tu-dortmund.de>
2021-06-23 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-24 06:23 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox