internal error: null rejection requested on non-null column

[DerZc]

Describe the problem

The following program triggers an internal error:

CREATE DATABASE database5;
USE database5;
CREATE TABLE t0 (c0 INTERVAL);
CREATE TABLE t1 (c0 INTERVAL);
CREATE TABLE t2 (c0 FLOAT);
SELECT t1.c0 AS c0 FROM t0, t2 FULL OUTER JOIN t1 ON true WHERE (((t0.c0) IN (t1.c0)) AND ((t2.c0) IN (SELECT STDDEV(t2.c0) FROM t2)));

This is the error message:

> SELECT t1.c0 AS c0 FROM t0, t2 FULL OUTER JOIN t1 ON true WHERE (((t0.c0) IN (t1.c0)) AND ((t2.c0) IN (SELECT STDDEV(t2.c0) FROM t2)));
ERROR: internal error: null rejection requested on non-null column
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/norm/reject_nulls_funcs.go:218: DeriveRejectNullCols()
github.com/cockroachdb/cockroach/pkg/sql/opt/norm/reject_nulls_funcs.go:25: RejectNullCols()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/norm/factory.og.go:3474: ConstructInnerJoin()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/xform/explorer.og.go:1677: exploreSemiJoin()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/xform/explorer.og.go:34: exploreGroupMember()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/explorer.go:181: exploreGroup()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:528: optimizeGroup()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:291: optimizeExpr()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:570: optimizeGroupMember()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:515: optimizeGroup()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:261: Optimize()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:575: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:232: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1474: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1086: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:696: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:131: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2428: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:130: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1973: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1977: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1898: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:857: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:730: func1()
GOROOT/src/runtime/asm_amd64.s:1594: 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.

To Reproduce

I build cockroach from the last commit in github repo (07c7d4b), but this bug can reproduce with the last release version v22.2.7.

I run cockroach in a single machine, and execute the query with CLI cockroach sql --echo-sql --insecure --port 26257 --user root < database5.sql

Expected behavior
No error.

Environment:

• CockroachDB version [last commit version and last release version v22.2.7]
• Server OS: [ubuntu 22.04]
• Client app [CLI]

Jira issue: CRDB-26492

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

[DrewKimball]

Hi @DerZc, thanks for submitting this find! It looks like the problem is that our null-rejection rules (which simplify outer joins like LEFT JOIN into non-outer joins like INNER JOIN) assume that a child operator should always carry out null-rejection before its parent. This assumption is violated for cases like this one, where query plan exploration uncovers that null-rejection is possible after normalization fails to do so.

[DerZc]

Hi @DrewKimball, I got it, thank you for such a quick and detailed explanation!