Weird error message for EXP(a really big negative number)

Database
N/A

Weird error message for EXP(a really big negative number)

Hi,

when running EXP(1000) you get a complaint about an overflow, which is fine. But it really took me a good plenty of time to understand why I was getting a "Bad argument for ** operator" errors. My SQL contains both powers (**) and exponents EXP(). I tried to replicate the error with the power functions without success. Only much later I noticed than calling EXP(-100000) results in this error. I'm now damping the arguments also for too small numbers (I had it already for the positive case).

The point is that this error message is really distracting, especially as it refers to the totally unrelated (in the view of the user) ** power operator and the reference manuals talk only about cases like 0**0 for that error.

A better error message would be much more similar to the one for EXP(1000).

2 REPLIES
Enthusiast

Re: Weird error message for EXP(a really big negative number)

Hi, I am getting the same error .

 

SELECT DatabaseName ,Tablename ,SUM(CurrentPerm) AS CurrentPerm ,MAX(CurrentPerm)*(HASHAMP()+1) AS PermWithSkew , 100-(AVG(CurrentPerm)/MAX(CurrentPerm)*100) AS Skewfactor ----, 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 AS SkewThresh , CASE WHEN MAX(CurrentPerm) / AVG(CurrentPerm+1) > 100 AND SUM(CurrentPerm) > 500000 THEN 'N' WHEN MAX(CurrentPerm) / MIN(CurrentPerm+1) > 1000 AND SUM(CurrentPerm) > 500000 THEN 'N' WHEN SUM(CurrentPerm) < HASHAMP()*128*1024 THEN 'Y' WHEN SUM(CurrentPerm) > 1024*1024*1024 AND Skewfactor > 15.0 THEN 'N' ----WHEN Skewfactor < 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 THEN 'Y' ELSE 'N' END AS SkewCompliant FROM DBC.TableSizeV GROUP BY 1,2 ORDER BY Skewfactor DESC;

 

Wanted to understand , what ind of bad data conversion is hapenning w.r.t EXP, and what we can change.

Enthusiast

Re: Weird error message for EXP(a really big negative number)

the query runs fine with commenting as shown below, how ever throws error, witout comment at two places..

How ever the same sql runs fine in other teradata systems.

 

SELECT
DatabaseName
,Tablename
,SUM(CurrentPerm) AS CurrentPerm
,MAX(CurrentPerm)*(HASHAMP()+1) AS PermWithSkew
, 100-(AVG(CurrentPerm)/MAX(CurrentPerm)*100) AS Skewfactor
, 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 AS SkewThresh
, CASE
WHEN MAX(CurrentPerm) / AVG(CurrentPerm+1) > 100 AND
SUM(CurrentPerm) > 500000 THEN 'N'
WHEN MAX(CurrentPerm) / MIN(CurrentPerm+1) > 1000 AND
SUM(CurrentPerm) > 500000 THEN 'N'
WHEN SUM(CurrentPerm) < HASHAMP()*128*1024 THEN 'Y'
WHEN SUM(CurrentPerm) > 1024*1024*1024 AND Skewfactor > 15.0 THEN 'N'
WHEN Skewfactor < 50.0*EXP(-3.0*(SUM(CurrentPerm) / (1024*1024*1024))) + 15.0 THEN 'Y'
ELSE 'N'
END AS SkewCompliant
FROM
DBC.TableSizeV
GROUP BY
1,2
ORDER BY
Skewfactor DESC;