Just Code‎ > ‎

TSQL - Query processor could not produce a query plan because of the hints defined in this query

posted Nov 24, 2015, 5:47 AM by Peter Henell   [ updated Oct 12, 2016, 12:13 AM ]
I received this error while writing TSQLT unit tests in SQL Server 2014 (I have not tested on any other version yet).

Msg 8622, Level 16, State 1, Line 24
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 turns out to be related to returning a fixed set from a function while using LEFT HASH JOIN. The reason can be seen in the query plan. The GetCustomers function have completely removed and replaced by a constant scan. What is more is that the filter predicate have been pushed down to the scan of the Orders table. This leaves the Hash Join operator left with nothing to compute its hashes on!

What is interesting is that this only occurs when we select rows from a row value constructor. It works fine if we use a table instead. It also work fine if we add another row to the VALUES clause.

This is the code to reproduce the error and to show that it is does work using tables instead.

USE master;
GO
DROP DATABASE TestDatabase
GO
CREATE DATABASE TestDatabase;
GO
USE TestDatabase
GO

CREATE TABLE Orders(OrderID int, CustomerID int);
INSERT dbo.Orders
        ( OrderID, CustomerID )
VALUES  ( 1, 1 ),
        ( 2, 2 );

GO
CREATE FUNCTION GetCustomers()
RETURNS TABLE AS RETURN
    SELECT CustomerID 
    FROM (VALUES (1)) cu(CustomerID)
GO

-- This query now results in an ERROR
SELECT * 
FROM GetCustomers() c
LEFT HASH JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
GO

-- If we try the same again, but this time we get our rows inside the function from a table
CREATE TABLE Customer (CustomerID INT);
INSERT dbo.Customer(CustomerID)
VALUES (1);

GO
-- Alter the function to read from the table instead
ALTER FUNCTION GetCustomers()
RETURNS TABLE AS RETURN
    SELECT CustomerID 
    FROM dbo.Customer;
GO

-- This time the function will work as intended
SELECT * 
FROM GetCustomers() c
LEFT HASH JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
Comments