Cheesy Code

No, on 9i it evaluates and fails on a subquery it had no business looking at since the first argument isn’t NULL:

SQL> SELECT COALESCE('Not NULL',
    (SELECT 'True short circuit' FROM dual
      UNION
     SELECT 'should ignore this' FROM dual))
  FROM dual;  2    3    4    5
    (SELECT 'True short circuit' FROM dual
     *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Maybe this is a compile time issue and the database doesn’t actually run the subquery? Let’s try asking for the AVG of 100,000 random numbers in the ignored/short circuited part of the COALESCE to see what happens:

SQL> set timi on
SQL>
SQL> SELECT COALESCE('Not NULL',
  2      (SELECT TO_CHAR(AVG(TRUNC(dbms_random.value(1,5)))) rnd
  3         FROM (
  4              SELECT     LEVEL num
  5                FROM     dual
  6              CONNECT BY LEVEL <= 100000 + 1
  7              ))
  8  ) no_short_circuit
  9    FROM DUAL;

NO_SHORT_CIRCUIT
----------------------------------------
Not NULL

Elapsed: 00:00:01.45

1.45 seconds to evaluate what boils down to "SELECT 'Not NULL' FROM dual". Let's look at one last method - testing how many values from a random sequence are used by various calls to COALESCE:

EXEC dbms_random.initialize(0);

SELECT TRUNC (DBMS_RANDOM.VALUE (1, 10)) rnd0,
       TRUNC (DBMS_RANDOM.VALUE (1, 10)) rnd1,
       TRUNC (DBMS_RANDOM.VALUE (1, 10)) rnd2,
       TRUNC (DBMS_RANDOM.VALUE (1, 10)) rnd3
  FROM dual;

EXEC dbms_random.initialize(0);

SELECT COALESCE(TRUNC (DBMS_RANDOM.VALUE (1, 10)),
                TRUNC (DBMS_RANDOM.VALUE (1, 10))) rnd
  FROM dual;

SELECT TRUNC (DBMS_RANDOM.VALUE (1, 10)) rnd FROM dual;

EXEC dbms_random.initialize(0);

SELECT COALESCE(TRUNC (DBMS_RANDOM.VALUE (1, 10)),
                TRUNC (DBMS_RANDOM.VALUE (1, 10)),
                TRUNC (DBMS_RANDOM.VALUE (1, 10))) rnd
  FROM dual;

SELECT TRUNC (DBMS_RANDOM.VALUE (1, 10)) rnd FROM dual;

First we seed the sequence then use four values in a query to see what the random sequence looks like for this seed. Then we test a COALESCE with two calls to DBMS_RANDOM and three calls to DBMS_RANDOM. If COALESCE did short circuit there should have been only one call to DBMS_RANDOM, so only one value used from the random sequence. The very next call to DBMS_RANDOM in both cases should retrieve 8 which is the second value in the sequence.

PL/SQL procedure successfully completed.

      RND0       RND1       RND2       RND3
---------- ---------- ---------- ----------
         1          8          3          2

PL/SQL procedure successfully completed.

       RND
----------
         1

       RND
----------
         3

PL/SQL procedure successfully completed.

       RND
----------
         1

       RND
----------
         2

Instead what comes back are the third and fourth values in the random sequence which is consistent with every call to DBMS_RANDOM being executed and no short circuiting happening.

Three different tests and COALESCE doesn't appear to do any kind of short circuit on 9i.

Edit: After reading Jeff's comment I ran the test cases above on 10g and 11gR1 and they both passed all three test cases. So the answer to the question which started this post "Does COALESCE short-circuit?" is: It depends on the version of Oracle, no for 9i, yes for 10g and 11gR1.