pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
4+ messages / 3 participants
[nested] [flat]

* BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
@ 2026-10-18 23:22  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 4+ messages in thread

From: PG Bug reporting form @ 2026-10-18 23:22 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com

The following bug has been logged on the website:

Bug reference:      19772
Logged by:          Alperen Keles
Email address:      alpkeles99@gmail.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 24.04 x86_64 (also macOS arm64)
Description:        

Hello,

With an ndistinct extended statistics object on a table, a GROUP BY
that has a plain column in every grouping set and an expression of the
same column in only some of them fails at plan time:

  CREATE TABLE t0 (c0 int8, c1 int8);
  INSERT INTO t0 VALUES (NULL, NULL);
  CREATE STATISTICS t0_s ON c0, c1 FROM t0;
  ANALYZE t0;
  SELECT c0 FROM t0 GROUP BY c0, ROLLUP (abs(c0));
  ERROR:  XX000: corrupt MVNDistinct entry
  LOCATION:  estimate_multivariate_ndistinct, selfuncs.c:4485   (18.6)

CREATE STATISTICS makes ANALYZE store, among other things, the number
of distinct values of each combination of two or more of the listed
columns, and the planner uses those to estimate how many groups a GROUP
BY yields. ROLLUP (abs(c0)) means the grouping sets (c0, abs(c0)) and
(c0), so the planner estimates the group count of each set separately.

The NULL row is not needed: any non-empty table that is analyzed after
the statistics object is created fails the same way (for example 1000
rows of (i % 10, i % 7)), and so does EXPLAIN. The same error comes
from CUBE (abs(c0)), GROUPING SETS ((c0, abs(c0)), (c0)),
GROUP BY abs(c0), ROLLUP (c0), from c0 + 1, c0::text and c0::numeric
instead of abs(c0), from statistics with only the ndistinct kind, on
three columns, or ON c0, abs(c0), and from a view over the query. It
does not happen with a plain GROUP BY c0, abs(c0), with
ROLLUP (c0, abs(c0)), with GROUP BY c0, c1, ROLLUP (abs(c0)), with an
expression over a different column (abs(c1), c0 + c1), with only the
dependencies or mcv kinds, or before the table is analyzed. It is an
ordinary ERROR; the cassert builds do not hit an assertion.

Reproduced on 18.6 (REL_18_STABLE dfbc1507cdb), 19rc1 (REL_19_STABLE
1212d5d33b1) and master a261fc2b722, Ubuntu 24.04 x86_64,
--enable-cassert; also on 18.6 and master on macOS arm64. 14.24
(REL_14_STABLE 14904052e65) runs all of these queries without error.

Found with a query fuzzer.

Regards,
Alperen Keles








^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
@ 2026-10-20 19:30  Matheus Alcantara <matheusssilv97@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 4+ messages in thread

From: Matheus Alcantara @ 2026-10-20 19:30 UTC (permalink / raw)
  To: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Richard Guo <guofenglinux@gmail.com>

