Home Dashboard Directory Help
Search

Errors in the topic of coalesce() by Erland Sommarskog


Status: 

Active


5
0
Sign in
to vote
Type: Bug
ID: 774284
Opened: 12/13/2012 1:11:57 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

For a background, see the resolution to
https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null

The topic for coalesce starts off with:

Returns the first nonnull expression among its arguments.

This statement is inaccurate, and gives false promises. A correct summary would be:

Evaluates the arguments in order and returns the current value of the first expression
that initially does not evaluate to NULL.

Later in the topic is the sentence, it says:

    ISNULL and COALESCE though equivalent, can behave differently.

This is grossly confusing. They are certainly not equivalent. A better description is that they serve a similar purpose, but they are of very different nature. isnull() is a function, coalesce is a shortcut for the CASE expression given in the topic. The word equivalent is used here as well, but again this is not the best word. It would be better to say that coalesce is a syntactic shortcut.

Most of all the topic should include a discussion that if you have something like:

coalesce(<subquery>, 0)

This can return NULL under READ COMMITTED in a multi-user environment, since the subquery is evaluated twice. The topic could also say that if this undesirable, isnull() is a better alternative.

Similar notes can be made in the topic on nullif().


Details
Sign in to post a comment.
Posted by Microsoft on 8/1/2013 at 4:46 PM
We have updated the Coalesce topic. Our changes that address the issues that you raised will be published August 6, 2013. The URL is http://msdn.microsoft.com/en-us/library/ms190349.aspx.

Thank you for reporting this and apologies for the delay.

Kind regards,
Gail Erickson
SQL Server Documentation Team
Posted by Microsoft on 12/14/2012 at 4:09 PM
Hi Erland,
We will investigate and update the topic in early 2013.

Kind regards,
Gail Erickson
SQL Server Documetation Team
Posted by Microsoft on 12/14/2012 at 4:09 PM
Hi Erland,
We will investigate and update the topic in early 2013.

Kind regards,
Gail Erickson
SQL Server Documetation Team
Sign in to post a workaround.