No matching signature for function
A function was called with arguments of the wrong type or the wrong number. BigQuery lists the types it got and every signature it tried, with the reason each one failed. Cast the argument, or fix the argument count.
Practice this in a free lesson on learn.querylab.io: Read the error and fix the query.
No matching signature for function SUBSTR
Argument types: INT64, INT64, INT64
Signature: SUBSTR(STRING, INT64, [INT64])
Argument 1: Unable to coerce type INT64 to expected type STRING
Signature: SUBSTR(BYTES, INT64, [INT64])
Argument 1: Unable to coerce type INT64 to expected type BYTES at [2:8]
What BigQuery means
The function exists, but not for the arguments you gave it. BigQuery tried each form of the function (each signature) and none fit. The message shows all of them.
Read it from the top:
Argument types:is what you passed. These are the types of the values, so a column of numbers isINT64even if you think of it as an ID or a code.- Each
Signature:line is one form the function accepts. Square brackets, as in[INT64], mark an optional argument. - The line under a signature says why it was rejected.
Argument 1: Unable to coerce type INT64 to expected type STRINGmeans the first argument has the wrong type.Signature requires at least 3 arguments, found 2 argumentsmeans there are too few arguments. at [2:8]is the line and column of the start of the function call.
SUBSTR has two signatures, one for text and one for bytes. An INT64 fits neither, so there are two failures to read. The fix is the same for both: pass a STRING.
BigQuery does not convert a number to text for you in many functions. See CAST and conversion. The error is raised when the query compiles, so a dry run reports it without reading data.
The smallest query that reproduces it
WITH orders AS (SELECT 12345 AS order_id)
SELECT SUBSTR(order_id, 1, 2) AS prefix
FROM orders
The fixes
Convert the argument.
WITH orders AS (SELECT 12345 AS order_id)
SELECT SUBSTR(CAST(order_id AS STRING), 1, 2) AS prefix
FROM orders
The prefix is 12. Use SAFE_CAST when the value comes from text that might not convert, because it returns NULL instead of stopping the query. See SAFE functions.
Convert the column before an aggregate. SUM takes numbers. A STRING column of amounts fails with a longer message, which starts No matching signature for aggregate function SUM:
WITH payments AS (SELECT '10' AS amount UNION ALL SELECT '15' UNION ALL SELECT 'n/a')
SELECT SUM(amount) AS total
FROM payments
No matching signature for aggregate function SUM
Argument types: STRING
Signature: SUM(INT64)
Argument 1: Unable to coerce type STRING to expected type INT64
Signature: SUM(FLOAT64)
Argument 1: Unable to coerce type STRING to expected type FLOAT64
Signature: SUM(NUMERIC)
Argument 1: Unable to coerce type STRING to expected type NUMERIC
Signature: SUM(BIGNUMERIC)
Argument 1: Unable to coerce type STRING to expected type BIGNUMERIC
Signature: SUM(INTERVAL)
Argument 1: Unable to coerce type STRING to expected type INTERVAL at [2:8]
WITH payments AS (SELECT '10' AS amount UNION ALL SELECT '15' UNION ALL SELECT 'n/a')
SELECT SUM(SAFE_CAST(amount AS INT64)) AS total
FROM payments
The total is 25. The row with n/a becomes NULL and SUM skips it. That also hides the bad row, so look for the rows that did not convert before you trust the sum.
Fix the number of arguments. The reason line then names a count:
SELECT DATE_DIFF(DATE '2024-01-02', DATE '2024-01-01')
No matching signature for function DATE_DIFF
Argument types: DATE, DATE
Signature: DATE_DIFF(DATE, DATE, DATE_TIME_PART)
Signature requires at least 3 arguments, found 2 arguments
Signature: DATE_DIFF(DATETIME, DATETIME, DATE_TIME_PART)
Signature requires at least 3 arguments, found 2 arguments
Signature: DATE_DIFF(TIMESTAMP, TIMESTAMP, DATE_TIME_PART)
Signature requires at least 3 arguments, found 2 arguments at [1:8]
Add the date part: DATE_DIFF(DATE '2024-01-02', DATE '2024-01-01', DAY) returns 1. With too many arguments the line reads Signature accepts at most 1 argument, found 2 arguments, which is what LOWER('A', 'B') prints.
Make the argument types agree. Some functions need all their arguments to be one type. DATE_DIFF takes two dates, two datetimes or two timestamps, and a DATE and a TIMESTAMP do not meet:
SELECT DATE_DIFF(DATE '2024-01-02', TIMESTAMP '2024-01-01', DAY)
No matching signature for function DATE_DIFF
Argument types: DATE, TIMESTAMP, DATE_TIME_PART
Signature: DATE_DIFF(DATE, DATE, DATE_TIME_PART)
Argument 2: Unable to coerce type TIMESTAMP to expected type DATE
Signature: DATE_DIFF(DATETIME, DATETIME, DATE_TIME_PART)
Argument 2: Unable to coerce type TIMESTAMP to expected type DATETIME
Signature: DATE_DIFF(TIMESTAMP, TIMESTAMP, DATE_TIME_PART)
Argument 1: Unable to coerce type DATE to expected type TIMESTAMP at [1:8]
Convert one side: DATE_DIFF(DATE '2024-01-02', DATE(TIMESTAMP '2024-01-01'), DAY) returns 1.
The variants of the message
Each message was produced by BigQuery.
Mixed types in COALESCE, IFNULL, GREATEST and IF. The function needs one common type, and there is none:
No matching signature for function COALESCE
Argument types: INT64, STRING
Signature: COALESCE([T1, ...])
Unable to find common supertype for templated argument <T1>
Input types for <T1>: {INT64, STRING} at [1:8]
SELECT COALESCE(1, 'a') gives this. COALESCE(CAST(1 AS STRING), 'a') returns 1.
Window functions. SELECT NTILE('a') OVER (ORDER BY 1) FROM UNNEST([1]) prints No matching signature for analytic function NTILE, with the same lines under it. With no arguments the first line ends with no arguments: No matching signature for analytic function LAG with no arguments. An aggregate used as a window function, such as SUM(x) OVER (), says aggregate function.
A condition that is not BOOL. SELECT IF(1, 'yes', 'no') gives Argument 1: Unable to coerce type INT64 to expected type BOOL. BigQuery has no truthy numbers.
Not an array. SELECT ARRAY_LENGTH('abc') gives Argument 1: expected array type but found STRING.
A function you defined. A TEMP FUNCTION gets the same message, with the signature you declared:
CREATE TEMP FUNCTION shout(x STRING) AS (UPPER(x));
SELECT shout(1)
No matching signature for function shout
Argument types: INT64
Signature: shout(STRING)
Argument 1: Unable to coerce type INT64 to expected type STRING at [2:8]
Queries that look wrong but run
CONCAT takes numbers. SELECT CONCAT('order-', 1) returns order-1. CONCAT is one of the functions that accepts a number where it wants text. Most do not: UPPER(1), LENGTH(123) and SUBSTR(12345, 1, 2) all fail.
FORMAT_TIMESTAMP takes a DATE. SELECT FORMAT_TIMESTAMP('%Y', DATE '2024-01-01') returns 2024. FORMAT_TIMESTAMP has a form that takes a DATETIME, and a DATE coerces to a DATETIME. In the DATE_DIFF message above, a DATE was not accepted where a TIMESTAMP was expected, and a TIMESTAMP was not accepted where a DATE or DATETIME was expected.
SAFE. does not help. SELECT SAFE.UPPER(1) fails with the same message as SELECT UPPER(1). BigQuery’s documentation says the SAFE. prefix only prevents errors caused by the values of the inputs. This error is about types, and it is found when the query compiles, before any row is read.
Different errors that look like this one
| Message | Cause |
|---|---|
Function not found: UPPER_TYPO at [1:8] | The name does not exist. Check the spelling. |
MAX is not defined for arguments of type ARRAY<INT64> at [1:8] | MAX does not accept an array. UNNEST the array and take the MAX of its elements. |
DATE_ADD does not support the HOUR date part when the argument is DATE type at [1:8] | The date part does not fit the type. DATE_ADD(DATETIME '2024-01-01 10:00:00', INTERVAL 1 HOUR) runs, and so does DATETIME_ADD. |
No matching signature for operator = for argument types: INT64, STRING | This one is an operator. See No matching signature for operator. |
In Querylab.io
The editor does not check the argument types of a function, so nothing is marked as you type. BigQuery’s dry run reports the error, and Querylab.io shows the full message in the status bar when you pause typing. The marker is one character wide, at the start of the function call, the position in the message. There is no one-click fix for this error. Add the CAST or the missing argument by hand.