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.
NVLdoes not short-circuit, so even when the first input is not null the second argument is evaluated.
COALESCEdoes use short-circuit evaluation
True, but that’s not the reason for the behaviour Pantea has described here. References to
CURRVAL are not functions but pseudocolumns. No matter if you use
sequence.nextval will always be evaluated for every fetched row and the sequence value will grow.
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.
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 🙂