SQL nullif() Is A Snake, Or Is It?

 

 

1. Introduction

nullif() has two parameters. The first one, is a variable, and the second one is a real number. We want a result that when param1 equals to param2, the function will return null. Normally this is helpful when we do divide calculation. We can use nullif() to avoid the “divided by zero” problem.

For example:

variable1 / nullif(variable2, 0) - 1 as yoy

It works because when variable2 equals to zero, the function nullif() will return null. This makes the whole divide calculation as variable1 / null, which results null.

This is exactly what we want, because in math we just don’t divide a number with zero. If we have to do this for some reason, we will rather the result is null.

 

2. The Problem

Recently I met a strange problem that when I use variable1 / nullif(variable2, 0) , SQL returns data overflow.

I was surprised because data overflow is normally caused by divided by zero problem. But we have already use nullif() trick like above to avoid it. What happen here?

I have also try something else to debug like below, but SQL still return data overflow message.

variable1 / nullif(variable2 * 1.0, 0)

variable1 / nullif(variable2 , 0.000)

variable1 / nullif(variable2 , 100)

variable1 / nullif(variable2 , 100.000)

Finally I decided I have to make sure variable2 will not be zero in all cases beforehand. And I remove the nullif() function. Everything goes all right.

Simply like, variable1 / variable2

 

3. The Reason and Fix

After a few days and I came back to this problem. I notice it will not happen in other calculation but only in one special case.

The reason is, variable2 in our case is type decimal(32, 32). This type is special in some version of SQL and it is caused by two decimal variables calculation.

Until here, if we use nullif() function, it will try to compare param1 with param2. In our case, it is comparing decimal(32, 32) with integer zero.

This comparison will hiddenly convert the type of param2 into param1, because param1 is more complex than param2. In our case, it is converting zero an integer to decimal(32, 32). Finally, the converting hits data overflow.

Understanding this, we can fix it by manually cast the decimal(32, 32) variable to double, if we don’t care too mach about the precision lost.

Like:

variable1 / nullif(cast(variable2 as double), 0)

This formula will first convert variable2 from decimal(32, 32) to double. Then hiddenly convert zero to double in the comparison, which will not become data overflow. Finally nullif() will return a result for us.

Of course, you cannot use this method if you care about the precision lost. When we manually change decimal(32, 32) to double, it will lost some of the digits for sure. In my case, I am satisfied with type double. If you are calculating a planetary orbit, for example, you definitely cannot use it. Or, at the first place you should not use SQL for that kind of calculation.