Databases
StackOverflow2013
Postsdbo
POCOALESCE - guaranteed to short-circuit?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCOALESCE - guaranteed to short-circuit?
    text
    copied!<p>From <a href="https://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server">this question</a>, <a href="https://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server/505760#505760">a neat answer about using COALESCE</a> to simplify complex logic trees. I considered the problem of short circuiting.</p> <p>For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C:</p> <pre><code>int f(float x, float y) { return x; } f(a, a / b) ; // This will result in an error if b == 0 </code></pre> <p>That does not appear to be a limitation of the <code>COALESCE</code> "function" in SQL Server:</p> <pre><code>CREATE TABLE Fractions ( Numerator float ,Denominator float ) INSERT INTO Fractions VALUES (1, 1) INSERT INTO Fractions VALUES (1, 2) INSERT INTO Fractions VALUES (1, 3) INSERT INTO Fractions VALUES (1, 0) INSERT INTO Fractions VALUES (2, 0) INSERT INTO Fractions VALUES (3, 0) SELECT Numerator ,Denominator ,COALESCE( CASE WHEN Denominator = 0 THEN 0 ELSE NULL END, CASE WHEN Numerator &lt;&gt; 0 THEN Numerator / Denominator ELSE NULL END, 0 ) AS TestCalc FROM Fractions DROP TABLE Fractions </code></pre> <p>If it were evaluating the second case when Denominator = 0, I would expect to see an error like:</p> <pre><code>Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. </code></pre> <p>I found some <a href="http://it.toolbox.com/blogs/oracle-guide/minitip-5-coalesce-11378" rel="nofollow noreferrer">mentions</a> <a href="http://forums.oracle.com/forums/thread.jspa?threadID=685976" rel="nofollow noreferrer">related</a> to Oracle. And some tests with <a href="http://www.emoreau.com/Entries/Articles/2008/04/Short-circuiting-mainly-in-VBNet-and-SQL-Server.aspx" rel="nofollow noreferrer">SQL Server</a>. Looks like the short-circuiting might break down when you include user-defined functions.</p> <p>So, is this behavior supposed to be guaranteed by the ANSI standard?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload