問題描述
我在使用內(nèi)聯(lián)函數(shù)時(shí)得到奇怪的結(jié)果.代碼如下:
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)
所以在第一個(gè) SELECT 語句中，我只是對(duì)函數(shù)進(jìn)行了 OUTER APPLied 并返回結(jié)果.
So in the first SELECT statement I've just OUTER APPLied the function and it returns the result.
在下一個(gè)語句中，我從函數(shù)中取出代碼并將其直接放入 OUTER APPLY 語句中.
In the next statement I've took the code from function and put it straight to the OUTER APPLY statement.
最后一個(gè)語句只是直接的函數(shù)調(diào)用.
And the last statement is just the direct function call.
我不明白為什么 FIRST 查詢會(huì)返回值...
推薦答案
這是一個(gè)非常有趣的查詢.第一個(gè)查詢的行為取決于您是否使用 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
我懷疑這是由于查詢優(yōu)化器如何根據(jù)基數(shù)估計(jì)使這些內(nèi)聯(lián)函數(shù)短路的錯(cuò)誤.如果您查看這兩個(gè)查詢的查詢計(jì)劃，您會(huì)發(fā)現(xiàn)沒有 OPTION RECOMPILE 的那個(gè)只返回一個(gè)常量.
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.
這篇關(guān)于將函數(shù)與 OUTER APPLY 一起使用時(shí)，將返回該值而不是 NULL的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！