CODEWITHSUNDEEP
sqlgoogle-bigquery
cshin9
cshin9

Reputation: 1490

EXP() in BigQuery returns floating-point error

I have the following query:

SELECT EXP(col) FROM `project.dataset.tablename`;

Where col is FLOAT. However, I get this error: Error: Floating point error in function: EXP.

I've tried EXP() with dummy data, and it works. For example:

SELECT EXP(col) FROM (
    SELECT 1. as col UNION ALL
    SELECT 2. as col);

Why do I get a floating-point error with actual data and how do I work around it? I've tried such things as EXP(CAST(col as FLOAT64)) and EXP(ROUND(col, n)), but I still get the same error.

Upvotes: 2

Views: 4358

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Run the query:

SELECT MAX(col)
FROM project.dataset.tablename;

It will probably then be obvious why you are getting an overflow error. You can work around it by using a case:

SELECT (CASE WHEN col < ?? THEN EXP(col) END)
FROM project.dataset.tablename;

I could suggest a value, but it is probably obvious from your application -- say something larger than 10 or 100 might just be unreasonable.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59315

Probably you are working with numbers larger than 709.7827.

Weird number, but even in Fortran docs:

EXP(X)

Exponential.

X must be less than or equal to 709.7827.

http://sc.tamu.edu/IBM.Tutorial/docs/Compilers/xlf_8.1/html/lr277.HTM

This because numbers get too large after e^709.7827.

Upvotes: 5

Related Questions