roachtest: costfuzz/unoptimized-query-oracle make sure randomly generated aggregate functions are deterministic

[cockroach-teamcity]

roachtest.costfuzz failed with artifacts on master @ 4dcb32c0346e20a95847763f89b9b0796d9ed4dc:

		  |   		"000000000000000000000000,000000000000000000000000000000000000000",
		  |   		"000000000000000,",
		  | - 		"27251",
		  | + 		"53",
		  |   	}, ""),
		  |   	"000000000000000000000000,01,17781",
		  |   	strings.Join({
		  |   		"000000000000000000000000,010101,",
		  | - 		"20823",
		  | + 		"32054",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"000000000000000000000000,0101010101,",
		  | - 		"16645",
		  | + 		"20823",
		  |   	}, ""),
		  |   	strings.Join({
		  |   		"000000000000000000000000,01010101010101,",
		  | - 		"32054",
		  | + 		"16645",
		  |   	}, ""),
		  |   	"000011000001110101101000,00,15723",
		  |   	"000100011101010110001110,00,17831",
		  |   	... // 30 identical elements
		  |   	"110111011101100011110001,01,69",
		  |   	"111011000001000100101101,01,0",
		  | - 	"111111111111111111111111,00,17",
		  | + 	"111111111111111111111111,00,17833",
		  |   	strings.Join({
		  |   		"111111111111111111111111,000000,17",
		  | - 		"833",
		  |   	}, ""),
		  |   	... // 6 identical and 59 modified elements
		  |   }
		  | sql: SELECT
		  | 	tab_4700.col2_0 AS col_15194,
		  | 	string_agg(tab_4700.col2_6::STRING, tab_4700.col2_6::STRING ORDER BY tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING) OVER (PARTITION BY tab_4700.col2_6, tab_4700.col2_0 ORDER BY tab_4700.col2_6, tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING ROWS BETWEEN CURRENT ROW AND 8049193815714677612:::INT8 FOLLOWING)::STRING
		  | 		AS col_15195,
		  | 	tab_4700.col2_1 AS col_15196
		  | FROM
		  | 	defaultdb.public.table2@[0] AS tab_4700
		  | WHERE
		  | 	true
		  | GROUP BY
		  | 	tab_4700.col2_1, tab_4700.col2_0, tab_4700.col2_6
		  | HAVING
		  | 	bool_and(true::BOOL)::BOOL
		  | ORDER BY
		  | 	tab_4700.col2_6 ASC
		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.costfuzz failed due to scientific notation v. decimal notation #86796 roachtest.costfuzz failed due to scientific notation v. decimal notation [C-test-failure T-sql-queries]

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-19289

[mgartner]

Tried reducing the failure above with reduce but running into bugs in reduce: #87418 and #87419. I'll try manually reducing.

[mgartner]

Reduced reproduction:

CREATE TABLE table2 (col2_0 BIT(24) NULL, col2_1 INT2 NOT NULL, col2_2 TIMETZ NOT NULL, col2_3 TIMESTAMPTZ NOT NULL, col2_4 BIT(2) NOT NULL, col2_5 FLOAT8 NOT NULL, col2_6 STRING NOT NULL AS (lower(CAST(col2_4 AS STRING))) VIRTUAL, col2_7 INT8 NOT NULL AS (col2_1 + 17852:::INT8) STORED, PRIMARY KEY (col2_2, col2_1, col2_3), INDEX (col2_7 ASC, col2_2, col2_5 ASC, col2_4 ASC, col2_6, col2_3 ASC) STORING (col2_0), INDEX (col2_4 ASC, col2_6 ASC, col2_7 DESC, col2_2 ASC, col2_0, col2_5 DESC, col2_1 DESC) WHERE (((((table2.col2_6 != e'\x00':::STRING) AND (table2.col2_3 >= '3000-01-01 00:00:00+00:00':::TIMESTAMPTZ)) AND (table2.col2_1 != (-1):::INT8)) OR (table2.col2_7 != (-2147483648):::INT8)) AND (table2.col2_2 = '00:00:00+15:59:00':::TIMETZ)) AND (table2.col2_5 < 1.7976931348623157e+308:::FLOAT8), INDEX (col2_4) WHERE (((((table2.col2_7 >= 2147483647:::INT8) AND (table2.col2_2 != '00:00:00+15:59:00':::TIMETZ)) AND (table2.col2_6 < 'X':::STRING)) OR (table2.col2_1 <= (-1):::INT8)) OR (table2.col2_5 = (-1.0):::FLOAT8)) AND (table2.col2_3 = '3000-01-01 00:00:00+00:00':::TIMESTAMPTZ), INDEX (col2_2, col2_1 DESC, col2_3 DESC, col2_5, col2_4 ASC) STORING (col2_0, col2_7), UNIQUE (col2_0, col2_2, col2_7, col2_5, col2_1), FAMILY (col2_2), FAMILY (col2_1, col2_4), FAMILY (col2_0, col2_5, col2_7), FAMILY (col2_3));

INSERT
INTO
	defaultdb.public.table2 AS tab_127 (col2_0, col2_1, col2_2, col2_3, col2_4, col2_5)
VALUES
	(
		NULL,
		(-6838):::INT8,
		'21:16:16.722186+08:49:00':::TIMETZ,
		'1973-09-12 06:31:42.000876+00:00':::TIMESTAMPTZ,
		B'00',
		1.234567890123456e+10:::FLOAT8
	);

