cockroachdb / cockroachdb/cockroach
sql: jsonb_agg with FOLLOWER_READ_TIMESTAMP() triggers Internal Error
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
The latest version of the CockroachDB (d20dec121) shows `Internal Error` when executing the following query:
```sql
CREATE TABLE v0 (c1 JSONB);
SELECT JSONB_AGG( c1 ORDER BY FOLLOWER_READ_TIMESTAMP()) FROM v0;
```
**To Reproduce**
Here is the detail steps to reproduce the bug.
1. In operating system Ubuntu 20.04, download the CockroachDB source code from the github source.
2. Use the latest version of the CockroachDB code (tested version: d20dec121)
3. Directly `make install` in the root repository folder.
4. Run `./cockroach demo`, and then paste the PoC query to the cockroach cli environment.
5. Observe the `Internal Error` and log the stack information.
**Expected behavior**
The SELECT should be executed successfully.
**Additional data / screenshots**
Here is the outputted stack trace:
```
demo@127.0.0.1:26257/movr> CREATE TABLE v0 (c1 JSONB);
CREATE TABLE
Time: 5ms total (execution 5ms / network 0ms)
demo@127.0.0.1:26257/movr> SELECT JSONB_AGG( c1 ORDER BY FOLLOWER_READ_TIMESTAMP()) FROM v0;
ERROR: internal error: cannot map variable 7 to an indexed var
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/scalar.go:143: indexedVar()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:2991: buildWindow()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:261: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1586: buildGroupByInput()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:1458: buildGroupBy()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/relational.go:218: buildRelational()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:303: build()
github.com/cockroachdb/cockroach/pkg/sql/opt/exec/execbuilder/builder.go:250: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:640: runExecBuilder()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:307: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1526: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1133: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:738: execStmtInOpenState()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:130: func1()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2522: 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:834: ServeConn()
github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:727: 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-756-gd20dec121b
Build Time: 2023/01/11 19:01:10
Distribution: CCL
Platform: linux arm64 (aarch64-linux-gnu)
Go Version: go1.19
C Compiler: gcc 9.4.0
Build Commit ID: d20dec121b183adb0b7db5edc1136b22f06ebc3d
Build Type: development
```
**Additional context**
The `Internal Error` can be reproduced by `CockroachDB v23.1 Testing Release`, `v22.2.2` and `v22.1.13`. We haven't tested earlier versions.
Jira issue: CRDB-23326
Contributor guide
Research direction
Reproduce the query with ./cockroach demo, then start at pkg/sql/opt/exec/execbuilder/scalar.go:143 and follow the buildWindow and buildGroupBy paths in pkg/sql/opt/exec/execbuilder/relational.go. Use the reported indexed-variable mapping failure to guide investigation; done means the JSONB_AGG query executes successfully without an Internal Error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100