Outer Apply Возвращающиеся столбцы неожиданно NOT NULL, когда нет совпадений

Я поражаю какое-то странное поведение в функции с табличной оценкой при использовании с OUTER APPLY. У меня есть простая встроенная функция, которая возвращает некоторые простые вычисления, основанные на строке в другой таблице. Когда входные значения для TVF являются жестко запрограммированными скалярами, возврат строки невозможен. Когда я беру одни и те же скаляры и вывожу из них одну строку в CTE, вы можете их подавать в виде столбцов, используя CROSS APPLY, без набора результатов. Когда я делаю то же самое с OUTER APPLY, я получаю 1 строку (как и ожидалось), но два из столбцов вывода NULL и два других NOT NULL. На основе BOL это не должно происходить с OUTER APPLY. Это ошибка пользователя? Я написал простую версию, чтобы продемонстрировать эту проблему.

Использование Cross Apply не возвращает строк, как ожидалось. Также удаление CTE и использование встроенных констант не возвращает строки.

Ответы

Ответ 1

Это, безусловно, ошибка в продукте.

Подобная ошибка уже была отмечена и закрыта как "Не исправлена " .

Включая этот вопрос, связанный элемент подключения и еще один два вопроса на этом сайте, я видел четыре случаи такого типа поведения с встроенными TVF и OUTER APPLY - все они имели формат

OUTER APPLY dbo.SomeFunction(...) F

И вернули правильные результаты при написании как

OUTER APPLY (SELECT * FROM dbo.SomeFunction(...)) F

Итак, это похоже на возможное обходное решение.

Для запроса

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;

План выполнения выглядит как

И список выходных столбцов в финальной проекции. Expr1000, Expr1001, Expr1003, Expr1004.

Однако только два из этих столбцов определены в таблице констант в правом нижнем углу.

Литерал $350000 определяется в таблице констант в правом верхнем углу (Expr1001). Затем он становится внешним, соединенным с таблицей констант в правом нижнем углу. Поскольку никакие строки не соответствуют условию объединения, два указанных там столбца (Expr1003, Expr1004) корректно оцениваются как NULL. то, наконец, вычислительный скаляр добавляет литерал 12 в поток данных как новый столбец (Expr1000) независимо от результата внешнего соединения.

Это не совсем правильная семантика. Сравните с (правильным) планом, когда встроенный TVF настроен вручную.

WITH Test
     AS (SELECT 12      AS PropertyID,
                $350000 AS Ap1,
                350000  AS Ap2)
SELECT LP.*
FROM   Test T
       OUTER APPLY (SELECT KeyID,
                           MatchValue1,
                           MatchValue2,
                           CASE
                             WHEN MatchValue1 <> MatchValue2
                               THEN 'Not equal'
                             ELSE 'Something else'
                           END AS MatchTest
                    FROM   (SELECT T.PropertyID AS KeyID,
                                   T.Ap1        AS MatchValue1,
                                   T.Ap2        AS MatchValue2) TestRow
                    WHERE  MatchValue1 <> MatchValue2) LP

Здесь столбцы, используемые в финальной проекции, Expr1003, Expr1004, Expr1005, Expr1006. Все они определены в нижнем правом постоянном сканировании.

В случае TVF все, кажется, идет не так очень рано.

Добавление OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8606); показывает, что дерево ввода для процесса уже неверно. Выраженный в SQL это что-то вроде.

SELECT Expr1000,
       Expr1001,
       Expr1003,
       Expr1004
FROM   (VALUES (12,
               $350000,
               350000)) V1(Expr1000, Expr1001, Expr1002)
       OUTER APPLY (SELECT Expr1003,
                           IIF(Expr1001 <> Expr1003, 
                               'Not equal', 
                               'Something else') AS Expr1004
                    FROM   (SELECT CAST(Expr1002 AS MONEY) AS Expr1003) D
                    WHERE  Expr1001 <> Expr1003) OA

Полный вывод этого флага трассировки следующий (И 8605 показывает в основном одно и то же дерево.)

