Condition for generating NULL

Database
Enthusiast

Condition for generating NULL

Hi,

I'm wondering when will NULL value being generated.

Given the following query:

        SELECT CASE WHEN (ACOS(2.3) IS NULL) THEN 'T' ELSE 'F' END AS C0

I'm expecting 'T', because '2.3' is out of value range for ACOS(), thus ACOS(2.3) should be 'NULL'. However 'F' is returned actually.

Teradata will complain the following query as 'Invalid Input: numeric value within range only.':

        SELECT ACOS(2.3) AS C0

The points I don't understand are:

(1) Why the first query won't complain about the invalid argument?

(2) Suppose the invalid argument check will happen on the fly, then ACOS(2.3) should be 'NULL', but since the first query return 'F', it indicates ACOS(2.3) is valid. Is this by design?

Thanks!

Eliot

5 REPLIES
Enthusiast

Re: Condition for generating NULL

when you run the above query, the when condition is checked and as the argument is invalid, the condition becomes false and it goes to the else part. 

SELECT CASE WHEN (ACOS(2.3) IS NULL) THEN 'T' ELSE 'F' END AS C0

try it without the else part and you will get what is actually happining.

SELECT CASE WHEN (ACOS(2.3) IS NULL) THEN 'T' END AS C0

Khurram
Enthusiast

Re: Condition for generating NULL

Hi Khurram,

Thanks for the help! I'm able to observe NULL value with the query you give. However another question is how can I come up with a query that check for NULL values from output of an expression? In other words, how to re-write the first query that judge whether an expression will generate NULL value and based on that, build a flag to indicate whether NULL value is found or not?

Following your idea, I wrote this query, which seems to be working:

        SELECT CASE WHEN (CASE WHEN (ACOS(2.3) IS NULL) THEN 'T' END) IS NULL THEN 'T' ELSE 'F' END AS C0

However this query will always return 'T', even when the expression is valid. For example, the query below will return two 'T' upon execution, however 'C1' should actually be 'F' in this case.

        SELECT CASE WHEN (CASE WHEN (ACOS(2.3) IS NULL) THEN 'T' END) IS NULL THEN 'T' ELSE 'F' END AS C0,

                    CASE WHEN (CASE WHEN (ACOS(0.23) IS NULL) THEN
'T' END) IS NULL THEN 'T' ELSE 'F' END AS C1

Best Regards,

Eliot

Enthusiast

Re: Condition for generating NULL

What I think is that IS NULL is just being checked against the function output, whether it is an error code or a true value. 

If you check the same against the NOT NULL, then it gives the correct results. for example

SELECT CASE WHEN ACOS(2.3)  IS NOT NULL THEN 'T' ELSE 'F' END AS C0,

                    CASE WHEN ACOS(0.23) IS NOT NULL THEN 'T' ELSE 'F' END AS C1

To avoid an error value to be checked and return an error instead of blind check, you can use a derived table as below:

SELECT TOP 2 CASE WHEN  DRV.A  IS NULL THEN  'T' ELSE 'F' 
END AS C0
FROM
(
SELECT ACOS(0.23) AS A
)DRV;

Khurram
Senior Apprentice

Re: Condition for generating NULL

Hi Eliot,

ACOS will return NULL only for NULL as input value, but when the value is out of the valid range there should be a 5585 error.

So this seems to be a bug, the optimizer doesn't actually evaluate the expression, but assumes that it will return a value (which is NOT NULL).

The get a NULL you should check if the input value is within the allowed range, e.g.

CASE WHEN x BETWEEN -1 AND 1 THEN ACOS(x) END

Dieter

Enthusiast

Re: Condition for generating NULL

Hi Dieter and Khurram,

Your suggestion is really helpful! For now, I'm adding range check for all the trigonometric functions and they are working well in my query jobs. Really appreciated for the help!

Eliot