将函数与 OUTER APPLY 一起使用时，将返回该值而不是 NULL

2021-11-16 00:00:00 apply tsql sql-server sql-server-2014 outer-apply

我在使用内联函数时得到奇怪的结果.代码如下:

I am getting strange results when using inline function. Here is the code:

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 APPLied 并返回结果.

So in the first SELECT statement I've just OUTER APPLied the function and it returns the result.

在下一个语句中，我从函数中取出代码并将其直接放入 OUTER APPLY 语句中.

In the next statement I've took the code from function and put it straight to the OUTER APPLY statement.

最后一个语句只是直接的函数调用.

And the last statement is just the direct function call.

我不明白为什么 FIRST 查询会返回值...

推荐答案

这是一个非常有趣的查询.第一个查询的行为取决于您是否使用 OPTION (RECOMPILE).

This is a very interesting query. The behaviour of your first query depends upon whether you use OPTION (RECOMPILE) or not.

正如您指出的那样:

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

but if you add OPTION (RECOMPILE) like this:

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 的那个只返回一个常量.

I suspect this is due to a bug in how the query optimiser short circuits these inline functions due to cardinality estimates. If you look at the query plan for the two queries you will see that the one without the OPTION RECOMPILE just returns a constant.

相关文章