Post date: Nov 24, 2015 1:47:22 PM
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;GODROP DATABASE TestDatabase GOCREATE DATABASE TestDatabase;GOUSE TestDatabase GOCREATE TABLE Orders(OrderID int, CustomerID int);INSERT dbo.Orders ( OrderID, CustomerID )VALUES ( 1, 1 ), ( 2, 2 );GOCREATE FUNCTION GetCustomers()RETURNS TABLE AS RETURN SELECT CustomerID FROM (VALUES (1)) cu(CustomerID)GO-- This query now results in an ERRORSELECT * FROM GetCustomers() cLEFT 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 tableCREATE TABLE Customer (CustomerID INT);INSERT dbo.Customer(CustomerID)VALUES (1);GO-- Alter the function to read from the table insteadALTER FUNCTION GetCustomers()RETURNS TABLE AS RETURN SELECT CustomerID FROM dbo.Customer;GO-- This time the function will work as intendedSELECT * FROM GetCustomers() cLEFT HASH JOIN dbo.Orders o ON c.CustomerID = o.CustomerID