[MDEV-40604] Query using Sequence nextval() produces incorrect results. - Jira

XML

Word

Printable

Run this test

--source include/have_sequence.inc

create table t1 (pk int primary key, a int);

insert into t1 select seq, seq from seq_1_to_1000;

create sequence s1;

select nextval(s1);

select nextval(s1);

select * from t1 where t1.pk=3;

select * from t1 where t1.pk=nextval(s1);

select nextval(s1);

and get this

create table t1 (pk int primary key, a int);

insert into t1 select seq, seq from seq_1_to_1000;

create sequence s1;

select nextval(s1);

nextval(s1)

select nextval(s1);

nextval(s1)

select * from t1 where t1.pk=3;

pk      a

3       3

Good so far. So, we will now get a row with pk=3?

select * from t1 where t1.pk=nextval(s1);

pk      a

No, we get nothing.
Note that s seems to have been incremented twice. We got the value 2, somebody got 3,4, and now we get 5:

select nextval(s1);

nextval(s1)

EXPLAIN also seems to evaluate nextval():

explain

select * from t1 where t1.pk=nextval(s1);

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra

1       SIMPLE  t1      const   PRIMARY PRIMARY 4       const   1

select nextval(s1);

nextval(s1)

We saw the value 5, EXPLAIN query got the 6, and now we got the 7.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.