0

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.

Omaer
  • 817
  • 1
  • 7
  • 22
  • 1
    see this answer by Martin smith and this connect item – TheGameiswar Mar 10 '16 at 09:47
  • 1
    http://stackoverflow.com/questions/32407384/outer-apply-returning-columns-unexpectedly-not-null-when-no-match – TheGameiswar Mar 10 '16 at 09:47
  • https://connect.microsoft.com/SQLServer/feedback/details/725323/outer-apply-returns-incorrect-results – TheGameiswar Mar 10 '16 at 09:47
  • @TheGameiswar Thank you! You are a search Guru... I tried all sorts of search phrases but didn't get a hit. – Omaer Mar 10 '16 at 09:51
  • And for anyone interested, Microsoft says there's not enough people complaining about this particular issue, and it's difficult for them to fix - so they won't. – Omaer Mar 10 '16 at 09:55
  • i read stackoverflow daily,so i am aware of this and i tried like martin smith tvp bug,please do upvote connect item – TheGameiswar Mar 10 '16 at 10:01

0 Answers0