Sql-server – Issue with CAST using linked servers (possible bug?)

So I've got a some SQL Server 2008 DBs arranged in a star schema. Server A is the one users submit queries to. On Server A there is a DPV called Detection that's basically a UNION of table Detection on servers X, Y and Z.

One of the columns in Detection is a BIGINT called infoFlag that is basically a bitmask. Users need to use bitwise operators to filter their queries on this mask. This works fine in situation such as this:

    SELECT TOP 500 colA colB from Detection
    WHERE (infoFlag & 16 <> 0)

But if you use a mask that is too big for an INT, for example:

    SELECT TOP 500 colA, colB 
    FROM Detection
    WHERE (infoFlag & 3758333096 <> 0)

we get an error:

    System.Exception: The data types bigint and numeric are incompatible in the '&' operator. 

Fair enough. A simple cast should fix the problem, right? It should, but it doesn't.

    SELECT TOP 500 colA, colB 
    FROM Detection
    WHERE (infoFlag & CAST(3758333096 as BIGINT) <> 0)

gives the same error.

While troubleshooting this, I ran the query directly on the Detection table on Server X and it worked fine (with the cast.) So I figured that the problem had something to do with the linked servers.

So I fired up profiler on Server X and submitted the query against the Detection DPV on Server A. Here is what came through:

    SELECT TOP (5000) "Tbl1002"."colA" "Col1008","Tbl1002"."colB" "Col1009" 
    FROM "myDbName"."dbo"."Detection" "Tbl1002" 
    WHERE ("Tbl1002"."infoFlag"&(3758333096))<>(0) 

It seems that the CAST statement is not getting propogated to the remote server!

I did figure out a workaround, which is to do this:

    declare @a bigint
    set @a = 3758333096

    SELECT TOP 500 colA, colB 
    FROM Detection 
    WHERE (infoFlag & @a <>  0) 

However I am concerned that this might be a bug. Or is there something I'm missing?

thank you!

–suz (@capnsue)

Version: Microsoft SQL Server 2008 (SP1) – 10.0.2710.0 (X64) Apr 7 2009 20:36:04 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

EDIT: Here is the definition of the Detection table. http://pastebin.com/ErGJ9fSG