04 Feb 19 · npack · #Bigquery ·   Bookmark   ×

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

Problem 🔍

Using comparison (=) or arithmetic operators (*, +, -) on two fields that are of different incompatible datatypes. eg. Trying to multiply an Integer with String column. You get the below error message:

 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 compare columns have the same type

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

npack

posted on 04 Feb 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds