当将函数与OUTER APPLY一起使用时，返回值而不是NULL [英] The value is returned instead of NULL when using function with 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语句中，我只对函数进行了外部应用并返回了结果.

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

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)   

您正确地获得了此信息:

you correctly get this:

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.

这篇关于当将函数与OUTER APPLY一起使用时，返回值而不是NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！