pgsql: Fix GROUP BY column removal for NULLS NOT DISTINCT indexes

Fix GROUP BY column removal for NULLS NOT DISTINCT indexes

remove_useless_groupby_columns() removes GROUP BY columns that are
functionally dependent on other GROUP BY columns, as proven by a
unique index. The index columns are normally required to be NOT NULL,
but commit bd10ec529 relaxed that for indexes defined with NULLS NOT
DISTINCT, on the grounds that such an index allows at most one row
with a NULL in the indexed columns.

That reasoning does not hold if the relation is on the nullable side
of an outer join. Rows that are null-extended by the join also have
NULLs in the indexed columns, so they fall into the same group as the
relation's one real NULL row once the other columns are removed from
the GROUP BY, even though they differ in those columns. This results
in wrong query results.

To fix, skip the NULLS NOT DISTINCT relaxation for relations whose
GROUP BY Vars can be nulled by an outer join, and insist on NOT NULL
index columns as before. That case remains safe, since no real row
can then be mistaken for a null-extended one.

Back-patch to v18, where the NULLS NOT DISTINCT relaxation was
introduced.

Author: Richard Guo <guofenglinux(at)gmail(dot)com>
Reviewed-by: wenhui qiu <qiuwenhuifx(at)gmail(dot)com>
Discussion: https://postgr.es/m/CAMbWs484GmrQNb6nYV8cbV2sq2a7DKDDb=WwJS=2rtS8ma+Mxw@mail.gmail.com
Backpatch-through: 18

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/48938780c3577835dda3dd17db4f8c968bb1e166

Modified Files
--------------
src/backend/optimizer/plan/initsplan.c | 12 ++++++++++--
src/test/regress/expected/aggregates.out | 16 ++++++++++++++++
src/test/regress/sql/aggregates.sql | 5 +++++
3 files changed, 31 insertions(+), 2 deletions(-)