error: bad argument for sqrt function

Database
Enthusiast

error: bad argument for sqrt function

Hello.

I have a table (a bigint, b bigint)? where for any a is true that a>=b.
When queriing:

select sqrt(a-b) from table;

there is error, bad argument in sqrt() (don't remember exactly).

select * from table where aReturns 0 rows.

So, why error appears? Is there any special features with data types?

Thanx.

Tags (2)
12 REPLIES
Fan

Re: error: bad argument for sqrt function

Try to cast bigint to float
Enthusiast

Re: error: bad argument for sqrt function

This is useless, first, when i meet the problem, fields was decimal(18,5) (this is enough for stored values).
Junior Contributor

Re: error: bad argument for sqrt function

Is this the error?
2604 Bad argument involving ... for SQRT function.

First try adding a condition to see if actually a >= b:
WHERE a >= b

Do you run that query in SQL Assistant pre-TD13?
Then it might be because of the ODBC SQRT function, too.
Uncheck Tools - options - query - 'Allow use of ODBC SQL extensions in queries'
I can't check this, because i don't have access to a SQLA 12 anymore.

Dieter
Enthusiast

Re: error: bad argument for sqrt function

i'm use sqla v13.

query
"select * from table where a < b"

returns zero rows.

the error is "2603 Bad argument for SQRT function."
Junior Contributor

Re: error: bad argument for sqrt function

Strange.

I would suggest to run that query from BTEQ or any other query tool, maybe it's because of a SQLA/ODBC/.NET "feature".

To eliminate issues with SQRT you could replace it with (a-b)**0.5.

How many rows are in that table?
Does it work for a single row?
select top 1 sqrt(a-b) from table;

If averythiong fails, could you post your actual query?
Before opening an incident :-)

Dieter
Enthusiast

Re: error: bad argument for sqrt function

May be you mean (a-b) + 0.5?

I already use sqrt(abs(...)), but will try to run from bteq, thx.

>How many rows are in that table?
there are about 100 000 000 rows

>Does it work for a single row?
No, it doesn't.

Here you are (only field_names renamed):
select X,Y,
sqrt(cast(N * sum(CNT**2) as bigint) - cast(sum(CNT)**2 as bigint)) as D
from table_1
group by X,Y,N
Junior Supporter

Re: error: bad argument for sqrt function

It could be an overflow issue for the CNT column (the sum exceeds the column precision):

Here is an example:

CREATE MULTISET TABLE PRUEBA01(
X INTEGER NOT NULL,
Y INTEGER NOT NULL,
N INTEGER,
CNT BYTEINT)
PRIMARY INDEX(X)
;

*** Table has been created.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO PRUEBA01 VALUES(1,1,1,111);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO PRUEBA01 VALUES(1,1,1,111);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT X,
Y,
SQRT(CAST(N * SUM(CNT**2) AS BIGINT) - CAST(SUM(CNT)**2 AS BIGINT)) AS D
FROM PRUEBA01
GROUP BY X,Y,N
;

*** Failure 2603 Bad argument for SQRT function.

SUM(CNT) exceeds the byteint precission and the SQRT returns the error.

Could you check that SUM(CNT**2) fits in the CNT datatype?

HTH.

Cheers.

Carlos.
Junior Contributor

Re: error: bad argument for sqrt function

Hi Carlos,
the query fails even if you use BIGINT instead of BYTEINT :-)
Remove the SQRT and see the negative value.

This looks like a part of a standard deviation calculation, in that case the formula is wrong:
Instead of "N * SUM" it's "CNT * SUM"

teradater should have shown the actual calculation in the beginning :-)

SUM(CNT**2) will not overflow, because the result of an exponentiation is a FLOAT, but SUM(CNT) might (as the cast to BIGINT).
If this happens better use DEC(38,0) instead of BIGINT.

To get the highest possible precision i would suggest:
sqrt(CNT * sum(cast(CNT**2 as DEC(38,0))) - sum(cast(CNT as DEC(38,0)))**2 ) as D

Dieter
Enthusiast

Re: error: bad argument for sqrt function

CarlosAL, when i use sqrt(abs(...)), query isn't fails.

dnoeth, the formula is right, it's unsuccessful renaming variables.

Isn't DECIMAL(38,0) and float are the same?
Thanx, i'll try the first one, and write after this here