Error messagesTypes

No matching signature for operator

BigQuery will not compare a number to a string, not even a string literal. The error prints the two types it found and the signature it wanted, which is all you need to fix it.

No matching signature for operator = for argument types: INT64, STRING
  Signature: T1 = T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {INT64, STRING} at [1:50]

What BigQuery means

The two sides of the operator have different types and BigQuery will not convert one for you.

= takes two values of the same type. That is what Signature: T1 = T1 says: one type variable, used twice. BigQuery then tried to find a type both sides could become, found none, and listed what it had: {INT64, STRING}.

The last line of the error is the useful one. Input types for <T1> names exactly the two types that met.

The smallest query that reproduces it

WITH t AS (SELECT 1 AS id)
SELECT * FROM t WHERE id = '1'

A quoted literal is still a STRING. The quotes are not decoration.

The fix

Make the literal the right type:

SELECT * FROM t WHERE id = 1

Or convert the column, if the column is the thing that is wrong:

SELECT * FROM t WHERE CAST(id AS STRING) = '1'

Prefer fixing the literal. A CAST on the column stops partition and cluster pruning, so it can make the query read far more than it needs to. If the column is a STRING that holds numbers and you cannot change the schema, SAFE_CAST is the safe direction, because it gives NULL instead of failing on the first row that is not a number:

SELECT * FROM t WHERE SAFE_CAST(id AS INT64) = 1

See CAST and conversion and SAFE functions.

Where BigQuery does convert for you

A STRING literal is coerced to a date or timestamp, so this is fine:

WITH t AS (SELECT DATE '2024-01-01' AS d)
SELECT * FROM t WHERE d = '2024-01-01'

But a DATE and a TIMESTAMP do not meet:

WITH t AS (SELECT TIMESTAMP '2024-01-01' AS ts)
SELECT * FROM t WHERE ts = DATE '2024-01-01'

No matching signature for operator = for argument types: TIMESTAMP, DATE
  Signature: T1 = T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {DATE, TIMESTAMP} at [2:23]

The rule is literal coercion, not general conversion. A string literal can become a DATE, a DATETIME, a TIME or a TIMESTAMP. That is the whole list: a string literal never becomes a number, so n = '1.5' against a NUMERIC column fails like any other mismatch. A string column never becomes a number either, and a date never becomes a timestamp without TIMESTAMP(d) or DATE(ts) written out.

The same error with other shapes

Every operator and function has its own wording. Read the lines under the first one.

IN. No colon after “types”, and the right side is in braces:

WITH t AS (SELECT 1 AS id) SELECT * FROM t WHERE id IN ('a','b')

No matching signature for operator IN for argument types INT64 and {STRING} at [1:53]

A function. You get every candidate signature and why each one was rejected:

SELECT UPPER(1)

No matching signature for function UPPER
  Argument types: INT64
  Signature: UPPER(STRING)
    Argument 1: Unable to coerce type INT64 to expected type STRING
  Signature: UPPER(BYTES)
    Argument 1: Unable to coerce type INT64 to expected type BYTES at [1:8]

AND and OR. They want BOOL, and they say so:

SELECT TRUE AND 1

No matching signature for operator AND
  Argument types: BOOL, INT64
  Signature: BOOL AND ([BOOL, ...])
    Argument 2: Unable to coerce type INT64 to expected type BOOL at [1:8]

There is no implicit truthiness in BigQuery. WHERE 1 is not WHERE TRUE either, though that one gets its own message: WHERE clause should return type BOOL, but returns INT64.

Message	Cause
`Column 1 in UNION ALL has incompatible types: INT64, STRING`	a set operation whose branches disagree. Position in the list, not a name.
`Equality is not defined for arguments of type ARRAY<INT64>`	arrays cannot be compared with `=` at all, whatever their element type
`Could not cast literal "a" to type DATE`	`SELECT 1 + 'a'`. `+` has a `DATE + INT64` signature, so BigQuery tried that one and the literal failed to coerce.
`Bad int64 value: n/a`	a `CAST` that was accepted and then hit a value it could not parse. A data error, not a type error.
`Operands of = cannot be literal NULL`	`x = NULL`. Use `x IS NULL`.

The job’s error_result.reason for all the type errors is invalidQuery.

In Querylab.io

Querylab.io warns in the browser when you compare a numeric column to a string literal, and suggests the unquoted number. The dry run covers the rest, including the IN and join cases, and puts BigQuery’s own message on the operator.