cockroachdb / cockroachdb/cockroach

sql: internal error when creating partial index with predicate that references a UDF

Open
#155,488 10 comments 0 reactions 0 assignees View on GitHub
A-partial-indexes A-schema-changes C-bug docs-known-limitation E-quick-win O-qa T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.