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

Поиск
Список
Период
Сортировка
Искать

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

От:
Richard Guo <rguo@postgresql.org>
Дата:
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 
Reviewed-by: wenhui qiu 
Discussion: https://postgr.es/m/CAMbWs484GmrQNb6nYV8cbV2sq2a7DKDDb=WwJS=2rtS8ma+Mxw@mail.gmail.com
Backpatch-through: 18

Branch
------
REL_19_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/dc8347ca63b9696117661b0362cf147904aea9fe

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(-)

FAQ