I'm hitting some weird behavior on a table valued function when used with OUTER APPLY. I have a simple inline function that returns some simple calculations based on a row in another table. When the input values for the TVF are hard-coded scalars, there is no row returned. When I take the same scalars and make a single row out of them in a CTE, then feed them in as columns using CROSS APPLY, no result set. When I do the same with OUTER APPLY, I get 1 row (as expected), but two of the output columns are NULL and the other two NOT NULL. Based on BOL, that shouldn't happen with an OUTER APPLY. Is this a user error? I wrote a simple version to demonstrate the issue.
--Test set-up
CREATE FUNCTION dbo.TVFTest
(
@keyID INT,
@matchValue1 MONEY,
@matchValue2 MONEY
)
RETURNS TABLE AS RETURN
(
WITH TestRow
AS (SELECT @keyID AS KeyID,
@matchValue1 AS MatchValue1,
@matchValue2 AS MatchValue2)
SELECT KeyID,
MatchValue1,
MatchValue2,
CASE
WHEN MatchValue1 <> MatchValue2
THEN 'Not equal'
ELSE 'Something else'
END AS MatchTest
FROM TestRow
WHERE MatchValue1 <> MatchValue2
)
GO
Query
WITH Test AS
(
SELECT 12 AS PropertyID,
$350000 AS Ap1,
350000 AS Ap2
)
SELECT LP.*
FROM Test T
OUTER APPLY dbo.TVFTest
(
T.PropertyID,
T.Ap1,
T.Ap2
) LP;
Results
+-------+-------------+-------------+-----------+
| KeyID | MatchValue1 | MatchValue2 | MatchTest |
+-------+-------------+-------------+-----------+
| 12 | 350000.00 | NULL | NULL |
+-------+-------------+-------------+-----------+
Using Cross Apply returns no rows as expected. Also removing the CTE and using inline constants returns no row.
--Scalars, no row here...
SELECT LP.*
FROM dbo.TVFTest
(
12,
$350000,
350000
) LP;