On Sun Oct 18, 2026 at 8:22 PM -03, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      19772
> Logged by:          Alperen Keles
> Email address:      alpkeles99@gmail.com
> PostgreSQL version: 18.6
> Operating system:   Ubuntu 24.04 x86_64 (also macOS arm64)
> Description:
>
> Hello,
>
> With an ndistinct extended statistics object on a table, a GROUP BY
> that has a plain column in every grouping set and an expression of the
> same column in only some of them fails at plan time:
>
>   CREATE TABLE t0 (c0 int8, c1 int8);
>   INSERT INTO t0 VALUES (NULL, NULL);
>   CREATE STATISTICS t0_s ON c0, c1 FROM t0;
>   ANALYZE t0;
>   SELECT c0 FROM t0 GROUP BY c0, ROLLUP (abs(c0));
>   ERROR:  XX000: corrupt MVNDistinct entry
>   LOCATION:  estimate_multivariate_ndistinct, selfuncs.c:4485   (18.6)
>
> CREATE STATISTICS makes ANALYZE store, among other things, the number
> of distinct values of each combination of two or more of the listed
> columns, and the planner uses those to estimate how many groups a GROUP
> BY yields. ROLLUP (abs(c0)) means the grouping sets (c0, abs(c0)) and
> (c0), so the planner estimates the group count of each set separately.
>
> The NULL row is not needed: any non-empty table that is analyzed after
> the statistics object is created fails the same way (for example 1000
> rows of (i % 10, i % 7)), and so does EXPLAIN. The same error comes
> from CUBE (abs(c0)), GROUPING SETS ((c0, abs(c0)), (c0)),
> GROUP BY abs(c0), ROLLUP (c0), from c0 + 1, c0::text and c0::numeric
> instead of abs(c0), from statistics with only the ndistinct kind, on
> three columns, or ON c0, abs(c0), and from a view over the query. It
> does not happen with a plain GROUP BY c0, abs(c0), with
> ROLLUP (c0, abs(c0)), with GROUP BY c0, c1, ROLLUP (abs(c0)), with an
> expression over a different column (abs(c1), c0 + c1), with only the
> dependencies or mcv kinds, or before the table is analyzed. It is an
> ordinary ERROR; the cassert builds do not hit an assertion.
>
> Reproduced on 18.6 (REL_18_STABLE dfbc1507cdb), 19rc1 (REL_19_STABLE
> 1212d5d33b1) and master a261fc2b722, Ubuntu 24.04 x86_64,
> --enable-cassert; also on 18.6 and master on macOS arm64. 14.24
> (REL_14_STABLE 14904052e65) runs all of these queries without error.
>
> Found with a query fuzzer.
>

Hi,

Thanks for the report!

I think that the problem is that since f5050f795 a grouping expression
that is not present in every grouping set is marked as nullable by the
grouping step, and since the expression itself can't carry the
nullingrels, the GROUP RTE's index is added to the nullingrels of the
Vars inside it. So in GROUP BY c0, ROLLUP (abs(c0)) the plain c0 has
empty nullingrels, but the c0 inside abs(c0) is marked with the GROUP
RTE.

IIUC when estimate_num_groups() looks at the grouping set (c0, abs(c0)),
it has no statistics for abs(c0) and falls back to pulling out its Vars,
so it ends up adding c0 twice to the list of GroupVarInfos, once for
each nullingrels value. add_unique_group_var() strips nullingrels before
comparing, but only the outer join ones (e28033fe1 fixed the same error
for outer joins), so the GROUP RTE bit is kept and the two Vars are not
considered equal. estimate_multivariate_ndistinct() then counts two
matching attributes in the statistics object, fails to find an item for
what is actually a single column, and reports the "corrupt MVNDistinct
entry" error.

The attached patch fixes this by also stripping the grouping step's RT
index wherever selfuncs.c already strips the outer join nullingrels
before looking up statistics.

The GROUP RTE was introduced in v18, so I think this should be
backpatched to 18.

CCing Richard since he worked on both of the commits mentioned above.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Attachments:

