Re: Wrong results with grouping sets

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Wrong results with grouping sets
Date: 2023-11-17 03:25:11
Message-ID: CAMbWs4_9_A77tFgUAKtMHV0X2rCFQAiY1oj+=E38NtTWkXUxxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Nov 16, 2023 at 11:25 PM Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
wrote:

> I noticed that this query worked correctly in the main branch with the
> inequality operator:
>
> postgres=# select distinct on (a, b) a, b from (values (3, 1), (2, 2)) as
> t (a, b) where a > b group by grouping sets((a, b), (a)); a | b ---+--- 3 |
> 1 3 | (2 rows)
>
> So, I think you are right)
>

Thanks for taking an interest in this patch and verifying it.

> I looked at your patch and noticed a few things:
>
> 1. I think you should add a test with the cube operator, because I noticed
> that the order of the query in the result has also changed:
>

Hmm, I'm not sure if that's necessary. The wrong result order you saw
here is caused by the same reason explained above: the planner fails to
realize that Var 'a' and 'b' are nullable by the grouping sets, making
them no longer always equal to each other. This issue should have been
covered in the tests added by v1 patch.

Thanks
Richard

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2023-11-17 03:38:19 Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500
Previous Message Michael Paquier 2023-11-17 02:26:58 Re: [HACKERS] Should logtape.c blocks be of type long?