I came across this issue recently and couldn't find any discussion of it online.
The query below
DECLARE @S VARCHAR(1) = ''; WITH T AS (SELECT name + @S AS name2, * FROM master..spt_values) SELECT * FROM T T1 INNER JOIN T T2 ON T1.name2 = T2.name2;
Always gets a nested loops plan
Attempting to force the issue with
INNER HASH JOIN or
INNER MERGE JOIN hints produces the following error.
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
I found a workaround which does allow hash or merge joins to be used - wrapping the variable in an aggregate. The plan generated is significantly lower costed (19.2025 vs 0.261987)
DECLARE @S2 VARCHAR(1) = ''; WITH T AS (SELECT name + (SELECT MAX(@S2)) AS name2, * FROM spt_values) SELECT * FROM T T1 INNER JOIN T T2 ON T1.name2 = T2.name2;
What is the reason for this behaviour? and is there a better workaround than the one that I found? (that perhaps doesn't require the extra execution plan branches)