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...
> > 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
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:
>> <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
> 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/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_3qpf.asp
>> 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), & (Bitwise AND)
>> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
>> ^ (Bitwise Exlusive OR), | (Bitwise OR)
>> NOT
>> AND
>> ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
>> = (Assignment)
>> When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression. For example, in the expression used in the SET statement of this example, the subtraction operator 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.

OPENXML Question

How do I return the number of rows inserted/updated using OPENXML?
I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
Generic update example trying to return the # of rows updated:
declare @.i int
exec sp_xml_preparedocument @.i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
update test
set test.xmlData = ox.xmlData
from OpenXml(@.i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
RETURN @.@.ROWCOUNT --Returns a 0
exec sp_xml_removedocument @.i
Thanks,It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @.@.rowcount as see.
"Robert" wrote:

> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>|||Check my procedure here:
It works for me (rowcount stuff that is)
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@.xml_doc TEXT ,
@.numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @.hdoc INT -- handle to XML doc
DECLARE @.errorTracker int -- used to "remember" the @.@.ERROR
DECLARE @.updateRowCount int
DECLARE @.insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @.titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @.hdoc(with is the holder for
@.xml_doc), and put it IN a variableTable
INSERT @.titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@.hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit
)
--select * from @.titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @.titleupdate
SET
alreadyExists = 1
FROM
@.titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@.titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @.updateRowCount = @.@.ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@.titleupdate
WHERE
alreadyExists = 0
Select @.insertRowCount = @.@.ROWCOUNT
select @.numberRowsAffected = @.insertRowCount + @.updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E-82CE-FE91CCFDC5AF@.microsoft.com...
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>