SQL Exception with Bitwise AND ...

31 Aug 2006 4:25 PM

Brent D.

I believe there is a bug in the SQL 2000 parser that causes the following SQL
exception:

System.Data.SqlClient.SqlException: Invalid operator for data type. Operator
equals boolean AND, type equals numeric.
Invalid operator for data type. Operator equals boolean AND, type equals
numeric.

when a statement such as the following:

SELECT some_field
   WHERE field1 > 10
    AND ( field2 & -2147483648 <> 0 )

is used.   However, if the integer constant (-2147483648) is changed to the
HEX form 0xFFFFFF the query will execute just fine.

In the above example, field1 and field2 are both INT (NOT NULL). I have run
numerous tests and the query will only fail if the Integer constant
-2147483648 is present in the query.

Microsoft -- any comments?

31 Aug 2006 6:19 PM

David Browne

<DIV>"Brent D." <Brent D*@discussions.microsoft.com> wrote
in message

news:EA986444-3B22-4F06-AD61-19E92DB800DD@microsoft.com...</DIV>>I believe

there is a bug in the SQL 2000 parser that causes the following SQL
Show quote

> exception:
>
> System.Data.SqlClient.SqlException: Invalid operator for data type.
> Operator
> equals boolean AND, type equals numeric.
> Invalid operator for data type. Operator equals boolean AND, type equals
> numeric.
>
> when a statement such as the following:
>
> SELECT some_field
>   WHERE field1 > 10
>    AND ( field2 & -2147483648 <> 0 )
>
> is used.   However, if the integer constant (-2147483648) is changed to
> the
> HEX form 0xFFFFFF the query will execute just fine.
>
> In the above example, field1 and field2 are both INT (NOT NULL). I have
> run
> numerous tests and the query will only fail if the Integer constant
> -2147483648 is present in the query.
>

It's a bug. I passed it on.

Oh, and the hex equivalent of -2147483648 is 0x80000000.

The easiest workaround would be to explicitly cast the literal to int, and
override the interpretation of the literal.

AND ( field2 & cast(-2147483648 as int) <> 0)

David

31 Aug 2006 6:34 PM

Brent D.

Hi David,

Thanks for confirmining this as a bug -- and yes your right on the HEX value
and using the CAST function -- I also tried CONVERT which seems to work as
well, ie:

SELECT some_field
WHERE field1 > 10
AND ( field2 & CONVERT( INT, -2147483648 ) <> 0 )

Brent D.

Show quote

"David Browne" wrote:

>
>
> <DIV>"Brent D." <Brent D*@discussions.microsoft.com> wrote
> in message
> news:EA986444-3B22-4F06-AD61-19E92DB800DD@microsoft.com...</DIV>>I believe
> there is a bug in the SQL 2000 parser that causes the following SQL
> > exception:
> >
> > System.Data.SqlClient.SqlException: Invalid operator for data type.
> > Operator
> > equals boolean AND, type equals numeric.
> > Invalid operator for data type. Operator equals boolean AND, type equals
> > numeric.
> >
> > when a statement such as the following:
> >
> > SELECT some_field
> >   WHERE field1 > 10
> >    AND ( field2 & -2147483648 <> 0 )
> >
> > is used.   However, if the integer constant (-2147483648) is changed to
> > the
> > HEX form 0xFFFFFF the query will execute just fine.
> >
> > In the above example, field1 and field2 are both INT (NOT NULL). I have
> > run
> > numerous tests and the query will only fail if the Integer constant
> > -2147483648 is present in the query.
> >
>
> It's a bug. I passed it on.
>
> Oh, and the hex equivalent of -2147483648 is 0x80000000.
>
> The easiest workaround would be to explicitly cast the literal to int, and
> override the interpretation of the literal.
>
> AND ( field2 & cast(-2147483648 as int) <> 0)
>
> David
>
>
>
>