The query processor could not produce a query plan

Question

I'm getting this message:

Msg 8624, Level 16, State 17, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

This is what I run

;WITH TableA AS
(
   SELECT 101 as A_ID
)
,TableB AS
(
   SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses 
   UNION ALL
   SELECT 2 , 101  , 'YYY'  
   UNION ALL
   SELECT 3 , 101  , 'ZZZ'  
   UNION ALL
   SELECT 4 , 102  , 'AAA' 
)
SELECT
    A_id
    ,x.x.value('(./text())[1]','varchar(500)') AS fieldX
FROM
    TableA AS A
OUTER APPLY
    -- CROSS APPLY
    (SELECT
         ',' + B_Courses
     FROM
         TableB AS B            
     WHERE 
            1=1
            AND B.B_A_ID  = A.A_ID          
     FOR XML PATH(''),TYPE
    ) x(x)

With OUTER APPLY, I get the message shown above.

With CROSS APPLY, everything works nicely.

I resolve by adding in select this :

STUFF((SELECT ','+B_Courses
    FROM
         TableB AS B    
    WHERE 
        B.B_A_ID  = A.A_ID
    FOR XML PATH(''),TYPE).value('(./text())[1]' ,'VARCHAR(500)'),1,1,'')               

Tested on:

Edition           ProductVersion    ProductLevel
Express Edition     9.00.3042.00    SP2

and

Edition                     ProductVersion  ProductLevel
Developer Edition (64-bit)  11.0.2100.60    RTM

Any idea way is this happening?

Thanks

Answer 1

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is no real need for the APPLY, eg

;WITH TableA
AS
    (
    SELECT 101 as A_ID
    ), TableB
AS
    (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL
    SELECT 2 , 101  , 'YYY'  UNION ALL
    SELECT 3 , 101  , 'ZZZ'  UNION ALL
    SELECT 4 , 102  , 'AAA' 
    )
SELECT
     a.A_id,
    (
    SELECT ',' + B_Courses
    FROM TableB AS B            
    WHERE B.B_A_ID  = A.A_ID          
    FOR XML PATH(''),TYPE
    ).value('.', 'VARCHAR(20)')
FROM TableA A

NB These errors will also be generating 20MB stack dumps (.mdmp) files to your Log directory.