June 9, 2020 at 2:55 pm
SQL reads from left to right
Example 16/4/4 is interpreted as (16/4)/4 = 1 instead of 16/(4/4)=16
SQL is using implied multiplication.
Multiplication is interpreted as having higher precedence than division, so that 1 ÷ 2x equals 1 ÷ (2x), not (1 ÷ 2)x.
So -100/-100*10 is being interpreted as (-100/(-100*10))
(-100/(-100*10)) = -100/-1000
-100/-1000 = 0.1
June 9, 2020 at 3:21 pm
The implied multiplication comes into play because of the negative sign.
Think about Algebra
-100/-100*x can be written as -100/-100x
Algebra would solve this problem by multiplying the -100 and x first.
In our case, it is treating the *10 like the x.
-100/-100x = 0.1 where x is equal to 10
June 9, 2020 at 3:48 pm
I understand how and why this works, but I do think this is inconsistent with other languages. Quick tests when I wrote this in Python and PowerShell show the answer as 10, not .1.
This is either an implementation bug or a design flaw, IMHO, and the working here should conform to what other languages do.
Note that this works as expected.
DECLARE @a NUMERIC(10,2) = -100.0
, @b NUMERIC(10,2) = -100.0,
@c NUMERIC(10,2) = 10.0
SELECT @a/@b*@c
Since we don't often write explicit unary minus signs, maybe this doesn't matter, but it could. If you change this to set all variables to positive and include the minus in the SELECT, you get 0.1
I wrote this question so that you would think about this and understand where math in SQL could be problematic.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 9, 2020 at 4:05 pm
It may not be a "bug" but, as you say, it's inconsistent and could therefore trip up the unwary (a bit like using BETWEEN for date comparison, which bit me in the backside a few years ago). Anyway, excellent question!
June 9, 2020 at 4:28 pm
This is apparently a Microsoft thing. Both Oracle and IBM DB2 put +, - , when used for signed numeric values, at highest priority. I couldn't find documentation on what ANSI SQL says about this.
June 9, 2020 at 4:32 pm
Without minus signs the expression would evaluate to 10 as division and multiplication are equal in the order of execution. Plus and minus are to follow.
I do understand the explanation and the fact that there are so many opinions in the discussion confirms my opinion that all SQL Server code is written by multiple Microsoft programmers and you never will know what a certain programmer had in mind when writing code. Microsoft programmers have as many different opinions as us in this discussion. So it is better not to leave your logic at the hands of other people, but to explicitly include your expressions in the round brackets (parentheses)
Regards,Yelena Varsha
June 9, 2020 at 5:39 pm
I agree that this is a confusing one. Fun little tidbit, changing the - to + yields the same result as the QOTD 0.1000000:
SELECT +100/+100*10.0
To me, this seems very unexpected and seems to be tied to the signs. If you put brackets around the +100 and nothing else, you get a result of 10.0, even though this shouldn't be changing the order of operations. I am really confused why SQL decides that putting signs in means it should change the order it does the operations...
EDIT - I think that it might be how SQL is applying the - and + signs... is it that SQL interprets the above query as:
SELECT +(100/+(100*10.0))
That is, the sign (+ or -) is applied to all operations to the right of the sign?
June 9, 2020 at 6:43 pm
Mathew Flower,
Excellent point. In most programming products, the division operator always took precedence over the multiplication. The parser should have recognized that two consecutive operators were present (division followed by minus sign). That should have been sufficient to treat the following number as a negative number (without attempting to do a subtraction operation). The first minus sign was also NOT an arithmetic operation. Therefore the first two numbers were negative. Thus the division operation should have been calculated FIRST. The answer is 10.
This is the thing that really annoys me about Microsoft. They are SO arrogant. Very frequently choosing to veer away from the obvious or the intuitive. No wonder they make out like bandits in forcing so many users of their products in having to take courses to make any sense of their protocols.
June 9, 2020 at 7:30 pm
Just tried that in Python, in Jupyter Notebooks
print(-100.0/-100.0*10.0)
The output is 10.0
Also tried in Excel entering in the Formula bar
=-100.0/-100.0*10.0
The cell contain the value of 10. Excel is a Microsoft product as well as SQL Server, so I would expect consistency.
Regards,Yelena Varsha
June 9, 2020 at 7:53 pm
Just tried that in Python, in Jupyter Notebooks
print(-100.0/-100.0*10.0)
The output is 10.0
Also tried in Excel entering in the Formula bar
=-100.0/-100.0*10.0
The cell contain the value of 10. Excel is a Microsoft product as well as SQL Server, so I would expect consistency.
This may be leftover from some really old version of SQL Server, and never changed "for compatibility reasons." My take is give us a switch we can SET, and let us choose.
June 10, 2020 at 7:54 pm
I remember this bothering me with Sybase in the mid- 90's and with SQL 6.5 through to today.
something i learned writing COBOL on mainframes was to always, always use parentheses, and that has stuck with me...
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
June 10, 2020 at 11:11 pm
I tried the code in a variety of database engines (Db2, Oracle, MySQL, PostgeSQL, Hana, Teradata) and the answer is 10.
June 11, 2020 at 4:28 pm
The only reason why I got it right was because it has bitten so many people that work in every company that I've ever worked in including me back in my 6.5 days. So far as I'm concerned, it IS a bug but, if you manually try the same formula on the Windows Calculator, you'll find that SQL Server isn't the only place where this happens except the Windows calculator comes up with an even odder answer. If you paste the formula, it changes the formula to come up with the correct answer. It's why I almost always use parentheses for all but the simplest of formulas.
--Jeff Moden
June 11, 2020 at 4:48 pm
I think windows calculator gets it right if you use the +/- button for the negative numbers:
-100 ÷ -100 × 10 = 10
If you key in - before the 100s it thinks you have changed your mind about the divide and wanted to subtract instead giving
0 - 100 - 100 × 10 =? -2000
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply