外部应用在不匹配时意外返回非空的列 [英] Outer Apply Returning columns unexpectedly NOT NULL when no match
问题描述
当与 OUTER APPLY 一起使用时，我在表值函数上遇到了一些奇怪的行为.我有一个简单的内联函数，它根据另一个表中的一行返回一些简单的计算.当 TVF 的输入值是硬编码标量时，不会返回任何行.当我使用相同的标量并在 CTE 中从它们中取出一行，然后使用 CROSS APPLY 将它们作为列输入时，没有结果集.当我对 OUTER APPLY 做同样的事情时，我得到 1 行(正如预期的那样)，但其中两个输出列是 NULL 和另外两个 NOT NULL.基于 BOL，OUTER APPLY 不应该发生这种情况.这是用户错误吗?我写了一个简单的版本来演示这个问题.
--测试设置创建函数 dbo.TVFTest(@keyID INT，@matchValue1 钱，@matchValue2 钱)返回表作为返回(带测试行AS (SELECT @keyID AS KeyID,@matchValue1 作为 MatchValue1，@matchValue2 作为 MatchValue2)选择密钥 ID，匹配值1，匹配值2，案件当 MatchValue1 <>匹配值2然后'不等于'ELSE '别的东西'作为匹配测试结束从测试行WHERE MatchValue1 <>匹配值2)走查询
WITH 测试 AS(选择 12 作为 PropertyID，$350000 AS AP1,350000 AS AP2)选择LP.*从测试 T外部应用 dbo.TVFTest(T.PropertyID，T.Ap1,Ap2)LP；结果
+-------+-------------+-------------+-----------+|密钥 ID |匹配值 1 |匹配值2 |匹配测试 |+-------+--------------+-------------+-----------+|12 |350000.00 |空 |空 |+-------+--------------+-------------+-----------+使用 Cross Apply 没有按预期返回任何行.同样删除 CTE 并使用内联常量不会返回任何行.
--标量，这里没有行...选择LP.*从 dbo.TVFTest(12、35 万美元，350000)LP；这肯定是产品中的一个错误.
一个类似的错误
最终投影中的输出列列表是.Expr1000、Expr1001、Expr1003、Expr1004.
然而，在右下角的常量表中只定义了其中的两列.
文字 $350000 在右上角的常量表 (Expr1001) 中定义.然后将其外部连接到右下角的常量表.由于没有行匹配连接条件，在那里定义的两列(Expr1003、Expr1004)被正确评估为 NULL.然后最终计算标量将文字 12 作为新列 (Expr1000) 添加到数据流中，而不管外连接的结果如何.
这些根本不是正确的语义.与手动内联内联 TVF 时的(正确)计划进行比较.
WITH 测试AS (SELECT 12 AS PropertyID,$350000 AS AP1,350000 AS AP2)选择LP.*从测试 T外部应用(选择 KeyID，匹配值1，匹配值2，案件当 MatchValue1 <>匹配值2然后'不等于'ELSE '别的东西'作为匹配测试结束FROM (SELECT T.PropertyID AS KeyID,T.Ap1 AS MatchValue1,T.Ap2 AS MatchValue2) TestRowWHERE MatchValue1 <>匹配值2) LP这里用于最终投影的列是 Expr1003、Expr1004、Expr1005、Expr1006.所有这些都在右下角的常量扫描中定义.
就 TVF 而言，一切似乎都很早就出错了.
添加 OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8606); 显示进程的输入树已经不正确.用 SQL 表示它是类似的东西.
SELECT Expr1000,Expr1001，Expr1003，Expr1004从(值(12，35 万美元，350000)) V1(Expr1000, Expr1001, Expr1002)外部应用(选择 Expr1003，IIF(Expr1001 <> Expr1003,'不等于','别的东西')AS Expr1004FROM (SELECT CAST(Expr1002 AS MONEY) AS Expr1003) D哪里 Expr1001 <>Expr1003) OA该跟踪标志的完整输出如下(与 8605 显示的树基本相同.)
*** 输入树:***LogOp_Project COL:Expr1000 COL:Expr1001 COL:Expr1003 COL:Expr1004LogOp_Apply (x_jtLeftOuter)日志Op_ProjectLogOp_ConstTableGet (1) [空]AncOp_PrjListAncOp_PrjEl COL:Expr1000ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=12)AncOp_PrjEl COL:Expr1001ScaOp_Const TI(money,ML=8) XVAR(money,Not Owned,Value=(10000units)=(-794967296))AncOp_PrjEl COL:Expr1002ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=350000)日志Op_Project日志操作选择日志Op_ProjectLogOp_ConstTableGet (1) [空]AncOp_PrjListAncOp_PrjEl COL:Expr1003ScaOp_Convert money,Null,ML=8ScaOp_Identifier COL:Expr1002ScaOp_Comp x_cmpNeScaOp_Identifier COL:Expr1001ScaOp_Identifier COL:Expr1003AncOp_PrjListAncOp_PrjEl COL:Expr1004ScaOp_IIF varchar 整理 53256,Var,Trim,ML=14ScaOp_Comp x_cmpNeScaOp_Identifier COL:Expr1001ScaOp_Identifier COL:Expr1003ScaOp_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*******************I'm hitting some weird behavior on a table valued function when used with OUTER APPLY. I have a simple inline function that returns some simple calculations based on a row in another table. When the input values for the TVF are hard-coded scalars, there is no row returned. When I take the same scalars and make a single row out of them in a CTE, then feed them in as columns using CROSS APPLY, no result set. When I do the same with OUTER APPLY, I get 1 row (as expected), but two of the output columns are NULL and the other two NOT NULL. Based on BOL, that shouldn't happen with an OUTER APPLY. Is this a user error? I wrote a simple version to demonstrate the issue.
--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
Query
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;
Results
+-------+-------------+-------------+-----------+
| KeyID | MatchValue1 | MatchValue2 | MatchTest |
+-------+-------------+-------------+-----------+
| 12 | 350000.00 | NULL | NULL |
+-------+-------------+-------------+-----------+
Using Cross Apply returns no rows as expected. Also removing the CTE and using inline constants returns no row.
--Scalars, no row here...
SELECT LP.*
FROM dbo.TVFTest
(
12,
$350000,
350000
) LP;
This is certainly a bug in the product.
A similar bug was already reported and closed as "Won't Fix".
Including this question, the linked connect item and another two questions on this site I have seen four cases of this type of behaviour with inline TVFs and OUTER APPLY - All of them were of the format
OUTER APPLY dbo.SomeFunction(...) F
And returned correct results when written as
OUTER APPLY (SELECT * FROM dbo.SomeFunction(...)) F
So this looks like a possible workaround.
For the query
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;
The execution plan looks like
And the list of output columns in the final projection is. Expr1000, Expr1001, Expr1003, Expr1004.
However only two of those columns are defined in the table of constants in the bottom right.
The literal $350000 is defined in the table of constants in the top right (Expr1001). This then gets outer joined onto the table of constants in the bottom right. As no rows match the join condition the two columns defined there (Expr1003, Expr1004) are correctly evaluated as NULL. then finally the compute scalar adds the literal 12 into the data flow as a new column (Expr1000) irrespective of the result of the outer join.
These are not at all the correct semantics. Compare with the (correct) plan when the inline TVF is manually inlined.
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
Here the columns used in the final projection are Expr1003, Expr1004, Expr1005, Expr1006. All of these are defined in the bottom right constant scan.
In the case of the TVF it all seems to go wrong very early on.
Adding OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8606); shows the input tree to the process is already incorrect. Expressed in SQL it is something like.
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
The full output of that trace flag is as follows (And 8605 shows basically the same tree.)
*** 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
*******************
这篇关于外部应用在不匹配时意外返回非空的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
