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