I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other:
WITH CTE1 AS
(
SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
FROM PhoneNumbersTable
GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
SELECT CTE1.PhoneNumberCleaned
FROM CTE1
INNER HASH JOIN PhoneNumbersTable
ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)
SELECT PhoneNumberCleaned
FROM CTE2
Notice the HASH JOIN
happening inside CTE2
. This all works well and good so far.
If I add the following WHERE
clause to the final SELECT
query so my entire query now becomes:
WITH CTE1 AS
(
SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
FROM PhoneNumbersTable
GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
SELECT CTE1.PhoneNumberCleaned
FROM CTE1
INNER HASH JOIN PhoneNumbersTable
ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)
SELECT PhoneNumberCleaned
FROM CTE2
WHERE PhoneNumberCleaned = 'SomePhoneNumberInTheResultSet' -- E.g. 7183998888
Then I get the classic error:
Msg 8622, Level 16, State 1, Line 50 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.
This only happens if the value I use in my WHERE
clause actually exists in the result set. If I pick any value that doesn't exist, then I don't receive the above error.
Now obviously my example is kind of stupid for what's going on, and I can re-write it a few different ways to probably fix it, but I'm more so curious as to why does this happen? If the SQL Server Engine is able to produce a query plan that returns all the records, why is it unable to add an additional filter operator at the end of that query plan for the scalar value I'm filtering on in my WHERE
clause?
Here is the blackbox code of my dbo.RemoveNonNumericCharacters
function (note I didn't write this):
CREATE FUNCTION [dbo].[RemoveNonNumericCharacters] (@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @strText) > 0
BEGIN
SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
END
RETURN @strText
END
Also note that the column PhoneNumber
in the PhoneNumbersTable
is of type VARCHAR(20)
.