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;
Еще одна попытка, это возвращает с ожидаемым результатом (пусто)
Мой вывод: обрабатываются только скалярные значения, которые требуют дополнительной обработки и, следовательно, "знают", что они не должны отображаться. Все скалярные значения, которые могут быть переданы без какой-либо дополнительной работы, не обрабатываются внутри функции и отображаются - что является ошибкой.
Каково твое мнение?