*** Input Tree: ***
        LogOp_Project COL: Expr1000  COL: Expr1001  COL: Expr1003  COL: Expr1004 

            LogOp_Apply (x_jtLeftOuter)

                LogOp_Project

                    LogOp_ConstTableGet (1) [empty]

                    AncOp_PrjList 

                        AncOp_PrjEl COL: Expr1000 

                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=12)

                        AncOp_PrjEl COL: Expr1001 

                            ScaOp_Const TI(money,ML=8) XVAR(money,Not Owned,Value=(10000units)=(-794967296))

                        AncOp_PrjEl COL: Expr1002 

                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=350000)

                LogOp_Project

                    LogOp_Select

                        LogOp_Project

                            LogOp_ConstTableGet (1) [empty]

                            AncOp_PrjList 

                                AncOp_PrjEl COL: Expr1003 

                                    ScaOp_Convert money,Null,ML=8

                                        ScaOp_Identifier COL: Expr1002 

                        ScaOp_Comp x_cmpNe

                            ScaOp_Identifier COL: Expr1001 

                            ScaOp_Identifier COL: Expr1003 

                    AncOp_PrjList 

                        AncOp_PrjEl COL: Expr1004 

                            ScaOp_IIF varchar collate 53256,Var,Trim,ML=14

                                ScaOp_Comp x_cmpNe

                                    ScaOp_Identifier COL: Expr1001 

                                    ScaOp_Identifier COL: Expr1003 

                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=9) XVAR(varchar,Owned,Value=Len,Data = (9,Not equal))

                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=14) XVAR(varchar,Owned,Value=Len,Data = (14,Something else))

            AncOp_PrjList 

*******************

Ответ 2

Я провел некоторое исследование (SQL Server 2012) - и это действительно странно!

Вы можете упростить это. Мне кажется, что это имеет какое-то отношение к неявному преобразованию типов. Вот почему я попытался использовать типы данных...

Попробуйте следующее:

--Test set-up
CREATE FUNCTION dbo.TVFTest
(
       @ValueInt INT,
       @ValueMoney MONEY,
       @ValueVarchar VARCHAR(10),
       @ValueDate DATE,
       @DateAsVarchar DATE

)
RETURNS TABLE AS RETURN
(
       SELECT @ValueInt AS ValueInt
             ,@ValueMoney AS ValueMoney
             ,@ValueVarchar AS ValueVarchar
             ,@ValueDate AS ValueDate
             ,@DateAsVarchar AS DateAsVarchar
        WHERE 1 != 1
)
GO

Эта функция никогда не вернет строку из-за WHERE...

DECLARE @d AS DATE='20150101';

Эта типизированная переменная даты необходима позже, попробуйте заменить ее в вызовах с помощью GETDATE()...

--direct call: comes back with no row
SELECT * FROM dbo.TVFTest(1,2,'test',@d,'20150101');

--parameters via CTE: 
WITH Test AS
(
       SELECT 1 AS valint,
              2 AS valmoney,
              'test' AS valchar,
              @d AS valdate, --try GETDATE() here!
              '20150101' AS valdateasvarchar
)
SELECT * 
FROM Test AS T
OUTER APPLY dbo.TVFTest(T.valint,T.valmoney,T.valchar,T.valdate,T.valdateasvarchar) AS LP;

Оба неявно преобразованных параметра (Money and DateAsVarchar) не отображаются, но INT, VARCHAR и "реальная" DATE делают!!!
Посмотрите план выполнения: Этот вызов был выполнен с помощью GETDATE(). В противном случае было бы только 2 скалярных оператора...

EDIT: первый "Compute Scalar" в плане выполнения отображает все столбцы, постоянное сканирование (сканирование внутренней таблицы с константами) имеет только два столбца (три, если вы используете GETDATE()). "Плохие" столбцы даже не кажутся частью CTE на этом этапе...

--parameters via CTE with single calls 
WITH Test AS
(
       SELECT 1 AS valint,
              2 AS valmoney,
              'test' AS valchar,
              @d AS valdate,
              '20150101' AS valdateasvarchar
)
SELECT * FROM dbo.TVFTest((SELECT valint FROM Test)
                         ,(SELECT valmoney FROM Test)
                         ,(SELECT valchar FROM Test)
                         ,(SELECT valdate FROM Test)
                         ,(SELECT valdateasvarchar FROM Test));
GO
DROP FUNCTION dbo.TVFTest;

Еще одна попытка: возвращается с ожидаемым результатом (пустой)

Мой conclusio: обрабатываются только скалярные значения, требующие дополнительной обработки, и поэтому "знают", что они не должны отображаться. Все скалярные значения, которые могут быть переданы без дополнительной работы, не обрабатываются внутри функции и отображаются - что является ошибкой.

Каково ваше мнение?