I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?
TIA
SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fnameI think you can get the results you want by enclosing all your OR clauses in one set of parenthesis. If I understand correctly, your records must satisfy at least one of the OR clauses, and the AND clause:
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE ((journal.product_code IN ('ABLE'))
OR ((journal.product_code IN ('JOBS')) AND (journal.product_status IN ('12','14','15')))
OR ((products.prod_design IN ('audio')) AND (products.library_code IN ('hrss'))))
AND (journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE (contacts.educ_audio IN ('no'))))
ORDER BY contacts.lname asc,
contacts.fname
But you can clean this up a lot more
First, IN ('ABLE') is equivalent to ='ABLE', so don't muddy the waters with more parentheses than you need.
Second, many of your Parenthesis pairs are superfluous, in that they enclose only one clause. Eliminate the clutter.
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no')
ORDER BY contacts.lname asc,
contacts.fname
--Lastly, consider converting you NOT IN clause to a LEFT OUTER JOIN subquery:
SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
LEFT OUTER JOIN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no') ExcludeRecords
on journal.journal_id = ExludeRecords.journal_id
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND ExcludeRecords.journal_id is null
ORDER BY contacts.lname asc,
contacts.fname
If you take the time to develop a coding style that includes neat and consistent indenting and formatting, you will be rewarded with much clearer and more bug-free code.|||Thanks for your help.
Reason the SQL looks like it does (not indented, etc) is because that was a paste from my application. This is for an ad-hoc query tool I'm building.
As to your suggestions, they all seem to work except the last one.
Error: The column prefix 'ExludeRecords' does not match with a table name or alias name used in the query.|||Just a typo.
on journal.journal_id = ExludeRecords.journal_id
...should have been:
on journal.journal_id = ExcludeRecords.journal_id
Showing posts with label precedence. Show all posts
Showing posts with label precedence. Show all posts
Wednesday, March 7, 2012
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.googlegr oups.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.googlegr oups.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/de...oa-oz_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.
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.googlegr oups.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.googlegr oups.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/de...oa-oz_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.
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.
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.
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.
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.
Subscribe to:
Posts (Atom)