Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?

Question

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).

Answer 1

The question doesn't contain a reproduction script, but this error often arises because an implied predicate makes the join predicate redundant.

In other words, a logical implication in your query specification turns the inner join into a logical cross product (after simplification). This is not necessarily a bad thing (as people tend to assume cross products are) it just means the query specification can be simplified in that way.

A hash join requires an equality predicate. Without a suitable predicate in the query tree at the time the optimizer considers join implementation, compilation fails with an error. In an ideal world, perhaps, the optimizer would not simplify away a join predicate required to satisfy a hint.

This is my educated guess based on the textual content of the question. If you need a more detailed explanation, please provide a complete repro and specify the environment.

---

As a side issue, and in addition to the suggestions by Erik Darling, here is a deterministic numbers-only scalar function suitable for SQL Server 2016:

CREATE FUNCTION dbo.RemoveNonNumericCharacters
    (@string nvarchar(4000))
RETURNS 
    nvarchar(4000)
WITH 
    SCHEMABINDING, 
    RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
    ISNULL(
        CONVERT(nvarchar(4000),
        (
            SELECT This.ch AS [text()]
            FROM OPENJSON(N'[1' + REPLICATE(N',1', LEN(@string) - 1) + N']') AS J
            CROSS APPLY (SELECT 1 + CONVERT(integer, J.[Key])) AS V (v)
            OUTER APPLY (SELECT SUBSTRING(@string COLLATE Latin1_General_100_BIN2, V.v, 1)) AS This (ch)
            WHERE This.ch COLLATE Latin1_General_100_BIN2 LIKE N'[0123456789]'
            ORDER BY V.v
            FOR XML PATH (N'')
        )), 
        N'');
END;

That may not be the last word in efficiency, but it is interesting. You could convert your function to be deterministic by using CHARINDEX.

For SQL Server 2017 and later:

CREATE FUNCTION dbo.RemoveNonNumericCharacters
    (@string nvarchar(4000))
RETURNS 
    nvarchar(4000)
WITH 
    SCHEMABINDING, 
    --INLINE = ON, /* for 2019 */
    RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN
        REPLACE(
            TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                    N'0123456789',
                    N'XXXXXXXXXX'),
                REPLICATE(N'X', LEN(@string))), 
            N'X', N'');
END;

Both are trivial to convert to an inline table-valued function.