Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

stats: prevent NaNs in histograms #142022

Open
yuzefovich opened this issue Feb 26, 2025 · 0 comments
Open

stats: prevent NaNs in histograms #142022

yuzefovich opened this issue Feb 26, 2025 · 0 comments
Labels
A-sql-debug-bundle Issues related to statement bundle improvements A-sql-table-stats Table statistics (and their automatic refresh). C-cleanup Tech debt, refactors, loose ends, etc. Solution not expected to significantly change behavior. T-sql-queries SQL Queries Team

Comments

@yuzefovich
Copy link
Member

yuzefovich commented Feb 26, 2025

When running the reproduction from #141448

SET sql_safe_updates = false;

CREATE TABLE seed AS SELECT g AS _float8,
                            g * '1 day'::INTERVAL,
                            g % 2 = 1 AS _bool,
                            g::DECIMAL AS _decimal
                       FROM generate_series(1, 1) AS g;

INSERT INTO seed (_float8, _bool, _decimal) VALUES (0.9, true, 0);

UPDATE seed AS t SET _bool = t._bool AND false;

INSERT INTO seed (_bool, _decimal) VALUES (false, 1.0);

ANALYZE seed;

SHOW STATISTICS FOR TABLE seed;

SHOW HISTOGRAM <id>;

on M1 I'm getting

  upper_bound | range_rows | distinct_range_rows | equal_rows
--------------+------------+---------------------+-------------
  false       |          0 |                   0 |          0
  true        |          0 |                 NaN |          0

NaN is problematic since it then will prohibit the stats inclusion into the stmt bundle due to

stmtEnvCollector: json: unsupported value: NaN

I observed this behavior on two tables when looking into a stmt bundle issue on the customer cluster.

The root cause of the reproduction is being addressed elsewhere, but clearly we have some ways for NaNs to sneak into the histograms, so we should improve things.

Jira issue: CRDB-48121

@yuzefovich yuzefovich added A-sql-debug-bundle Issues related to statement bundle improvements A-sql-table-stats Table statistics (and their automatic refresh). T-sql-queries SQL Queries Team labels Feb 26, 2025
@blathers-crl blathers-crl bot added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Feb 26, 2025
@github-project-automation github-project-automation bot moved this to Triage in SQL Queries Feb 26, 2025
@cockroachdb cockroachdb deleted a comment from blathers-crl bot Feb 26, 2025
@yuzefovich yuzefovich added C-cleanup Tech debt, refactors, loose ends, etc. Solution not expected to significantly change behavior. and removed C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) labels Feb 26, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
A-sql-debug-bundle Issues related to statement bundle improvements A-sql-table-stats Table statistics (and their automatic refresh). C-cleanup Tech debt, refactors, loose ends, etc. Solution not expected to significantly change behavior. T-sql-queries SQL Queries Team
Projects
Status: Triage
Development

No branches or pull requests

1 participant