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 «««34567»»»

Hidden Formatting Troubles with STR() (SQL Spackle) Expand / Collapse
Author
Message
Posted Sunday, January 9, 2011 3:46 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . . STR is deterministic!!!!!!!!!!!!!!!!!! Look it up in BOL!

As for the article itself . . . I say again "KNOW YOUR DATA TYPES"! Float and Real should automatically catch one's attention. All values may not be represented in certain formats, e.g. one third cannot be precisely represented in decimal format . . . then saying decimal is flawed does not make one insightful.

"Bad Rounding" . . . Just how is it the article's author knows something that an army of PhD scientists and mathematicians have not realized after well over a half century of computer development?! Moreover, he then judges not just SQL Server but by implication virtually all software products in the world as having flaws. Among others, Float/Real is addressed by:

- The Institute of Electrical and Electronics Engineers
- American National Standards Institute
- International Organization for Standardization
- International Electrotechnical Commission

Should we let them, electronics manufactures (INTEL & AMD), and all other software companies (ORACLE too) know they have "Bad rounding"? Finally, poor Sir Isaac Newton, the inventor of calculus, never realized he too had "bad rounding" . . . but he did say:

"But the errors are not in the art, but in the artificers. He that works with less accuracy is an imperfect mechanic; and if any could work with perfect accuracy, he would be the most perfect mechanic of all . . ."

From Philosophiae Naturalis Principia Mathematica, Author's Preface to the Reader, first published 5 July 1687.

The point that STR "MAY" be imprecise is valid. But, that being at the extreme margins, the article's tone and approach to the issue is presumptuous, and only serves to promote ignorance and misunderstanding.

Here' some advice, especially for the up and coming (it's free so you know what it's worth). This article demonstrates the "inside out view" problem. Making SQL Server or any other one software (or thing) your point of reference limits your potential. Instead develop an "outside in view". All the fundamental knowledge that went into in SQL Server was already discovered or invented. Take the time to do a small amount of "outside" research to develop breath and depth of knowledge. Do this every day . . . over time the effort pay$.


BWAA-HAA!!!... having a bad day, Mike?

It's totally amazing to me that you missed the whole intent of the article. A great number of users don't know that STR makes a conversion to float and that the related "imprecision" (which they will most certainly interpret as "bad rounding") will occur because the function gives no warning.

Take the same advice you dish out so readily. Look at things from a different point of view.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1045073
Posted Tuesday, March 8, 2011 10:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:44 AM
Points: 44, Visits: 209
Oh crap... I 'discovered' STR about a 18 months back and have been using it as LTRIM(STR(n)) for creating strings.

.... well, at least my old code was using cast or convert.

Thanks Jeff for bringing this to my attention.
Post #1075299
Posted Friday, March 11, 2011 6:10 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
Jay Hopping (3/8/2011)
Oh crap... I 'discovered' STR about a 18 months back and have been using it as LTRIM(STR(n)) for creating strings.

.... well, at least my old code was using cast or convert.

Thanks Jeff for bringing this to my attention.


Be careful, now. The code you've written may not produce computational errors due to rounding errors. Check it and make sure. As I indicated in the article, there are places where STR works just fine. And the performance problems of STR may not be a problem for you. If you're stuff is running quickly, there's no sense in possibly making a mistake with another code change.

It IS worth double-checking, though.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1076812
Posted Thursday, January 2, 2014 7:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:41 AM
Points: 309, Visits: 279
Jeff - Great article. I never liked STR() in any language. Hope that it just gets deprecated since there are many better ways to do what it does.

I would like to point out that contrary to the wording in your article STR is not actually rounding incorrectly. It is using the simplest and least accurate mode of rounding a Float that IEEE754 allows; round to zero. This mode gives us the common behavior experienced with float-to-integer conversions in mathematics. This is from the Wikipedia (not the best source on earth but one everybody can get to) article on the subject.

round toward zero (truncation; it is similar to the common behavior of float-to-integer conversions, which convert −3.9 to −3 and 3.9 to 3)
http://en.wikipedia.org/wiki/Floating_point#Rounding_modes

I am not sure why anyone would have a function use this rounding mode for float values without documenting it very well. Unless the of course the person that wrote this TSQL function had no control over its documentation
Post #1527125
Posted Thursday, January 2, 2014 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 12:55 PM
Points: 46, Visits: 339
Jeff,
Outstanding article, nicely done with great examples.
I am certain this misuse is rampant in the real world and your examples help clarify where to look for these pitfalls.
Post #1527127
Posted Thursday, January 2, 2014 9:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
There's a use case when STR() works better/is easier than other methods of converting numbers to strings - when the numbers are already typed as float or real. CAST and CONVERT have some limitations on converting float and real types to strings (scroll down to Remarks and see the second subheading, "float and real Styles").

