Can someone verify this for me?

Question

Post reply

Can someone verify this for me?

Frank Kalis

SSC Guru

Points: 111183
More actions
April 14, 2005 at 4:21 am

#67392

Hi ya,
consider the following script
SET NOCOUNT ON
CREATE TABLE testcase
(
MyDecimal DECIMAL(2, 0) NOT NULL
)
CREATE INDEX ix_test ON testcase (MyDecimal)
INSERT INTO testcase VALUES (2)
SELECT * FROM testcase WITH (INDEX=0) WHERE MyDecimal <999999
--SELECT * FROM testcase WHERE MyDecimal <999999
SELECT * FROM testcase WHERE CAST(MyDecimal AS DECIMAL(6,0)) <999999
DROP TABLE testcase
SET NOCOUNT OFF
MyDecimal
---------
2
MyDecimal
---------
2
This works just fine. However, when you uncomment the second SELECT statement, you'll receive a
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
I'm on Microsoft SQL Server 2000 - 8.00.760. The original questioner claims that this behaviour came with SP3/3a. Running this against an unpatched server should work.
Since I don't have an unpatched server available, can someone please verify or falsify this for me?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

chitra-225043 Grasshopper Points: 20 More actions · Answer 1

Works fine without error after the select comment is removed on an unpatched server.

prsnlid SSC Enthusiast Points: 110 More actions · Answer 2

Try this,

SET NOCOUNT ON

CREATE TABLE testcase

(

MyDecimal DECIMAL(2, 0) NOT NULL

)

CREATE INDEX ix_test ON testcase (MyDecimal)

DECLARE @v-2 REAL

SET @v-2=999999

INSERT INTO testcase VALUES (2)

SELECT * FROM testcase WITH (INDEX=0) WHERE MyDecimal <999999

SELECT * FROM testcase WHERE MyDecimal < @v-2 -- 999999

SELECT * FROM testcase WHERE CAST(MyDecimal AS DECIMAL(6,0)) <999999

DROP TABLE testcase

SET NOCOUNT OFF

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Amazing. Now I must find out what has changed in SP3a that caused this error

Thank you both!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Anoop P. SSCommitted Points: 1632 More actions · Answer 4

Since I am already working on the misteries caused by isnumeric & convert functions, I tried the following SQL & it worked !!!!

SET NOCOUNT ON

CREATE TABLE testcase

(

MyDecimal DECIMAL(2, 0) NOT NULL

)

CREATE INDEX ix_test ON testcase (MyDecimal)

INSERT INTO testcase VALUES (2)

SELECT * FROM testcase WITH (INDEX=0) WHERE MyDecimal <999999

SELECT * FROM testcase WHERE MyDecimal < convert(float,999999 )

SELECT * FROM testcase WHERE CAST(MyDecimal AS DECIMAL(6,0)) <999999

DROP TABLE testcase

SET NOCOUNT OFF

All I have done is added a convert() function

Please do not ask me how this worked... I am also searching for thesame answer

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

To add to the confusion, (this second line errors on my server as well) I altered the third select to CAST MyDecimal to DECIMAL(2,0); its existing state... And that ran fine...

SET NOCOUNT ON

CREATE TABLE #testcase

(

MyDecimal DECIMAL(2, 0) NOT NULL

)

CREATE INDEX ix_test ON #testcase (MyDecimal)

INSERT INTO #testcase VALUES (2)

SELECT * FROM #testcase WITH (INDEX=0) WHERE MyDecimal <999999

--SELECT * FROM #testcase WHERE MyDecimal <999999

SELECT * FROM #testcase WHERE CAST(MyDecimal AS DECIMAL(2,0)) <999999

DROP TABLE #testcase

SET NOCOUNT OFF

I wasn't born stupid - I had to study.

Anoop P. SSCommitted Points: 1632 More actions · Answer 6

But Fallell, I find that the statement which is giving error is still commented in your SQL

--SELECT * FROM #testcase WHERE MyDecimal <999999

Do you mean to say that even after uncommenting this line it worked fine ?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Yes, when you're explicite in your conversion it works. The commented line fails because SQL Server is trying to bring 999999 down to a DECIMAL(2,0) which obviously won't work. This FLOAT thingy works because FLOAT has a higher precedence. I'm a bit suprised that

SELECT * FROM #testcase WHERE CAST(MyDecimal AS DECIMAL(2,0)) <999999

works. I don't have an explanation for this right now

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Farrell Keough SSCoach Points: 17414 More actions · Answer 8

'zactly Frank. This is very perplexing. Even CASTing MyDecimal as its own datatype works...

I have forwarded this thread to our DBA. We deal with different numeric datatypes all the time and this is really odd?

How did you find this?

I wasn't born stupid - I had to study.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Another oddity!

Till now the column is declared NOT NULL. Change this to NULL and run

CREATE TABLE testcase

(

MyDecimal DECIMAL(2,0)

)

CREATE INDEX ix_test ON testcase (MyDecimal)

INSERT INTO testcase VALUES(1)

SELECT

*

FROM

testcase

WHERE

CAST(MyDecimal AS DECIMAL(2,0)) < 999999 --fails now

DROP TABLE testcase

(1 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 8

Arithmetic overflow error converting numeric to data type numeric.

I'll guess this will be my first posting in the private MVP newsgroup.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

Frank, I recently had the opportunity to try this on a machine that did not have SP3a. Got the same results...

I wasn't born stupid - I had to study.