opt: internal error: estimated row count must be non-zero

[bajinsheng]

Describe the problem

Unexpected internal error.

To Reproduce

CREATE TABLE t0 (c0 INT);
SELECT t0.rowid FROM t0 WHERE (t0.rowid) BETWEEN (0) AND (CASE  WHEN NULL THEN t0.rowid ELSE IF(NULL, 1, 9223372036854775807) END) UNION SELECT t0.rowid FROM t0; --ERROR: internal error: estimated row count must be non-zero SQLSTATE: XX000

Expected behavior
No error.

Additional data / screenshots

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:2708: finalizeFromCardinality()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/statistics_builder.go:1995: buildSetNode()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:790: buildSetProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:661: buildUnionProps()
github.com/cockroachdb/cockroach/bazel-out/k8-fastbuild/bin/pkg/sql/opt/memo/expr.og.go:20297: MemoizeUnion()
github.com/cockroachdb/cockroach/bazel-out/k8-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:10511: ConstructUnion()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/union.go:101: buildSetOp()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/union.go:37: buildUnionClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:939: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:908: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:115: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:907: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:302: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:252: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:226: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:557: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:228: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1374: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1052: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:697: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:146: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1920: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1924: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1846: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:785: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:727: func1()
GOROOT/src/runtime/asm_amd64.s:1571: 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.

Environment:

• CockroachDB version [5cd0309]
• Server OS: [Ubuntu 20.04]
• Client app [cockroach sql]

Additional context

Jira issue: CRDB-18271

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: Optimizer,plan)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[yuzefovich]

Thanks for the report!

I confirmed that the bug is present on 22.1.5 and 21.2.14, but not on 21.1.19. Moving to backlog, but cc @rytaft since you've fixed a handful issues like this.