The value is returned instead of NULL when using function with OUTER APPLY

Question

I am getting strange results when using inline function. Here is the code:

IF EXISTS (
SELECT * FROM sys.objects AS o WHERE name = 'vendor_relation_users'
) DROP FUNCTION dbo.vendor_relation_users;
GO
CREATE FUNCTION [dbo].[vendor_relation_users]
(
    @user_name CHAR(12)
)
RETURNS TABLE
AS
    RETURN (SELECT @user_name AS user_name WHERE @user_name NOT LIKE '06%');
GO

DECLARE @u CHAR(12) = '066BDLER'
SELECT a.user_name, is_v.user_name 
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](@u) AS is_v

SELECT a.user_name, is_v.user_name 
FROM (SELECT @u AS user_name) a
OUTER APPLY (SELECT @u AS user_name WHERE @u NOT LIKE '06%') AS is_v


SELECT * FROM [dbo].[vendor_relation_users](@u)

So in the first SELECT statement I've just OUTER APPLied the function and it returns the result.

In the next statement I've took the code from function and put it straight to the OUTER APPLY statement.

And the last statement is just the direct function call.

I can't get why do the FIRST query returns the value...

Answer 1

This is a very interesting query. The behaviour of your first query depends upon whether you use OPTION (RECOMPILE) or not.

As you point out, this:

DECLARE @u CHAR(12) = '066BDLER'
SELECT a.user_name, is_v.user_name 
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](@u) AS is_v

returns this:

user_name       user_name
066BDLER        066BDLER

but if you add OPTION (RECOMPILE) like this:

SELECT a.user_name, is_v.user_name 
FROM (SELECT @u AS user_name) a
OUTER APPLY [dbo].[vendor_relation_users](@u) AS is_v
OPTION (RECOMPILE)   

you correctly get this:

user_name       user_name
066BDLER        NULL

I suspect this is due to a bug in how the query optimiser short circuits these inline functions due to cardinality estimates. If you look at the query plan for the two queries you will see that the one without the OPTION RECOMPILE just returns a constant.

Answer 2

I guess it is old bug described here. And it is Closed as Won't Fix

Please use this function like:

SELECT a.user_name, is_v.user_name 
FROM (SELECT @u AS user_name) a
OUTER APPLY (
    SELECT *
    FROM [dbo].[vendor_relation_users](a.user_name) 
    ) AS is_v

UPDATE#1

Just read the comments:

Might be the same basic issue as here stackoverflow.com/a/32414450/73226 – Martin Smith

That is it! Same issue, same link I have provided to MS Connect site.

UPDATE#2

Instead:

RETURN (SELECT @user_name AS user_name WHERE @user_name NOT LIKE '06%');

You need to use:

RETURN (SELECT CASE WHEN @user_name LIKE '06%' THEN NULL ELSE @user_name END)