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

[cockroach-teamcity]

roachtest.unoptimized-query-oracle/disable-rules=all failed with artifacts on master @ 2372698da1dfacb90f60c6a63f2c1298d1db16b8:

		  |   	}, ""),
		  |   	strings.Join({
		  |   		"à,105,1,)nq{b],7226836753237314999998802426396.594538945,0,-119",
		  |   		"7573603",
		  | + 		".405461055",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"à,105,1,a\n,7226836753237315000000000000001.1,0,",
		  | + 		"1.",
		  |   		"1",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"à,105,1,igqt\x06,7226836753237314999999999999999.99999999899,0,-",
		  | + 		"1.",
		  |   		"0",
		  | + 		"1E-9",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"à,105,1,rgc,1.1859662682854660147315E+37,0,1",
		  | + 		".",
		  |   		"185965545601790691",
		  | - 		"0000000000000000000",
		  | + 		"E+37",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"à,105,1,x,1.244048348144881532315E+36,0,1",
		  | + 		".",
		  |   		"244041121308128295",
		  | - 		"000000000000000000",
		  | + 		"E+36",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"à,105,1,�,7.226836753237315000000000001E+30,0,1",
		  | - 		"000",
		  | + 		"E+3",
		  |   	}, ""),
		  |   }
		  | sql: SELECT
		  | 	e'\u00E0':::STRING AS col_99547,
		  | 	tab_33623.tableoid AS col_99548,
		  | 	'1':::JSONB AS col_99549,
		  | 	tab_33623.col2_4 AS col_99550,
		  | 	tab_33623.col2_3 AS col_99551,
		  | 	0:::DECIMAL AS col_99552,
		  | 	(tab_33623.col2_0::DECIMAL // 1:::DECIMAL::DECIMAL)::DECIMAL AS col_99553
		  | FROM
		  | 	defaultdb.public.table2@[0] AS tab_33623
		  | ORDER BY
		  | 	tab_33623.col2_0 ASC, tab_33623.crdb_internal_mvcc_timestamp DESC
		Error types: (1) *withstack.withStack (2) *errutil.withPrefix (3) *withstack.withStack (4) *errutil.leafError

Parameters: ROACHTEST_cloud=gce , ROACHTEST_cpu=4 , 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-19373

[DrewKimball]

Definitely related to #86790 (more problems with decimal precision), except this query uses //

[DrewKimball]

Minimized repro:

statement ok
CREATE TABLE t (x DECIMAL NOT NULL);
INSERT INTO t VALUES (1.0);

query T
EXPLAIN (VEC) SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
│
└ Node 1
  └ *colfetcher.ColBatchScan

query R
SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
1.0

statement ok
SET testing_optimizer_disable_rule_probability = 1;

query T
EXPLAIN (VEC) SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
│
└ Node 1
  └ *colexecprojconst.projFloorDivDecimalDecimalConstOp
    └ *colfetcher.ColBatchScan

query R
SELECT (x::DECIMAL // 1::DECIMAL)::DECIMAL FROM t;
----
1

[DrewKimball]

This reproduces on 22.1

[msirek]

test case:

CREATE TABLE table2 (
      col2_0 DECIMAL NOT NULL
  );

INSERT INTO table2 values (1.244041121308128295E+36), (6.111449000976130572E+37), ( 1.1),
                          (-1197573603.405461055), (1E+3), (1.185965545601790691E+37),
                          (-1.01E-9), (9.9);

SET testing_optimizer_random_seed = 5781298965816457561;

SET testing_optimizer_disable_rule_probability = 1.000000;

SET vectorize = off;

SELECT
        (tab_33623.col2_0::DECIMAL // 1:::DECIMAL::DECIMAL)::DECIMAL AS col_99553
FROM
        defaultdb.public.table2@[0] AS tab_33623
ORDER BY
        tab_33623.col2_0 ASC, tab_33623.crdb_internal_mvcc_timestamp DESC;

RESET testing_optimizer_random_seed;

RESET testing_optimizer_disable_rule_probability;


SELECT
        (tab_33623.col2_0::DECIMAL // 1:::DECIMAL::DECIMAL)::DECIMAL AS col_99553
FROM
        defaultdb.public.table2@[0] AS tab_33623
ORDER BY
        tab_33623.col2_0 ASC, tab_33623.crdb_internal_mvcc_timestamp DESC;

[cockroach-teamcity]

roachtest.unoptimized-query-oracle/disable-rules=all failed with artifacts on master @ 389661e823c19f318fa07ec2278336262531692d:

		  |   		"755F641102423C41F3CF441,1.4920385687080904,0,1662817357975643127",
		  | + 		".0000000000",
		  |   		",1.4920385687080904",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"BOX(-10 -10,10 10),010600000000000000,1.234567890123456e+29,0,16",
		  |   		"6281735326528765",
		  | + 		"0.000000000",
		  |   		"0,1.234567890123456e+29",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		... // 5402 identical bytes
		  |   		"4632B4EDBF441F0D77C0CC38CFD410101000040D4F5418F048AEF41C076FD9A2",
		  |   		"6A4CC412A68D7D5B9140242,1.6188689335198163,0,1662817354629957845",
		  | + 		".0000000000",
		  |   		",1.6188689335198163",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		... // 2664 identical bytes
		  |   		"4D3BAC89CF14101010000C0686773C53179FAC101BFA2305C3B02C29E5D0489B",
		  |   		"39A02C2A2FD2247E416E5C1,-1.011130956360439,0,1662817356982571465",
		  | + 		".0000000000",
		  |   		",-1.011130956360439",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"BOX(10.999 1.1,99900000010.999 88800000001.1),010700000000000000",
		  |   		",-0.20210999409258956,0,1662817357476370105",
		  | + 		".0000000000",
		  |   		",-0.20210999409258956",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		... // 350 identical bytes
		  |   		"02C2B095F1136298C741A0C622D69D39CC41F62C49F8D03FFAC1345AA547139F",
		  |   		"DAC150DE1790D27BFCC1,1.2345686985456967e+06,0,166281735597155036",
		  | + 		"0.000000000",
		  |   		"0,1.2345686985456967e+06",
		  |   	}, ""),
		  |   }
		  | sql: SELECT
		  | 	tab_25180.col1_1 AS col_63940,
		  | 	tab_25180.col1_2 AS col_63941,
		  | 	tab_25180.col1_3 AS col_63942,
		  | 	0:::OID AS col_63943,
		  | 	(tab_25180.crdb_internal_mvcc_timestamp::DECIMAL // 1:::DECIMAL::DECIMAL)::DECIMAL AS col_63944,
		  | 	tab_25180.col1_3 AS col_63945
		  | FROM
		  | 	defaultdb.public.table1@[0] AS tab_25180
		  | WHERE
		  | 	(NOT (' *=}O':::STRING ~* e'\x11$MvTEh':::STRING))
		Error types: (1) *withstack.withStack (2) *errutil.withPrefix (3) *withstack.withStack (4) *errutil.leafError

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

Help

See: roachtest README

See: How To Investigate (internal)

Same failure on other branches

• roachtest: unoptimized-query-oracle/disable-rules=all failed #87605 roachtest: unoptimized-query-oracle/disable-rules=all failed [C-test-failure O-roachtest O-robot T-sql-queries branch-release-22.2 release-blocker]

This test on roachdash | Improve this report!

[mgartner]

Closing this as a duplicate of #86790.