I recently wrote about a real-world use case where STR() avoided a problem caused by these limitations of CAST and CONVERT with float and real values. While the problem could be solved by converting the float/real values to decimal values and then converting them to strings, the STR() function does the same thing without having to code the nested conversions.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1527187
Posted Thursday, January 2, 2014 9:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 11:41 AM
Points: 309, Visits: 279
Jeff Moden (1/9/2011)
[quote]Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . .

Jeff Moden (1/9/2011)

BWAA-HAA!!!... having a bad day, Mike?


Take the same advice you dish out so readily. Look at things from a different point of view.

Wow....
Someone corrects an obvious mistake in your article that that you made, points out your misinformation, and your response is to mock/laugh at them? Did you really accuse this person of not being able to understand a higher purpose of your misinformation? Did you just Chastise them for not understanding your poor word choice or poor research or your own subject matter, as them not seeing things from a different point of view?

This helps me see you from a different point of view Jeff. Very different...
Post #1527198
Posted Thursday, January 2, 2014 10:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
I was burned by this 8 years ago.....never again. Caffiene-deprived, Xanax induced, secondhand THC inhalation.....whatever the state (j/k on the chemical reference--maybe). What is amazing to me is how an article, created to help the SQL community, could incite such scathing grandstanding.

You were quite clear in the beginning of your article regarding your topic; so, don't let the negative feedback get you down. We just cannot please all the people all of the time. There is NOTHING ridiculous about this article.
Post #1527223
Posted Thursday, January 2, 2014 10:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:21 PM
Points: 1,210, Visits: 2,512
PHYData DBA (1/2/2014)
Jeff Moden (1/9/2011)
[quote]Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . .

Jeff Moden (1/9/2011)

BWAA-HAA!!!... having a bad day, Mike?


Take the same advice you dish out so readily. Look at things from a different point of view.

Wow....
Someone corrects an obvious mistake in your article that that you made, points out your misinformation, and your response is to mock/laugh at them? Did you really accuse this person of not being able to understand a higher purpose of your misinformation? Did you just Chastise them for not understanding your poor word choice or poor research or your own subject matter, as them not seeing things from a different point of view?

This helps me see you from a different point of view Jeff. Very different...


Whoa, easy there, PHYData DBA. No need to heat up a three year old flame war. Jeff may have caused some confusion with the phrase "bad rounding", but he made a valid point about the counterintuitive behavior of the STR() function in some cases (a point that obviously enlightened many readers, as previous posts in this thread demonstrate) even if he misapprehended or misrepresented the cause of that behavior. That mistake warrants a correction based on facts, such as your previous post in this thread that pointed out that STR() doesn't round "badly", it just uses the simplest and least accurate method of rounding a float value. I learned something from your post. Mike McIver, on the other hand, dished out a huge helping of sarcasm with a clear insinuation that the author of the article must be stupid, but never even explained *why* the phrase "bad rounding" was wrong. That kind of post comes off as nothing more than the poster's attempt to assert his superiority and doesn't contribute anything of value to the community. I can't blame Jeff for being a little prickly in response to an attack like that, especially considering that he is one of the most scrupulous members of this forum about backing up his statements with real evidence and data. We would all do well to remember that the purpose of these forums is to share knowledge and that petty bickering defeats that purpose.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1527225
Posted Thursday, January 2, 2014 10:47 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 36,706, Visits: 31,156
PHYData DBA (1/2/2014)
Jeff Moden (1/9/2011)
[quote]Mike McIver (1/9/2011)
Really . . . another ridiculous article! I am responding to this reader's reply to illustrate the problem. Misinformation breeds misinformation . . .

Jeff Moden (1/9/2011)

BWAA-HAA!!!... having a bad day, Mike?


Take the same advice you dish out so readily. Look at things from a different point of view.

Wow....
Someone corrects an obvious mistake in your article that that you made, points out your misinformation, and your response is to mock/laugh at them? Did you really accuse this person of not being able to understand a higher purpose of your misinformation? Did you just Chastise them for not understanding your poor word choice or poor research or your own subject matter, as them not seeing things from a different point of view?

This helps me see you from a different point of view Jeff. Very different...


I never take exception to constructive criticism. My laugh-off was to summarily dismiss words like "ridiculous" and the general tone of that particular post.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527226
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse