Execute a THEN clause where WHEN is false

Question

An error occurs in the code of THEN clause when a UDF is used in the WHEN clause, despite the fact that the function returns false.

-- a simple function for example
CREATE FUNCTION "is_one"
( 
      IN checkNum INTEGER
)
RETURNS is_one INTEGER
LANGUAGE SQLSCRIPT
AS
BEGIN
    IF 
        :checkNum = 1
    THEN 
        is_one := 1;
    ELSE 
        is_one := 0;
    END IF;
END;

SELECT "is_one"(1) FROM DUMMY; -- returns 1
SELECT "is_one"(0) FROM DUMMY; -- returns 0

-- error...  [304]: division by zero undefined: cannot be divided by zero at function /()
SELECT
    CASE
        WHEN "is_one"(0) = 1 THEN 1/0
        ELSE 0
    END AS x 
FROM DUMMY;

-- an error does not occur if the function is not used
-- returns 0
SELECT
    CASE
        WHEN 0 = 1 THEN 1/0
        ELSE 0
    END AS x 
FROM DUMMY;

I expect the output to be 0, but the actual I get an error.

Is there a method in HANA that changes the behavior of function to be "deterministic"? — Salman A, Sep 02 '19 at 12:35
Yes, SAP HANA 2 supports the `DETERMINISTIC` attribute for functions. But as written in the answer, this is not what is behind the unexpected behavior in this case. — Lars Br., Nov 07 '19 at 05:09

score 0 · Answer 1 · answered Nov 07 '19 at 05:06

0

Just tried to recreate this with SAP HANA 2.40 and I did not get an error but the expected value 0

answered Nov 07 '19 at 05:06

Lars Br.

9,949
2
15
29

Execute a THEN clause where WHEN is false

1 Answers1