Skip to content

sql: incorrect results due to sort in-between paired joins #89603

@cockroach-teamcity

Description

@cockroach-teamcity

roachtest.costfuzz failed with artifacts on release-22.2 @ 64049e4b9210de3af4a1d814a9af0123b59a055f:

		  | + 	``a	,0,NULL,X,0``,
		  | + 	``a	,0,NULL,X,0``,
		  |   	"a,0,0,X,0",
		  |   	"a,0,NULL,X,0",
		  |   	... // 166 identical elements
		  |   	"\x8eC\xec,0,NULL,X,0",
		  |   	"\x8eC\xec,0,NULL,X,0",
		  | + 	"\x8eC\xec,0,NULL,X,0",
		  |   	"\xa7\xa8\xa1\xcc\xfd\xa6\xc2\xc1,0,0,X,0",
		  | - 	"\xa7\xa8\xa1\xcc\xfd\xa6\xc2\xc1,0,NULL,X,0",
		  |   	"\xa7\xa8\xa1\xcc\xfd\xa6\xc2\xc1,0,NULL,X,0",
		  |   	"\xa7\xa8\xa1\xcc\xfd\xa6\xc2\xc1,0,NULL,X,0",
		  |   	... // 64 identical elements
		  |   	"á,0,NULL,X,0",
		  |   	"\xc4\x12|\x19\xab,0,0,X,0",
		  | + 	"\xc4\x12|\x19\xab,0,NULL,X,0",
		  |   	"\xc4\x12|\x19\xab,0,NULL,X,0",
		  |   	"\xc4\x12|\x19\xab,0,NULL,X,0",
		  |   	... // 30 identical elements
		  |   	"\xc4\x12|\x19\xab,0,NULL,X,0",
		  |   	"\xca,0,0,X,0",
		  | - 	"\xca,0,NULL,X,0",
		  |   	"\xca,0,NULL,X,0",
		  |   	"\xca,0,NULL,X,0",
		  |   	... // 64 identical elements
		  |   }
		  | sql: SELECT
		  | 	tab_17534.col1_3 AS col_52140,
		  | 	0:::OID AS col_52141,
		  | 	tab_17533.col1_7 AS col_52142,
		  | 	'\x58':::BYTES AS col_52143,
		  | 	0:::OID AS col_52144
		  | FROM
		  | 	defaultdb.public.table1@[0] AS tab_17533
		  | 	RIGHT JOIN defaultdb.public.table1@[0] AS tab_17534
		  | 		JOIN defaultdb.public.table1@[0] AS tab_17535 ON
		  | 				(tab_17534.crdb_internal_mvcc_timestamp) = (tab_17535.crdb_internal_mvcc_timestamp)
		  | 				AND (tab_17534.col1_4) = (tab_17535.col1_4)
		  | 		JOIN defaultdb.public.table1@[0] AS tab_17536 ON
		  | 				(tab_17535.col1_1) = (tab_17536.col1_1)
		  | 				AND (tab_17535.col1_3) = (tab_17536.col1_3)
		  | 				AND (tab_17534.col1_6) = (tab_17536.col1_6)
		  | 		JOIN defaultdb.public.table1@[0] AS tab_17537
		  | 			JOIN defaultdb.public.table1@[0] AS tab_17538 ON
		  | 					(tab_17537.crdb_internal_mvcc_timestamp) = (tab_17538.crdb_internal_mvcc_timestamp) ON
		  | 				(tab_17536.col1_2) = (tab_17538.col1_2) AND (tab_17535.col1_4) = (tab_17537.col1_4) ON
		  | 			(tab_17533.col1_3) = (tab_17537.col1_3) AND (tab_17533.col1_6) = (tab_17536.col1_6)
		  | ORDER BY
		  | 	tab_17535.col1_4, tab_17535.col1_3 DESC
		Error types: (1) *withstack.withStack (2) *errutil.withPrefix (3) *withstack.withStack (4) *errutil.leafError

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

Help

See: roachtest README

See: How To Investigate (internal)

Same failure on other branches

/cc @cockroachdb/sql-queries

This test on roachdash | Improve this report!

Jira issue: CRDB-20329

Metadata

Metadata

Assignees

Labels

C-test-failureBroken test (automatically or manually discovered).O-roachtestO-robotOriginated from a bot.T-sql-queriesSQL Queries Teambranch-release-22.2Used to mark GA and release blockers, technical advisories, and bugs for 22.2

Type

No type

Projects

Status

Done

Relationships

None yet

Development

No branches or pull requests

Issue actions