June 9, 2020 at 12:00 am
Comments posted to this topic are about the item The Order of Operations
June 9, 2020 at 6:52 am
That's right!
It's good to know.
Here's to force the minus precedence:
SELECT (-100.0)/(-100.0)*10.0
June 9, 2020 at 7:16 am
I don't really understand the explanation. In what was is anything "crazy" about the handling of negatives? Whatever the precedence you end up dividing a negative by a negative, which must always be positive. The only question to decide was whether multiplication or division is executed first.
June 9, 2020 at 7:27 am
So, this means that a negative number is not considered to be a "number", but a "formula".
In essence: -100.0 does NOT mean to subtract 100, but to represent a negative value.
In my opinion there is a fundamental difference between "some number minus 10" and "some number plus -10".
So yes, i would call that "crazy".
It is good to to know this 'bug' in SQL Server.
P.S.
I would expect SQL to execute :
SELECT (-100.0)/(-100.0)*(10.0)
Instead, what apparently (according to the explanation) is executed, is:
SELECT -1*((100.0)/(-1*(100.0*10.0)))
June 9, 2020 at 7:56 am
For many years at least in the UK the school system has been teaching "BODMAS" as the way to solve potentially ambiguous equations. The BBC educational assistance page about it: https://www.bbc.co.uk/bitesize/topics/z69k7ty/articles/z24ctv4
"From left to right, start with division and multiplication and continue with addition and subtraction."
-100.0/-100.0*10.0
The minus signs just indicate negative numbers and with two of them they are always cancelling out. But according the education system the problem should be solved by doing the divide and then the multiply.
-100.0 / -100.0 = 1.0
1.0 * 10.0 = 10.0
I am sure that there is a very good reason to confuse students by having software that doesn't comply with the basic mathematical principles that they have been taught in school.
June 9, 2020 at 9:05 am
It does seem somewhat crazy to me I must admit. At least it reinforces the maxim that you should always make the precedence you intend, be clearly enforced.
June 9, 2020 at 9:26 am
I really really must learn to concentrate more.
I have done this several times in past questions, now again on this one - clicked on an anwser without properly thinking and then losing the point.
It isn't that I didn't understand the question - I did, BUT I rushed into it without thinking! Such a pity there's no ROLLBACK on this forum!
Kenneth Spencer
(PS.: by the way, HarryK - Steve's explanation is absolutely fine. Any programming language with precedence rules similar to SQL Server - and most are - would give exectly the same result. Is anyone else troubled by it?)
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
June 9, 2020 at 10:48 am
I always, always, always parenthesise my operations so that the intent and the outcome are congruent. It drives other people crazy when they have to code review my work, but tough.
June 9, 2020 at 11:23 am
Yes, it is crazy.
So if I have a statement like
SET @x = -1;
Does that mean it actually does an arithmetic operation?
June 9, 2020 at 11:48 am
Can't have too many parentheses, don't know how many times I have been caught out by software not following the order of precedence.
June 9, 2020 at 12:01 pm
The BOL definitely does not explain it as per the outcome.
I will call it a bug as well.
June 9, 2020 at 12:45 pm
I don't really understand the explanation. In what was is anything "crazy" about the handling of negatives? Whatever the precedence you end up dividing a negative by a negative, which must always be positive. The only question to decide was whether multiplication or division is executed first.
What's "crazy" isn't the final sign. It's that SQL doesn't recognize unary minus. In math, and in every ordinary programming language I've ever encountered, unary operators have higher precedence than anything except parentheses. SQL doesn't recognize unary minus. If it's not a unary minus, SQL sees subtraction. Subtraction occurs after multiplication and division. So SQL treats it as if there's a set of parentheses after the division.
SELECT -100.0/-(100.0*10.0)
The minus signs still cancel, as you say, but the interpreted parentheses trump multiplication and division's normal left to right processing, and you wind up with the unexpected result of 0.1.
If unary minus were a thing, as in math and ordinary programming languages, the unary minus operator would act first, which interprets as:
SELECT (-100.0)/(-100.0)*10.0
The minus signs still cancel, but now the multiplication and division are the only operations remaining. They operate left to right, and give us a result of 10.
June 9, 2020 at 12:54 pm
What's "crazy" isn't the final sign. It's that SQL doesn't recognize unary minus. In math, and in every ordinary programming language I've ever encountered, unary operators have higher precedence than anything except parentheses. SQL doesn't recognize unary minus. If it's not a unary minus, SQL sees subtraction. Subtraction occurs after multiplication and division. So SQL treats it as if there's a set of parentheses after the division.
I don't believe that's true. SQL isn't treating either of the minus signs as subtraction - apart from anything else, there's nothing specified to subtract from.
What it's doing is simply to apply the multiplication before the division. BODMAS would lead you to expect the division to occur first
(-100.0/-100.0)*10.0
but it is actually doing the multiplication first
-100.0/(-100.0*10.0)
The way I was taught, the "DM" in BODMAS were ranked equally, so it works from left to right. My guess is that SQL is working from right to left.
June 9, 2020 at 1:04 pm
It is treating the minus signs as subtraction in precedence order.
Take them out, and the answer is 10.000...
Put the first one back in, the answer is -10.000...
But with both minus signs, the answer is 0.1000...
June 9, 2020 at 1:22 pm
Does ANY other computer language yield .1? Probably not. THIS is a serious bug. No matter what they say this kind of issue could cripple a project and probably not even be suspected.
YAG! (Yet ANOTHER Gotcha)
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply