opt: v20.1.4: assertion failed: estimated row count must be non-zero

[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/1856109940/?referrer=webhooks_plugin

Panic message:

*errors.errorString
*safedetails.withSafeDetails: estimated row count must be non-zero (1)
statistics_builder.go:2359: *withstack.withStack (2)
*assert.withAssertionFailure
(check the extra data payloads)

Stacktrace (expand for inline code snippets):

cockroach/pkg/sql/opt/memo/statistics_builder.go

Lines 2358 to 2360 in 12049d3

	if s.RowCount <= 0 && relProps.Cardinality.Max > 0 {
	panic(errors.AssertionFailedf("estimated row count must be non-zero"))
	}

in pkg/sql/opt/memo.(*statisticsBuilder).finalizeFromCardinality

cockroach/pkg/sql/opt/memo/statistics_builder.go

Lines 764 to 766 in 12049d3

	sb.finalizeFromCardinality(relProps)
	}

in pkg/sql/opt/memo.(*statisticsBuilder).buildSelect

cockroach/pkg/sql/opt/memo/logical_props_builder.go

Lines 245 to 247 in 12049d3

	if !b.disableStats {
	b.sb.buildSelect(sel, rel)
	}

in pkg/sql/opt/memo.(*logicalPropsBuilder).buildSelectProps
https://github.com/cockroachdb/cockroach/blob/12049d3fe3650660e1b6abf1e522d9bb016acb88/pkg/sql/opt/memo/expr.og.go#L15684-L15686 in pkg/sql/opt/memo.(*Memo).MemoizeSelect
https://github.com/cockroachdb/cockroach/blob/12049d3fe3650660e1b6abf1e522d9bb016acb88/pkg/sql/opt/norm/factory.og.go#L1036-L1038 in pkg/sql/opt/norm.(*Factory).ConstructSelect
https://github.com/cockroachdb/cockroach/blob/12049d3fe3650660e1b6abf1e522d9bb016acb88/pkg/sql/opt/norm/factory.og.go#L664-L666 in pkg/sql/opt/norm.(*Factory).ConstructSelect
https://github.com/cockroachdb/cockroach/blob/12049d3fe3650660e1b6abf1e522d9bb016acb88/pkg/sql/opt/norm/factory.og.go#L385-L387 in pkg/sql/opt/norm.(*Factory).ConstructSelect

cockroach/pkg/sql/opt/optbuilder/select.go

