Skip to content

sql: support resolving a user-defined function from a view query #146123

New issue
New issue
Closed
#148616
Closed
sql: support resolving a user-defined function from a view query#146123
#148616
Assignees
rafiss
Labels
A-sql-routineUDFs and Stored ProceduresC-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)GA-blockerT-sql-queriesSQL Queries Teambranch-release-25.3target-release-25.3.0target-release-25.4.0
@rafiss

Description

@rafiss
rafiss
opened on May 5, 2025 · edited by DrewKimball
Collaborator

User-defined functions cannot be resolved in view queries. Example:

root@localhost:26257/defaultdb> CREATE FUNCTION f() RETURNS BOOL LANGUAGE SQL AS $$ SELECT true; $$;
CREATE FUNCTION

root@localhost:26257/defaultdb> create table t (a int);
CREATE TABLE

root@localhost:26257/defaultdb> create view v as select a, f() from t;
ERROR: unknown function: f()
SQLSTATE: 42883
HINT: There is probably a typo in function name. Or the intention was to use a user-defined function in the view query, which is currently not supported.

This issue tracks support for that.

Epic CRDB-49018

Jira issue: CRDB-50398

Activity

rafiss
added
A-sql-routineUDFs and Stored Procedures
C-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
T-sql-foundationsSQL Foundations Team (formerly SQL Schema + SQL Sessions)
on May 5, 2025
rafiss

rafiss commented on May 8, 2025

@rafiss
rafiss
on May 8, 2025 · edited by rafiss
CollaboratorAuthor

Notes:

The "unknown function" error is encountered at this call to GetBuiltinFuncDefinitionOrFail:

cockroach/pkg/sql/sem/tree/function_name.go

Lines 94 to 103 in 489d057

if resolver == nil {
// If a resolver is not provided, just try to fetch a builtin function.
fn, err := t.ToRoutineName()
if err != nil {
return nil, err
}
fd, err := GetBuiltinFuncDefinitionOrFail(fn, path)
if err != nil {
return nil, err
}

To avoid the error, we need to pass in a non-nil resolver to (*ResolvableFunctionReference).Resolve when this function is called by the optimizer code:

cockroach/pkg/sql/opt/optbuilder/scope.go

Line 1118 in f9efd1f

def, err := t.Func.Resolve(s.builder.ctx, semaCtx.SearchPath, semaCtx.FunctionResolver)

The resolver is nil at that point because semaCtx.FunctionResolver is not set. After digging through some history, I found that afe78f9 modified the code so that we intentionally do not set the FunctionResolver.

cockroach/pkg/sql/opt/optbuilder/create_view.go

Lines 19 to 20 in be2cc31

preFuncResolver := b.semaCtx.FunctionResolver
b.semaCtx.FunctionResolver = nil

Using a non-nil resolver is blocked by a different task: we'd need to update the CREATE VIEW code so that we rewrite function references to refer by OID rather than by name. This rewrite functionality is not strictly needed, but can be done once the rest of this issue is completed. Previously, we added support for referencing functions by OID in #83231. This is the area of the code where we'd do the rewrite:

cockroach/pkg/sql/create_view.go

Lines 426 to 439 in 2932e59

if sc != nil {
sequenceReplacedQuery, err := replaceSeqNamesWithIDs(ctx, sc, viewQuery, false /* multiStmt */)
if err != nil {
return tabledesc.Mutable{}, err
}
desc.ViewQuery = sequenceReplacedQuery
}
typeReplacedQuery, err := serializeUserDefinedTypes(ctx, semaCtx, desc.ViewQuery,
false /* multiStmt */, "view queries")
if err != nil {
return tabledesc.Mutable{}, err
}
desc.ViewQuery = typeReplacedQuery

That work is captured under #87699, but we can leave this issue open since this one is specific to views, but that one is more general.

While resolving this issue, we should add tests to make sure the view definition is serialized correctly, and also verify that dropping a function that is referenced by a view is blocked.

We also need to update this area of the code to add the dependency on the function to desc.DependsOnFunctions:

cockroach/pkg/sql/create_view.go

Lines 283 to 296 in 2932e59

// Collect all the tables/views this view depends on.
orderedDependsOn := catalog.DescriptorIDSet{}
for backrefID := range n.planDeps {
orderedDependsOn.Add(backrefID)
}
desc.DependsOn = append(desc.DependsOn, orderedDependsOn.Ordered()...)
// Collect all types this view depends on.
orderedTypeDeps := catalog.DescriptorIDSet{}
for backrefID := range n.typeDeps {
orderedTypeDeps.Add(backrefID)
}
desc.DependsOnTypes = append(desc.DependsOnTypes, orderedTypeDeps.Ordered()...)
newDesc = &desc

In order to do that, the function dependencies need to be correctly set by the optimizer in this code:

cockroach/pkg/sql/opt_exec_factory.go

Line 1977 in 89d6c57

planDeps, typeDepSet, _, err := toPlanDependencies(deps, typeDeps, intsets.Fast{} /* funcDeps */)

The SQL Queries team is best equipped for that task.

dikshant

dikshant commented on May 9, 2025

@dikshant
dikshant
on May 9, 2025 · edited by dikshant

Speaking to @rafiss, his thoughts are:

i am realizing that the SQL Foundations part of this is only needed if we want to support renaming the function while it's referenced by a view. however, we don't even support that for functions referencing other functions:

root@localhost:26257/defaultdb> CREATE FUNCTION f() RETURNS BOOL LANGUAGE SQL AS $$ SELECT true; $$;;
CREATE FUNCTION

root@localhost:26257/defaultdb> CREATE FUNCTION g() RETURNS BOOL LANGUAGE SQL AS $$ SELECT f(); $$;;
CREATE FUNCTION

root@localhost:26257/defaultdb> alter function f rename to t;
ERROR: cannot rename function "f" because other functions ([defaultdb.public.g]) still depend on it
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/83233/v25.2

root@localhost:26257/defaultdb> drop function f;
ERROR: cannot drop function "f" because other objects ([defaultdb.public.g]) still depend on it
SQLSTATE: 2BP01

We could have that same behavior for views. in that case, all the work here is for Queries.

rafiss
mentioned this on May 9, 2025
craig
added a commit that references this issue on May 9, 2025

Merge #144442 #146239 #146461 #146468

96dfdb4
rafiss
mentioned this on May 9, 2025
dikshant

dikshant commented on May 27, 2025

@dikshant
dikshant
on May 27, 2025

@rytaft @michae2 I am moving this issue to SQL Queries based on Rafi's latest comment. We can triage it in our next triage.

dikshant
added
T-sql-queriesSQL Queries Team
and removed
T-sql-foundationsSQL Foundations Team (formerly SQL Schema + SQL Sessions)
on May 27, 2025
github-project-automation
added this to SQL Querieson May 27, 2025
github-project-automation
moved this to Triage in SQL Querieson May 27, 2025
DrewKimball
moved this from Triage to 25.3 Release in SQL Querieson Jun 3, 2025
DrewKimball
added a commit that references this issue on Jun 20, 2025

sql,schemachanger: add remaining handling for routine references in v…

702d2a3

37 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

A-sql-routineUDFs and Stored ProceduresC-enhancementSolution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)GA-blockerT-sql-queriesSQL Queries Teambranch-release-25.3target-release-25.3.0target-release-25.4.0

Type

No type

Projects

SQL Queries

Status

Done

Milestone

No milestone

Relationships

None yet

    Development

    Participants

    @rafiss@rytaft@dikshant@michae2@DrewKimball

    Issue actions
      sql: support resolving a user-defined function from a view query · Issue #146123 · cockroachdb/cockroach