problem with & bit operator

Question

problem with & bit operator

nimdil

Say Hey Kid

Points: 676
More actions
April 2, 2009 at 2:21 am

#219600

I have this simple problem:
I can perform following queries:
SELECT COUNT(1) FROM
WHERE [col] & POWER(-2,29) != 0
SELECT COUNT(1) FROM
WHERE [col] & POWER(2,30) != 0
SELECT POWER(-2,31) & 1
but I can't perform:
SELECT COUNT(1) FROM
WHERE [col] & POWER(-2,31) != 0
despite POWER(-2,31) being a correct integer value. The error says that numeric and integer are incompatible types in bit & operator. Gosh. Even the following:
SELECT COUNT(1) FROM
WHERE CAST([col] AS INT) & CAST(POWER(-2,31) AS INT) != CAST(0 AS INT)
does not work (which shouldn't really make any difference because POWER(-2,31) and [col] are integers).
The [col] definition is INT, NOT NULL
I understand that I can check for this bit with clasue:
...[col] < 0
but anyway this is really weird. Is there any workaround - or even better flaw in my code?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3661 More actions · Answer 1

I guess it is the problem with values inside [col] column. Some how it is exceeding the INT size limit. Please make sure [col] is INT not BIGINT.

Regards,
Nitin

nimdil Say Hey Kid Points: 676 More actions · Answer 2

I checked it three times - it is just INT, not BIGINT. Besides i think BIGINT is actually compatible. Anyway - this is just ordinary INT column. And also - if the column exceeds INT limits other queries (with ... & power(2,30) for example) should fail in the same way, shouldn't they?

Anyway - I will say it once again - this is INT column.

And additionall - weird - thing.

I work with 2 (physically 2) computers with SQL Server 05 - let call them A and B.

I have linked computer B to A so that I can perform queries on tables on computer B working on connection to computer A. Then - the previosly mentioned query was performed on connection to computer A and properly addressing table on computer B.

If I connect directly to computer B the query is performed properly. Weird.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

This works fine for me...

IF ( OBJECT_ID( 'tempdb..#SomeTable' ) IS NOT NULL )

DROP TABLE #SomeTable

CREATE TABLE #SomeTable

(

SomeColumn1 INT NOT NULL,

SomeColumn2 INT NOT NULL

)

INSERT #SomeTable( SomeColumn1, SomeColumn2 )

SELECT TOP 10 ( CHECKSUM( NEWID() ) ), ( CHECKSUM( NEWID() ) )

FROM sys.columns

SELECT COUNT( * )

FROM #SomeTable

WHERE SomeColumn2 & POWER( -2, 31 ) != 0

SELECT COUNT( * )

FROM #SomeTable

WHERE SomeColumn2 & POWER( -2, 29 ) != 0

--Ramesh

nimdil Say Hey Kid Points: 676 More actions · Answer 4

Try to link remote server and do it with

[remote_computer].[database].[schema].

instead of

[database].[schema].

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

nimdil (4/2/2009)
Try to link remote server and do it with
[remote_computer].[database].[schema].
instead of
[database].[schema].

It still works fine...

EXECUTE ( 'SELECT COUNT( * ) FROM TestServer.RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 29 ) != 0' )

SELECT * FROM OPENQUERY( TestServer , 'SELECT COUNT( * ) FROM RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 29 ) != 0' )

Edit:

1. Added OPENQUERY example

--Ramesh

nimdil Say Hey Kid Points: 676 More actions · Answer 6

As I stated previously - it works fine with POWER(-2,29); It crushes only with POWER(-2,31)

If it works fine with you - well. Something is unfortunately wrong with my configuration

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

nimdil (4/2/2009)
As I stated previously - it works fine with POWER(-2,29); It crushes only with POWER(-2,31)
If it works fine with you - well. Something is unfortunately wrong with my configuration

Aaah, now I see, there's indeed a issue with EXECUTE (not linked server version)

-- This raises an error "The data types int and numeric are incompatible in the boolean AND operator"

EXECUTE ( 'SELECT COUNT( * ) FROM TestServer.RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 31 ) != 0' )

-- This executes without any error

EXECUTE ( 'SELECT COUNT( * ) FROM RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 31 ) != 0' ) AT TestServer

-- This executes without any error

SELECT * FROM OPENQUERY( TestServer , 'SELECT COUNT( * ) FROM RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 31 ) != 0' )

--Ramesh

nimdil Say Hey Kid Points: 676 More actions · Answer 8

Wow, thanks for the solution. It makes code look simpler.

Do you know why the first syntax returns error? Shouldn't they be equivalent or s.t.?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 9

nimdil (4/2/2009)
Wow, thanks for the solution. It makes code look simpler.
Do you know why the first syntax returns error? Shouldn't they be equivalent or s.t.?

I'm still trying find why it failed at first?:cool:;-) I'll ping back when I get the answer for it!!

--Ramesh