weird behaviour of NVL(<Column_name>,Sequence.nextval) when the colummn has data?

All we need is an easy explanation of the problem, so here it is.

I’ve created a sequence like this :

CREATE SEQUENCE seq_test2
  MINVALUE 0
  MAXVALUE 999999999999999999999999999
  START WITH 0
  INCREMENT BY 1;

Both of these two queries show me the number 0:

 select SEQ_TEST2.nextval from DUAL;   
 select SEQ_TEST2.currval from DUAL;

And I have a table as you can see here :

create table STUDENT
(
  st_id      NUMBER,
  first_name VARCHAR2(150),
  last_name  VARCHAR2(150)
)

After executing the query below ,

select t.st_id , nvl(t.st_id , seq_test2.nextval) as seq
from STUDENT t

When there is no null in st_id column , the result I get is this :

    st_id    seq
   ---------------
      1       1
      2       2
      3       3
      4       4
      5       5

when I insert a row for which the column st_id is null , after executing the query above the result is like below :

      st_id    seq
   ---------------
       1       1
       2       2
       3       3
       4       4
       5       5
       null    10

And if I execute it again I see:

      st_id    seq
   ---------------
       1       1
       2       2
       3       3
       4       4
       5       5
       null    16

Seems like even if column st_id has data , this part of the NVL function (seq_test2.nextval)
gets executed !!!! Why is this happening?

Thanks in advance

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

NVL does not short-circuit, so even when the first input is not null the second argument is evaluated.

COALESCE does use short-circuit evaluation

True, but that’s not the reason for the behaviour Pantea has described here. References to NEXTVAL and CURRVAL are not functions but pseudocolumns. No matter if you use nvl or coalesce (or case or decode), sequence.nextval will always be evaluated for every fetched row and the sequence value will grow.

Method 2

NVL does not short-circuit, so even when the first input is not null the second argument is evaluated.

COALESCE does use short-circuit evaluation.

–edit
But that doesn’t matter, see the answer from Andi Schloegl

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0