BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics

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

BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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




Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics

От:
Matheus Alcantara <matheusssilv97@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

Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics

От:
Aleksander Alekseev <aleksander@tigerdata.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


Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics

От:
"Matheus Alcantara" <matheusssilv97@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
FAQ