sql: Internal Error when executing query

[SteveLeungYL]

Describe the problem

The latest version of the CockroachDB (version v22.2.3 and v23.1.0 alpha) shows Internal Error when executing the following query:

To Reproduce

CREATE TABLE v0 (c1 BYTES PRIMARY KEY, c2 TIMESTAMP, INDEX i3(c2));
SELECT * FROM v0 WHERE (c1 = c1 AND c2 = '01-31-2023 00:00:00'::TIMESTAMP) OR (c1 = b'00' AND c1 = b'0') OR (c1 IS NULL AND c2 IS NULL );

To Reproduce

Here is the detail steps to reproduce the bug.

1. In operating system Ubuntu 20.04 LTS, download the CockroachDB source code from the github source.
2. Use the latest version of the CockroachDB code (tested version: 778b6fb) (I am not able to compile the latest master branch of CockroachDB through make)
3. Directly make install in the root repository folder.
4. Alternatively, we can download the pre-build CockroachDB binaries (v22.2.3 and v23.1.0 alpha) from link
5. Run ./cockroach demo, and then paste the PoC query to the cockroach cli environment.
6. Observe the Internal Error and log the stack information.

Expected behavior
The SELECT statement should return empty result, indicating that no data in the table v0 is matching the given constraints.

Additional data / screenshots

Here is the outputted stack trace from version: 778b6fb

ERROR: internal error: no output column equivalent to 6
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/ordering/ordering.go:370: remapProvided()
github.com/cockroachdb/cockroach/pkg/sql/opt/ordering/project.go:93: projectBuildProvided()
github.com/cockroachdb/cockroach/pkg/sql/opt/ordering/ordering.go:90: BuildProvided()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:816: setLowestCostTree()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:803: setLowestCostTree()
github.com/cockroachdb/cockroach/pkg/sql/opt/xform/optimizer.go:274: Optimize()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:584: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:237: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1532: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1139: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:744: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:130: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2527: execWithProfiling()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:129: execStmt()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1959: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1964: execCmd()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1882: run()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:836: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:731: func1()
runtime/asm_arm64.s:1165: goexit()

HINT: You have encountered an unexpected error.

Environment:

• CockroachDB version: v23.1.0-alpha
• Server OS: Ubuntu 20.04 LTS
• Client app: CockroachDB demo command. (./cockroach demo)
• Detailed CockroachDB version output:

cockroach version details:
Build Tag:        v23.1.0-alpha.1-1216-g778b6fbb15
Build Time:       2023/01/24 16:47:34
Distribution:     CCL
Platform:         linux arm64 (aarch64-linux-gnu)
Go Version:       go1.19
C Compiler:       gcc 9.4.0
Build Commit ID:  778b6fbb15ebc9dfcb519b47b0afdeda9aade3a8
Build Type:       development

Additional context

The bug can be reproduced by CockroachDB release version: CockroachDB v23.1 Testing Releases , v22.2.3, v22.2.2 and v22.2.1. However, the PoC does work find in v22.2.0, The bug may be introduced in a commit between the time frame of v22.2.2 and v22.2.1.

Jira issue: CRDB-24046

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

[blathers-crl]

Hi @knz, please add branch-* labels to identify which branch(es) this release-blocker affects.

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

[cucaroach]

Bisected to: #87735

[cucaroach]

Reopen to explore Drew's thoughts on #98092

[mgartner]

@DrewKimball is there still something we need to do here?

What was the motiviation for the GA-blocker label? It looks like this reproduces on v22.2.

[DrewKimball]

is there still something we need to do here?

We should probably try and figure out why we're unable to tell that a column is constant/redundant in remapProvided. @cucaroach do you have bandwidth to do that? I can take a look if not.

[cucaroach]

I was hoping to get to it later this week but focusing on getting copy in before branch cut ATM.

[DrewKimball]

Looking into this one. There's a filter ((c1:5 IS NULL) AND (c2:6 IS NULL)) OR (((c1:5 IS DISTINCT FROM CAST(NULL AS BYTES)) OR CAST(NULL AS BOOL)) AND (c2:6 = '2023-01-31 00:00:00')) [outer=(5,6), constraints=(/6: [/NULL - /NULL] [/'2023-01-31 00:00:00' - /'2023-01-31 00:00:00'])] that could be simplified to c2:6 = '2023-01-31 00:00:00' but isn't. At some point it is used to build a constrained scan, at which the optimizer is able to prove that the rest of the filter is unnecessary. The new constrained scan "knows" that column 6 is constant and therefore optional in any ordering, but none of its ancestor operators can prove this.

While this probably points to some missing filter simplification logic, we can't guarantee that we'll prevent all cases like this, where transformations further down in the tree expose properties that can't be (or weren't) proven further up the tree. The ordering logic needs to be able to correctly handle these cases.

[DrewKimball]

It's possible to trigger the error even with the fix in #98092 with this case:

CREATE TABLE v0 (c1 BYTES PRIMARY KEY, c2 TIMESTAMP, INDEX i3(c2));
SELECT c1 FROM v0 WHERE (c1 = c1 AND c2 = '01-31-2023 00:00:00'::TIMESTAMP) OR (c1 = b'00' AND c1 = b'0') OR (c1 IS NULL AND c2 IS NULL ) ORDER BY c1;