[MDEV-16031] SEQUENCE with zero increment is not described in mariadb documentation Created: 2018-04-25  Updated: 2018-04-26  Resolved: 2018-04-26

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Sequences
Affects Version/s: 10.3.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Oleksandr Byelkin Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16035 SEQUENCE with zero increment does not... Closed

 Description   

https://mariadb.com/kb/en/library/create-sequence/

lack information about case of INCREMENT equal to 0.

ORACLE and MSSQL prohibit it according to documentation
DB2 thread the sequence as accidental and so we do (at least according my experience)

CREATE SEQUENCE s INCREMENT 0;
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
1
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
2
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
3



 Comments   
Comment by Oleksandr Byelkin [ 2018-04-25 ]

I checked implementation if increment is 0, it will be taken from global system variable:

  if (!(real_increment= increment))
 
    real_increment= global_system_variables.auto_increment_increment;

Comment by Oleksandr Byelkin [ 2018-04-25 ]

It looks like sequence with 0 INCREMENT store current variable value, but it is invisible(!!!):

set global auto_increment_increment=2;
 
CREATE SEQUENCE s INCREMENT 0;
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
1
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
3
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
5
 
set global auto_increment_increment=1;
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
7
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
9
 
set global auto_increment_increment=0;
 
Warnings:
 
Warning	1292	Truncated incorrect auto_increment_increment value: '0'
 
set global auto_increment_increment=default;
 
SHOW CREATE SEQUENCE s;
 
Table	Create Table
 
s	CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM
 
connect  con1,localhost,root,,;
 
set global auto_increment_increment=1;
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
11
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
13
 
SELECT NEXTVAL(s);
 
NEXTVAL(s)
 
15
 
DROP SEQUENCE s;

Comment by Oleksandr Byelkin [ 2018-04-25 ]

and value will be stored till rereading "increment" value from table, when it will be adjusted to current auto_increment_increment again. (Is it nice behavior?)

Comment by Ian Gilfillan [ 2018-04-26 ]

I will document the current behaviour, but have reported as a separate bug in MDEV-16035

Comment by Ian Gilfillan [ 2018-04-26 ]

Documented at https://mariadb.com/kb/en/library/create-sequence/

Generated at Thu Feb 08 08:25:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.