Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.6
-
None
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
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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;
|
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?)
I will document the current behaviour, but have reported as a separate bug in MDEV-16035
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;