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

[cockroach-teamcity]

Note: This build has runtime assertions enabled. If the same failure was hit in a run without assertions enabled, there should be a similar failure without this message. If there isn't one, then this failure is likely due to an assertion violation or (assertion) timeout.

roachtest.unoptimized-query-oracle/disable-rules=all/rand-multi-region failed with artifacts on release-24.2.5-rc @ ee625f92aaca9a68ff53706626b2c5050a91a6f4:

(query_comparison_util.go:416).runOneRoundQueryComparison: . 271 statements run: expected unoptimized and optimized results to be equal
  []string{
- 	"00000000000,00000000000",
+ 	"0,00000000000",
- 	"00100000110,00100000110",
+ 	"0,00100000110",
- 	"00100001101,00100001101",
+ 	"0,00100001101",
- 	"00101100100,00101100100",
+ 	"0,00101100100",
- 	"00101110010,00101110010",
+ 	"0,00101110010",
- 	"01100001110,01100001110",
+ 	"0,01100001110",
- 	"01100101001,01100101001",
+ 	"0,01100101001",
- 	"01101000110,01101000110",
+ 	"0,01101000110",
- 	"01111111111,01111111111",
+ 	"0,01111111111",
- 	"10011000101,10011000101",
+ 	"1,10011000101",
- 	"10100100001,10100100001",
+ 	"1,10100100001",
- 	"10110001111,10110001111",
+ 	"1,10110001111",
- 	"10110100110,10110100110",
+ 	"1,10110100110",
- 	"10110101101,10110101101",
+ 	"1,10110101101",
- 	"11000111010,11000111010",
+ 	"1,11000111010",
- 	"11001010000,11001010000",
+ 	"1,11001010000",
- 	"11101111111,11101111111",
+ 	"1,11101111111",
  }
sql: SELECT
	COALESCE(tab_286.col2_8, tab_285.col2_8) AS col_866, tab_285.col2_8 AS col_867
FROM
	defaultdb.public.table_2 AS tab_285 JOIN defaultdb.public.table_2 AS tab_286 ON (tab_285.col2_8) = (tab_286.col2_8)
ORDER BY
	tab_285.col2_8 DESC NULLS LAST, col_867 NULLS LAST, col_866, tab_286.col2_8 DESC
LIMIT
	64:::INT8
test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=all/rand-multi-region/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_runtimeAssertionsBuild=true
• ROACHTEST_ssd=0

Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

Jira issue: CRDB-43930

[yuzefovich]

Reduced repro on ./cockroach demo --insecure --empty --nodes=3:

CREATE TABLE t (c BIT(4) NOT NULL, PRIMARY KEY (c));
INSERT INTO t (c) VALUES (B'0000');

\connect - - - 26257
SELECT COALESCE(t2.c, t1.c) FROM t AS t1 JOIN t AS t2 ON (t1.c) = (t2.c);

\connect - - - 26258

SELECT COALESCE(t2.c, t1.c) FROM t AS t1 JOIN t AS t2 ON (t1.c) = (t2.c);

[yuzefovich]

This is fixed by 29a73f6, so it's a dup of #131346