sql: v21.1.2: non-intersecting sets assertion from ordering_choice

[cockroach-teamcity]

This issue was autofiled by Sentry. It represents a crash or reported error on a live cluster with telemetry enabled.

Sentry link: https://sentry.io/organizations/cockroach-labs/issues/3172549581/?referrer=webhooks_plugin

Panic message:

ordering_choice.go:422: non-intersecting sets
(1) assertion failure
Wraps: (2) attached stack trace
-- stack trace:
| github.com/cockroachdb/cockroach/pkg/sql/opt/props.(*OrderingChoice).Intersection
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/props/ordering_choice.go:422
| github.com/cockroachdb/cockroach/pkg/sql/opt/ordering.groupByBuildChildReqOrdering
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/ordering/group_by.go:49
| github.com/cockroachdb/cockroach/pkg/sql/opt/ordering.BuildChildRequired
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/ordering/ordering.go:40
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.BuildChildPhysicalProps
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/physical_props.go:80
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:507
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeGroup
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:455
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeExpr
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:255
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:510
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeGroup
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:455
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeEnforcer
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:620
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).enforceProps
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:586
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:495
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).optimizeGroup
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:455
| github.com/cockroachdb/cockroach/pkg/sql/opt/xform.(*Optimizer).Optimize
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:225
| github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).buildReusableMemo
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:417
| github.com/cockroachdb/cockroach/pkg/sql.(*optPlanningCtx).buildExecMemo
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:493
| github.com/cockroachdb/cockroach/pkg/sql.(*planner).makeOptimizerPlan
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:194
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).makeExecPlan
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1260
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:972
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:709
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:129
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd.func1
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1705
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1707
| github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1629
| github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:668
| github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1
| /go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:648
| runtime.goexit
| /usr/local/go/src/runtime/asm_amd64.s:1371
Wraps: (3) non-intersecting sets
Error types: (1) *assert.withAssertionFailure (2) *withstack.withStack (3) *errutil.leafError
-- report composition:
*errutil.leafError: non-intersecting sets
ordering_choice.go:422: *withstack.withStack (top exception)
*assert.withAssertionFailure

Stacktrace (expand for inline code snippets):

cockroach/pkg/sql/opt/props/ordering_choice.go

Lines 421 to 423 in cad0000

	default:
	panic(errors.AssertionFailedf("non-intersecting sets"))
	}

in pkg/sql/opt/props.(*OrderingChoice).Intersection

cockroach/pkg/sql/opt/ordering/group_by.go

Lines 48 to 50 in cad0000

	result = result.Intersection(&groupBy.Ordering)

in pkg/sql/opt/ordering.groupByBuildChildReqOrdering

cockroach/pkg/sql/opt/ordering/ordering.go

