title: COALESCE - guaranteed to short-circuit?
body: <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>