Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 420856
Opened: 3/4/2009 11:10:00 PM
Access Restriction: Public
Primary Feedback Item: 386810
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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.
Details
Sign in to post a comment.
Posted by Microsoft 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.

Regards,
Boris.
Posted by Microsoft on 3/16/2009 at 12:55 AM
Hello,

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.

e.g.

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.

Regards,
Boris Baryshnikov.
SQL Server Engine
Sign in to post a workaround.