# 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. Source: https://docs.querylab.io/bigquery-errors/no-matching-signature-for-function/ Updated: 2026-11-04 ```text 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 is `INT64` even 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 STRING` means the first argument has the wrong type. `Signature requires at least 3 arguments, found 2 arguments` means 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](https://docs.querylab.io/bigquery/casting-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 ```sql WITH orders AS (SELECT 12345 AS order_id) SELECT SUBSTR(order_id, 1, 2) AS prefix FROM orders ``` ## The fixes **Convert the argument.** ```sql 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](https://docs.querylab.io/bigquery/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`: ```sql WITH payments AS (SELECT '10' AS amount UNION ALL SELECT '15' UNION ALL SELECT 'n/a') SELECT SUM(amount) AS total FROM payments ``` ```text 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] ``` ```sql 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: ```sql SELECT DATE_DIFF(DATE '2024-01-02', DATE '2024-01-01') ``` ```text 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: ```sql SELECT DATE_DIFF(DATE '2024-01-02', TIMESTAMP '2024-01-01', DAY) ``` ```text 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: ```text No matching signature for function COALESCE Argument types: INT64, STRING Signature: COALESCE([T1, ...]) Unable to find common supertype for templated argument Input types for : {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: ```sql CREATE TEMP FUNCTION shout(x STRING) AS (UPPER(x)); SELECT shout(1) ``` ```text 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 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](https://docs.querylab.io/bigquery-errors/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. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Conversion rules](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules) - [Conversion functions, CAST and SAFE_CAST](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions)