Question Re: SAP HANA SQL Error: [304]: division by zero ... in Technology Q&A

SAP HANA SQL Error: [304]: division by zero ...

Former Member — Wed, 25 Oct 2017 09:53:01 GMT

In SAP HANA I had the following query (simplified):

select col1, col2 from TBL
where col1 > 0 and col2/col1 > 3

This results in the error:

[304]: division by zero undefined: search table error: [6859] AttributeEngine: divide by zero

Even when I try

select * from (
  select col1, col2 from TBL
  where col1 > 0 
) where col2/col1 > 3

results in the same error.

NOTE for simplification I changed the SQL.

TBL is acually a Graphical Calculation view and there are more attributes.

But executing the inner SQL works OK

When adding the outer where condition the error occurs.

Re: SAP HANA SQL Error: [304]: division by zero ...

pratik_doshi2 — Wed, 25 Oct 2017 11:30:39 GMT

There might the some condition when col1 is 0. You need to handle these condition specifically with the help of CASE statement or there are also other way you can do these.

Re: SAP HANA SQL Error: [304]: division by zero ...

eralper_yilmaz — Wed, 25 Oct 2017 13:04:16 GMT

The divisors should be checked for 0 values

Following code will help you eliminate the rows from the resultset where col1 value is equal to 0

select 
    col1, col2 
from TBL
where 
    col1 > 0 and 
    col2 / (CASE WHEN col1 = 0 then null else col1 end) > 3

What is important with your sample, using sub-select statement, we all assume that rows with col1 value equal to 0 are excluded by using where clause

col1 > 0

So we are sure that this criteria will exclude all rows with col1=0 so we are safe to use it as divisor

I guess the SQL engine which parses this SQL query and creates the SQL execution path applies all WHERE clause criteria on a single step.

Unfortunately, this smart act causes this error

But surprisingly following code does not work also and causes division by zero error

do 
begin

tbl_tmp2 = 
select 
    col1, col2 
from TBL
where 
    col1 <> 0 ;

select 
    col1, col2 
from :tbl_tmp2
where (col2 / col1) > 3;

end;

Re: SAP HANA SQL Error: [304]: division by zero ...

lbreddemann — Thu, 26 Oct 2017 01:30:13 GMT

The "check for 0" in your query silently assumes that the col1 >0 is executed before the rest of the query.

That's a false assumption for SQL - all predicates in your statement have to be true at the same time.

With HANA2 there is a comfortable way around this: the NDIV0 function.

Another workaround, in case you're on an older version that doesn't support NDIV0 is to use NULLIF in the division:

select a,b
from TBL
where b >0 
and a/NULLIF(b, 0) >3

Re: SAP HANA SQL Error: [304]: division by zero ...

Former Member — Thu, 26 Oct 2017 06:36:39 GMT

Thanks for your detailed explanation. This is very helpful.

Re: SAP HANA SQL Error: [304]: division by zero ...

Former Member — Thu, 26 Oct 2017 06:37:49 GMT

Thx for improving the answer of Eralper and even pointing out the possibility in HANA2

Re: SAP HANA SQL Error: [304]: division by zero ...

eralper_yilmaz — Thu, 26 Oct 2017 06:45:14 GMT

Hi Lars, thank you for update.

NULLIF(col1, 0)

is the short representation of

CASE WHEN col1 = 0 THEN null ELSE col1 END

Re: SAP HANA SQL Error: [304]: division by zero ...

lbreddemann — Fri, 27 Oct 2017 00:31:48 GMT

You're right, semantically both expressions lead to the same outcome.

From an implementation point of view, CASE adds an additional type-coercion to the NULL return value, but that does not have a measurable impact on runtime or memory consumption.

More important, from my point of view, is that NULLIF makes it easier to see what the intention behind that bit of code is and it's shorter to write, as well.