udf: name resolution broken when switching databases

[rafiss]

Describe the problem

If you resolve a UDF, then USE a different database, you can still resolve the old UDF by name even if it should no longer be visible.

To Reproduce

root@localhost:26257/defaultdb> create database d;
CREATE DATABASE

root@localhost:26257/defaultdb> select f(1);
ERROR: unknown function: f(): function undefined
SQLSTATE: 42883

root@localhost:26257/defaultdb> use d;
SET

root@localhost:26257/d> CREATE FUNCTION f(i INT) RETURNS INT LANGUAGE SQL AS 'SELECT i';
CREATE FUNCTION

root@localhost:26257/d> select f(1);
  f
-----
  1
(1 row)

root@localhost:26257/d> use defaultdb;
SET

root@localhost:26257/defaultdb> select f(1);
  f
-----
  1
(1 row)

Expected behavior
After switching databases, the name shouldn't be resolvable.

Environment:

• CockroachDB version v22.2.0

Jira issue: CRDB-22274

[ajwerner]

I think what's going on is the this code does not check on the UDFs.

cockroach/pkg/sql/opt/metadata.go

Lines 284 to 293 in 48bf2ff

	// CheckDependencies resolves (again) each data source on which this metadata
	// depends, in order to check that all data source names resolve to the same
	// objects, and that the user still has sufficient privileges to access the
	// objects. If the dependencies are no longer up-to-date, then CheckDependencies
	// returns false.
	//
	// This function cannot swallow errors and return only a boolean, as it may
	// perform KV operations on behalf of the transaction associated with the
	// provided catalog, and those errors are required to be propagated.
	func (md *Metadata) CheckDependencies(

[chengxiong-ruan]

looks like same thing as #93082

[mgartner]

Thanks for tracking this down. Working on a fix now.