Intermittant "Query processor could not produce a query plan" error when MERGE/HASH join hint provided in the query - by Roji. P. Thomas

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 420856 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 3/4/2009 11:10:00 PM
Access Restriction Public
Primary Feedback Item 386810


The query optimizer throws the following error when it gets visibility to the local variables/parameters.  I have used OPTION(RECOMPILE) to repro the behaviour, but I think the same can happen with parameter sniffing/statement level recompilation due to other reasons. 

Msg 8622, Level 16, State 1, Line 6
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.
Sign in to post a comment.
Posted by Greg L. Wright (3M) on 1/5/2015 at 2:39 PM
Also, when running this ... it works.
DECLARE @p int
DECLARE @k int
SET @p=1
SET @k=1
select *
from T1 inner merge join T2 on T1.a = T2.a
and t1.a <= @p AND T1.a >=@k
--option (recompile)

but for this it doesn't work
DECLARE @p int
DECLARE @k int
SET @p=1
SET @k=1
select *
from T1 inner merge join T2 on T1.a = T2.a
and t1.a IN (0)
--option (recompile)
Posted by Greg L. Wright (3M) on 1/5/2015 at 2:36 PM
I have this sql server and I am still able to run this Reproduction and it fails in my dev environment.

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
    Aug 19 2014 12:21:34
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Posted by Boris [MSFT] on 3/17/2009 at 2:25 PM
To comment on the resolution: the problem was fixed in the case of option (recompile) for an unrelated reason. The fix should be available in the next service pack for SQL Server 2008.

Posted by Boris [MSFT] on 3/16/2009 at 12:55 AM

Thank you for submitting the feedback!

Here's what happens in this case: we simplify this query to a Cartesian product for which we only use Loop Join so the Merge Join hint is invalid. This will happen whenever the parameters are the same (and not NULL) because the filter, which is on the join column, will be copied to the other side of the join. After that the join is a Cartesian product because the join predicate is redundant - T1.a = T2.a = 2.


select *
from T1 inner join T2 on T1.a = T2.a
and t1.a >= 2 AND T1.a <=2

Consider this as a limitation of today's optimizer model. Hints are treated as directives and if we cannot satisfy the hint, the optimizer fails. In this case, logic of the optimizer collides with the hint.

Boris Baryshnikov.
SQL Server Engine