Floating point error in Bigquery POW function

[suganthanS]

I'm trying a calculation using the formula in BigQuery.

SELECT
B * POW(rank, -A) AS C
FROM
...
It has been working so far without any issues. Today somewhere in the middle the values become

POW(858, 109.752)

, and I'm getting the error

Floating point error in function: POW(858, 109.752)
I would really appreciate it if anyone can help me to solve this problem.

[RC1 · Accepted solution]

@suganthanS I guess that your are reaching that limit of Big Query Power function (Although same thing works in Mysql. Strange 😐 )  [ Similar issue :  https://stackoverflow.com/questions/39862470/exp-in-bigquery-returns-floating-point-error ]

For such cases I have an approach that should do your job with little cost
You can use Big Query remote functions for exceptional cases handling [ https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions ]

SELECT (CASE WHEN Y > limit THEN big_query_remote_fun(X,Y) ELSE POW(X,Y) END)
FROM project.dataset.tablename;

You can implement the remote function (ie :big_query_remote_fun) in cloud functions using python (basically return pow(a,b) or use some powerful numpy libraries )

I would suggest you to add it as a defect report in issue tracker also. [ Public Trackers  > Cloud Platform  > Data Analytics  > BigQuery  ]

https://issuetracker.google.com/issues