Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

A round number Expand / Collapse
Author
Message
Posted Tuesday, May 4, 2010 7:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
Paul White NZ (5/4/2010)
Hugo Kornelis (5/4/2010)
Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:
-- Or, the more direct approach
SELECT CAST(ROUND(0.5,0) AS decimal(12,2));


According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:
https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0

I don't think that this explanation is 100% correct, as we had already found out that the error is raised within the Client, not SQL Server (at least thats what I think)

This all still does not make too much sense to me, especially after you gave the hint regarding SQLCMD returning 0. Did not see that in the first place.

And thanks for your nice comment as well. Feels really good to get such positive feedback from real experts


Best Regards,
Chris Büttner
Post #915262
Posted Tuesday, May 4, 2010 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:18 PM
Points: 6,056, Visits: 8,342
Paul White NZ (5/4/2010)
Hugo Kornelis (5/4/2010)
Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:
-- Or, the more direct approach
SELECT CAST(ROUND(0.5,0) AS decimal(12,2));


According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:
https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0

Good find, Paul.

But Jim is wrong. If his explanation was correct, than
DECLARE @x numeric(2,1); SET @x = ROUND(9.5,0);

should return an error, on all clients. It does not. But
DECLARE @x numeric(2,1); SET @x = ROUND(9.5,0); SELECT @x;

does return an error, on some clients.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #915265
Posted Tuesday, May 4, 2010 7:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 2,394, Visits: 2,788
Interesting question, and fascinating discussion. At this point, I'm not sure what the final correct explanation will be.

If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it? - just kidding!

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #915310
Posted Tuesday, May 4, 2010 8:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:11 AM
Points: 660, Visits: 401
webrunner (5/4/2010)
Interesting question, and fascinating discussion. At this point, I'm not sure what the final correct explanation will be.

If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it? - just kidding!

- webrunner

I appreciate the discussion, but the question was ambiguous in the extreme, and I join those who didn't consider it a good QotD per se. Where I come from (the UpOver land of SimpleDom ), SELECT ROUND(0.5, 0) returns the sum of (a) the rounded-up value of the first operand and (b) a zapped fractional component in a value formatted the same as the first operand (in this case, NUMERIC(2,1)) -- again, I appreciate knowing that different query engines return different results, but that fact means that the QotD was not well-phrased! Just my tuppence.
Post #915336
Posted Tuesday, May 4, 2010 8:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 6:49 AM
Points: 1,581, Visits: 1,859
Good question, great discussion. Very interesting problem. I definitely learned a lot today, including to watch how I set up ad hoc queries.
Thanks!
Post #915339
Posted Tuesday, May 4, 2010 8:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
webrunner (5/4/2010)
If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it?

Superb. Well done, sir!

It definitely is client-specific, but also server-specific

Results so far for SELECT ROUND(0.5, 0) for me:

osql client to 2005 server = 1.0 (correct!)
SQLCMD client to 2005 server = .0 (wrong!)
SSMS 2005 to 2005 server = An error occurred while executing batch. Error message is: Arithmetic Overflow.
SSMS 2008 to 2005 server = An error occurred while executing batch. Error message is: Arithmetic Overflow.

osql client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SQLCMD client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SSMS 2008 to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.

Results so far for PRINT ROUND(0.5, 0) for me:
osql to 2005: 1.0 (correct!)
SQLCMD to 2005: 1.0 (correct!)
SSMS 2005 to 2005 server: 1.0 (correct!)
SSMS 2008 to 2005 server: 1.0 (correct!)

osql client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SQLCMD client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SSMS 2008 to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915341
Posted Tuesday, May 4, 2010 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:15 PM
Points: 31,210, Visits: 15,654
It's an interesting question. Good to see there's a Connect item on it, and people should have learned things.

Not sure how I feel about the question. Definitely if the server handles it, the client should.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #915352
Posted Tuesday, May 4, 2010 8:44 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
Steve Jones - Editor (5/4/2010)
It's an interesting question. Good to see there's a Connect item on it, and people should have learned things.

I have..for one

Not sure how I feel about the question. Definitely if the server handles it, the client should.

I think there's another lesson here - patch your client tools as well as the server.
I wonder how many people will be surprised by the client tools version returned by Help...About in SSMS.

The situation with SQL Server 2005 is very odd.
The server seems happy to process SELECT ROUND(0.5, 0) but assigns invalid metadata:

SELECT a = ROUND(0.5,0) INTO #a;
SELECT a FROM #a;

The first statement succeeds, but the data type of column a in #a is numeric(1,1)!

DBCC CHECKDB on tempdb returns:
Msg 2570, Level 16, State 3, Line 1
Page (1:2373), slot 0 in object ID 258099960, index ID 0, partition ID 72057594068271104, alloc unit ID 72057594073382912
(type "In-row data"). Column "a" value is out of range for data type "numeric".
Update column to a legal value.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915370
Posted Tuesday, May 4, 2010 8:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 17,845, Visits: 15,797
Wow...

Who would have thought the QOD would have unveiled a bug and produced great discussion. Congrats to all who have contributed to this discussion!!




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #915375
Posted Tuesday, May 4, 2010 9:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:39 PM
Points: 1,275, Visits: 220
from within the Query Analyzer (SQL 2000 SP4) I got the resultant 1.0 as well.

just my $0.02.

M


Free Expert Advice . . .
http://xkcd.com/627/

Mark
Post #915435
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse