Home » Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

Solutons:


Nope. Here’s a simple test:

SELECT COALESCE(1, (SELECT 1/0)) -- runs fine
SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error

If the second condition is evaluated, an exception is thrown for divide-by-zero.

Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter – it’s just an easy way to write a CASE statement.

CASE is well known to be one of the only functions in SQL Server that (mostly) reliably short circuits.

There are some exceptions when comparing to scalar variables and aggregations as shown by Aaron Bertrand in another answer here (and this would apply both to CASE and COALESCE):

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

will generate a division by zero error.

This should be considered a bug, and as a rule COALESCE will parse from left to right.

How about this one – as reported to me by Itzik Ben-Gan, who was told about it by Jaime Lafargue?

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

Result:

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

There are trivial workarounds of course, but the point is still that CASE does not always guarantee left-to-right evaluation / short-circuiting. I reported the bug here and it was closed as “by design.” Paul White subsequently filed this Connect item, and it was closed as Fixed. Not because it was fixed per se, but because they updated Books Online with a more accurate description of the scenario where aggregates can change the evaluation order of a CASE expression. I recently blogged more about this here.

EDIT just an addendum, while I agree that these are edge cases, that most of the time you can rely on left-to-right evaluation and short-circuiting, and that these are bugs that contradict the documentation and will probably eventually be fixed (this isn’t definite – see the follow-up conversation on Bart Duncan’s blog post to see why), I have to disagree when folks say that something is always true even if there is a single edge case that disproves it. If Itzik and others can find solitary bugs like this, it makes it at least in the realm of possibility that there are other bugs as well. And since we don’t know the rest of the OP’s query, we can’t say for certain that he will rely on this short-circuiting but end up being bitten by it. So to me, the safer answer is:

While you can usually rely on CASE to evaluate left-to-right and short-circuit, as described in the documentation, it is not accurate to say that you can always do so. There are two demonstrated cases on this page where it is not true, and neither bug has been fixed in any publicly available version of SQL Server.

EDIT here is another case (I need to stop doing that) where a CASE expression does not evaluate in the order you would expect, even though no aggregates are involved.

The documentation makes it reasonably clear that the intention is for CASE to short-circuit. As Aaron mentions, there have been several reported instances where this has been shown to not always be true. So far, most of these have been acknowledged as bugs and fixed.

There are other issues with CASE (and therefore COALESCE) where side-effecting functions or sub-queries are used. Consider:

SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);

The COALESCE form often returns null, as described in a bug report by Hugo Kornelis.

The demonstrated issues with optimizer transforms and common-expression-tracking mean that it is impossible to guarantee that CASE will short-circuit in all circumstances.

I think you can be reasonably confident that CASE will short-circuit in general (particularly if a reasonably-skilled person inspects the execution plan, and that execution plan is ‘enforced’ with a plan guide or hints) but if you need an absolute guarantee, you have to write SQL that does not include the expression at all.

Related Solutions

Extract file from docker image?

You can extract files from an image with the following commands: docker create $image # returns container ID docker cp $container_id:$source_path $destination_path docker rm $container_id According to the docker create documentation, this doesn't run the...

Transfer files using scp: permission denied

Your commands are trying to put the new Document to the root (/) of your machine. What you want to do is to transfer them to your home directory (since you have no permissions to write to /). If path to your home is something like /home/erez try the following:...

What’s the purpose of DH Parameters?

What exactly is the purpose of these DH Parameters? These parameters define how OpenSSL performs the Diffie-Hellman (DH) key-exchange. As you stated correctly they include a field prime p and a generator g. The purpose of the availability to customize these...

How to rsync multiple source folders

You can pass multiple source arguments. rsync -a /etc/fstab /home/user/download bkp This creates bkp/fstab and bkp/download, like the separate commands you gave. It may be desirable to preserve the source structure instead. To do this, use / as the source and...

Benefits of Structured Logging vs basic logging

There are two fundamental advances with the structured approach that can't be emulated using text logs without (sometimes extreme levels of) additional effort. Event Types When you write two events with log4net like: log.Debug("Disk quota {0} exceeded by user...

Interfaces vs Types in TypeScript

2019 Update The current answers and the official documentation are outdated. And for those new to TypeScript, the terminology used isn't clear without examples. Below is a list of up-to-date differences. 1. Objects / Functions Both can be used to describe the...

Get total as you type with added column (append) using jQuery

One issue if that the newly-added column id's are missing the id number. If you look at the id, it only shows "price-", when it should probably be "price-2-1", since the original ones are "price-1", and the original ones should probably be something like...

Determining if a file is a hard link or symbolic link?

Jim's answer explains how to test for a symlink: by using test's -L test. But testing for a "hard link" is, well, strictly speaking not what you want. Hard links work because of how Unix handles files: each file is represented by a single inode. Then a single...

How to restrict a Google search to results of a specific language?

You can do that using the advanced search options: http://www.googleguide.com/sharpening_queries.html I also found this, which might work for you: http://www.searchenginejournal.com/how-to-see-google-search-results-for-other-locations/25203/ Just wanted to add...

Random map generation

Among the many other related questions on the site, there's an often linked article for map generation: Polygonal Map Generation for Games you can glean some good strategies from that article, but it can't really be used as is. While not a tutorial, there's an...

How to prettyprint a JSON file?

The json module already implements some basic pretty printing in the dump and dumps functions, with the indent parameter that specifies how many spaces to indent by: >>> import json >>> >>> your_json = '["foo", {"bar":["baz", null,...

How can I avoid the battery charging when connected via USB?

I have an Android 4.0.3 phone without root access so can't test any of this but let me point you to /sys/class/power_supply/battery/ which gives some info/control over charging issues. In particular there is charging_enabled which gives the current state (0 not...

How to transform given dataset in python? [closed]

From your expected result, it appears that each "group" is based on contiguous id values. For this, you can use the compare-cumsum-groupby pattern, and then use agg to get the min and max values. # Sample data. df = pd.DataFrame( {'id': [1, 2, 2, 2, 2, 2, 1, 1,...

Output of the following C++ Program [closed]

It works exactly like this non-recursive translation: int func_0() { return 2; } int func_1() { return 3; } int func_2() { return func_1() + func_0(); } // Returns 3 + 2 = 5 int func_3() { return func_2() + func_1(); } // Returns 5 + 3 = 8 int func_4() { return...

Making a circle out of . (periods) [closed]

Here's the maths and even an example program in C: http://pixwiki.bafsoft.com/mags/5/articles/circle/sincos.htm (link no longer exists). And position: absolute, left and top will let you draw: http://www.w3.org/TR/CSS2/visuren.html#choose-position Any further...

Should I use a code converter (Python to C++)?

Generally it's an awful way to write code, and does not guarantee that it will be any faster. Things which are simple and fast in one language can be complex and slow in another. You're better off either learning how to write fast Python code or learning C++...

tkinter: cannot concatenate ‘str’ and ‘float’ objects

This one line is more than enough to cause the problem: text="რეგულარი >> "+2.23+ 'GEL' 2.23 is a floating-point value; 'GEL' is a string. What does it mean to add an arithmetic value and a string of letters? If you want the string label 'რეგულარი...