Hash join hint returns an error when using local variable

Question

The query below is using the StackOverflow2010 sample database:

DECLARE @Id INT = 18471

SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id

With a hash join hint, it returns 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

It works when I add OPTION(RECOMPILE) or replace the variable with a string literal.

What could be causing this issue?

(The original query was using a hash join hint to deal with a parameter sniffing issue. The query above is just a simplified example to replicate the issue.)

Answer 1

Your query:

DECLARE @Id INT = 18471

SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id

has c.UserId = u.Id and AND c.UserId = @Id.

By implication, that means @Id = u.Id so the query simplifies to:

DECLARE @Id INT = 18471

SELECT c.UserId 
FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON @Id = u.Id

But now there's no equality join predicate between the two joined tables. It's effectively:

DECLARE @Id INT = 18471

SELECT c.UserId 
FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
WHERE @Id = u.Id

Which is then logically:

DECLARE @Id INT = 18471

SELECT c.UserId 
FROM dbo.Comments AS c
CROSS JOIN dbo.Users AS u
WHERE @Id = u.Id

A hash join requires an equality join predicate. Consequently, it can't be employed to execute this query specification.

So far, this is all a variation of what Craig Freedman wrote in Implied Predicates and Query Hints back in 2009.

Since then, someone at Microsoft realised that a compilation failure in this case was confusing to users and counterproductive in general. So, it was fixed in one or more Cumulative Updates.

These days, if a compilation failure would result from this simplification, it is not applied. This is one of the rare cases where a join hint is ignored. Unfortunately, the fix was only developed for literal values like 18471, not variables or parameters*.

The exception being when OPTION (RECOMPILE) is present. This allows the parameter embedding optimization, where the runtime value of the variable or parameter is injected into the query as part of a local recompilation that does not cache a new plan or replace any existing one. With a literal now visible to the optimizer, the fix kicks in and compilation succeeds.

An odd consequence is that you can't get an estimated plan with a variable or parameter and OPTION (RECOMPILE), but the plan runs successfully when you hit "go". The parameter embedding optimization is only functional at runtime, not when generating an estimated plan.

---

* This is similar to the situation with the SelOnSeqPrj rule (doc, original problem described by me), which only worked with literals for many years. Eventually, they were bribed or threatened sufficiently to do the extra work to encompass variables and parameters. This might eventually happen for this example as well.