internal error: comparison overload not found (ge, unknown, unknown)

[suyZhong]

Describe the problem

The below test case causes an internal error in cockroachdb.

To Reproduce

Start the CockroachDB instance by using Docker.

docker run --name cockroachdb-test -p 10011:26257 -p 10012:8080 cockroachdb/cockroach:latest start-single-node --insecure

Execute below statements through psql

DROP DATABASE IF EXISTS database1;
CREATE DATABASE database1;
USE database1;

CREATE TABLE t1(c1 INTEGER); 
CREATE VIEW v0(c0) AS SELECT ('a'||NULL) FROM t1;
SELECT * FROM v0 WHERE (v0.c0 BETWEEN (CASE v0.c0 WHEN v0.c0 THEN v0.c0 END ) AND ('a'));
-- internal error: comparison overload not found (ge, unknown, unknown)

Expected behavior
No internal error

Additional data / screenshots

github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1764: BuildSharedProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1818: BuildSharedProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1611: buildFiltersItemProps()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/memo/expr.og.go:9797: PopulateProps()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/norm/factory.og.go:13148: ConstructFiltersItem()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1378: buildWhere()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1247: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1176: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1128: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1127: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:360: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:299: buildStmtAtRootWithScope()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:280: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:259: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:599: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:250: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2325: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1817: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1150: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:145: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:3457: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:144: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2292: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2297: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:2214: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:965: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:253: processCommands()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/server.go:997: func3()
src/runtime/asm_amd64.s:1695: goexit()

Environment:

• CockroachDB version 24.1.3
• Server OS: Docker image
• Client app psql and JDBC

Additional context
Sorry that I might be responsible for these issues: #128738

I wasn't aware that the internal errors will automatically reported and also somehow ignored them.

Add any other context about the problem here.

Jira issue: CRDB-41260

[blathers-crl]

Hi @suyZhong, please add branch-* labels to identify which branch(es) this C-bug affects.

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

[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)
• @cockroachdb/sql-foundations (found keywords: DROP DATABASE)

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 dev-inf.}

[DrewKimball]

[triage] There may be some sentry issues related to this.

[DrewKimball]

Hi @suyZhong, thanks for the report! I can confirm that it reproduces on my machine.