A round number

Question

A round number

Viewing 15 posts - 16 through 30 (of 43 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Hugo Kornelis (5/4/2010)
Good observation, Christian. The format of the error message, which looks quite different from errors that are thrown by SQL Server, is also an indication that there is a different source of the error.

This just gets more and more interesting. I have been able to reproduce the SSMS-only behaviour when connecting to a default instance of SQL Server 2005 from SSMS and SQLCMD. I found this in Books Online:

Important:

SQL Server Management Studio uses the Microsoft .NET Framework SqlClient for execution in regular and SQLCMD mode in Query Editor. When sqlcmd is run from the command line, sqlcmd uses the OLE DB provider. Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility.

So, I thought...that explains that. Except that it doesn't. I get a proper SQL-Server-generated error for all the tests presented so far when connected to a named instance of SQL Server 2008 - connecting from SSMS or SQLCMD! The error is:

.Net SqlClient Data Provider: Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type numeric.

The error is captured in a Profiler trace under the Errors and Warnings: User Error Message category, so this is definitely a SQL Server error. This does not happen when connected to SQL Server 2005 - the error definitely comes from the provider not SQL Server.

Here's a repro to try (notice no value is returned to the client - it is written to a temporary table):

BEGIN TRY

SELECT ROUND(0.5, 0) AS a INTO #a;

END TRY

BEGIN CATCH

SELECT message = ERROR_MESSAGE(),

number = ERROR_NUMBER(),

line = ERROR_LINE(),

severity = ERROR_SEVERITY(),

state = ERROR_STATE();

END CATCH;

DROP TABLE #a;

That produces full error details when connected to 2008, but runs without error on 2005 - even from SSMS. Finally, to add to the evidence, this is a screenshot of the results of SELECT ROUND(0.5, 0) when run from SQLCMD against 2005 and 2008:

Answers on a postcard, please!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

I got the answer correct for the wrong reason... This really surprises me and definitely learned a lot from this! Shows how important it is to always convert your data before doing calculations. To me, this is a great question. If you want questions without any discussion about the results, things will get real boring around here.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Paul White SSC Guru Points: 150468 More actions · Answer 3

Christian Buettner-167247 (5/4/2010)
Does not look like it is settings related.
Here is the code executed via each client tool:

Hey Christian,

Thanks for the awesome feedback there - but have you noticed that SQLCMD gets the answer wrong?

ROUND(0.5, 0) is "1.0" not ".0"! I got the same results in my tests (see the rather pretty screenshot).

This has to be a bug. If no-one comes up with a comprehensive answer, this is going on Connect.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 4

r.hensbergen (5/4/2010)
I got the answer correct for the wrong reason... This really surprises me and definitely learned a lot from this! Shows how important it is to always convert your data before doing calculations. To me, this is a great question. If you want questions without any discussion about the results, things will get real boring around here.

Fantastic, thank you Ronald. That's exactly how I feel about it.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 5

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

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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, thanDECLARE @x numeric(2,1); SET @x = ROUND(9.5,0);should return an error, on all clients. It does not. ButDECLARE @x numeric(2,1); SET @x = ROUND(9.5,0); SELECT @x;does return an error, on some clients.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

webrunner SSC-Dedicated Points: 31880 More actions · Answer 8

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

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Michael Poppers SSCrazy Points: 2151 More actions · Answer 9

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.

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 10

Good question, great discussion. Very interesting problem. I definitely learned a lot today, including to watch how I set up ad hoc queries.

Thanks!

Paul White SSC Guru Points: 150468 More actions · Answer 11

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! :laugh:

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

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
All articles available on SQL.kiwi
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 12

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.

Paul White SSC Guru Points: 150468 More actions · Answer 13

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)! :w00t:

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
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

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[/url]
Learn Extended Events

mark.pleasance SSCrazy Points: 2070 More actions · Answer 15

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