All Forums Database
teradater 29 posts Joined 11/10
17 Nov 2010
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 a

btpys 4 posts Joined 06/10
17 Nov 2010

Try to cast bigint to float

teradater 29 posts Joined 11/10
17 Nov 2010

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

dnoeth 4628 posts Joined 11/04
17 Nov 2010

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

Dieter

teradater 29 posts Joined 11/10
17 Nov 2010

i'm use sqla v13.

query
"select * from table where a &lt b"

returns zero rows.

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

dnoeth 4628 posts Joined 11/04
17 Nov 2010

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

Dieter

teradater 29 posts Joined 11/10
17 Nov 2010

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

CarlosAL 512 posts Joined 04/08
17 Nov 2010

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.

dnoeth 4628 posts Joined 11/04
17 Nov 2010

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

Dieter

teradater 29 posts Joined 11/10
17 Nov 2010

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

dnoeth 4628 posts Joined 11/04
18 Nov 2010

If sqrt(abs(...)) work then you definitely got negative values.
Remove the sqrt and check for it.

FLOAT is a 8 byte IEEE floating point format with approximately 15/16 digits precision, whereas DEC(38) is a 16 byte exact numeric.

Dieter

Dieter

CarlosAL 512 posts Joined 04/08
18 Nov 2010

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

Agreed. The first site to look was the negative values, which were "The Usual Suspects", but teradater misled me : "select * from table where a < b returns zero rows."

>>"but SUM(CNT) might (as the cast to BIGINT)."

That was my point.

An I overlooked the fact that SUM(CNT)**2 is potentially greater than SUM(CNT**2) ...

Cheers.

Carlos.

teradater 29 posts Joined 11/10
19 Nov 2010

I replaced bigint with decimal(38,0), and it's work correctly! :)

Thanx all!

ps: CarlosAL, i use formula, that described in SQL Reference: Functions and Operators, function STDDEV_POP ("Computation" part or description). i hope, teradata&co can't allow such trivial mistakes ;), so the fact, that formula has such description in documentation, initiate my (and your) researching with sqrt().

You must sign in to leave a comment.