v1-0001-Ignore-grouping-step-nullingrels-when-looking-up-.patch (application/octet-stream, 7.1K) download From 9d485f70a3bdab85931a775666999d4ebd9b7b27 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <mths.dev@pm.me> Date: Tue, 20 Oct 2026 16:14:50 -0300 Subject: [PATCH v1] Ignore grouping-step nullingrels when looking up statistics Since commit f5050f795, a grouping expression that does not appear in every grouping set is marked nullable by the grouping step: the RT index of the GROUP RTE is added to the nullingrels of the Vars it contains. When looking up statistics, selfuncs.c strips nullingrels belonging to outer joins (commit e28033fe1), but not the grouping step's RT index. As a result, estimate_num_groups could create two GroupVarInfos for the same Var: one for a plain grouping column that is present in all grouping sets, and one for the same column pulled out of an expression that is not. When an ndistinct extended statistics object covers that column, estimate_multivariate_ndistinct counts the duplicate as two matching attributes, then fails to find an item for what is really a single attribute, and reports "corrupt MVNDistinct entry". The extra nullingrels bit also kept examine_variable from matching such expressions to expression indexes or to expressions in extended statistics, since pull_varnos reports the GROUP RTE as a second relation and equal() sees the differing nullingrels. To fix, also strip the grouping step's RT index wherever we currently strip outer-join nullingrels before looking up statistical data. Back-patch to v18, where the GROUP RTE was introduced. Reported-by: alpkeles99@gmail.com <alpkeles99@gmail.com> Discussion: https://www.postgresql.org/message-id/19772-d8c886b4db230132@postgresql.org Backpatch-through: 18 --- src/backend/utils/adt/selfuncs.c | 28 ++++++++++++++++++++----- src/test/regress/expected/stats_ext.out | 27 ++++++++++++++++++++++++ src/test/regress/sql/stats_ext.sql | 21 +++++++++++++++++++ 3 files changed, 71 insertions(+), 5 deletions(-) diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 5ee377595bd..1064f10b420 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -3656,6 +3656,21 @@ matchingjoinsel(PG_FUNCTION_ARGS) } +/* + * Get the set of nulling relids that should be ignored when looking up + * statistical data about an expression (the outer joins, plus the grouping + * step if there is one). + */ +static Relids +get_stats_nulling_relids(PlannerInfo *root) +{ + if (root->group_rtindex == 0) + return root->outer_join_rels; + + return bms_add_member(bms_copy(root->outer_join_rels), + root->group_rtindex); +} + /* * Helper routine for estimate_num_groups: add an item to a list of * GroupVarInfos, but only if it's not known equal to any of the existing @@ -3687,7 +3702,7 @@ add_unique_group_var(PlannerInfo *root, List *varinfos, * extended statistics (see estimate_multivariate_ndistinct). So strip * them out first. */ - var = remove_nulling_relids(var, root->outer_join_rels, NULL); + var = remove_nulling_relids(var, get_stats_nulling_relids(root), NULL); foreach(lc, varinfos) { @@ -4268,7 +4283,8 @@ estimate_multivariate_bucketsize(PlannerInfo *root, RelOptInfo *inner, * Clear nullingrels to correctly match hash keys. See * add_unique_group_var()'s comment for details. */ - expr = remove_nulling_relids(expr, root->outer_join_rels, NULL); + expr = remove_nulling_relids(expr, get_stats_nulling_relids(root), + NULL); /* * Detect and exclude exact duplicates from the list of hash @@ -5653,6 +5669,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, Node *basenode; Relids varnos; Relids basevarnos; + Relids nullingrels; RelOptInfo *onerel; /* Make sure we don't return dangling pointers in vardata */ @@ -5703,7 +5720,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, * relation are considered "real" vars. */ varnos = pull_varnos(root, basenode); - basevarnos = bms_difference(varnos, root->outer_join_rels); + nullingrels = get_stats_nulling_relids(root); + basevarnos = bms_difference(varnos, nullingrels); onerel = NULL; @@ -5775,8 +5793,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, * matching it to expressional index columns or to the expressions in * extended statistics. So strip them out first. */ - if (bms_overlap(varnos, root->outer_join_rels)) - node = remove_nulling_relids(node, root->outer_join_rels, NULL); + if (bms_overlap(varnos, nullingrels)) + node = remove_nulling_relids(node, nullingrels, NULL); foreach(ilist, onerel->indexlist) { diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 4512670d525..206b45ee0d1 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -1082,6 +1082,33 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s11; DROP STATISTICS s12; +-- grouping sets where an expression on a column is nullable by grouping but +-- the plain column is not, so the column's Var gets different nullingrels +CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY GROUPING SETS ((a, (a+1)), (a))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +DROP STATISTICS s11; +-- same, with statistics on the expression +CREATE STATISTICS s11 (ndistinct) ON a, (a+1) FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +DROP STATISTICS s11; -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index a9176e74f3c..1af55b5eea7 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -580,6 +580,27 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s11; DROP STATISTICS s12; +-- grouping sets where an expression on a column is nullable by grouping but +-- the plain column is not, so the column's Var gets different nullingrels +CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY GROUPING SETS ((a, (a+1)), (a))'); + +DROP STATISTICS s11; + +-- same, with statistics on the expression +CREATE STATISTICS s11 (ndistinct) ON a, (a+1) FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + +DROP STATISTICS s11; + -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, -- 2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
@ 2026-11-16 10:45  Aleksander Alekseev <aleksander@tigerdata.com>
  parent: Matheus Alcantara <matheusssilv97@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Aleksander Alekseev @ 2026-11-16 10:45 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Matheus Alcantara <matheusssilv97@gmail.com>; alpkeles99@gmail.com, Richard Guo <guofenglinux@gmail.com>

Hi Matheus,

