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

[mgartner]

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

[blathers-crl]

Hi @mgartner, please add a branch-* label to identify the earliest affected branch for this C-bug

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

[dils2k]

@mgartner Hi! I was looking into this issue and would like to work on it. Is it okay if I take this on? 🙏🏻

I tried to reproduce the problem and it seems that a query with a UDF is normalized into a project:

SELECT k from t WHERE is_even(k);

project
├── columns: k:1!null
├── immutable
├── key: (1)
└── select
    ├── columns: k:1!null "?column?":6!null
    ├── immutable
    ├── key: (1)
    ├── fd: ()-->(6)
    ├── project
    │    ├── columns: "?column?":6!null k:1!null
    │    ├── immutable
    │    ├── key: (1)
    │    ├── fd: (1)-->(6)
    │    ├── scan t
    │    │    ├── columns: k:1!null
    │    │    └── key: (1)
    │    └── projections
    │         └── (k:1 % 2) = 0 [as="?column?":6, outer=(1), immutable]
    └── filters
         └── "?column?":6 [outer=(6), constraints=(/6: [/true - /true]; tight), fd=()-->(6)]

while a query without a UDF is normalized into a select:

SELECT k from t WHERE k % 2 = 0;

select
├── columns: k:1!null
├── immutable
├── key: (1)
├── scan t
│    ├── columns: k:1!null
│    └── key: (1)
└── filters
    └── (k:1 % 2) = 0 [outer=(1), immutable]

I was wondering if both of these queries should be normalized into the same select query and if it's a root of the problem. Or would it be better to just handle ProjectExpr in partial index construction (but at this point I am not sure if it can be done)?

[mgartner]

@dils2k great find! The Project in the first query is required to project-away the "?column?":6 column. I think the best solution is to handle the ProjectExpr in partial predicate construction, as you describe. Before the switch statement we can recursively traverse into ProjectExprs until we find something other than a ProjectExpr. Then the logic in the switch should work for this case.

[dils2k]

@mgartner I tried this approach but ran into a couple of issues. Specifically, after applying the changes discussed above, the following query produces a new error:

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)
);

SELECT * FROM t@t_a_idx WHERE is_even(k);

ERROR: index "t_a_idx" is a partial index that does not contain all the rows needed to execute this query
SQLSTATE: 42809

I found out that the error happens, because the index predicate implication fails.

Looking at the final optimized expression:

project
 ├── columns: bool:9!null
 ├── immutable
 ├── fd: ()-->(9)
 ├── select
 │    ├── columns: "?column?":8!null
 │    ├── immutable
 │    ├── fd: ()-->(8)
 │    ├── project
 │    │    ├── columns: "?column?":8!null
 │    │    ├── immutable
 │    │    ├── scan t
 │    │    │    ├── columns: k:1!null
 │    │    │    ├── partial index predicates
 │    │    │    │    └── t_a_idx: filters
 │    │    │    │         └── "?column?":6 [outer=(6), constraints=(/6: [/true - /true]; tight), fd=()-->(6)]
 │    │    │    ├── flags: force-index=t_a_idx
 │    │    │    └── key: (1)
 │    │    └── projections
 │    │         └── (k:1 % 2) = 0 [as="?column?":8, outer=(1), immutable]
 │    └── filters
 │         └── "?column?":8 [outer=(8), constraints=(/8: [/true - /true]; tight), fd=()-->(8)]
 └── projections
      └── true [as=bool:9]

it's clear that the predicate and filter expressions are different and probably that's why can't be implied.

"?column?":6 [outer=(6), constraints=(/6: [/true - /true]; tight), fd=()-->(6)]

vs

"?column?":8 [outer=(8), constraints=(/8: [/true - /true]; tight), fd=()-->(8)]

Optimizer (or InlineUDF specifically) produces expressions with different column IDs. I was thinking maybe it's because these two UDF expressions were not interned properly. But I'm not sure, and I'd like to clarify whether these two expressions are actually supposed to be different, or if this is indeed an optimizer issue and I should dive deeper.