none
Query processor could not produce a query plan because of the hints defined in this query.

    Question

  • Can anyone tell me why this query has just started to fail?  The tables haven't changed, and its been working just fine for a while now.  The error I get is:

    "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 doesn't happen all the time, just once in a while.  If I remove the query hints, it works all the time, but it runs much slower b/c it changes some of the joins to loops.

    ********************

    select (some stuff)
    from iscPetCatAgron a with(nolock)
    left outer join iscPtcAgron b with (nolock) on a.ptcID=b.ptcID
    left outer join iscPtcDetSub c with (nolock) on a.ptcDetID=c.ptcDetID
    inner join iscPhxDetCategory d with(nolock) on c.phxCatID=d.phxCatID
    cross apply dbo.Phx2Ptc(a.phxSumCat)
    where a.dt_trans between @dtStart and @dtEnd
    and a.ptcID between @rptStart and @rptEnd
    option(hash join, maxdop 4)

    Saturday, June 13, 2009 4:54 PM

Answers

  • If the query has worked with a hash join in the past, then I'm guessing the QO is optimizing out one of the join clauses.  You're joining iscPtcAgron with iscPetCatAgron on ptcID.  But you're also specifying a.ptcID between a range of values. 

    If those values are ever equal (say =1), then you'll wind up with something like this:

    where a.ptcID between 1 and 1

    which optimizes to:

    where a.ptcID = 1

    But since your join specifies that b.ptc has to equal a.ptcID, then, the QO can drop the join clause entirely and just have

    where aptcID =1 and b.ptcID = 1

    Unfortunately, that leaves you with just a cross join for those values.  But without an equijoin clause, a hash join can't operate...and thus your plan fails due to the hint.

    You should be able to solve this by writing a special case for rptStart = rptEnd, and acting appropriately.

    Michael Asher
    Saturday, June 13, 2009 5:42 PM

All replies

  • If the query has worked with a hash join in the past, then I'm guessing the QO is optimizing out one of the join clauses.  You're joining iscPtcAgron with iscPetCatAgron on ptcID.  But you're also specifying a.ptcID between a range of values. 

    If those values are ever equal (say =1), then you'll wind up with something like this:

    where a.ptcID between 1 and 1

    which optimizes to:

    where a.ptcID = 1

    But since your join specifies that b.ptc has to equal a.ptcID, then, the QO can drop the join clause entirely and just have

    where aptcID =1 and b.ptcID = 1

    Unfortunately, that leaves you with just a cross join for those values.  But without an equijoin clause, a hash join can't operate...and thus your plan fails due to the hint.

    You should be able to solve this by writing a special case for rptStart = rptEnd, and acting appropriately.

    Michael Asher
    Saturday, June 13, 2009 5:42 PM
  • That was it.  The id range was being passed as zeros somehow.  Thanks.
    Saturday, June 13, 2009 9:45 PM