CODEWITHSUNDEEP
sqlsubqueryamazon-redshiftdivisiondivide-by-zero
Chuck
Chuck

Reputation: 1293

why is Redshift giving me a divide by zero error?

I'm aware that there are some pitfalls around floating points in Redshift, but I can't get past this.

I have columns col1 and col2 and want to create a simple division as "calc". col1 and col2 are sometimes zero, so I use where col2 > 0. There are no null values for col1 and col2. This works fine:

select col1*1.0/col2 as calc
from t1
where col1 > 0 and col2 > 0

But if I use any value in the where clause for calc, I get the error:

select calc from 
(select col1*1.0/col2 as calc
from t1
where col1 > 0 and col2 > 0) 
where calc < 1 -- error here for any value, gt or lt

SQL Error [500310] [57014]: [Amazon](500310) Invalid operation: Divide by zero;

I tried a whole bunch of things including different values for calc, cast as decimal or float, but I get the same error. Why?

Upvotes: 0

Views: 2746

Answers (1)

GMB
GMB

Reputation: 222412

Redshift probably optimizes the operation tree of the query in a manner that results in the division in the select clause being evaluated before the conditions in the where clause.

You can work around this with nullif():

select calc 
from (
    select col1 * 1.0 / nullif(col2, 0) as calc
    from t1
    where col1 > 0 and col2 > 0
) 
where calc < 1

Upvotes: 2

Related Questions