Lines 1049 to 1051 in 12049d3

	// Wrap the filter in a FiltersOp.
	inScope.expr = b.factory.ConstructSelect(
	inScope.expr.(memo.RelExpr),

in pkg/sql/opt/optbuilder.(*Builder).buildWhere

cockroach/pkg/sql/opt/optbuilder/select.go

Lines 922 to 924 in 12049d3

	b.processWindowDefs(sel, fromScope)
	b.buildWhere(sel.Where, fromScope)

in pkg/sql/opt/optbuilder.(*Builder).buildSelectClause

cockroach/pkg/sql/opt/optbuilder/select.go

Lines 868 to 870 in 12049d3

	case *tree.SelectClause:
	outScope = b.buildSelectClause(t, orderBy, locking, desiredTypes, inScope)

in pkg/sql/opt/optbuilder.(*Builder).buildSelectStmtWithoutParens

cockroach/pkg/sql/opt/optbuilder/select.go

Lines 841 to 843 in 12049d3

	return b.processWiths(with, inScope, func(inScope *scope) *scope {
	return b.buildSelectStmtWithoutParens(
	wrapped, orderBy, limit, locking, desiredTypes, inScope,

in pkg/sql/opt/optbuilder.(*Builder).buildSelect.func1

cockroach/pkg/sql/opt/optbuilder/with.go

Lines 28 to 30 in 12049d3

	inScope.atRoot = false
	outScope := buildStmt(inScope)
	inScope.atRoot = prevAtRoot

in pkg/sql/opt/optbuilder.(*Builder).processWiths

cockroach/pkg/sql/opt/optbuilder/select.go

Lines 840 to 842 in 12049d3

	return b.processWiths(with, inScope, func(inScope *scope) *scope {
	return b.buildSelectStmtWithoutParens(

in pkg/sql/opt/optbuilder.(*Builder).buildSelect

cockroach/pkg/sql/opt/optbuilder/builder.go

Lines 252 to 254 in 12049d3

	case *tree.Select:
	return b.buildSelect(stmt, noRowLocking, desiredTypes, inScope)

in pkg/sql/opt/optbuilder.(*Builder).buildStmt

cockroach/pkg/sql/opt/optbuilder/builder.go

Lines 216 to 218 in 12049d3

	return b.buildStmt(stmt, desiredTypes, inScope)
	}

in pkg/sql/opt/optbuilder.(*Builder).buildStmtAtRoot

cockroach/pkg/sql/opt/optbuilder/builder.go

Lines 187 to 189 in 12049d3

	// and physical properties.
	outScope := b.buildStmtAtRoot(b.stmt, nil /* desiredTypes */, b.allocScope())

in pkg/sql/opt/optbuilder.(*Builder).Build

cockroach/pkg/sql/plan_opt.go

Lines 432 to 434 in 12049d3

	bld := optbuilder.New(ctx, &p.semaCtx, p.EvalContext(), &opc.catalog, f, opc.p.stmt.AST)
	if err := bld.Build(); err != nil {
	return nil, err

in pkg/sql.(*optPlanningCtx).buildExecMemo

cockroach/pkg/sql/plan_opt.go

Lines 168 to 170 in 12049d3

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

in pkg/sql.(*planner).makeOptimizerPlan

cockroach/pkg/sql/conn_executor_exec.go

Lines 800 to 802 in 12049d3

	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 690 to 692 in 12049d3

	// 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 480 to 482 in 12049d3

	p.autoCommit = os.ImplicitTxn.Get() && !ex.server.cfg.TestingKnobs.DisableAutoCommit
	if err := ex.dispatchToExecutionEngine(ctx, p, res); err != nil {
	return nil, nil, err

in pkg/sql.(*connExecutor).execStmtInOpenState

cockroach/pkg/sql/conn_executor_exec.go

Lines 95 to 97 in 12049d3

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

in pkg/sql.(*connExecutor).execStmt

cockroach/pkg/sql/conn_executor.go

Lines 1391 to 1393 in 12049d3

	stmtCtx := withStatement(ctx, ex.curStmt)
	ev, payload, err = ex.execStmt(stmtCtx, curStmt, stmtRes, nil /* pinfo */)
	if err != nil {

in pkg/sql.(*connExecutor).execCmd

cockroach/pkg/sql/conn_executor.go

Lines 1320 to 1322 in 12049d3

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

in pkg/sql.(*connExecutor).run

cockroach/pkg/sql/conn_executor.go

Lines 478 to 480 in 12049d3

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

in pkg/sql.(*Server).ServeConn

cockroach/pkg/sql/pgwire/conn.go

Lines 581 to 583 in 12049d3

	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#L1356-L1358 in runtime.goexit

pkg/sql/opt/memo/statistics_builder.go in pkg/sql/opt/memo.(*statisticsBuilder).finalizeFromCardinality at line 2359
pkg/sql/opt/memo/statistics_builder.go in pkg/sql/opt/memo.(*statisticsBuilder).buildSelect at line 765
pkg/sql/opt/memo/logical_props_builder.go in pkg/sql/opt/memo.(*logicalPropsBuilder).buildSelectProps at line 246
pkg/sql/opt/memo/expr.og.go in pkg/sql/opt/memo.(*Memo).MemoizeSelect at line 15685
pkg/sql/opt/norm/factory.og.go in pkg/sql/opt/norm.(*Factory).ConstructSelect at line 1037
pkg/sql/opt/norm/factory.og.go in pkg/sql/opt/norm.(*Factory).ConstructSelect at line 665
pkg/sql/opt/norm/factory.og.go in pkg/sql/opt/norm.(*Factory).ConstructSelect at line 386
pkg/sql/opt/optbuilder/select.go in pkg/sql/opt/optbuilder.(*Builder).buildWhere at line 1050
pkg/sql/opt/optbuilder/select.go in pkg/sql/opt/optbuilder.(*Builder).buildSelectClause at line 923
pkg/sql/opt/optbuilder/select.go in pkg/sql/opt/optbuilder.(*Builder).buildSelectStmtWithoutParens at line 869
pkg/sql/opt/optbuilder/select.go in pkg/sql/opt/optbuilder.(*Builder).buildSelect.func1 at line 842
pkg/sql/opt/optbuilder/with.go in pkg/sql/opt/optbuilder.(*Builder).processWiths at line 29
pkg/sql/opt/optbuilder/select.go in pkg/sql/opt/optbuilder.(*Builder).buildSelect at line 841
pkg/sql/opt/optbuilder/builder.go in pkg/sql/opt/optbuilder.(*Builder).buildStmt at line 253
pkg/sql/opt/optbuilder/builder.go in pkg/sql/opt/optbuilder.(*Builder).buildStmtAtRoot at line 217
pkg/sql/opt/optbuilder/builder.go in pkg/sql/opt/optbuilder.(*Builder).Build at line 188
pkg/sql/plan_opt.go in pkg/sql.(*optPlanningCtx).buildExecMemo at line 433
pkg/sql/plan_opt.go in pkg/sql.(*planner).makeOptimizerPlan at line 169
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).makeExecPlan at line 801
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).dispatchToExecutionEngine at line 691
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).execStmtInOpenState at line 481
pkg/sql/conn_executor_exec.go in pkg/sql.(*connExecutor).execStmt at line 96
pkg/sql/conn_executor.go in pkg/sql.(*connExecutor).execCmd at line 1392
pkg/sql/conn_executor.go in pkg/sql.(*connExecutor).run at line 1321
pkg/sql/conn_executor.go in pkg/sql.(*Server).ServeConn at line 479
pkg/sql/pgwire/conn.go in pkg/sql/pgwire.(*conn).processCommandsAsync.func1 at line 582
/usr/local/go/src/runtime/asm_amd64.s in runtime.goexit at line 1357

Tag	Value
Cockroach Release	v20.1.4
Cockroach SHA:	12049d3
Platform	linux amd64
Distribution	CCL
Environment	v20.1.4
Command	server
Go Version	go1.13.9
# of CPUs	2
# of Goroutines	297

[yuzefovich]

Hm, a similar issue has been fixed (#49911), but apparently there is another one lurking somewhere. cc @rytaft

[rytaft]

Ack thanks.

Queries from sentry:

SELECT * FROM _ LEFT JOIN _ ON _._ = _._ WHERE (((_ = _) AND (_._::DATE >= _)) AND (_._::DATE <= _)) AND ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( [...]

SELECT * FROM _ LEFT JOIN _ ON _._ = _._ WHERE ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( [...]

[rytaft]

I think what may be going on is that these queries clearly have a lot of filter conditions. We prevent the selectivity of a single filter from ever going below 1e-10, but to get the overall selectivity we multiply the individual selectivities together. We only need 32 filter conditions in which the selectivity is 1e-10 for the overall selectivity to underflow the floating point representation and result in selectivity 0. The code for buildSelect (where this error occurred) is tight enough that as far as I can tell, my theory seems to be the only way to trigger this particular error condition.

I was able to reproduce the error on v20.1.4 using this theory as follows:

> create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int,
c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, c21 int,
c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int, c31 int, c32 int, c33 int);

> insert into t values (NULL);

> select * from t where c1 = 1 and c2 = 1 and c3 = 1 and c4 = 1 and c5 = 1 and c6 = 1 and
c7 = 1 and c8 = 1 and c9 = 1 and c10 = 1 and c11 = 1 and c12 = 1 and c13 = 1 and c14 = 1 and
c15 = 1 and c16 = 1 and c17 = 1 and c18 = 1 and c19 = 1 and c20 = 1 and c21 = 1 and c22 = 1 and
c23 = 1 and c24 = 1 and c25 = 1 and c26 = 1 and c27 = 1 and c28 = 1 and c29 = 1 and c30 = 1 and
c31 = 1 and c32 = 1 and c33 = 1;
ERROR: internal error: estimated row count must be non-zero
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/statistics_builder.go:2359: finalizeFromCardinality()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/statistics_builder.go:765: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:246: buildSelectProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/expr.og.go:15685: MemoizeSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/norm/factory.og.go:1037: ConstructSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/norm/factory.og.go:386: ConstructSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1050: buildWhere()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:923: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:869: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:842: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:29: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:841: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:253: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:217: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:188: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:433: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:169: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:801: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:691: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:481: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:96: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1392: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1321: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:479: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:582: func1()
runtime/asm_amd64.s:1357: goexit()

HINT: You have encountered an unexpected error.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.

I'm not sure if this is a problem on master (I think the multi-column stats work may prevent this problem from happening), but I'll investigate further.

[rytaft]

Just a note: I fixed this on master (before the release-20.2 branch was cut), but did not backport to 20.1 or 19.2. The backport was causing too many plan diffs, and I decided that it was not worth the potential for plan regressions to fix this extremely rare issue.