roachtest: unoptimized-query-oracle/disable-rules=all/seed-multi-region failed

[cockroach-teamcity]

roachtest.unoptimized-query-oracle/disable-rules=all/seed-multi-region failed with artifacts on release-23.1 @ b4533bdbc4b478f0ad311bad80b62bd072cf61cf:

test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=all/seed-multi-region/run_1
(query_comparison_util.go:252).runOneRoundQueryComparison: . 1051 statements run: expected unoptimized and optimized results to be equal
  []string{
  	"-0.11994045023365757,3",
- 	"0.5879446066021979,1",
+ 	"0.5879446066021253,1",
  	"1.2345678901234568e-34,3",
  	"1.234567890123456e+06,3",
  	"NULL,0",
  }
sql: SELECT
	avg("Tab63"._float8::FLOAT8)::FLOAT8 AS col😹220,
	regr_count("Tab63"._float8::FLOAT8, "Tab63"._float8::FLOAT8) OVER (PARTITION BY "Tab63"._int8 ORDER BY "Tab63"._int8 ASC NULLS FIRST, "Tab63"._float8 ASC, "Tab63"._int8 ASC GROUPS BETWEEN UNBOUNDED PRECEDING AND 10:::INT8 FOLLOWING)::INT8
		AS "&c""ol221"
FROM
	defaultdb.public.seed_mr_table@[0] AS tab62
	JOIN defaultdb.public.seed_mr_table@[0] AS "Tab63" ON
			(tab62._int4) = ("Tab63"._int4) AND (tab62._int2) = ("Tab63"._int2)
GROUP BY
	"Tab63"._float8, "Tab63"._int8
HAVING
	bool_and("Tab63"._bool::BOOL)::BOOL
ORDER BY
	"Tab63"._float8 ASC

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , ROACHTEST_encrypted=false , ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-27901

[yuzefovich]

Seems like a precision issue, so removing the release blocker label.

[rharding6373]

We discussed this issue in our collab session today. It looks similar to #95665, where @msirek demonstrated how computing a sum on floats in different order of inputs resulted in high variance of precision.

Options we discussed (in no particular order or recommendation):

1. Only compare floats to some amount of precision.
2. Don't use floats in unoptimized-query-oracle testing.
3. Don't run queries with stddev or avg on floats in unoptimized-query-oracle testing.
4. Constrain floats inserted into the test tables to a narrow range of precision.
5. Use higher precision types (like decimal) internally for the sum and convert back after taking the average.

[rharding6373]

Dupe of #95665