sql: partial predicate evaluation fails for newly added columns

[ajwerner]

Describe the problem

Consider the case where you add a column and a partial index in the same transaction and the partial index predicate references that column. When the column and index are in DELETE_ONLY or DELETE_AND_WRITE_ONLY we need to evaluate the predicate. Unfortunately, the predicate evaluation logic doesn't work correctly in this state.

To Reproduce

create table t (i int primary key);
set cluster setting jobs.debug.pausepoints = 'schemachanger.before.exec';
begin;
    alter table t add column z timestamp;
    create index idx2 on t(z) where z IS NOT NULL;
commit;
select * from t;

This will result in the following error:

ERROR: internal error: unexpected error during partial index predicate type resolution: column "z" does not exist
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/partial_index.go:214: func1()
GOROOT/src/runtime/panic.go:1038: gopanic()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:1657: wrapColTupleStarPanic()
GOROOT/src/runtime/panic.go:1038: gopanic()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/util.go:126: expandStar()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:986: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:998: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:821: WalkExpr()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:421: walkExprTree()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:460: resolveType()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/util.go:68: expandStar()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:986: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:1008: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:1009: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:998: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:821: WalkExpr()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:448: Walk()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:824: WalkExpr()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:421: walkExprTree()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:479: resolveAndRequireType()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/partial_index.go:217: resolvePartialIndexPredicate()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/partial_index.go:128: buildPartialIndexPredicate()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/partial_index.go:104: addPartialIndexPredicatesForTable()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:598: buildScan()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:115: buildDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:60: buildDataSource()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1155: buildFromTablesRightDeep()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1132: buildFromTables()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1059: buildFrom()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:980: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:928: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:901: func1()

HINT: You have encountered an unexpected error.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.

Additional context

Until the column backfill and index backfill concludes, any query against the table will fail.

Jira issue: CRDB-14943

[vy-ton]

Schema plans to disallow this problematic combination of schema changes until this issue can be resolved. cc @devadvocado

BEGIN;
ALTER TABLE … ADD COLUMN n … DEFAULT …
CREATE INDEX … ON TABLE … WHERE (n …);
COMMIT;

[mgartner]

See https://github.com/cockroachdb/cockroach/pull/79691

[michae2]

Based on #96337 it looks like this might also happen with user-defined types used in partial indexes, not just columns.

[mgartner]

Very interesting! We need to sort out what is guaranteed (or not) in cases like this. Do we expect the optimizer to handle cases where a deletable index (or really any index, though I believe this is currently only possible with a deletable index) references a delete-only column? Or is this an invalid state? cc @ajwerner

[mgartner]

See https://github.com/cockroachdb/cockroach/issues/111619#issuecomment-1909011072.

[mgartner]

I think #119254 was aiming to fix this. @cockroachdb/sql-foundations is there any plan to finish that PR?

[fqazi]

@mgartner That PR was aimed at fixing: https://github.com/cockroachdb/cockroach/issues/111608, https://github.com/cockroachdb/cockroach/issues/111619, and https://github.com/cockroachdb/cockroach/issues/118314, which is in the declarative schema changer. @rimadeodhar Is going to be picking those up. We can discuss this one in our triage meeting, and at the very least block it in the legacy world because of broken behavior.

[rafiss]

this actually is blocked now in the legacy schema changer, so i will close this out

root@localhost:26257/defaultdb> create table t (i int primary key);
                             -> ;
CREATE TABLE

Time: 2ms total (execution 2ms / network 0ms)

OK

Time: 0ms total (execution 0ms / network 0ms)

root@localhost:26257/defaultdb> set cluster setting jobs.debug.pausepoints = 'schemachanger.before.exec';
                             ->
SET CLUSTER SETTING

Time: 2ms total (execution 2ms / network 0ms)

root@localhost:26257/defaultdb> begin;
                             ->     alter table t add column z timestamp;
                             ->     create index idx2 on t(z) where z IS NOT NULL;
                             -> commit;
BEGIN

Time: 0ms total (execution 0ms / network 0ms)

ALTER TABLE

Time: 2ms total (execution 2ms / network 0ms)

ERROR: cannot create partial index on column "z" (2) which is not public
SQLSTATE: 0A000
ROLLBACK