Lines 39 to 41 in cad0000

	) props.OrderingChoice {
	result := funcMap[parent.Op()].buildChildReqOrdering(parent, required, childIdx)
	if util.CrdbTestBuild && !result.Any() {

in pkg/sql/opt/ordering.BuildChildRequired

cockroach/pkg/sql/opt/xform/physical_props.go

Lines 79 to 81 in cad0000

	childProps.Ordering = ordering.BuildChildRequired(parent, &parentProps.Ordering, nth)

in pkg/sql/opt/xform.BuildChildPhysicalProps

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 506 to 508 in cad0000

	// the nth child.
	childRequired := BuildChildPhysicalProps(o.mem, member, i, required)

in pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 454 to 456 in cad0000

	// Optimize the group member with respect to the required properties.
	memberOptimized := o.optimizeGroupMember(state, member, required)

in pkg/sql/opt/xform.(*Optimizer).optimizeGroup

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 254 to 256 in cad0000

	case memo.RelExpr:
	state := o.optimizeGroup(t, required)
	return state.cost, state.fullyOptimized

in pkg/sql/opt/xform.(*Optimizer).optimizeExpr

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 509 to 511 in cad0000

	// Optimize the child with respect to those properties.
	childCost, childOptimized := o.optimizeExpr(member.Child(i), childRequired)

in pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 454 to 456 in cad0000

	// Optimize the group member with respect to the required properties.
	memberOptimized := o.optimizeGroupMember(state, member, required)

in pkg/sql/opt/xform.(*Optimizer).optimizeGroup

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 619 to 621 in cad0000

	// enforcer properties.
	innerState := o.optimizeGroup(member, memberProps)
	fullyOptimized = innerState.fullyOptimized

in pkg/sql/opt/xform.(*Optimizer).optimizeEnforcer

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 585 to 587 in cad0000

	memberProps := BuildChildPhysicalProps(o.mem, enforcer, 0, required)
	fullyOptimized = o.optimizeEnforcer(state, enforcer, required, member, memberProps)

in pkg/sql/opt/xform.(*Optimizer).enforceProps

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 494 to 496 in cad0000

	// of requiring one of the merge join children to be sorted.
	fullyOptimized = o.enforceProps(state, member, required)

in pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 454 to 456 in cad0000

	// Optimize the group member with respect to the required properties.
	memberOptimized := o.optimizeGroupMember(state, member, required)

in pkg/sql/opt/xform.(*Optimizer).optimizeGroup

cockroach/pkg/sql/opt/xform/optimizer.go

Lines 224 to 226 in cad0000

	rootProps := o.mem.RootProps()
	o.optimizeGroup(root, rootProps)

in pkg/sql/opt/xform.(*Optimizer).Optimize

cockroach/pkg/sql/plan_opt.go

Lines 416 to 418 in cad0000

	opc.log(ctx, "optimizing (no placeholders)")
	if _, err := opc.optimizer.Optimize(); err != nil {
	return nil, err

in pkg/sql.(*optPlanningCtx).buildReusableMemo

cockroach/pkg/sql/plan_opt.go

Lines 492 to 494 in cad0000

	} else if isStale {
	cachedData.Memo, err = opc.buildReusableMemo(ctx)
	if err != nil {

in pkg/sql.(*optPlanningCtx).buildExecMemo

cockroach/pkg/sql/plan_opt.go

Lines 193 to 195 in cad0000

	execMemo, err := opc.buildExecMemo(ctx)
	if err != nil {

in pkg/sql.(*planner).makeOptimizerPlan

cockroach/pkg/sql/conn_executor_exec.go

Lines 1259 to 1261 in cad0000

	func (ex *connExecutor) makeExecPlan(ctx context.Context, planner *planner) error {
	if err := planner.makeOptimizerPlan(ctx); err != nil {
	log.VEventf(ctx, 1, "optimizer plan failed: %v", err)

in pkg/sql.(*connExecutor).makeExecPlan

cockroach/pkg/sql/conn_executor_exec.go

Lines 971 to 973 in cad0000

	// between here and there needs to happen even if there's an error.
	err := ex.makeExecPlan(ctx, planner)
	// We'll be closing the plan manually below after execution; this

in pkg/sql.(*connExecutor).dispatchToExecutionEngine

cockroach/pkg/sql/conn_executor_exec.go

Lines 708 to 710 in cad0000

	if err := ex.dispatchToExecutionEngine(ctx, p, res); err != nil {
	stmtThresholdSpan.Finish()

in pkg/sql.(*connExecutor).execStmtInOpenState

cockroach/pkg/sql/conn_executor_exec.go

Lines 128 to 130 in cad0000

	} else {
	ev, payload, err = ex.execStmtInOpenState(ctx, parserStmt, prepared, pinfo, res)
	}

in pkg/sql.(*connExecutor).execStmt

cockroach/pkg/sql/conn_executor.go

Lines 1704 to 1706 in cad0000

	ev, payload, err = ex.execStmt(ctx, tcmd.Statement, nil /* prepared */, nil /* pinfo */, stmtRes)
	return err

in pkg/sql.(*connExecutor).execCmd.func1

cockroach/pkg/sql/conn_executor.go

Lines 1706 to 1708 in cad0000

	return err
	}()
	// Note: we write to ex.statsCollector.PhaseTimes, instead of ex.phaseTimes,

in pkg/sql.(*connExecutor).execCmd

cockroach/pkg/sql/conn_executor.go

Lines 1628 to 1630 in cad0000

	var err error
	if err = ex.execCmd(ex.Ctx()); err != nil {
	if errors.IsAny(err, io.EOF, errDrainingComplete) {

in pkg/sql.(*connExecutor).run

cockroach/pkg/sql/conn_executor.go

Lines 667 to 669 in cad0000

	}()
	return h.ex.run(ctx, s.pool, reserved, cancel)
	}

in pkg/sql.(*Server).ServeConn

cockroach/pkg/sql/pgwire/conn.go

Lines 647 to 649 in cad0000

	reservedOwned = false // We're about to pass ownership away.
	retErr = sqlServer.ServeConn(ctx, connHandler, reserved, cancelConn)
	}()

in pkg/sql/pgwire.(*conn).processCommandsAsync.func1
/usr/local/go/src/runtime/asm_amd64.s#L1370-L1372 in runtime.goexit

pkg/sql/opt/props/ordering_choice.go in pkg/sql/opt/props.(*OrderingChoice).Intersection at line 422
pkg/sql/opt/ordering/group_by.go in pkg/sql/opt/ordering.groupByBuildChildReqOrdering at line 49
pkg/sql/opt/ordering/ordering.go in pkg/sql/opt/ordering.BuildChildRequired at line 40
pkg/sql/opt/xform/physical_props.go in pkg/sql/opt/xform.BuildChildPhysicalProps at line 80
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember at line 507
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeGroup at line 455
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeExpr at line 255
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember at line 510
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeGroup at line 455
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeEnforcer at line 620
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).enforceProps at line 586
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeGroupMember at line 495
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).optimizeGroup at line 455
pkg/sql/opt/xform/optimizer.go in pkg/sql/opt/xform.(*Optimizer).Optimize at line 225
pkg/sql/plan_opt.go in pkg/sql.(*optPlanningCtx).buildReusableMemo at line 417
pkg/sql/plan_opt.go in pkg/sql.(*optPlanningCtx).buildExecMemo at line 493
pkg/sql/plan_opt.go in pkg/sql.(*planner).makeOptimizerPlan at line 194
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).makeExecPlan at line 1260
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).dispatchToExecutionEngine at line 972
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).execStmtInOpenState at line 709
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).execStmt at line 129
pkg/sql/conn_executor.go in pkg/sql.(*connExecutor).execCmd.func1 at line 1705
pkg/sql/conn_executor.go in pkg/sql.(*connExecutor).execCmd at line 1707
pkg/sql/conn_executor.go in pkg/sql.(*connExecutor).run at line 1629
pkg/sql/conn_executor.go in pkg/sql.(*Server).ServeConn at line 668
pkg/sql/pgwire/conn.go in pkg/sql/pgwire.(*conn).processCommandsAsync.func1 at line 648
/usr/local/go/src/runtime/asm_amd64.s in runtime.goexit at line 1371