> The attached patch fixes this by also stripping the grouping step's RT
> index wherever selfuncs.c already strips the outer join nullingrels
> before looking up statistics.

I reviewed v1.  It applies cleanly and the tests pass. Your analysis
matches what I see. The three new cases do fail with "corrupt
MVNDistinct entry" without the fix.

However, only add_unique_group_var() is covered.  If I revert the
examine_variable() and estimate_multivariate_bucketsize() but keep the
tests, the whole regression suite still passes.

-- 
Best regards,
Aleksander Alekseev






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
@ 2026-11-16 16:07  Matheus Alcantara <matheusssilv97@gmail.com>
  parent: Aleksander Alekseev <aleksander@tigerdata.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Matheus Alcantara @ 2026-11-16 16:07 UTC (permalink / raw)
  To: Aleksander Alekseev <aleksander@tigerdata.com>; pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com; Richard Guo <guofenglinux@gmail.com>

Hi Aleksander,

Thanks for the review!

On Mon Nov 16, 2026 at 7:45 AM -03, Aleksander Alekseev wrote:
>> The attached patch fixes this by also stripping the grouping step's RT
>> index wherever selfuncs.c already strips the outer join nullingrels
>> before looking up statistics.
>
> I reviewed v1.  It applies cleanly and the tests pass. Your analysis
> matches what I see. The three new cases do fail with "corrupt
> MVNDistinct entry" without the fix.
>
> However, only add_unique_group_var() is covered.  If I revert the
> examine_variable() and estimate_multivariate_bucketsize() but keep the
> tests, the whole regression suite still passes.

Right. (a+1) has the same number of distinct values as a, so falling
back to the column's statistics gives the same estimate either way. v2
adds tests with GROUP BY ROLLUP ((d % 2)), using statistics on (d % 2)
in one and an expression index in the other. Both estimate 3 groups with
the fix and 21 with the examine_variable() change reverted.

I think that the estimate_multivariate_bucketsize() change can't
actually take effect, because hash join keys are planned below the
grouping step and never carry its RT index. v2 drops that change and
keeps stripping only outer-join nullingrels there.

v2 attached.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Attachments:

