Error when using views on a linked server

I have this query:

SELECT
    -
FROM -.dbo.tb1
WHERE 
    -IN
    (
        SELECT 
            CASE 
                WHEN EXISTS
                (
                    SELECT * 
                    FROM -.dbo.tb2U1 WITH (NOLOCK)
                    WHERE col1 = '8614'
                )
                THEN
                (
                    SELECT TOP (1)
                        CASE WHEN ISNULL(col2,0) IN (1,2,3,6,8) 
                            THEN '2'
                            ELSE '1'
                        END AS col3
                    FROM -.dbo.tb1U1 WITH (NOLOCK)
                    LEFT join -.dbo.tb3U2 WITH (NOLOCK)
                        ON U1.-= U2.-  
                    WHERE 
                        -= '8614'  
                    ORDER by 
                        -DESC
                )  
                ELSE '3'
            END
    );

When I run it on -, it's ok. It returns me what I want:
col2
-------------------------------------------------- Value


But if I run it on - (we have the same database -, but with no tables, only views redirecting to - with the same names of the tables) it shows me this error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared.
Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a
context where a condition is expected, near 'THEN'.


The views to the linked server are of the form:
SELECT *
FROM -.-.dbo.tb2


If I write the query without the SELECT here:
...Where -in ( NO SELECT HERE Case When Exists
(Select * from -.dbo...


...it runs perfectly.

I just want to know why this happens. How could this be possible? It's the same query.

This is a bug, though one that only arises in very specific circumstances.

SQL Server uses a component called Distributed Query (DQ) to build T-SQL commands to run against the linked server. The optimizer transforms a CASE expression including a sub-select to a form that works perfectly well on local objects, but which is not translated correctly to remote query form (in this particular circumstance).

The bug results in a remote node being generated that requires the result of local computations. An internal query tree is created that expects these computed values as parameters, but no parameters are generated. This bug only occurs if the sub-selects are not correlated to the outer query (as is the case here), because a simplification that is only safe for a local query is applied.

DQ generates the remote query with parameters (using standard ? syntax), but no parameters are passed, causing the "statement cannot be prepared" error (a prepared statement is the technical name for a parameterized statement).

If subquery contained an outer reference (correlation) instead of literal values, the local-only simplification would not be applied, and the query tree would be correct.

Reproduction

Create a linked server SQL2008 to another instance of SQL Server 2008, which contains the AdventureWorks sample database. Create the following views on the local instance:

CREATE VIEW dbo.Product AS
SELECT * 
FROM SQL2008.AdventureWorks.Production.Product AS P;
GO
CREATE VIEW dbo.TransactionHistory AS
SELECT *
FROM SQL2008.AdventureWorks.Production.TransactionHistory AS TH;

Generate an estimated execution plan for the following query (running it will just produce the error message, of course):

SELECT 
    P.ProductID 
FROM dbo.Product AS P
WHERE P.ProductID IN
(
    SELECT -- Comment this
        CASE 
            WHEN EXISTS 
                (
                    SELECT * 
                    FROM dbo.TransactionHistory AS TH 
                    WHERE TH.ProductID = 1 -- Not correlated
                )
            THEN 
                (
                    SELECT TOP (1) 
                        TH.TransactionID 
                    FROM dbo.TransactionHistory AS TH 
                    WHERE 
                         -- Not correlated
                        TH.ProductID = 1
                    ORDER BY 
                        TH.TransactionID DESC
                )
            ELSE 0
        END
);

The estimated query plan below highlights the problem operator:

The Remote Query property of that operator is:

SELECT "Tbl1001"."ProductID" "Col1019" 
FROM "AdventureWorks"."Production"."Product" "Tbl1001" 
WHERE CASE WHEN ? THEN ? ELSE (0) END = "Tbl1001"."ProductID"

Notice the ? parameter markers. The unresolved parameters generate the second error message when the remote server tries to compile WHEN ? THEN ?.

If you comment out the marked SELECT, the transformation in question is not applied, and the erroneous remote query is not generated.

Equally, replacing either of the TH.ProductID = 1 instances with a correlation, for example TH.ProductID = P.ProductID also generates correct remote queries.

This bug reproduces in all current versions of SQL Server, up to and including SQL Server 2014 RTM CU4.

StackExchange Database Administrators Q#89181, answer score: 6