roachtest.unoptimized-query-oracle/disable-rules=half

[rytaft]

roachtest.unoptimized-query-oracle/disable-rules=half failed with artifacts on master @ 524fd14da3fefcd849f44a835cc5f88f5dbdadcc:

		  |   	"-99,a\n,NULL,BOX(-1 15,0 16),0,{\"\\\\xc5b5f3991f524f\"},NULL,\ta",
		  |   	... // 15 identical elements
		  |   	"-99,a\n,NULL,BOX(-1.2958624195456403 -0.7007902262760126,-0.76394"...,
		  |   	"-99,a\n,NULL,BOX(-1.3207114753925442 0.07543733824577625,0.411722"...,
		  |   	strings.Join({
		  |   		"-99,a\n,NULL,BOX(-1.371471829151467 1.5445529591343243,-0.4097185",
		  |   		``8119740223 1.5844682297595416),0,{"\\x97","\\x4161"},``,
		  | + 		"NULL",
		  |   		``,	a``,
		  |   	}, ""),
		  |   	"-99,a\n,NULL,BOX(-1.3926406249344883 -0.6842762115529295,-0.23034"...,
		  |   	"-99,a\n,NULL,BOX(-1.3965146560734436 -0.5498867231573248,-0.99270"...,
		  |   	... // 35 identical elements
		  |   	"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x\",\"\\\\x2061\"},NULL,\ta",
		  |   	"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x\",\"\\\\x2f78f42ae5ad84f2\",\"\\"...,
		  |   	strings.Join({
		  |   		"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x\",\"\\\\x6120\",\"\\\\x\",\"\\\\x840c",
		  |   		"148fef14\"},\xc3",
		  | - 		"\x80",
		  | + 		"\xa0",
		  |   		",h\xc5",
		  |   	}, ""),
		  |   	"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x\",\"\\\\x616162\",\"\\\\xaf928150"...,
		  |   	"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x\",\"\\\\x7c3fdc0d748201\"},NUL"...,
		  |   	... // 55 identical elements
		  |   	"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x58\"},',\ta",
		  |   	"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x5b\"},\f,\ta",
		  |   	strings.Join({
		  |   		"-99,a\n,NULL,BOX(-10 -10,10 10),0,{\"\\\\x5f\",\"\\\\x4161\",\"\\\\xc3a0c3a1",
		  |   		``","\\x3a9d1d5c8d"},``,
		  | - 		"\x00",
		  |   		",aaaaaa",
		  |   	}, ""),
		  |   	... // 347 identical and 12 modified elements
		  |   }
		  | sql: SELECT
		  | 	(-99):::INT8 AS col_3616,
		  | 	e'a\n':::STRING AS col_3617,
		  | 	tab_1118.col1_0 AS col_3618,
		  | 	tab_1118.col1_7 AS col_3619,
		  | 	0:::OID AS col_3620,
		  | 	tab_1118.col1_1 AS col_3621,
		  | 	lag(tab_1118.col1_2::STRING) OVER (PARTITION BY tab_1118.col1_3, tab_1118.crdb_internal_mvcc_timestamp ORDER BY tab_1118.col1_16, tab_1118.col1_2 ROWS CURRENT ROW)::STRING
		  | 		AS col_3622,
		  | 	tab_1118.col1_6 AS col_3623
		  | FROM
		  | 	defaultdb.public.table1@[0] AS tab_1118
		  | ORDER BY
		  | 	tab_1118.col1_15 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)

This test on roachdash | Improve this report!

Originally posted by @cockroach-teamcity in #86308 (comment)

Jira issue: CRDB-19105

[blathers-crl]

Hi @rytaft, please add a C-ategory label to your issue. Check out the label system docs.

While you're here, please consider adding an A- label to help keep our repository tidy.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[DrewKimball]

It looks like the plans that generated the diff are the same - so it's probably vectorize on vs off that makes the difference, specifically for the lag column.

[DrewKimball]

I think I see the problem... in this query, the lag operator is taking the next row that orders greater than the current row. We make sure to add the lag input column to the ORDER BY clause, but consider what happens when there are at least two rows that order the same. Within that peer group, any order is allowed, but lag will output different values depending on whether the row happens to be the last one in its peer group (in which case it will output the next smallest/largest value as opposed to the current value).
I think it may be necessary to ensure we order on a key in cases like this one to prevent nondeterminism.

[mgartner]

Good find! Sounds like it's not a release blocker then.