sql: ALTER DROP COLUMN CASCADE gets confused by UDFs referencing same table multiple times #97546
Description
Found while poking at #97544, but not specific to stars at all.
Describe the problem / To reproduce
The following SQL fails:
CREATE TABLE t_twocol (a INT, b INT);
CREATE FUNCTION f1() RETURNS t_twocol AS 'SELECT * FROM t_twocol' LANGUAGE SQL;
CREATE FUNCTION f2() RETURNS t_twocol AS 'SELECT * FROM t_twocol' LANGUAGE SQL;
CREATE FUNCTION f3() RETURNS INT AS 'SELECT a FROM t_twocol' LANGUAGE SQL;
SET sql_safe_updates = false;
SET use_declarative_schema_changer = 'off';
ALTER TABLE t_twocol DROP COLUMN b CASCADE;Failure in ALTER:
(XX000) internal error: relation "t_twocol" (106): column ID 2 found in depended-on-by references, no such column in this relation
validate.go:659: in ValidateSelf()
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/catalog/tabledesc/validate.go:659: ValidateSelf()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:80: func1()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:198: validateDescriptorsAtLevel()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:76: Validate()
github.com/cockroachdb/cockroach/pkg/sql/catalog/internal/validate/validate.go:41: Self()
github.com/cockroachdb/cockroach/pkg/sql/catalog/descs/collection.go:286: WriteDescToBatch()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1773: func1()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:2460: func1()
github.com/cockroachdb/cockroach/pkg/sql/internal.go:1401: func1()
github.com/cockroachdb/cockroach/pkg/sql/internal.go:1489: func4()
github.com/cockroachdb/cockroach/pkg/kv/db.go:960: func1()
github.com/cockroachdb/cockroach/pkg/kv/txn.go:928: exec()
github.com/cockroachdb/cockroach/pkg/kv/db.go:959: runTxn()
github.com/cockroachdb/cockroach/pkg/kv/db.go:922: TxnWithAdmissionControl()
github.com/cockroachdb/cockroach/pkg/kv/db.go:897: Txn()
github.com/cockroachdb/cockroach/pkg/sql/internal.go:1477: txn()
github.com/cockroachdb/cockroach/pkg/sql/internal.go:1399: DescsTxn()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:2457: txn()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1344: done()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:1898: runStateMachineAndBackfill()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:791: exec()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:2604: func1()
github.com/cockroachdb/cockroach/pkg/sql/schema_changer.go:2768: Resume()
github.com/cockroachdb/cockroach/pkg/jobs/registry.go:1599: func2()
github.com/cockroachdb/cockroach/pkg/jobs/registry.go:1600: stepThroughStateMachine()
github.com/cockroachdb/cockroach/pkg/jobs/adopt.go:413: runJob()
github.com/cockroachdb/cockroach/pkg/jobs/adopt.go:333: func1()
github.com/cockroachdb/cockroach/pkg/util/stop/stopper.go:470: func2()
GOROOT/src/runtime/asm_amd64.s:1594: goexit()
The failure does not happen when using the declarative schema changer.
cc @mgartner @ajwerner @DrewKimball
Jira issue: CRDB-24763