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

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

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

запрос

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;

Результаты

+-------+-------------+-------------+-----------+
| KeyID | MatchValue1 | MatchValue2 | MatchTest |
+-------+-------------+-------------+-----------+
|    12 | 350000.00   | NULL        | NULL      |
+-------+-------------+-------------+-----------+

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

--Scalars, no row here...
SELECT LP.*
FROM dbo.TVFTest
(
       12,
       $350000,
       350000
) LP;

2 ответа

Решение

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

О подобной ошибке уже сообщалось, и она была закрыта как "Не исправлю".

Включая этот вопрос, связанный элемент подключения и еще два вопроса на этом сайте, я видел четыре случая такого типа поведения со встроенными 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 

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

Я провел дальнейшие исследования (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

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

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 и DateAsVarchar) не отображаются, но есть INT, VARCHAR и "настоящая" DATE!!!
Посмотрите на план выполнения: Этот вызов был сделан с помощью GETDATE (). В противном случае было бы только 2 скалярных оператора...

РЕДАКТИРОВАТЬ: первый "Вычислить скаляр" в плане выполнения показывает все столбцы, сканирование констант (сканирование внутренней таблицы с константами) имеет только два столбца (три, если вы используете 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;

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

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

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

Другие вопросы по тегам