No matching signature for operator = for argument types: INT64, STRING. Supported signatures: ANY = ANY

Problem 🔍

Using comparison (=, >, <) or arithmetic operators (*, +, -) on two fields that are of different incompatible datatypes. eg. Trying to multiply an Integer with String column. 

 Error: No matching signature for operator = for argument types: 
 INT64, STRING. Supported signatures: ANY = ANY at [4:7]

Solution 💡

#1 Check all the equality signs (=) to make sure the comparison columns have the same datatype

Verify all the equality operators in your SQL query and verify the datatypes on both sides are compatible. (eg. Compare INT with INT, STRING with STRING, DATE with DATE...) 

Example:
Below query compares an integer column (eg. Id) against a string

SELECT field1,field2
FROM `dataset.table`
WHERE Id = '100';

Try this instead

SELECT field1,field2
FROM `dataset.table`
WHERE Id = 100;

#2 Use explicit CAST to convert columns to same datatype

You might want to convert the columns to same datatype before comparing or performing any arithmetic operations

Example:

SELECT numcol1,stringcol2,(numcol1 + stringcol2) as col3
FROM `dataset.table`

Try this instead

SELECT numcol1,stringcol2,(numcol1 + cast(stringcol2 as int64)) as col3
FROM `dataset.table`

#3 Check all the Arithmetic operators (+ / - *) to make sure they have the same type

Look for arithmetic operators in your SQL query and make sure they have the same datatype

Example:

SELECT numcol1,stringcol2,(numcol1 * stringcol2) as col3
FROM `dataset.table`

Try this instead

SELECT numcol1,stringcol2,(numcol1 * cast(stringcol2 as int64)) as col3
FROM `dataset.table`

More Troubleshooting tips ⚡

• Check all the hardcoded values in a query are within single quotes (')
• Check the datatypes on the left hand side vs right hand side are compatible in all comparison operators including =, >, <. >=, <=, <>
• In few cases, You may be doing an invalid comparison. eg. The left side has STRUCT<id STRING, name STRING>. Comparing this with the string is not valid. You may have to rewrite your query

Related Errors:

This steps in this guide can be used for resolving the below errors as well:

• No matching signature for operator * for argument types: STRING, INT64. Supported signatures: INT64 * INT64; FLOAT64 * FLOAT64; NUMERIC * NUMERIC
• No matching signature for operator + for argument types: STRING, INT64
  
• No matching signature for operator - for argument types: STRING, INT64
  
• No matching signature for operator / for argument types: STRING, INT64
  

mCollins

posted on 04 Feb 19