I'm no expert in Ms SQL, but I came across some really strange behavior in Microsoft SQL Server. I am quite sure it's a bug - but wanted to bring it to more experienced eyes before I conclude that it is. (And I'm not sure how I would go about reporting it to Microsoft if it is).
Environment
@@Version =>
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Step 1: Create the offending function
I'm going to ask you to create two functions - which should both work the same IMHO, but it seems one of them is acting a little funny. First, the offending function:
if OBJECT_ID('test_function_bad') is not null
drop function test_function_bad;
go
create function test_function_bad (@val int)
returns table
as return
(select @val as MyBadValue
where @val < 3
);
go
Next, the almost-identical function which works as expected:
if OBJECT_ID('test_function_good') is not null
drop function test_function_good;
go
create function test_function_good (@val int)
returns table
as return
(select @val * 1 as MyGoodValue
where @val < 3
);
Step 2: Create the test table
if OBJECT_ID('tempdb..#test') is not null
drop table #test;
create table #test (id int, val char);
insert into #test
(id, val)
values (1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D');
The Bug (maybe) Run the following code:
select *
from #test a
outer apply dbo.test_function_bad(a.id);
And it gives the result:
id val MyValue
----------- ---- -----------
1 A 1
2 B 2
3 C 3
4 D 4
That's strange... I expected NULLS instead of the "3" and "4" under MyValue. Why is this function returning values >= 3? It should just return 1 and 2, and nothing else. Right? To confirm this, let's run the following:
select *
from #test a
outer apply dbo.test_function_good(a.id);
This works... It gives the following:
id val MyValue
----------- ---- -----------
1 A 1
2 B 2
3 C NULL
4 D NULL
The Question
Here's the question: Is this really a bug? (I'm guessing some of you experts might be familiar with all sorts of bugs in Ms SQL - but I'm not, this is the first bug I'm seeing. I just want to make sure there's no explanation for this behavior).
Follow up question: If this is a bug, then do I report it to Microsoft? Is there any point in reporting it? Do they care?
Disclaimer: I did try searching for this but didn't find anything.