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)

Answer 2

Explanation of why the short-circuit evaluation does not apply to sequences might be the following. What is a sequence? Putting internals aside, it's a combination of sequence definition(record in seq$ data dictionary table) and some internal SGA component, it's not a function and might be considered, although the documentation does not state it directly(but execution plan does) as row source. And every time a sequence is being referenced directly in the select list of a query, it has to be evaluated by the optimizer when it searches for optimal execution plan. During the process of forming an optimal execution plan a sequence gets incremented if nextval pseudocolumn is referenced:

SQL> create sequence seq1;
Sequence created

Here is our sequence:

SQL> select o.obj#
  2       , o.name
  3       , s.increment$
  4       , s.minvalue
  5       , s.maxvalue
  6       , s.cache
  7    from sys.seq$ s
  8    join sys.obj$ o
  9       on (o.obj# = s.obj#)
 10    where o.name = 'SEQ1'
 11  ;


      OBJ# NAME    INCREMENT$   MINVALUE   MAXVALUE      CACHE
---------- ------- ---------- ---------- ---------- ----------
     94442 SEQ1             1          1       1E28         20

Lets trace below query, and also take a look at its execution plan

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
Session altered

SQL> select case
  2           when 1 = 1 then 1
  3           when 2 = 1 then seq1.nextval
  4         end as res
  5    from dual;

       RES
----------
         1

/* sequence got incremented by 1 */

SQL> select seq1.currval from dual;

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

Trace file information:

STAT #1016171528 id=1 cnt=1 pid=0 pos=1 obj=94442 op='SEQUENCE SEQ1 ...
STAT #1016171528 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL ...
CLOSE #1016171528:c=0,e=12,dep=0,type=0,tim=12896600071500 /* close the cursor */

The execution plan will show us basically the same:

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            else seq1.nextval
  4                          end
  5                      from dual
  6  /
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 51561390
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SEQUENCE        | SEQ1 |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.172 seconds

In terms of evaluation, referencing a sequence directly in a query, roughly the same as including a correlated sub-query. That correlated sub-query will always be evaluated by the optimizer:

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            when 2 = 1 then (select 1
  4                                               from dual)
  5                          end as res
  6                      from dual;
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 1317351201
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     4   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
|   2 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.063 seconds  

We can see that dual table has been included in the execution plan twice.

The analogy with a sub-query was made in a rush. There are more differences than similarities, of course. Sequences are absolutely different mechanisms. But, a sequences are viewed by the optimizer as a row source, and as long as it doesn't see the nextval pseudocolumn of a sequence being directly referenced in the select list of a top-level query, it won't evaluate the sequence, otherwise sequence will be incremented, whether a short-circuit evaluation logic is being used or not. PL/SQL engine,obviously, (starting from Oracle 11g r1) has a different way to access a sequence value. Should be noted that in previous 11gR1 versions of RDBMS we should write a query to reference a sequence in PL/SQL block, which PL/SQL engine sent directly to the SQL engine.

The answer to the "why a sequence gets incremented during generating an execution plan by the optimizer" question, lies in the internal implementation of sequences.