Partager via

UNRESOLVED_ROUTINE error class

  • Article

SQLSTATE: 42883

Cannot resolve function <routineName> on search path <searchPath>.

Parameters

  • routineName: The name of the function which cannot be resolved.
  • searchPath: The ordered list of schemas that was searched if routineName was not schema qualified.

Explanation

Persisted functions consist of a three name parts: <catalog>.<schema>.<relation>. If you do not specify all three parts of the name, it is implicitly completed using the current catalog or the current schema. This is similar to the way how the working directory of your filesystem influences which files you can see, unless you fully specify the path.

Temporary functions only exist within the session or query and must never be qualified.

The most common reason for not finding a function are:

  • The function doesn’t exist.
  • The function name is misspelled.
  • The user defined function is located in a different schema.
  • The user defined function is not located in the current schema.
  • You can’t view the user-defined function because you don’t have access.
  • The built-in function you are trying to invoke is not available on this release of Azure Databricks.

Mitigation

Mitigate errors by reviewing the following.

  • Did you spell the function name incorrectly?

    Use SHOW FUNCTIONS IN <schema> to verify the correct function name.

  • Is the function is a different schema?

    If the function is located on a catalog in Unity Catalog, run the following query:

    SELECT routine_schema FROM information_schema.routines WHERE routine_name = '<routinename>'

    This lists the schema within the current catalog where the function is located.

    If the function is located outside Unity Catalog, use SHOW SCHEMAS to find candidate schemas. Use SHOW FUNCTIONS IN <schema> to probe for the function.

  • Did you not fully qualify the name, and the result of VALUES current_schema() does not match the qualified name of the function?

    Qualify functionName with its schema and catalog, or run USE SCHEMA to set the implicit schema.

  • Did you reference a temporary function, but it was in a previous, expired, or different session?

    Recreate the temporary function using CREATE TEMPORARY FUNCTION <routineName> …, or switch to using a persisted function.

  • Do you want to issue a DDL statement, such as DROP FUNCTION just in case the object exists?

    Issue the statement using the IF EXISTS clause, such as: DROP FUNCTION <routineName> IF EXISTS.

  • Do you know the function exists, but you cannot see it in SHOW FUNCTIONS?

    Contact your administrator to get access to the function. You may also need access to the schema and catalog.

For more information on how to resolve the error, see Function resolution.

Examples

> CREATE SCHEMA IF NOT EXISTS myschema;
> CREATE OR REPLACE FUNCTION myschema.myfunc() RETURNS INT RETURN 5;

--
-- The function name has been misspelled
--
> SELECT myschema.myfun();
  [UNRESOLVED_ROUTINE] Cannot resolve function `myschema`.`myfun` on search path [`system`.`builtin`, `system`.`session`, `spark_catalog`.`default`].; line 1 pos 7

-- Use SHOW FUNCTIONS to find the correct nme
> SHOW USER FUNCTIONS IN myschema;
  spark_catalog.myschema.myfunc

-- Correct the spelling
> SELECT myschema.myfunc();
 5

--
-- The qualifier has been misspelled
--
> CREATE SCHEMA IF NOT EXISTS wrongschema;
> SELECT wrongschema.myfunc;
 [UNRESOLVED_ROUTINE] Cannot resolve function `wrongschema`.`myfunc` on search path [`system`.`builtin`, `system`.`session`, `spark_catalog`.`default`].; line 1 pos 7

-- Find candidate schemas
> SHOW SCHEMAS;
  myschema
  wrongschema

-- Verify the function exists in the candidate schema
> SHOW USER FUNCTIONS IN myschema;
  spark_catalog.myschema.myfunc

> SELECT myschema.myfunc();
 5

--
-- Change current schema to find an unqualified function
--
> SELECT myfunc();
  [UNRESOLVED_ROUTINE] Cannot resolve function `myfunc` on search path [`system`.`builtin`, `system`.`session`, `spark_catalog`.`default`].; line 1 pos 7

> USE SCHEMA myschema;

SELECT myfunc();
5