Operator precedence bug in SQL Server 2000?

Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level
.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/d...br />
3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2> ).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.