Значение возвращается вместо NULL при использовании функции с OUTER APPLY

Question

Я получаю странные результаты при использовании встроенной функции. Вот код:

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)

Итак, в первом операторе SELECT я просто применил функцию OUTER, и она возвращает результат.

В следующем утверждении я взял код из функции и поместил его прямо в оператор OUTER APPLY.

И последнее утверждение - это просто прямой вызов функции.

Я не могу понять, почему запрос FIRST возвращает значение...

Answer 1

Это очень интересный запрос. Поведение вашего первого запроса зависит от того, используете ли вы OPTION (RECOMPILE) или нет.

Как вы указываете, это:

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

возвращает это:

user_name       user_name
066BDLER        066BDLER

но если вы добавите OPTION (RECOMPILE) как это:

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)   

Вы правильно поняли это:

user_name       user_name
066BDLER        NULL

Я подозреваю, что это связано с ошибкой в ​​том, как оптимизатор запросов замыкает эти встроенные функции из-за оценок количества элементов. Если вы посмотрите на план запроса для двух запросов, то увидите, что тот, у которого нет OPTION RECOMPILE, просто возвращает константу.

Answer 2

Я думаю, что это старая ошибка, описанная здесь. И это Closed as Won't Fix

Пожалуйста, используйте эту функцию как:

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

Просто прочитайте комментарии:

Может быть та же самая основная проблема, что и здесь /questions/27083479/outer-apply-vozvraschenie-stolbtsov-neozhidanno-not-null-esli-net-sovpadenij/27083481#27083481 - Мартин Смит

Вот и все! Та же проблема, та же ссылка, которую я предоставил на сайт MS Connect.

UPDATE # 2

Вместо:

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

Вам необходимо использовать:

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

Answer 3

CREATE FUNCTION [dbo].[vendor_relation_users]
(
    @user_name CHAR(12)
)
RETURNS TABLE
AS
  RETURN (SELECT CASE WHEN @user_name NOT LIKE '06%' THEN  @user_name  ELSE NULL END as [user_name]);  
GO

Хорошо, https://docs.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql

Если инструкция SELECT не возвращает строк, переменная сохраняет свое текущее значение. Если выражение является скалярным подзапросом, который не возвращает значения, переменная устанавливается в NULL.

Например

DECLARE @u CHAR(12) = '066BDLER'
SELECT  @u  = 'AAAA' WHERE 1 <> 1
SELECT @u

При любых условиях значение SELECT @variable не изменится, и DB ENGINE упростит оператор до

|- Постоянное сканирование

При использовании RECOMPILE sql создайте новый план с условием и изменением плана

        |--Compute Scalar(DEFINE:([Expr1001]='066BDLER    '))
             |--Filter(WHERE:(STARTUP EXPR(NOT '066BDLER    ' like '06%')))
                  |--Constant Scan

Но RECOMPILE требует затрат на компиляцию, затем используйте CASE для возврата

ps Если вы не измените функцию, то вы можете обойти назначение, иначе зачем вам когда-либо подавать заявку:)

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](a.user_name) AS is_v