問題描述
我在使用內聯函數時得到奇怪的結果.代碼如下:
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.
這篇關于將函數與 OUTER APPLY 一起使用時，將返回該值而不是 NULL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！