cockroachdb / cockroachdb/cockroach
sql: internal error when creating partial index with predicate that references a UDF
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
```sql
CREATE FUNCTION is_even(i INT) RETURNS BOOL AS $$
SELECT i % 2 = 0;
$$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE t (
k INT PRIMARY KEY,
a INT,
INDEX (a) WHERE is_even(k)
);
CREATE INDEX partial_idx ON t(a) WHERE is_even(k);
-- ERROR: internal error: executing declarative schema change PostCommitPhase stage 7 of 7 with 1 ValidationType op (rollback=false) for CREATE INDEX: VERIFY INDEX: unexpected expression during partial index normalization: *memo.ProjectExpr
-- SQLSTATE: XX000
-- DETAIL: stack trace:
-- pkg/sql/opt/optbuilder/partial_index.go:198: buildPartialIndexPredicate()
-- pkg/sql/opt/optbuilder/partial_index.go:99: addPartialIndexPredicatesForTable()
-- pkg/sql/opt/optbuilder/select.go:755: buildScan()
-- pkg/sql/opt/optbuilder/select.go:484: buildScanFromTableRef()
-- pkg/sql/opt/optbuilder/select.go:280: buildDataSource()
-- pkg/sql/opt/optbuilder/select.go:89: buildDataSource()
-- pkg/sql/opt/optbuilder/select.go:1404: buildFromTablesRightDeep()
-- pkg/sql/opt/optbuilder/select.go:1381: buildFromTables()
-- pkg/sql/opt/optbuilder/select.go:1306: buildFrom()
-- pkg/sql/opt/optbuilder/select.go:1222: buildSelectClause()
-- pkg/sql/opt/optbuilder/select.go:1154: buildSelectStmtWithoutParens()
-- pkg/sql/opt/optbuilder/select.go:1106: func1()
-- pkg/sql/opt/optbuilder/with.go:110: processWiths()
-- pkg/sql/opt/optbuilder/select.go:1105: buildSelect()
-- pkg/sql/opt/optbuilder/builder.go:393: buildStmt()
-- pkg/sql/opt/optbuilder/builder.go:331: buildStmtAtRootWithScope()
-- pkg/sql/opt/optbuilder/builder.go:307: buildStmtAtRoot()
-- pkg/sql/opt/optbuilder/builder.go:286: Build()
-- pkg/sql/plan_opt.go:855: buildExecMemo()
-- pkg/sql/plan_opt.go:261: makeOptimizerPlan()
-- pkg/sql/conn_executor_exec.go:3199: makeExecPlan()
-- pkg/sql/conn_executor_exec.go:2775: dispatchToExecutionEngine()
-- pkg/sql/conn_executor_exec.go:1067: execStmtInOpenState()
-- pkg/sql/conn_executor_exec.go:175: func2()
-- pkg/sql/conn_executor_exec.go:4394: execWithProfiling()
-- pkg/sql/conn_executor_exec.go:174: execStmt()
-- pkg/sql/conn_executor.go:2374: func1()
-- pkg/sql/conn_executor.go:2379: execCmd()
-- pkg/sql/conn_executor.go:2287: run()
-- pkg/sql/internal.go:258: func2()
-- src/runtime/asm_arm64.s:1223: goexit()
```
Jira issue: CRDB-55454
Contributor guide
Research direction
Start by reproducing the SQL example, then read pkg/sql/opt/optbuilder/partial_index.go at buildPartialIndexPredicate() and addPartialIndexPredicatesForTable(), using the stack trace to follow the partial-index path. Done means creating the partial index with a predicate referencing the SQL UDF no longer produces the reported internal error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 50/100