Reaching MAXVALUE of SEQUENCE returns wrong errors

[Weishiun Tsai]

Reaching the maximum value for the SEQUENCE feature is not properly handled. As shown in the following example, the 3rd insert has reached the maximum value 9223372036854775807 for a LARGEINT identity column, but the errors returned are 8411 about a numeric overflow and 8609 about transaction rollback, instead of the proper error 8934 ‘The MAXVALUE for the sequence generator has been exceeded.’

This is seen on the v0121 build installed on a workstation.

------------------------------------------------------------------------

Here is the entire script to reproduce it.

create schema mysch;
set schema mysch;

create table mytab (
c1 LARGEINT GENERATED ALWAYS AS IDENTITY (START WITH 9223372036854775805),
c2 int);

insert into mytab values (DEFAULT, 1);
select * from mytab order by c2;

insert into mytab values (DEFAULT, 2);
select * from mytab order by c2;

insert into mytab values (DEFAULT, 3);
select * from mytab order by c2;

drop table mytab cascade;
get tables;
drop schema mysch cascade;
get tables;

------------------------------------------------------------------------

Here is the execution output:

>>create schema mysch;

--- SQL operation complete.
>>set schema mysch;

--- SQL operation complete.
>>
>>create table mytab (
+>c1 LARGEINT GENERATED ALWAYS AS IDENTITY (START WITH 9223372036854775805),
+>c2 int);

--- SQL operation complete.
>>
>>insert into mytab values (DEFAULT, 1);

--- 1 row(s) inserted.
>>select * from mytab order by c2;

C1 C2
-------------------- -----------

 9223372036854775805 1

--- 1 row(s) selected.
>>
>>insert into mytab values (DEFAULT, 2);

--- 1 row(s) inserted.
>>select * from mytab order by c2;

C1 C2
-------------------- -----------

 9223372036854775805 1
 9223372036854775806 2

--- 2 row(s) selected.
>>
>>insert into mytab values (DEFAULT, 3);

*** ERROR[8609] Waited rollback performed without starting a transaction.

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion.

--- 0 row(s) inserted.
>>select * from mytab order by c2;

C1 C2
-------------------- -----------

 9223372036854775805 1
 9223372036854775806 2

--- 2 row(s) selected.
>>
>>drop table mytab cascade;

--- SQL operation complete.
>>get tables;

--- SQL operation complete.
>>drop schema mysch cascade;

--- SQL operation complete.
>>get tables;

--- SQL operation complete.
>>

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0324 build installed on a workstation. This problem has been fixed and a proper 8934 error is returned:

>>create schema mysch;

--- SQL operation complete.
>>set schema mysch;

--- SQL operation complete.
>>
>>create table mytab (
+>c1 LARGEINT GENERATED ALWAYS AS IDENTITY (START WITH 9223372036854775805),
+>c2 int);

--- SQL operation complete.
>>
>>insert into mytab values (DEFAULT, 1);

--- 1 row(s) inserted.
>>select * from mytab order by c2;

C1 C2
-------------------- -----------

 9223372036854775805 1

--- 1 row(s) selected.
>>
>>insert into mytab values (DEFAULT, 2);

--- 1 row(s) inserted.
>>select * from mytab order by c2;

C1 C2
-------------------- -----------

 9223372036854775805 1
 9223372036854775806 2

--- 2 row(s) selected.
>>
>>insert into mytab values (DEFAULT, 3);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:9223372036854775807 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:2. Instruction:ADD_MBIN64S_MBIN64S_MBIN64S Operation:ADD.

*** ERROR[8839] Transaction was aborted.

*** ERROR[8934] The MAXVALUE for the sequence generator has been exceeded.

--- 0 row(s) inserted.
>>select * from mytab order by c2;

C1 C2
-------------------- -----------

 9223372036854775805 1
 9223372036854775806 2

--- 2 row(s) selected.
>>
>>drop table mytab cascade;

--- SQL operation complete.
>>get tables;

--- SQL operation complete.
>>drop schema mysch cascade;

--- SQL operation complete.
>>get tables;

--- SQL operation complete.