Function not found: X
BigQuery has no function with that name where it looked. The cause is a typo, a function from another SQL dialect or legacy SQL, a UDF written without its dataset or in the wrong case, or a TEMP function from another query.
Practice this in a free lesson on learn.querylab.io: A temporary function.
Function not found: UPPERR; Did you mean upper? at [1:8]
What BigQuery means
The name before the parenthesis is not a built-in function, and it is not a function BigQuery can find in the dataset you wrote in front of it. The error is raised when the query is compiled, so a dry run shows it and it costs nothing.
The position at the end is the line and column of the name. When BigQuery has a close guess it adds Did you mean. That guess is about spelling only. Read it as a hint and check that the function does what you meant.
The smallest query that reproduces it
SELECT UPPERR('a')
Function not found: UPPERR; Did you mean upper? at [1:8]
SELECT UPPER('a')
The message variants
| Query | Message |
|---|---|
SELECT UPPERR('a') | Function not found: UPPERR; Did you mean upper? at [1:8] |
SELECT NVL(NULL, 1) | Function not found: NVL at [1:8] |
SELECT SAFE.UPPERR('a') | Function not found: UPPERR; Did you mean SAFE.upper? at [1:8] |
SELECT SAFE.NOSUCH('a') | Function not found: NOSUCH at [1:8] |
SELECT NET.NOSUCH('a') | Function not found: NET.NOSUCH at [1:8] |
SELECT mydataset.myfn(1) where MyFn exists | Function not found: mydataset.myfn at [1:8] |
SELECT `my-project.mydataset.nofn`(1) | Function not found: `my-project.mydataset.nofn` at [1:8] |
SELECT `other-project`.some_ds.fn(1) | Function not found: `other-project`.some_ds.fn at [1:8] |
CREATE FUNCTION mydataset.f(x INT64) AS (nofn(x)) | Function not found: nofn at [1:42] |
SELECT * FROM mydataset.NoTvf(1) | Table-valued function not found: mydataset.NoTvf at [1:15] |
SELECT * FROM UNNEST([1]) AS x, NOSUCHFN(1) AS y | Table-valued function not found: NOSUCHFN at [1:33] |
The last two rows are a function called where a table is expected, in the FROM clause. The wording differs, and the causes are the same. Project and dataset names in the table are made up. The column in [1:8] is where the name starts, so it moves with the length of your query text.
A name with a mistyped dataset, or with a project that does not exist, gives the same message as a missing function. BigQuery does not say which part of the path is wrong.
Causes and fixes
A typo
Compare the name with the Did you mean text. ARRAYAGG gives Function not found: ARRAYAGG; Did you mean array_agg? at [1:8], and STRING_AGG2 gives Did you mean string_agg?. Built-in names are not case sensitive, so date_sub(...) and DATE_SUB(...) both work.
A function from another SQL dialect
These names are not BigQuery functions. Each gave Function not found with the message in the second column. The third column is a BigQuery form that runs.
| Dialect name | Message after Function not found: | In BigQuery |
|---|---|---|
DATEADD | DATEADD; Did you mean date_add? | DATE_ADD(d, INTERVAL 1 DAY) |
DATEDIFF | DATEDIFF; Did you mean date_diff? | DATE_DIFF(a, b, DAY) |
ISNULL | ISNULL; Did you mean ifnull? | IFNULL(x, y) |
NVL | NVL | IFNULL(x, y) or COALESCE(x, y, z) |
IFF | IFF; Did you mean if? | IF(cond, a, b) |
GETDATE | GETDATE | CURRENT_TIMESTAMP() |
NOW | NOW; Did you mean pow? | CURRENT_TIMESTAMP() |
LEN | LEN; Did you mean ln? | LENGTH(s) |
CHARINDEX | CHARINDEX | STRPOS(s, sub) |
TO_CHAR | TO_CHAR | FORMAT_DATE('%Y-%m', d) or FORMAT('%05d', n) |
TO_DATE | TO_DATE | PARSE_DATE(format, s) |
LISTAGG | LISTAGG | STRING_AGG(x, ', ' ORDER BY x) |
GROUP_CONCAT | GROUP_CONCAT | STRING_AGG(x) |
CONCAT_WS | CONCAT_WS | ARRAY_TO_STRING([a, b, c], '-') |
STRFTIME | STRFTIME | FORMAT_DATE('%Y', d) |
Two guesses point the wrong way: LEN gets ln and NOW gets pow, which are different functions. DATEADD gets date_add, whose arguments are in another order and take an INTERVAL.
The replacements above all ran, for example:
SELECT IFNULL(NULL, 1) AS a, LENGTH('abc') AS b, STRPOS('abc', 'b') AS c,
DATE_ADD(DATE '2020-01-31', INTERVAL 1 DAY) AS d,
ARRAY_TO_STRING(['a', 'b', 'c'], '-') AS e
CONCAT_WS has no direct form. ARRAY_TO_STRING skips NULL elements, so ARRAY_TO_STRING(['a', NULL, 'b'], ',') gives a,b.
A function from legacy SQL
Legacy SQL functions do not exist in GoogleSQL, and the message has no suggestion. All of these gave Function not found: NAME at [1:8]: TIMESTAMP_TO_MSEC, MSEC_TO_TIMESTAMP, UTC_USEC_TO_DAY, TABLE_DATE_RANGE, GROUP_CONCAT, NTH, EXACT_COUNT_DISTINCT and INTEGER.
| Legacy SQL | GoogleSQL |
|---|---|
TIMESTAMP_TO_MSEC(t) | UNIX_MILLIS(t) |
MSEC_TO_TIMESTAMP(n) | TIMESTAMP_MILLIS(n) |
UTC_USEC_TO_DAY(u) | TIMESTAMP_TRUNC(t, DAY) |
EXACT_COUNT_DISTINCT(x) | COUNT(DISTINCT x) |
GROUP_CONCAT(x) | STRING_AGG(x) |
NTH(1, x) | an array: ARRAY_AGG(x ORDER BY ...)[OFFSET(0)] |
INTEGER(x), STRING(x) | CAST(x AS INT64), CAST(x AS STRING) |
STRING(1) is a different case. STRING exists in GoogleSQL, so the error is No matching signature for function STRING. See No matching signature for function.
If the whole query is legacy SQL, see Legacy SQL.
A UDF without its dataset
A persistent function lives in a dataset, and the call must name it. Created with CREATE FUNCTION mydataset.MyFn(x INT64) AS (x + 1):
SELECT MyFn(1)
Function not found: MyFn at [1:8]
SELECT mydataset.MyFn(1)
The project can be added in front, as `my-project`.mydataset.MyFn(1) or `my-project.mydataset.MyFn`(1). Both compile. A default dataset on the job does not help: bq query --dataset_id=mydataset 'SELECT MyFn(1)' still gave Function not found: MyFn at [1:8], in a dry run and in a real run.
A UDF in the wrong case
Built-in names ignore case. A persistent UDF name does not:
SELECT mydataset.MyFn(1)
SELECT mydataset.myfn(1)
SELECT mydataset.MYFN(1)
The first runs and returns 2. The other two give Function not found: mydataset.myfn at [1:8] and Function not found: mydataset.MYFN at [1:8]. A real run gives the same text as a dry run.
A UDF that does not exist yet
A function body is compiled when the function is created. f_a calls f_b, so f_b must exist first:
CREATE OR REPLACE FUNCTION mydataset.f_a(x INT64) AS (mydataset.f_b(x) + 1)
Function not found: mydataset.f_b at [1:55]
Create f_b, then create f_a. A call to f_a(5) then returns 11.
A TEMP function defined in another query
A TEMP function lives only for the query or script that creates it. It is gone for the next query you run:
CREATE TEMP FUNCTION TmpFn(x INT64) AS (x + 1);
SELECT TmpFn(1);
This works. Run it as two separate queries and the second one gives:
Function not found: TmpFn at [1:8]
Put the CREATE TEMP FUNCTION and every query that calls it in one script, or create a persistent function in a dataset.
The CREATE TEMP FUNCTION must come before the call. In a script that calls TmpFn2(1) first and creates it afterwards, the message has the script prefix:
Query error: Function not found: TmpFn2 at [1:8]
A TEMP function can call another TEMP function only if the other one is created first. A run that creates a (which calls b) before b gave:
Function not found: b; failed to parse CREATE [TEMP] FUNCTION statement
TEMP function names ignore case. After CREATE TEMP FUNCTION AddOne(...), both AddOne(1) and ADDONE(2) run. Google’s lexical rules list user-defined function names as case sensitive. In our probes that held for persistent functions and not for TEMP ones.
Different errors that look similar
| Query | Message |
|---|---|
SELECT * FROM mydataset.MyFn | Not found: Table my-project:mydataset.MyFn was not found in location US |
SELECT MyFn FROM mydataset.t | Unrecognized name: MyFn at [1:8] |
SELECT date_add(CURRENT_DATE(), 1) | Expected INTERVAL expression at [1:33] |
The first is a function name in the table position, which BigQuery reads as a table. The second has no parentheses, so the name is read as a column. See Unrecognized name. In the third the function exists and the second argument is wrong.
In Querylab.io
The editor does not mark an unknown function name as you type. BigQuery’s dry run reports the error when you pause typing or run the query, and the editor puts the red mark on the function name.
When BigQuery gives a Did you mean name, the error bar and the lightbulb offer a fix that replaces the name with it. Check it first. For LEN it would write ln. When BigQuery gives no guess, as for NVL and GETDATE, there is no fix button. There is no button for a SAFE. call either: SAFE.UPPERR gets Did you mean SAFE.upper? and no replacement.