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