Tag	Value
Cockroach Release	v21.2.8
Cockroach SHA:	cad0000
Platform	linux amd64
Distribution	CCL
Environment	v21.2.8
Command	start-single-node
Go Version	``
# of CPUs
# of Goroutines

Jira issue: CRDB-14962

[tommilligan]

Hi, this was me reproducing a crash that I experienced in a non-telemetry enabled cluster. The following SQL triggers the crash:

CREATE TABLE items (
  id INT8 PRIMARY KEY,

  project_id INT8 NOT NULL,
  name STRING(1024),

  /* The name field and this constraint are required for reproduction, though not used in the query. */
  UNIQUE (project_id, name)
);
CREATE TABLE item_revisions (
  item_id INT8 NOT NULL REFERENCES items,
  revision_number INT4 NOT NULL,

  PRIMARY KEY (item_id, revision_number)
);
CREATE TABLE item_revision_datasets (
  item_id INT8 NOT NULL,
  revision_number INT4 NOT NULL,
  dataset_id INT8 NOT NULL,

  PRIMARY KEY (item_id, revision_number, dataset_id),

  /* This constraint is not required for reproduction. */
  CONSTRAINT fk_item_revision FOREIGN KEY (item_id, revision_number) REFERENCES item_revisions (item_id, revision_number)
);

INSERT INTO items (id, project_id, name) VALUES (1, -9213355328025498762, '1');
INSERT INTO item_revisions (item_id, revision_number) VALUES (1, 0);
INSERT INTO item_revisions (item_id, revision_number) VALUES (1, 1);
INSERT INTO item_revision_datasets (item_id, revision_number, dataset_id) VALUES (1, 0, 10);
INSERT INTO item_revision_datasets (item_id, revision_number, dataset_id) VALUES (1, 0, 11);
INSERT INTO item_revision_datasets (item_id, revision_number, dataset_id) VALUES (1, 1, 12);
INSERT INTO item_revision_datasets (item_id, revision_number, dataset_id) VALUES (1, 1, 13);

SELECT 
DISTINCT ON (item_revisions.item_id)
items.project_id, item_revisions.item_id, array_agg(item_revision_datasets.dataset_id) FROM item_revisions
INNER JOIN items ON items.id = item_revisions.item_id
INNER JOIN item_revision_datasets ON item_revisions.revision_number = item_revision_datasets.revision_number
GROUP BY (item_revisions.item_id, items.project_id, item_revisions.revision_number)
ORDER BY (item_revisions.item_id, item_revisions.revision_number)
;

Happy to provide any further details that would assist you. I was running the official latest docker image in single node development mode.

[mgartner]

@tommilligan Thanks for providing detailed reproduction steps! We'll look into this.

---

I've been able to reproduce back on v21.1.0, so I'm not labelling this a release blocker.

[tommilligan]

Ah yes, sorry I should have given the original environment details as well. For the record, they are:

$ cockroach version
Build Tag:        v21.1.2
Build Time:       2021/06/07 18:09:50
Distribution:     CCL
Platform:         linux amd64 (x86_64-unknown-linux-gnu)
Go Version:       go1.15.11
C Compiler:       gcc 6.5.0
Build Commit ID:  d9845bd3021b4f5b816bd8bcde58b90a73f0377c
Build Type:       release