Internal Query Processor Error: The query processor could not produce a query ..

  • R.P.Rozema

    SSChampion

    Points: 12300

    Hi all, I have hit a problem in SQL server 2016 and I don't have access to 2017 to see if the same problem occurs there to. If someone would be so kind as to try the below query in a SQL 2017 environment and report back here I would be very grateful.

    In SQL 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64), Std. Ed., the query results in an error message:

    Msg 8624, Level 16, State 17, Line 16

    Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

    With it a stackdump is created and logged in the sql log. So better not run it in a production environment...

    The query has no table requirements and no side effects, so it can be run in any database. This is a very simplified version of an extremely complex query. The crucial part seems to be to generate an xml type in an outer apply, which then gets processed by the .nodes() function.

    Here's the query I would like to hear your results for:

    select
    (
    select top 1
    n.e.value('@val','int')
    from t2.xmlfragment.nodes('/*') n(e)
    )
    from (
    select 'somevalue'
    ) t1 (v)
    outer apply (
    select
    t.val as [@val]
    from (
    select 1 as [val]
    ) t
    for xml path('el'), type
    ) t2 (xmlfragment)

    • This topic was modified 2 months ago by  R.P.Rozema. Reason: removed order by as it is not needed to reproduce the problem
    • This topic was modified 2 months ago by  R.P.Rozema. Reason: Also union all can be left out and still the dump occurs



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • R.P.Rozema

    SSChampion

    Points: 12300

    Using the outer apply is causing this. Replacing it by a cross apply avoids the problem. Now I just need to make sure that I won't filter out any rows by the cross apply...

     

    Actually I found that this problem exists since at least 2014 in SQL 2008 SP2: https://dba.stackexchange.com/questions/78659/the-query-processor-could-not-produce-a-query-plan

    And I was able to reproduce it myself in SQL 2012 too.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • Grant Fritchey

    SSC Guru

    Points: 395264

    Interesting. I just validated that this will fail on SQL Server 2019 as well as SQL Server 2017. The logic of the OUTER APPLY must be somehow invalid. I'm not sure.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply