Home Dashboard Directory Help
Search

Incorrect Row Counts Returned from SQL Trace and Extended Events when Using Scalar UDF by Guy Glantser


Status: 

Closed
 as Won't Fix Help for as Won't Fix


2
1
Sign in
to vote
Type: Bug
ID: 786313
Opened: 5/3/2013 1:04:06 PM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

When a query contains a call to a scalar user-defined function, the value of the "RowCount" field of the "BatchCompleted" and the "StatementCompleted" events returns incorrect results. The same results are returned in SQL Trace and in Extended Events (for the same corresponding events). If the query does not contain a call to a scalar UDF, then the results are correct.
Details
Sign in to post a comment.
Posted by Microsoft on 7/17/2013 at 12:21 PM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing these bugs are following:
1     Scenarios reported in the bug are not common enough.
2     A viable workaround is available.
Thanks again for reporting the product issue and continued support in improving our product.

Sincerely,
Manbeen
Posted by Guy Glantser on 5/6/2013 at 5:32 AM
Hi Pituach,

Thanks for the comment.
Everything you wrote about functions is true, but it has nothing to do with the fact that SQL Trace and Extended Events report incorrect values in the "RowCount" field. This value is supposed to represent the number of rows returned from the query, which is a value that is perfectly known to the engine at execution time, regardless of the structure of the query and the use of any type of function whatsoever.
The various "RowCount" columns in the "sys.dm_exec_query_stats" view do return the correct values, even when scalar functions are involved. But SQL Trace and Extended Events fail to report the correct values. This is a bug.

Thanks,
Guy
Posted by pituach on 5/4/2013 at 11:24 AM
It's not a bug in my opinion because it's a direct result of normal behavior using standard function

Function In computer programming is a subroutine, a sequence of program instructions that perform a specific task, packaged as a unit (*). What you mention as a bug is actually the normal behavior of Function.
http://en.wikipedia.org/wiki/Function_%28computer_science%29

The scalar functions behave like the Multi-Statement Table-Valued Functions in this context. For the benefit and effective use Microsoft also provided us Inline Table-Valued Functions for the use in databases. Those functions are the unique ones and not the "regular function" like scalar functions. The question should be why Microsoft did not provide INLINE Scalar Function too.

* "packaged as a unit" have a consequences and it is not always the best performance solution, especially for databases need. The idea of INLIN is that the SQL server query engine can break the code inside the "function" and to combine it with the whole query. It is not "a unit" any more. The INLIN function are unique and do not behave like a function in this context. The final "execution plan" is building from the whole query (the outer code and the in function code). This "BUG" is a direct consequence from this behavior and the "lack of information" in the outer code about the inner functions code.
Sign in to post a workaround.
Posted by pituach on 5/4/2013 at 11:29 AM
you can use INLINE function if needed

since Microsoft did not add inline scalar function then we can use Inline Table-Valued Functions. the idea is simple: we return table with one row and one column... the value that we need. and we use CROSS APPLY with our ITVF or sub query.