INSERT
INTO
	defaultdb.public.table2 AS tab_133 (col2_1, col2_2, col2_3, col2_4, col2_5)
VALUES
	((-12):::INT8, '06:28:31.085579-01:02:00':::TIMETZ, '1967-04-07 03:25:45+00:00':::TIMESTAMPTZ, B'00', 0.0:::FLOAT8);


SELECT
  tab_4700.col2_6,
	tab_4700.col2_0 AS col_15194,
	string_agg(tab_4700.col2_6::STRING, tab_4700.col2_6::STRING ORDER BY tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING) OVER (PARTITION BY tab_4700.col2_6, tab_4700.col2_0 ORDER BY tab_4700.col2_6, tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING ROWS BETWEEN CURRENT ROW AND 8049193815714677612:::INT8 FOLLOWING)::STRING
		AS col_15195,
	tab_4700.col2_1 AS col_15196
FROM
	defaultdb.public.table2@[0] AS tab_4700
WHERE
	true
GROUP BY
	tab_4700.col2_1, tab_4700.col2_0, tab_4700.col2_6
HAVING
	bool_and(true::BOOL)::BOOL
ORDER BY
	tab_4700.col2_6 ASC;

SET testing_optimizer_random_seed = 8664472571050980723;

SET testing_optimizer_cost_perturbation = 1.0;

SELECT
  tab_4700.col2_6,
	tab_4700.col2_0 AS col_15194,
	string_agg(tab_4700.col2_6::STRING, tab_4700.col2_6::STRING ORDER BY tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING) OVER (PARTITION BY tab_4700.col2_6, tab_4700.col2_0 ORDER BY tab_4700.col2_6, tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING ROWS BETWEEN CURRENT ROW AND 8049193815714677612:::INT8 FOLLOWING)::STRING
		AS col_15195,
	tab_4700.col2_1 AS col_15196
FROM
	defaultdb.public.table2@[0] AS tab_4700
WHERE
	true
GROUP BY
	tab_4700.col2_1, tab_4700.col2_0, tab_4700.col2_6
HAVING
	bool_and(true::BOOL)::BOOL
ORDER BY
	tab_4700.col2_6 ASC;

Looks like this is not a blocker, and probably due to string_agg not being deterministic.

[mgartner]

Further reduced:

CREATE TABLE table2 (
  col2_0 BIT(24) NULL,
  col2_1 INT2 NOT NULL,
  col2_2 TIMETZ NOT NULL,
  col2_4 BIT(2) NOT NULL,
  col2_5 FLOAT8 NOT NULL,
  col2_6 STRING NOT NULL AS (lower(CAST(col2_4 AS STRING))) VIRTUAL,
  col2_7 INT8 NOT NULL AS (col2_1 + 17852:::INT8) STORED,
  PRIMARY KEY (col2_2, col2_1),
  INDEX (col2_7 ASC, col2_2, col2_5 ASC, col2_4 ASC, col2_6) STORING (col2_0)
);

INSERT
INTO
	defaultdb.public.table2 AS tab_127 (col2_0, col2_1, col2_2, col2_4, col2_5)
VALUES
	(
		NULL,
		(-6838):::INT8,
		'21:16:16.722186+08:49:00':::TIMETZ,
		B'00',
		1.234567890123456e+10:::FLOAT8
	);

INSERT
INTO
	defaultdb.public.table2 AS tab_133 (col2_1, col2_2, col2_4, col2_5)
VALUES
	((-12):::INT8, '06:28:31.085579-01:02:00':::TIMETZ, B'00', 0.0:::FLOAT8);


SELECT
	tab_4700.col2_0 AS col_15194,
	string_agg(tab_4700.col2_6::STRING, tab_4700.col2_6::STRING ORDER BY tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING) OVER (PARTITION BY tab_4700.col2_6, tab_4700.col2_0 ORDER BY tab_4700.col2_6, tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING ROWS BETWEEN CURRENT ROW AND 8049193815714677612:::INT8 FOLLOWING)::STRING
		AS col_15195,
	tab_4700.col2_1 AS col_15196
FROM
	defaultdb.public.table2@[0] AS tab_4700
GROUP BY
	tab_4700.col2_1, tab_4700.col2_0, tab_4700.col2_6
ORDER BY
	tab_4700.col2_6 ASC;

SET testing_optimizer_random_seed = 8664472571050980723;

SET testing_optimizer_cost_perturbation = 1.0;

SELECT
  tab_4700.col2_6,
	tab_4700.col2_0 AS col_15194,
	string_agg(tab_4700.col2_6::STRING, tab_4700.col2_6::STRING ORDER BY tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING) OVER (PARTITION BY tab_4700.col2_6, tab_4700.col2_0 ORDER BY tab_4700.col2_6, tab_4700.col2_6::STRING ASC, tab_4700.col2_6::STRING ROWS BETWEEN CURRENT ROW AND 8049193815714677612:::INT8 FOLLOWING)::STRING
		AS col_15195,
	tab_4700.col2_1 AS col_15196
FROM
	defaultdb.public.table2@[0] AS tab_4700
GROUP BY
	tab_4700.col2_1, tab_4700.col2_0, tab_4700.col2_6
ORDER BY
	tab_4700.col2_6 ASC;

[mgartner]

I think this is a false positive. The query is not deterministic for all states of table2. I think we need to figure out how to guarantee that string_agg is deterministic. Until then, I've created #87426 to give us some relief from these types of failures.