roachtest: costfuzz/seed-multi-region failed, something to do with stddev_samp

[cockroach-teamcity]

roachtest.costfuzz/seed-multi-region failed with artifacts on release-24.2 @ 742a2251a05088a238fc6eb4c2466dace3cb1bb2:

(query_comparison_util.go:416).runOneRoundQueryComparison: . 1408 statements run: expected unperturbed and perturbed results to be equal
  []string{
    ... // 10 identical elements
    "0,27",
    "0,27",
-   "0,27",
    "NULL,1",
    "NULL,1",
    "NULL,1",
    "NULL,378",
+   "NaN,27",
  }
sql: SELECT
    stddev_samp(tab_1602._float8::FLOAT8)::FLOAT8 AS col_5208,
    regr_count(tab_1603._int8::INT8, tab_1603._int8::INT8)::INT8 AS col_5209
FROM
    defaultdb.public.seed AS tab_1602 JOIN defaultdb.public.seed AS tab_1603 ON (tab_1602._int8) = (tab_1603._int8)
GROUP BY
    tab_1603._int8, tab_1602._float8
ORDER BY
    col_5208 DESC, col_5209 NULLS FIRST, tab_1602._float8 DESC, tab_1603._int8 DESC NULLS FIRST
LIMIT
    17:::INT8
test artifacts and logs in: /artifacts/costfuzz/seed-multi-region/cpu_arch=arm64/run_1

Parameters:

• ROACHTEST_arch=arm64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_fs=ext4
• ROACHTEST_localSSD=false
• ROACHTEST_metamorphicBuild=false
• ROACHTEST_ssd=0
  Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

Jira issue: CRDB-40624

[cockroach-teamcity]

roachtest.costfuzz/seed-multi-region failed with artifacts on release-24.2 @ c754434cecbdc6016bc6cc6b7541ffbd71a50bed:

-       "00:00:00",
+       "NULL",
        ",1.2345679166470003e-11",
    }, ""),
    strings.Join({
-       "NULL,T",
+       "1987-01-28 19:31:46.000651 +0000 UTC,P2|",
        ",7300238742053125164,79,NULL,NULL,1.2345679166470003e-11",
    }, ""),
  }
sql: SELECT
    tab_3636._timestamptz AS col_12738,
    tab_3636._string AS col_12739,
    7300238742053125164:::INT8 AS col_12740,
    79:::INT8 AS col_12741,
    NULL AS col_12742,
    tab_3636._interval AS col_12743,
    1.2345679166470003e-11:::FLOAT8 AS col_12744
FROM
    defaultdb.public.seed AS tab_3636
ORDER BY
    tab_3636._bool NULLS FIRST,
    tab_3636._float8 NULLS LAST,
    col_12742 DESC,
    tab_3636._int8 ASC NULLS FIRST,
    tab_3636._int4 NULLS FIRST,
    tab_3636._int2 ASC NULLS LAST,
    col_12744 ASC NULLS FIRST,
    tab_3636._string NULLS LAST,
    col_12738,
    col_12740 DESC NULLS LAST,
    col_12739 DESC NULLS FIRST,
    tab_3636._timestamp ASC NULLS FIRST,
    tab_3636._enum DESC NULLS LAST,
    col_12743 ASC,
    tab_3636._date ASC NULLS FIRST,
    tab_3636._jsonb ASC NULLS LAST,
    tab_3636._uuid NULLS FIRST,
    tab_3636._timestamptz DESC,
    tab_3636._bytes ASC NULLS LAST,
    tab_3636._inet ASC NULLS FIRST,
    tab_3636.tableoid ASC,
    tab_3636._float4 DESC NULLS FIRST,
    tab_3636._interval NULLS FIRST,
    tab_3636.crdb_internal_mvcc_timestamp ASC NULLS LAST,
    col_12741 NULLS LAST,
    tab_3636._decimal ASC
LIMIT
    76:::INT8
test artifacts and logs in: /artifacts/costfuzz/seed-multi-region/cpu_arch=arm64/run_1

Parameters:

• ROACHTEST_arch=arm64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_fs=ext4
• ROACHTEST_localSSD=false
• ROACHTEST_metamorphicBuild=false
• ROACHTEST_ssd=0
  Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

[michae2]

Initial reduction of the first failure: repro.txt

running this with:

./cockroach demo --nodes 9 --multitenant=false --set=errexit=false --no-example-database -f ~/Downloads/127824/a/repro.txt

This does not reproduce on v24.1.2 or v24.2.0-alpha.2, but does reproduce on v24.2.0-beta.1, so it was a recent regression. I'm keeping release-blocker and will add P-2.

[michae2]

I haven't yet been able to reproduce the second failure.

[mgartner]

Reduced the repro a bit:

CREATE TABLE t (
  i INT8,
  f FLOAT8
);

INSERT INTO t
SELECT 0, g FROM generate_series(1, 5) AS g;

CREATE INDEX ON t (i, f);

ALTER TABLE t SCATTER;

INSERT INTO t (i, f) VALUES (0, 1.0e+308);

CREATE STATISTICS "" FROM t WITH OPTIONS AS OF SYSTEM TIME '-0.001ms';

SELECT stddev_samp(t1.f) AS samp
FROM t AS t1
JOIN t AS t2 ON t1.i = t2.i
GROUP BY t2.i, t1.f
ORDER BY samp
LIMIT 6;

SET testing_optimizer_random_seed = 5211164061409522076;

SET testing_optimizer_cost_perturbation = 1.0;

SELECT stddev_samp(t1.f) AS samp
FROM t AS t1
JOIN t AS t2 ON t1.i = t2.i
GROUP BY t2.i, t1.f
ORDER BY samp
LIMIT 6;

Run with:

/path/to/cockroach demo --nodes 9 --multitenant=false --empty -f repro_127824.sql

[mgartner]

Reduced a bit more:

CREATE TABLE t (
  i INT8,
  f FLOAT8
);

INSERT INTO t (i, f) VALUES
  (0, 1),
  (0, 1.0e+308);

CREATE INDEX ON t (i, f);

ALTER TABLE t SCATTER;

SELECT stddev_samp(t1.f) AS samp
FROM t AS t1
JOIN t AS t2 ON t1.i = t2.i
GROUP BY t2.i, t1.f
ORDER BY samp
LIMIT 6;
--   samp
-- --------
--      0
--      0
-- (2 rows)

-- Same as above, but with some hints.
SELECT stddev_samp(t1.f) AS samp
FROM t AS t1
INNER HASH JOIN t@t_i_f_idx AS t2 ON t1.i = t2.i
GROUP BY t2.i, t1.f
ORDER BY samp
LIMIT 6;
--   samp
-- --------
--    NaN
--      0
-- (2 rows)

[mgartner]

Duplicate of https://github.com/cockroachdb/cockroach/issues/115999.