Computer Math

Question

Computer Math

Steve Jones - SSC Editor

SSC Guru

Points: 736352
More actions
July 20, 2016 at 9:48 pm

#72650

Comments posted to this topic are about the item Computer Math
July 20, 2016 at 11:27 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 1

Agreed. There's not a "good" official MS reference that explicitly states how such compound operators work with respect to the right-hand expression but... if you make the realization that it's still an "assignment operator", the following explains it all when you realize that the assignment operator is last on the operator precedence totem pole, which causes functionality exactly like your parenthetical example.

https://msdn.microsoft.com/en-us/library/ms190276.aspx

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

Good to know, thanks. Missing parenthesis trip up a lot of new programmers.

dale_berta Ten Centuries Points: 1327 More actions · Answer 3

Interesting. Is this in every release of T-SQL, or could this vary?

I only ever use the += with single values on the right-hand side, so I haven't encountered this. Seems like if you have an expression on the right, you should put it in parentheses for clarity, even though they're not actually required.

[spelling]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Nice question with a very good explanation.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 5

I only ever use the += with single values on the right-hand side, so I haven't encountered this. Seems like if you have an expression on the right, you should put it in parentheses for clarity, even though they're not actually required.

IMO that would be best practice. Mixing the "compound operators" (e.g. +=) with "regular" operators is confusing and should be avoided.

sknox SSChampion Points: 12388 More actions · Answer 6

Jeff Moden (7/20/2016)
Agreed. There's not a "good" official MS reference that explicitly states how such compound operators work with respect to the right-hand expression but... if you make the realization that it's still an "assignment operator", the following explains it all when you realize that the assignment operator is last on the operator precedence totem pole, which causes functionality exactly like your parenthetical example.
https://msdn.microsoft.com/en-us/library/ms190276.aspx

That's exactly what's happening. Here's a counter-example which shows the other side of the coin:

declare @a smallint = -5,

@b-2 smallint = 32767,

@C smallint = 4;

set @a += @b-2 + @C;

select @a;

The net result is 32767 + 4 - 5 = 32766, which can fit into a smallint. But because the assignment happens last, the addition causes an overflow error.

Got the logic right; did the math right; clicked on the wrong answer...:doze:

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Good question. It is the only logical order to process that statement for me. I guess my brain just works like that or something. I spent quite a bit of time staring at that question trying to figure out the catch. It only makes sense you have to calculate the right side first and then add the result of that to the left side.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736352 More actions · Answer 8

I thought this was interesting as I hadn't ever considered the user of multiple items on the right side of the assignment.

j-1064772 SSCertifiable Points: 5316 More actions · Answer 9

Excellent question ! Even though my answer is wrong, I have learned something I never even suspected.

I azzumed that the fundamental property of addition : commutativity, i.e. the order of the terms does not change the result, would have been respected. Even though I already knew that subtracting float type values cannot yield exact results because of the spurious decimals way at the lowest end of the precision.

This question-of-the-day has the merit of reminding me not to bank on the overflow check as a means of trapping bugs. On a small set of test values everything may test out OK. Then fails on a production database where the worst case happens.

If there really is a need to reduce the space taken by a column, then the developer is responsible for verifying that the restricted range number will not produce intermittent bugs.

Funny that automatic spell check objected to my proper spelling of "assume" because I had italicized the first three letter to emphasize the laziness and carelessness of assuming without checking first. Is ***ume better than leaving the original letters ? Even if it does not print explicitely, I did put it in and everybody else will read it for what it is.

Revenant SSC-Forever Points: 42467 More actions · Answer 10

Revenant

SSC-Forever

Points: 42467

July 21, 2016 at 8:59 am

#1891356

Interesting, very interesting. Thanks, Steve!

Ray Herring SSCertifiable Points: 5533 More actions · Answer 11

The Range of a Small int is -32768 to 32767. This is an artifact of using 2's complement.

Your small demo is a great illustration of a serious problem many modern developers seem blissfully unaware of. Loss of precision in computer arithmetic is one of the bugs that "self educated" or "on the job trained" developers are often stunned to learn about. Unfortunately, the same is also true for many college educated computer professionals. 🙁

One of the advantages of being forced to do math oriented assembly level programming is that it emphasizes the importance of the order operations and knowing the range of the various operands.

Learn early that computer arithmetic is not always commutative and sometimes it is not even distributive 🙂

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 12

I can't agree that the computer math was any different than regular math in this case. The computer math was spot on. += is an assignment operator... not just a mathematical operator. It followed the correct mathematical hierarchy according to published documentation and did the assignment at the very end.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736352 More actions · Answer 13

Jeff Moden (7/21/2016)
I can't agree that the computer math was any different than regular math in this case. The computer math was spot on. += is an assignment operator... not just a mathematical operator. It followed the correct mathematical hierarchy according to published documentation and did the assignment at the very end.

Well, I'd disagree. Because orders matter here and storage matters. Is the math

x = x + y + z

In that case, if you ran the calculation, x + y causes an error based on the data types. What we have is

x = x + (y + z)

which is implied in computer math. However, one could easily read this statement as being

x = (x + y) + z

If my compiler operated in that way, I'd overflow. Certainly I think some people would see x = x + y as the first operation. C has done some things like this in the past, where you embed assignments in otherwise innocuous statements, and a bug is created.