Computer Math

Question

Computer Math

Viewing 6 posts - 16 through 20 (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

Steve Jones - SSC Editor (7/21/2016)
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.

The algebraic hierarchy of operations is pretty clear here and the order followed. Everything to the right of the += will be executed prior to the addition of the right expression to the left. Assignment operators are a form of grouping and, despite the "+" sign, "+=" is still an assignment operator that says to calculate everything in the right expression, add it to the left expression, and store the new result in the left expression.

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

Ray Herring SSCertifiable Points: 5533 More actions · Answer 2

The algebraic hierarchy of operations is pretty clear here and the order followed. Everything to the right of the += will be executed prior to the addition of the right expression to the left. Assignment operators are a form of grouping and, despite the "+" sign, "+=" is still an assignment operator that says to calculate everything in the right expression, add it to the left expression, and store the new result in the left expression.

Hi Jeff,

I did not mean the order of operations was incorrect or that SQL did not implement operator precedence correctly. However, just because an expression (TSQL, C#, VB.Net, ..) is algebraically correct does not guarantee it will return the correct value. This is particularly true with Multiplication/Division and with floating point numbers. A(B+C) = AB+ AC is always true in Algebra but not always correct in digital math.

There are many well know expressions that are very unstable in computer arithmetic. Wikipedia has a example of the quadratic equation.

What Every Computer Scientist Should Know About Floating-Point Arithmetic is an in-depth treatment of digital math.

My point is that many developers are quite naive when they implement mathematical algorithms. Combine that with the lackadaisical error handling we often see and the result is "random", "un-reproducible" errors.

We certainly need to understand how the compiler/interpreter parses expressions but we also have to understand the underlying details of how the data are actually encoded and processed.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Great question, interesting discussion.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

Ray Herring (7/21/2016)
However, just because an expression (TSQL, C#, VB.Net, ..) is algebraically correct does not guarantee it will return the correct value. This is particularly true with Multiplication/Division and with floating point numbers. A(B+C) = AB+ AC is always true in Algebra but not always correct in digital math.

Understood but having it return the incorrect value because of the limitations of precision is a whole lot different than execution in the correct algebraic hierarchical order, which is the only subject that I'm addressing.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Steve Jones - SSC Editor (7/21/2016)
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.

No, as Jeff pointed out it is very clear where assignment fits in the precedence hierarchy. Very clearly documented, on the page Jeff referenced. But even without that page, the syntax we are looking at is <variable>+=<expression> and an <expression> is a thing which denotes a value (which in non-declarative languages depends on the current state of the program) so it would be just plain crazy to try to avoid computing that expression - the syntax definition would make the precedednce clear even without separate documentation.

This precedence isn't something that was an arbitrary decision for the SQL standard, it's the precedence adopted by mathematicians for general calculi. The distinction in precedence between "=" (assignment) and "=" (comparison) is important (so we shouldn't pretend it's not documented) and goes right back to some of the earliest computer languages where you'll find that an expression like like (x:=y+z) was always interpreted as assigning the sum of y and z to x and returning that value (when did assignments cease to return a value as well as assigning it, I wonder), not assigning the value of y to x and returning the sum of that and z, simply because "+" (addition) preceded "=" (assignment).

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 6

dale_berta (7/21/2016)
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]

No, if you look at the precedence rules (as pointed out by Jeff) or even just look at the definition of the syntax of assignments using += it's obvious that the RHS has to be computed before it's used.

Tom