v2-0001-Ignore-grouping-step-nullingrels-when-looking-up-.patch (text/plain, 8.0K) download From eb12c61a4872f5ac735ba85f89fea5b9e543edb4 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <mths.dev@pm.me> Date: Tue, 20 Oct 2026 16:14:50 -0300 Subject: [PATCH v2] Ignore grouping-step nullingrels when looking up statistics Since commit f5050f795, a grouping expression that does not appear in every grouping set is marked nullable by the grouping step: the RT index of the GROUP RTE is added to the nullingrels of the Vars it contains. When looking up statistics, selfuncs.c strips nullingrels belonging to outer joins (commit e28033fe1), but not the grouping step's RT index. As a result, estimate_num_groups could create two GroupVarInfos for the same Var: one for a plain grouping column that is present in all grouping sets, and one for the same column pulled out of an expression that is not. When an ndistinct extended statistics object covers that column, estimate_multivariate_ndistinct counts the duplicate as two matching attributes, then fails to find an item for what is really a single attribute, and reports "corrupt MVNDistinct entry". The extra nullingrels bit also kept examine_variable from matching such expressions to expression indexes or to expressions in extended statistics, since pull_varnos reports the GROUP RTE as a second relation and equal() sees the differing nullingrels. To fix, also strip the grouping step's RT index in add_unique_group_var and examine_variable. The other place that strips outer-join nullingrels, estimate_multivariate_bucketsize, only sees hash join keys, which are computed below the grouping step and so never carry its RT index. Back-patch to v18, where the GROUP RTE was introduced. Reported-by: alpkeles99@gmail.com <alpkeles99@gmail.com> Reviewed-by: Aleksander Alekseev <aleksander@tigerdata.com> Discussion: https://www.postgresql.org/message-id/19772-d8c886b4db230132@postgresql.org Backpatch-through: 18 --- src/backend/utils/adt/selfuncs.c | 25 ++++++++++--- src/test/regress/expected/stats_ext.out | 47 +++++++++++++++++++++++++ src/test/regress/sql/stats_ext.sql | 39 ++++++++++++++++++++ 3 files changed, 107 insertions(+), 4 deletions(-) diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 5ee377595bd..05f0a2a69c6 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -3656,6 +3656,21 @@ matchingjoinsel(PG_FUNCTION_ARGS) } +/* + * Get the set of nulling relids that should be ignored when looking up + * statistical data about an expression (the outer joins, plus the grouping + * step if there is one). + */ +static Relids +get_stats_nulling_relids(PlannerInfo *root) +{ + if (root->group_rtindex == 0) + return root->outer_join_rels; + + return bms_add_member(bms_copy(root->outer_join_rels), + root->group_rtindex); +} + /* * Helper routine for estimate_num_groups: add an item to a list of * GroupVarInfos, but only if it's not known equal to any of the existing @@ -3687,7 +3702,7 @@ add_unique_group_var(PlannerInfo *root, List *varinfos, * extended statistics (see estimate_multivariate_ndistinct). So strip * them out first. */ - var = remove_nulling_relids(var, root->outer_join_rels, NULL); + var = remove_nulling_relids(var, get_stats_nulling_relids(root), NULL); foreach(lc, varinfos) { @@ -5653,6 +5668,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, Node *basenode; Relids varnos; Relids basevarnos; + Relids nullingrels; RelOptInfo *onerel; /* Make sure we don't return dangling pointers in vardata */ @@ -5703,7 +5719,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, * relation are considered "real" vars. */ varnos = pull_varnos(root, basenode); - basevarnos = bms_difference(varnos, root->outer_join_rels); + nullingrels = get_stats_nulling_relids(root); + basevarnos = bms_difference(varnos, nullingrels); onerel = NULL; @@ -5775,8 +5792,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, * matching it to expressional index columns or to the expressions in * extended statistics. So strip them out first. */ - if (bms_overlap(varnos, root->outer_join_rels)) - node = remove_nulling_relids(node, root->outer_join_rels, NULL); + if (bms_overlap(varnos, nullingrels)) + node = remove_nulling_relids(node, nullingrels, NULL); foreach(ilist, onerel->indexlist) { diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 42d6c2777ea..c1d2948291b 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -1104,6 +1104,53 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s11; DROP STATISTICS s12; +-- grouping sets where an expression on a column is nullable by grouping but +-- the plain column is not, so the column's Var gets different nullingrels +CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY GROUPING SETS ((a, (a+1)), (a))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +DROP STATISTICS s11; +-- same, with statistics on the expression +CREATE STATISTICS s11 (ndistinct) ON a, (a+1) FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +DROP STATISTICS s11; +-- a grouping expression that is nullable by grouping should still be +-- matched to expression statistics and expression indexes +CREATE STATISTICS s11 ON (d % 2) FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + estimated | actual +-----------+-------- + 3 | 3 +(1 row) + +DROP STATISTICS s11; +CREATE INDEX ndistinct_d_mod_idx ON ndistinct ((d % 2)); +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + estimated | actual +-----------+-------- + 3 | 3 +(1 row) + +DROP INDEX ndistinct_d_mod_idx; -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 6045cfa51e8..146e4ed558c 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -596,6 +596,45 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s11; DROP STATISTICS s12; +-- grouping sets where an expression on a column is nullable by grouping but +-- the plain column is not, so the column's Var gets different nullingrels +CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY GROUPING SETS ((a, (a+1)), (a))'); + +DROP STATISTICS s11; + +-- same, with statistics on the expression +CREATE STATISTICS s11 (ndistinct) ON a, (a+1) FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + +DROP STATISTICS s11; + +-- a grouping expression that is nullable by grouping should still be +-- matched to expression statistics and expression indexes +CREATE STATISTICS s11 ON (d % 2) FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + +DROP STATISTICS s11; + +CREATE INDEX ndistinct_d_mod_idx ON ndistinct ((d % 2)); + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + +DROP INDEX ndistinct_d_mod_idx; + -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, -- 2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 4+ messages in thread


end of thread, other threads:[~2026-11-16 16:07 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-18 23:22 BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics PG Bug reporting form <noreply@postgresql.org>
2026-10-20 19:30 ` Matheus Alcantara <matheusssilv97@gmail.com>
2026-11-16 10:45   ` Aleksander Alekseev <aleksander@tigerdata.com>
2026-11-16 16:07     ` Matheus Alcantara <matheusssilv97@gmail.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