CASE and COALESCE short-circuit evaluation works with sequences in PL/SQL but not in SQL

Question

Does the short-circuit evaluation described in the documentation for CASE and COALESCE() apply to sequences when used in SQL? This does not appear to be happening.

The Oracle documentation on CASE states that:

Oracle Database uses short-circuit evaluation. For a simple CASE expression... Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database... never evaluates a condition if the previous condition was true.

Similarly for COALESCE() the documentation states that:

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

When calling a sequence from SQL this does not appear to be the case; as you can see no short circuiting occurs and the sequence is incremented.

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> select tmp_test_seq.nextval from dual;

   NEXTVAL
----------
         1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         1
SQL> select coalesce(1, tmp_test_seq.nextval) from dual;

COALESCE(1,TMP_TEST_SEQ.NEXTVAL)
--------------------------------
                               1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         2
SQL> select case when 1 = 1 then 1 else tmp_test_seq.nextval end as s from dual;


         S
----------
         1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         3

SQL Fiddle.

However, when calling from PL/SQL the sequence is not incremented:

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> declare
  2     i number;
  3  begin
  4     i := tmp_test_seq.nextval;
  5     dbms_output.put_line(tmp_test_seq.currval);
  6     i := coalesce(1, tmp_test_seq.nextval);
  7     dbms_output.put_line(i);
  8     dbms_output.put_line(tmp_test_seq.currval);
  9     i := case when 1 = 1 then 1 else tmp_test_seq.nextval end;
 10     dbms_output.put_line(i);
 11     dbms_output.put_line(tmp_test_seq.currval);
 12  end;
 13  /
1
1
1
1
1
SQL> select tmp_test_seq.nextval from dual;

   NEXTVAL
----------
         2

Calling the sequence in SQL from PL/SQL the same results as with SQL happens:

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> declare
  2     i number;
  3  begin
  4     select tmp_test_seq.nextval into i from dual;
  5     dbms_output.put_line(tmp_test_seq.currval);
  6     select coalesce(1, tmp_test_seq.nextval) into i from dual;
  7     dbms_output.put_line(i);
  8     dbms_output.put_line(tmp_test_seq.currval);
  9     select case when 1 = 1 then 1 else tmp_test_seq.nextval end into i
 10       from dual;
 11     dbms_output.put_line(i);
 12     dbms_output.put_line(tmp_test_seq.currval);
 13  end;
 14  /
1
1
2
1
3

There doesn't seem to be anything in the documentation about this; the Administrator's guide for managing sequences, the SQL language reference on sequence psuedocolumns, the PL/SQL language reference on CURRVAL and NEXTVAL or the database concepts overview of sequences.

Does the short-circuit evaluation of CASE and COALESCE() occur for sequences when used in SQL? Is this documented?

We're on 11.2.0.3.5 if it's of interest.

Answer 1

For PL/SQL Oracle assures that it will use short-circuit evaluation:

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as it can determine the result. Therefore, you can write expressions that might otherwise cause errors.

From: 2 PL/SQL Language Fundamentals

When you use the nextval in SQL code, we have a different situation.

First of all we have to keep in mind that currval and nextval are pseudocolumns:

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a function without arguments (please refer to Chapter 5, "Functions". However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

From: 3 Pseudocolumns.

The question now is: why Oracle evaluate nextval? or Is this behaviour stated somewhere?

Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:

• For each row returned by the outer query block of a SELECT statement. Such a query block can appear in the following places:

  1. A top-level SELECT statement
  2. An INSERT ... SELECT statement (either single-table or multitable). For a multitable insert, the reference to NEXTVAL must appear in the VALUES clause, and the sequence is updated once for each row returned by the subquery, even though NEXTVAL may be referenced in multiple branches of the multitable insert.
  3. A CREATE TABLE ... AS SELECT statement
  4. A CREATE MATERIALIZED VIEW ... AS SELECT statement

• For each row updated in an UPDATE statement

• For each INSERT statement containing a VALUES clause

• For each row merged by a MERGE statement. The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause or both. The NEXTVALUE value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation. If NEXTVAL is specified more than once in any of these locations, then the sequence is incremented once for each row and returns the same value for all occurrences of NEXTVAL for that row.

From: Sequence Pseudocolumns

Your case is clearly "1. A top-level SELECT statement", but it doesn't mean that the short-circuit logic is not in place, but only that nextval is always evaluated.

If you are interested to the short-circuit logic, then it's better to remove the nextval from the equation.

A query like this doesn't evaluate the subquery:

select 6 c
  from dual
where  'a' = 'a' or 'a' = (select dummy from dual) 

But if try to do something similar with coalesce or case we will see that the Oracle Optimizer decides to execute the subqueries:

select 6 c
  from dual
where  'a' = coalesce('a', (select dummy from dual) )

I created annotated tests in this demo in SQLFiddle to show this.

It looks like Oracle applies the short-circuit logic only if with OR condition, but with coalesce and case it has to evaluate all branches.

I think your first tests in PL/SQL shows that coalsce and case use a short-circuit logic in PL/SQL, as Oracle states. Your second test, including the sequence in SQL statements, shows that in that case the nextval is evaluated anyway, even if the result is not used, and Oracle also documents that.

Putting together the two things looks a bit odd, because coalesce and case behaviour seems to be really inconsistent too me too, but we have also to keep in mind that the implementation of that logic is implementation dependent (here my source)