Google Git
Sign in
apache / trafodion / d632b192f6005a28c603e4a406848832d326e3bf / . / core / sql / regress / seabase / EXPECTED025
blob: 24c87f0131fdfb0cf4ef76a7a3dd0d569ce22c7f [file] [log] [blame]
>>--
>>?section positive_tests
>>--
>>-- create tables with IDENTITY Surrogate Key columns
>>--
>>-- IDENTITY column
>>
>>CREATE TABLE T025T001 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) );
--- SQL operation complete.
>>--
>>SHOWDDL T025T001;
CREATE TABLE TRAFODION.S025.T025T001
(
A LARGEINT GENERATED BY DEFAULT AS IDENTITY
( START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Create new tables for Generated ALWAYS as SMALLINT
>>CREATE TABLE T025T002 (a smallint unsigned
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>--
>>SHOWDDL T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A SMALLINT UNSIGNED GENERATED ALWAYS AS
IDENTITY ( START WITH 60 INCREMENT BY 1 MAXVALUE 100 MINVALUE 50
CACHE 25 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>drop table T025T002;
--- SQL operation complete.
>>
>>-- Create new tables for Generated ALWAYS for all types
>>-- Use combination of all default and NO MINVALUE and NO MAXVALUE
>>
>>CREATE TABLE T025T002 (a smallint unsigned
+> GENERATED ALWAYS AS IDENTITY
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>-- showddl
>>-- Confirm UNSIGNED SMALLINT MINVALUE and MAXVALUE
>>-- MINVALUE should be 1
>>-- MAXVALUE should be 65535
>>-- INCREMENT BY default should be 1
>>-- START WITH should be the MINVALUE
>>-- Should be NO CYCLE
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A SMALLINT UNSIGNED GENERATED ALWAYS AS
IDENTITY ( START WITH 1 INCREMENT BY 1 MAXVALUE 65535 MINVALUE 1
CACHE 25 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>
>>CREATE TABLE T025T002 (a smallint unsigned
+> GENERATED ALWAYS AS IDENTITY
+> (NO MINVALUE
+> NO MAXVALUE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>-- showddl
>>-- Confirm UNSIGNED SMALLINT MINVALUE and MAXVALUE
>>-- MINVALUE should be 1
>>-- MAXVALUE should be 65535
>>-- INCREMENT BY default should be 1
>>-- START WITH should be the MINVALUE
>>-- Should be NO CYCLE
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A SMALLINT UNSIGNED GENERATED ALWAYS AS
IDENTITY ( START WITH 1 INCREMENT BY 1 MAXVALUE 65535 MINVALUE 1
CACHE 25 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>
>>-- Create new tables for Generated ALWAYS as LARGEINT
>>CREATE TABLE T025T002 (a LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> (NO MINVALUE
+> NO MAXVALUE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>-- showddl
>>-- Confirm LARGEINT MINVALUE and MAXVALUE
>>-- MINVALUE should be 1
>>-- MAXVALUE should be 9223372036854775806
>>-- INCREMENT BY default should be 1
>>-- START WITH should be the MINVALUE
>>-- Should be NO CYCLE
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>
>>-- Create new tables for Generated ALWAYS as LARGEINT
>>-- No SG options specified
>>
>>CREATE TABLE T025T002 (a LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>-- showddl
>>-- Confirm LARGEINT MINVALUE and MAXVALUE
>>-- MINVALUE should be 1
>>-- MAXVALUE should be 9223372036854775806
>>-- INCREMENT BY default should be 1
>>-- START WITH should be the MINVALUE
>>-- Should be NO CYCLE
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>
>>-- Create new tables for Generated ALWAYS as SMALLINT UNSIGNED
>>CREATE TABLE T025T002 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> (NO MINVALUE
+> NO MAXVALUE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>-- showddl
>>-- Confirm UNSIGNED INT MINVALUE and MAXVALUE
>>-- MINVALUE should be 1
>>-- MAXVALUE should be 65535
>>-- START WITH should be equal to MINVALUE
>>-- Should be NO CYCLE
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A SMALLINT UNSIGNED GENERATED ALWAYS AS
IDENTITY ( START WITH 1 INCREMENT BY 1 MAXVALUE 65535 MINVALUE 1
CACHE 25 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>
>>-- Create new tables for Generated ALWAYS as INT UNSIGNED
>>-- No SG options specified
>>CREATE TABLE T025T002 (a INT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
--- SQL operation complete.
>>
>>-- showddl
>>-- Confirm UNSIGNED INT MINVALUE and MAXVALUE
>>-- MINVALUE should be 1
>>-- MAXVALUE should be 4294967295
>>-- INCREMENT BY default should be 1
>>-- START WITH should be the MINVALUE
>>-- CYCLE is NO
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
A INT UNSIGNED GENERATED ALWAYS AS IDENTITY
( START WITH 1 INCREMENT BY 1 MAXVALUE 4294967295 MINVALUE 1 CACHE
25 NO CYCLE INT UNSIGNED ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>
>>-- Some negative testing
>>
>>-- Expect error -1510
>>-- IDENTITY column can only be of type LARGEINT, SMALLINT UNSIGNED, INT UNSIGNED
>>CREATE TABLE T025T004 (a SMALLINT
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>;
*** ERROR[1510] IDENTITY column can be of the following data types only: LARGEINT, INTEGER UNSIGNED and SMALLINT UNSIGNED.
--- SQL operation failed with errors.
>>
>>-- Expect error -1510
>>-- 64 bit column type can only be of type LARGEINT
>>CREATE TABLE T025T004 (a NUMERIC(2,0)
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1510] IDENTITY column can be of the following data types only: LARGEINT, INTEGER UNSIGNED and SMALLINT UNSIGNED.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1510
>>-- 64 bit column type can only be of type LARGEINT
>>CREATE TABLE T025T004 (a interval day(13) to second(0)
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1510] IDENTITY column can be of the following data types only: LARGEINT, INTEGER UNSIGNED and SMALLINT UNSIGNED.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect syntax error
>>-- IDENTITY tokens are missing in the syntax
>>CREATE TABLE T025T004 (a LARGEINT
+> --GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[15001] A syntax error occurred at or before:
CREATE TABLE T025T004 (a LARGEINT
(START WITH 60 INCREMENT BY 1
^ (86 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>
>>
>>-- Expect error -3427 duplicate option errors
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60 START WITH 2
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[3427] Duplicate START WITH options were specified for the IDENTITY column.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1 INCREMENT BY 2
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[3427] Duplicate INCREMENT BY options were specified for the IDENTITY column.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100 MAXVALUE 30
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[3427] Duplicate MAXVALUE options were specified for the IDENTITY column.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50 MINVALUE 55
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[3427] Duplicate MINVALUE options were specified for the IDENTITY column.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[3427] Duplicate CYCLE options were specified for the IDENTITY column.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60 START WITH 65
+> INCREMENT BY 1 INCREMENT BY 2
+> MAXVALUE 100 MAXVALUE 110
+> MINVALUE 50 MINVALUE 40
+> NO CYCLE NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[3427] Duplicate START WITH options were specified for the IDENTITY column.
*** ERROR[3427] Duplicate INCREMENT BY options were specified for the IDENTITY column.
*** ERROR[3427] Duplicate MAXVALUE options were specified for the IDENTITY column.
*** ERROR[3427] Duplicate MINVALUE options were specified for the IDENTITY column.
*** ERROR[3427] Duplicate CYCLE options were specified for the IDENTITY column.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1570
>>-- MAXVALUE must be greater than MINVALUE
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MINVALUE 50
+> MAXVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[1570] MAXVALUE must be greater than MINVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expecting error -1570, MAXVALUE must be greater than the MINVALUE
>>
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1
+> MAXVALUE 10
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
*** ERROR[1570] MAXVALUE must be greater than MINVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>
>> -- Expect error -1571
>> -- INCREMENT BY cannot be zero
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 0
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1571] INCREMENT BY value cannot be zero for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>> -- Expect error -1573
>> -- START WITH must be less than or equal to MAXVALUE
>> -- and greater than or equal to the MINVALUE (for ascending SGs)
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 6000
+> INCREMENT BY 10
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>> -- Expect error -1573
>> -- START WITH must be less than or equal to MAXVALUE
>> -- and greater than or equal to the MINVALUE (for ascending SGs)
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 6000
+> INCREMENT BY 10
+> MINVALUE 7000
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1573
>>-- START WITH must be less than or equal to MAXVALUE
>>-- and greater than or equal to the MINVALUE (for ascending SGs)
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 6000
+> INCREMENT BY 10
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1573
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 6000
+> INCREMENT BY 10
+> MAXVALUE 100
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1573
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 10
+> MINVALUE 5000)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1575
>>-- With all data types having a base of zero,
>>-- test that INCREMENT BY is not greater than MAXVALUE
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1000
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1576
>>-- The MAXVALUE input is greater than the maximum
>>-- allowed for that data type.
>>-- SMALLINT max is 65535
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 50
+> INCREMENT BY 1000
+> MAXVALUE 65536
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1576] MAXVALUE value is greater than maximum allowed for this sequence.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1576
>>-- The MAXVALUE input is greater than the maximum
>>-- allowed for that data type.
>>-- INT max is 4294967295
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 4294967295
+> INCREMENT BY 1000
+> MAXVALUE 4294967296
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1576] MAXVALUE value is greater than maximum allowed for this sequence.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1576
>>-- The MAXVALUE input is greater than the maximum
>>-- allowed for that data type.
>>-- LARGEINT max is 9223372036854775806
>>
>> CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1000
+> MAXVALUE 9223372036854775808
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1576] MAXVALUE value is greater than maximum allowed for this sequence.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1570
>>-- The MINVALUE input is greater than the maximum
>>-- allowed for that data type.
>>-- SMALLINT max is 65535
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 50
+> INCREMENT BY 1000
+> MAXVALUE 65535
+> MINVALUE 65536
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1570] MAXVALUE must be greater than MINVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1570
>>-- The MINVALUE input is greater than the maximum
>>-- allowed for that data type.
>>-- INT max is 4294967295
>>
>> CREATE TABLE T025T004 (a INT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 4294967295
+> INCREMENT BY 1000
+> MAXVALUE 4294967295
+> MINVALUE 4294967296
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1570] MAXVALUE must be greater than MINVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1570
>>-- The MINVALUE input is greater than the maximum
>>-- allowed for that data type.
>>-- LARGEINT max is 9223372036854775806
>>
>> CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 60
+> INCREMENT BY 1000
+> MAXVALUE 9223372036854775806
+> MINVALUE 9223372036854775807
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1570] MAXVALUE must be greater than MINVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1573
>>-- The START WITH input is greater than the maximum
>>-- allowed for that data type.
>>-- SMALLINT max is 65535
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 65536
+> INCREMENT BY 1000
+> MAXVALUE 65535
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1573
>>-- The START WITH input is greater than the maximum
>>-- allowed for that data type.
>>-- INT max is 4294967295
>>
>> CREATE TABLE T025T004 (a INT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 4294967296
+> INCREMENT BY 1000
+> MAXVALUE 4294967295
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1576
>>-- The START WITH input is greater than the maximum
>>-- allowed for that data type.
>>-- LARGEINT max is 9223372036854775806
>>
>> CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 9223372036854775808
+> INCREMENT BY 1000
+> MAXVALUE 9223372036854775806
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1576] START WITH value is greater than maximum allowed for this sequence.
*** ERROR[8822] The statement was not prepared.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1575
>>-- The INCREMENT BY input is greater than the maximum
>>-- allowed for that data type.
>>-- SMALLINT max is 65535
>>
>> CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 1000
+> INCREMENT BY 65536
+> MAXVALUE 65535
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect error -1575
>>-- The INCREMENT BY input is greater than the maximum
>>-- allowed for that data type.
>>-- INT max is 4294967295
>>
>> CREATE TABLE T025T004 (a INT UNSIGNED
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 1000
+> INCREMENT BY 4294967296
+> MAXVALUE 4294967295
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>> -- Expect error -1575
>> -- The INCREMENT BY input is greater than the maximum
>> -- allowed for that data type.
>> -- For a LARGEINT, 9223372036854775806 is the maximum INCREMENT BY value.
>>
>> CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 1000
+> INCREMENT BY 9223372036854775807
+> MAXVALUE 9223372036854775806
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect -1572 to catch a negative input START WITH.
>>CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH -1
+> INCREMENT BY 1
+> MAXVALUE 9223372036854775806
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1572] START WITH value cannot be a negative number for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect -1572 to catch a negative input INCREMENT BY.
>>CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 1000
+> INCREMENT BY -1
+> MAXVALUE 9223372036854775806
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1572] INCREMENT BY value cannot be a negative number for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect -1572 to catch a negative input MINVALUE.
>>CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 1000
+> INCREMENT BY 1
+> MAXVALUE 9223372036854775806
+> MINVALUE -1
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1572] MINVALUE value cannot be a negative number for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Expect -1572 to catch a negative input MAXVALUE.
>>CREATE TABLE T025T004 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 1000
+> INCREMENT BY 1
+> MAXVALUE -1
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+>--HASH2 PARTITION BY(a)
+> ;
*** ERROR[1572] MAXVALUE value cannot be a negative number for IDENTITY column.
--- SQL operation failed with errors.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Start INSERT testing
>>
>>-- Use table T025T001 with the IDENTITY column built
>>-- with GENERATED BY DEFAULT with default sequence
>>-- generator default values
>>
>>-- The first, second and fourth inserts use the DEFAULT keyword.
>>-- The third, fifth and seventh inserts use a user supplied value.
>>-- The first, second, third, fourth and sixth
>>-- inserts should succeed. The fifth and seventh inserts
>>-- should fail with -8102.
>>
>>insert into T025T001 values(DEFAULT,1,1);
--- 1 row(s) inserted.
>>insert into T025T001 values(DEFAULT,2,2);
--- 1 row(s) inserted.
>>insert into T025T001 values(3333,3,3);
--- 1 row(s) inserted.
>>
>>-- Insert another row using DEFAULT, this should succeed
>>
>>insert into T025T001 values(DEFAULT,4,4);
--- 1 row(s) inserted.
>>
>>-- Insert another row using the same user supplied value.
>>-- This should receive -8102 unique constraint error
>>
>>insert into T025T001 values(3333,5,5);
*** ERROR[8102] The operation is prevented by a unique constraint.
--- 0 row(s) inserted.
>>
>>-- Insert again, with a unique user supplied value.
>>
>>insert into T025T001 values(6666,6,6);
--- 1 row(s) inserted.
>>
>>-- Insert again, using a number assigned by SG.
>>-- Should also receive -8102 error
>>
>>insert into T025T001 values(2,7,7);
*** ERROR[8102] The operation is prevented by a unique constraint.
--- 0 row(s) inserted.
>>
>>select * from T025T001 order by b,c;
A B C
-------------------- ---------- ----------
1 1 1
2 2 2
3333 3 3
3 4 4
6666 6 6
--- 5 row(s) selected.
>>
>>-- Should succeed, an IDENTITY column of type
>>-- GENERATED BY DEFAULT should be updatable
>>
>>update T025T001 set a = 25 where a = 2;
--- 1 row(s) updated.
>>
>>select * from T025T001 order by b,c;
A B C
-------------------- ---------- ----------
1 1 1
25 2 2
3333 3 3
3 4 4
6666 6 6
--- 5 row(s) selected.
>>
>>-- First create a table with non-default start with,
>>-- minimum value and maximum value
>>
>>-- First three inserts should succeed
>>-- Fourth insert should fail with -1579 MAXVALUE exceeded error
>>
>>-- Create new tables for Generated ALWAYS as SMALLINT
>>CREATE TABLE T025T002 (a smallint unsigned
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 80
+> INCREMENT BY 10
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> --HASH2 PARTITION BY(a)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>insert into T025T002 (b,c) values(1,1);
--- 1 row(s) inserted.
>>insert into T025T002 (b,c) values(2,2);
--- 1 row(s) inserted.
>>insert into T025T002 (b,c) values(3,3);
--- 1 row(s) inserted.
>>insert into T025T002 (b,c) values(4,4);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>select * from T025T002 order by b,c;
A B C
----- ---------- ----------
80 1 1
90 2 2
100 3 3
--- 3 row(s) selected.
>>
>>-- Insert testing Identity column is LARGEINT
>>-- Looks like our true LARGEINT max is one minus the true max
>>-- Test all insert patterns of DEFAULT and user supplied values.
>>
>>-- Table is GENERATED ALWAYS AS IDENTITY, so error -3428 should
>>-- be returned for user supplied values.
>>
>>-- Error -3414 should be returned for mixed DEFAULT and user supplied
>>-- values.
>>
>>-- The final insert values should receive -1579 MAXVALUE exceeded error
>>
>>
>>CREATE TABLE T025T003 (a LARGEINT
+> GENERATED ALWAYS AS IDENTITY (START WITH 9223372036854775802)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED, primary key(a))
+>--HASH2 PARTITION BY(a)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- First insert should succeed
>>
>>insert into T025T003 values(DEFAULT, 1,1),(DEFAULT, 2,2);
--- 2 row(s) inserted.
>>
>>-- Should fail with error -3428
>>
>>insert into T025T003 values(9223372036854775804,1,1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Should fail with error -3428
>>
>>insert into T025T003 values(9223372036854775804,1,1),(9223372036854775805,1,1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Should fail with error -3428 to an update
>>-- for an IDENTITY column of type GENERATED ALWAYS
>>
>>update T025T003 set a=300 where a=9223372036854775802;
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Should fail with error -3428
>>
>>insert into T025T003 values(DEFAULT,1,1), (9223372036854775804,1,1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Insert should succeed
>>insert into T025T003(b,c) values(1,1),(2,2);
--- 2 row(s) inserted.
>>
>>-- Insert should fail with MAXVALUE exceeded
>>
>>insert into T025T003 (b,c) values(1,1),(2,2);
*** ERROR[8934] The MAXVALUE for the sequence generator has been exceeded.
--- 0 row(s) inserted.
>>
>>select * from T025T003 order by a,b,c;
A B C
-------------------- ---------- ----------
9223372036854775802 1 1
9223372036854775803 2 2
9223372036854775804 1 1
9223372036854775805 2 2
--- 4 row(s) selected.
>>
>>-- Insert testing Identity column is UNSIGNED SMALLINT
>>-- First two inserts should succeed.
>>-- Third insert should receive -1579 MAXVALUE exceeded error
>>
>>CREATE TABLE T025T004 (a SMALLINT UNSIGNED
+> GENERATED ALWAYS AS IDENTITY (START WITH 65534)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED, primary key(a))
+>
+>--HASH2 PARTITION BY(a)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>insert into T025T004 (b,c) values(1,1);
--- 1 row(s) inserted.
>>insert into T025T004 (b,c) values(2,2);
--- 1 row(s) inserted.
>>insert into T025T004 (b,c) values(3,3);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>select * from T025T004 order by b,c;
A B C
----- ---------- ----------
65534 1 1
65535 2 2
--- 2 row(s) selected.
>>
>>-- Insert testing Identity column is UNSIGNED INTEGER
>>-- First four inserts should succeed.
>>-- Fifth/sixth insert should receive -1579 MAXVALUE exceeded error.
>>
>>CREATE TABLE T025T005 (a INTEGER UNSIGNED
+> GENERATED ALWAYS AS IDENTITY (START WITH 4294967291)
+>
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED, primary key(a))
+>
+>--HASH2 PARTITION BY(a)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Insert using DEFAULT for IDENTITY column
>>
>>insert into T025T005 (b,c) values(1,1),(2,2);
--- 2 row(s) inserted.
>>insert into T025T005 (b,c) values(3,3);
--- 1 row(s) inserted.
>>insert into T025T005 (b,c) values(4,4);
--- 1 row(s) inserted.
>>insert into T025T005 (b,c) values(5,5),(6,6);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>
>>select * from T025T005 order by b,c;
A B C
---------- ---------- ----------
4294967291 1 1
4294967292 2 2
4294967293 3 3
4294967294 4 4
--- 4 row(s) selected.
>>drop table t025t005;
--- SQL operation complete.
>>
>>-- Insert testing Identity column is UNSIGNED INTEGER
>>-- The third value in the insert will reach the maximum.
>>-- The error should cause a rollback of the insert.
>>-- No rows should be inserted.
>>-- The insert should receive -1579 MAXVALUE exceeded error.
>>
>>CREATE TABLE T025T005 (a INTEGER UNSIGNED
+> GENERATED ALWAYS AS IDENTITY (START WITH 4294967294)
+>
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED, primary key(a));
--- SQL operation complete.
>>
>>-- Insert using DEFAULT for IDENTITY column
>>
>>insert into T025T005 (b,c) values(1,1),(2,2),(3,3);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>
>>select * from T025T005 order by b,c;
--- 0 row(s) selected.
>>drop table t025t005;
--- SQL operation complete.
>>
>>-- Insert testing Identity column is UNSIGNED INTEGER (with DEFAULT keyword)
>>-- First two inserts should succeed, but this will create a gap
>>-- which will disallow the remaining entries to the maximum.
>>-- The next insert should fail, leaving no orphans in the up or down queues.
>>-- The failue is -1579 MAXVALUE exceeded.
>>
>>CREATE TABLE T025T005 (a INTEGER UNSIGNED
+> GENERATED ALWAYS AS IDENTITY (START WITH 4294967291)
+>
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED, primary key(a))
+>
+>--HASH2 PARTITION BY(a)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>insert into T025T005 values(DEFAULT,1,1),(DEFAULT,2,2);
--- 2 row(s) inserted.
>>insert into T025T005 (b,c) values(3,3),(4,4),(5,5),(6,6),(7,7),
+> (8,8),(9,9),(10,10),(11,11);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>select * from T025T005 order by b,c;
A B C
---------- ---------- ----------
4294967291 1 1
4294967292 2 2
--- 2 row(s) selected.
>>
>>-- Check late name resolution
>>-- Prepare insert; drop and recreate table; execute prepared statement;
>>-- expect recompilation
>>--
>>prepare i1 from
+>insert into T025T005 (b,c) values(7,7);
--- SQL command prepared.
>>
>>DROP TABLE T025T005;
--- SQL operation complete.
>>CREATE TABLE T025T005 (a INTEGER UNSIGNED
+> GENERATED ALWAYS AS IDENTITY (START WITH 4294967291)
+>
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED, primary key(a))
+>
+>--HASH2 PARTITION BY(a)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>-- Execute i1, should cause recompilation and successful execution
>>execute i1;
--- 1 row(s) inserted.
>>
>>-- Large inserts are only executed on NSK
>>-- NT does not always handle these large
>>-- inserts well
>>
>>CREATE TABLE T025T006
+> (
+> A INT NOT NULL
+> , B LARGEINT GENERATED BY DEFAULT AS IDENTITY (cache 1000)
+> NOT NULL
+> , C INT NOT NULL
+> , PRIMARY KEY (A)
+> )
+>salt using 4 partitions;
--- SQL operation complete.
>>
>>--control query default def_num_smp_cpus '4';
>>control query shape esp_exchange(cut,2);
--- SQL operation complete.
>>
>>prepare ins06 from
+>upsert using load into T025T006(a, c)
+>select x1+x2*10+x3*100+x4*1000, x2
+> from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(x4)
+>transpose 0,1,2,3,4,5,6,7,8,9 as x3
+>transpose 0,1,2,3,4,5,6,7,8,9 as x2
+>transpose 0,1,2,3,4,5,6,7,8,9 as x1;
--- SQL command prepared.
>>control query shape cut;
--- SQL operation complete.
>>--explain options 'f' ins06;
>>
>>execute ins06;
--- 10000 row(s) inserted.
>>
>>-- Expect 10,000
>>select count(*) from T025T006;
(EXPR)
--------------------
10000
--- 1 row(s) selected.
>>
>>-- Check for duplicates
>>-- Expect none.
>>select case when count(*) = 0 then '0' else '1' end from (select *
+>from (select b, offset(b, 1) b1 from T025T006 sequence by b) T
+>where b = b1);
(EXPR)
------
0
--- 1 row(s) selected.
>>
>>-- Parallel Plans
>>CREATE TABLE T025T007 (a INTEGER UNSIGNED GENERATED ALWAYS AS IDENTITY
+> (cache 1000)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a))
+>--salt using 4 partitions
+>;
--- SQL operation complete.
>>
>>CREATE TABLE T025T008 (a INTEGER UNSIGNED GENERATED ALWAYS AS IDENTITY
+> (cache 1000)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a))
+>--salt using 4 partitions
+>;
--- SQL operation complete.
>>
>>--control query default def_num_smp_cpus '4';
>>control query shape esp_exchange (cut, 2);
--- SQL operation complete.
>>
>>prepare ins07 from
+>upsert using load into t025t007 (b,c)
+>select x1, x2
+> from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T
+>transpose 0,1,2,3,4,5,6,7,8,9 as x3
+>transpose 0,1,2,3,4,5,6,7,8,9 as x2
+>transpose 0,1,2,3,4,5,6,7,8,9 as x1
+>;
--- SQL command prepared.
>>control query shape cut;
--- SQL operation complete.
>>explain options 'f' ins07;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
8 . 9 root 1.00E+004
7 . 8 esp_exchange 1:2(hash2) 1.00E+004
6 . 7 esp_exchange 2(hash2):1 1.00E+004
4 5 6 tuple_flow 1.00E+004
. . 5 trafodion_load h T025T007 1.00E+000
3 . 4 transpose 1.00E+004
2 . 3 transpose 1.00E+003
1 . 2 transpose 1.00E+002
. . 1 tuplelist 1.00E+001
--- SQL operation complete.
>>
>>execute ins07;
--- 10000 row(s) inserted.
>>
>>-- Check for gaps - Expect 3.
>>select count(*)
+>from (select a, offset(a, 1) a1 from t025t007 sequence by a) T
+>where a - a1 > 2;
(EXPR)
--------------------
0
--- 1 row(s) selected.
>>
>>control query shape esp_exchange (cut, 4);
--- SQL operation complete.
>>prepare ins08 from
+>upsert using load into t025t008(b,c) select b,c from t025t007;
--- SQL command prepared.
>>control query shape cut;
--- SQL operation complete.
>>explain options 'f' ins08;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
5 . 6 root 1.00E+002
4 . 5 esp_exchange 1:4(hash2) 1.00E+002
3 . 4 esp_exchange 4(hash2):1 1.00E+002
1 2 3 tuple_flow 1.00E+002
. . 2 trafodion_load h T025T008 1.00E+000
. . 1 trafodion_scan T025T007 1.00E+002
--- SQL operation complete.
>>
>>execute ins08;
--- 10000 row(s) inserted.
>>
>>-- Check for gaps - Expect 3
>>select count(*)
+>from (select a, offset(a, 1) a1 from t025t008 sequence by a) T
+>where a - a1 > 2;
(EXPR)
--------------------
0
--- 1 row(s) selected.
>>
>>-- Utilities testing
>>
>>drop table T025T003 cascade;
--- SQL operation complete.
>>drop table T025T004 cascade;
--- SQL operation complete.
>>
>>-- CREATE TABLE LIKE
>>
>>-- First GENERATED BY DEFAULT AS IDENTITY
>>
>>
>>CREATE TABLE T025T003 (a LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> ( MINVALUE 10
+> MAXVALUE 99999
+> START WITH 10)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) );
--- SQL operation complete.
>>
>>insert into T025T003 values(DEFAULT,1,1);
--- 1 row(s) inserted.
>>select * from T025T003;
A B C
-------------------- ---------- ----------
10 1 1
--- 1 row(s) selected.
>>
>>CREATE TABLE T025T004 LIKE T025T003;
--- SQL operation complete.
>>
>>insert into T025T004 values(DEFAULT,1,1);
--- 1 row(s) inserted.
>>select * from T025T004 order by a;
A B C
-------------------- ---------- ----------
10 1 1
--- 1 row(s) selected.
>>
>>showddl T025T003;
CREATE TABLE TRAFODION.S025.T025T003
(
A LARGEINT GENERATED BY DEFAULT AS IDENTITY
( START WITH 10 INCREMENT BY 1 MAXVALUE 99999 MINVALUE 10 CACHE 25
NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>showddl T025T004;
CREATE TABLE TRAFODION.S025.T025T004
(
A LARGEINT GENERATED BY DEFAULT AS IDENTITY
( START WITH 10 INCREMENT BY 1 MAXVALUE 99999 MINVALUE 10 CACHE 25
NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>drop table T025T003 cascade;
--- SQL operation complete.
>>drop table T025T004 cascade;
--- SQL operation complete.
>>
>>-- Second GENERATED ALWAYS AS IDENTITY
>>
>>CREATE TABLE T025T003 (a LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> ( MINVALUE 10
+> MAXVALUE 99999
+> START WITH 10)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(a) )
+> ;
--- SQL operation complete.
>>
>>CREATE UNIQUE INDEX IT025T003 ON T025T003(A) no populate;
--- SQL operation complete.
>>
>>insert into T025T003 values(DEFAULT,1,1);
--- 1 row(s) inserted.
>>select * from T025T003 order by b,c;
A B C
-------------------- ---------- ----------
11 1 1
--- 1 row(s) selected.
>>
>>CREATE TABLE T025T004 LIKE T025T003;
--- SQL operation complete.
>>
>>insert into T025T004 values(DEFAULT,1,1);
--- 1 row(s) inserted.
>>select * from T025T004 order by b,c;
A B C
-------------------- ---------- ----------
11 1 1
--- 1 row(s) selected.
>>
>>showddl T025T003;
CREATE TABLE TRAFODION.S025.T025T003
(
A LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 10 INCREMENT BY 1 MAXVALUE 99999 MINVALUE 10 CACHE 25 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>showddl T025T004;
CREATE TABLE TRAFODION.S025.T025T004
(
A LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 10 INCREMENT BY 1 MAXVALUE 99999 MINVALUE 10 CACHE 25 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Test populate index
>>populate index it025t003 on t025t003;
--- SQL operation complete.
>>
>>-- Constraint test looking for orphan entries
>>
>>drop table T025T003 cascade;
--- SQL operation complete.
>>
>>create table T025T003
+> (
+> c_surrogate_key LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> NOT NULL NOT DROPPABLE heading 'identity col',
+> c_nationfkey LARGEINT not null not droppable,
+> c_custkey int not null not droppable,
+> c_nationkey int not null not droppable,
+> c_phone char(15) not null not droppable,
+> c_acctbal numeric(12,2) not null not droppable,
+> c_mktsegment char(10) not null not droppable,
+> c_name varchar(25) not null not droppable,
+> c_address varchar(40) not null not droppable,
+> c_comment varchar(117) not null not droppable,
+> primary key (c_surrogate_key) not droppable , check (c_surrogate_key > 52)
+> )
+> store by ( c_surrogate_key asc )
+> ;
--- SQL operation complete.
>>
>>-- Should show 8101 check constr violation
>>
>>insert into T025T003 values
+>(DEFAULT,-3,0,1,'dasf',2.1,'dfaf','dfa','dfa','d');
*** ERROR[8101] The operation is prevented by check constraint TRAFODION.S025.T025T003_573479767_9484 on table TRAFODION.S025.T025T003.
--- 0 row(s) inserted.
>>
>>-- Should show 8101 check constr violation
>>
>>insert into T025T003 values
+>(DEFAULT,-2,1,1,'dasf',2.1,'dfaf','dfa','dfa','d'),
+>(DEFAULT,-1,2,1,'dasf',2.1,'dfaf','dfa','dfa','d');
*** ERROR[8101] The operation is prevented by check constraint TRAFODION.S025.T025T003_573479767_9484 on table TRAFODION.S025.T025T003.
--- 0 row(s) inserted.
>>
>>-- Should show zero rows
>>
>>select * from T025T003;
--- 0 row(s) selected.
>>
>>drop table T025T002 cascade;
--- SQL operation complete.
>>drop table T025T003 cascade;
--- SQL operation complete.
>>drop table T025T004 cascade;
--- SQL operation complete.
>>
>>-- Alter table alter column set SG option testing
>>-- Test first with LARGEINT GENERATED ALWAYS AS IDENTITY column
>>
>>CREATE TABLE T025T002 (surrogate_key LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 98
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(surrogate_key) );
--- SQL operation complete.
>>
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
SURROGATE_KEY LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 98 INCREMENT BY 1 MAXVALUE 100 MINVALUE 50 CACHE 3 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Fourth insert should fail with -1579 MAXVALUE exceeded
>>
>>insert into T025T002 values(default,1,1);
--- 1 row(s) inserted.
>>insert into T025T002 values(default,1,1);
--- 1 row(s) inserted.
>>insert into T025T002 values(default,1,1);
--- 1 row(s) inserted.
>>insert into T025T002 values(default,1,1);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>select * from T025T002 order by surrogate_key,b,c;
SURROGATE_KEY B C
-------------------- ---------- ----------
98 1 1
99 1 1
100 1 1
--- 3 row(s) selected.
>>
>>-- Alter the table to allow a new MAXVALUE
>>
>>alter table T025T002 alter column surrogate_key set MAXVALUE 900;
--- SQL operation complete.
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
SURROGATE_KEY LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 98 INCREMENT BY 1 MAXVALUE 900 MINVALUE 50 CACHE 3 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Alter the table to again allow a new MAXVALUE
>>
>>alter table T025T002 alter column surrogate_key set MAXVALUE 800;
--- SQL operation complete.
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
SURROGATE_KEY LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 98 INCREMENT BY 1 MAXVALUE 800 MINVALUE 50 CACHE 3 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Alter the table to again allow a new INCREMENT BY
>>
>>alter table T025T002 alter column surrogate_key set INCREMENT BY 2;
--- SQL operation complete.
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
SURROGATE_KEY LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 98 INCREMENT BY 2 MAXVALUE 800 MINVALUE 50 CACHE 3 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- New insert should succeed
>>
>>insert into T025T002 values(default,1,1);
--- 1 row(s) inserted.
>>
>>select * from T025T002 order by surrogate_key,b,c;
SURROGATE_KEY B C
-------------------- ---------- ----------
98 1 1
99 1 1
100 1 1
101 1 1
--- 4 row(s) selected.
>>
>>-- Negative testing
>>
>>-- Should show -1592
>>
>>alter table T025T002 alter column surrogate_key set MINVALUE 900;
*** ERROR[1592] MINVALUE cannot be specified for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1592
>>
>>alter table T025T002 alter column surrogate_key set MINVALUE 900 MAXVALUE 900;
*** ERROR[1592] MINVALUE cannot be specified for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1577
>>
>>alter table T025T002 alter column surrogate_key set MAXVALUE 99;
*** ERROR[1577] CACHE value must be greater than 1 and less than or equal to (maxValue-startValue+1)/incrementValue for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1572
>>
>>alter table T025T002 alter column surrogate_key set MAXVALUE -1;
*** ERROR[1572] MAXVALUE value cannot be a negative number for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1576
>>
>>alter table T025T002 alter column surrogate_key set MAXVALUE 9223372036854775808;
*** ERROR[1576] MAXVALUE value is greater than maximum allowed for this sequence.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Should show -1590
>>
>>alter table T025T002 alter column b set INCREMENT BY 900;
*** ERROR[1590] Column B is not an IDENTITY column.
--- SQL operation failed with errors.
>>
>>-- Should show -1009
>>
>>alter table T025T002 alter column f set INCREMENT BY 900;
*** ERROR[1009] Column F does not exist in the specified table.
--- SQL operation failed with errors.
>>
>>-- Should show -1572
>>
>>alter table T025T002 alter column surrogate_key set INCREMENT BY -1;
*** ERROR[1572] INCREMENT BY value cannot be a negative number for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1576
>>
>>alter table T025T002 alter column surrogate_key set INCREMENT BY 9223372036854775808;
*** ERROR[1576] INCREMENT BY value is greater than maximum allowed for this sequence.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Should show -1575
>>
>>alter table T025T002 alter column surrogate_key set INCREMENT BY 801;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show error -1571
>>
>>alter table T025T002 alter column surrogate_key set INCREMENT BY 0;
*** ERROR[1571] INCREMENT BY value cannot be zero for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should same results as before negative testing
>>
>>showddl T025T002;
CREATE TABLE TRAFODION.S025.T025T002
(
SURROGATE_KEY LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 98 INCREMENT BY 2 MAXVALUE 800 MINVALUE 50 CACHE 3 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>alter table T025T002 alter column surrogate_key set NO MAXVALUE;
--- SQL operation complete.
>>showddl t025t002;
CREATE TABLE TRAFODION.S025.T025T002
(
SURROGATE_KEY LARGEINT GENERATED ALWAYS AS IDENTITY (
START WITH 98 INCREMENT BY 2 NO MAXVALUE MINVALUE 50 CACHE 3 NO
CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- repeat tests with column type of INT UNSIGNED GENERATED BY DEFAULT
>>
>>CREATE TABLE T025T003 (b INT UNSIGNED NOT NULL,
+> surrogate_key int unsigned
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 98
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> c INT UNSIGNED,
+> primary key(surrogate_key) );
--- SQL operation complete.
>>
>>insert into T025T003 values(1,default,1);
--- 1 row(s) inserted.
>>
>>showddl T025T003;
CREATE TABLE TRAFODION.S025.T025T003
(
B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, SURROGATE_KEY INT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 100 MINVALUE 50
CACHE 3 NO CYCLE INT UNSIGNED ) NOT NULL NOT DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>insert into T025T003 values(1,default,1);
--- 1 row(s) inserted.
>>insert into T025T003 values(1,default,1);
--- 1 row(s) inserted.
>>
>>-- Should fail with -1579
>>
>>insert into T025T003 values(1,default,1);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>select * from T025T003 order by b,surrogate_key,c;
B SURROGATE_KEY C
---------- ------------- ----------
1 98 1
1 99 1
1 100 1
--- 3 row(s) selected.
>>
>>alter table T025T003 alter column surrogate_key set MAXVALUE 900;
--- SQL operation complete.
>>
>>showddl T025T003;
CREATE TABLE TRAFODION.S025.T025T003
(
B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, SURROGATE_KEY INT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 900 MINVALUE 50
CACHE 3 NO CYCLE INT UNSIGNED ) NOT NULL NOT DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>alter table T025T003 alter column surrogate_key set MAXVALUE 800;
--- SQL operation complete.
>>
>>showddl T025T003;
CREATE TABLE TRAFODION.S025.T025T003
(
B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, SURROGATE_KEY INT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 800 MINVALUE 50
CACHE 3 NO CYCLE INT UNSIGNED ) NOT NULL NOT DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Should succeed
>>
>>insert into T025T003 values(1,default,1);
--- 1 row(s) inserted.
>>
>>select * from T025T003 order by b,surrogate_key,c;
B SURROGATE_KEY C
---------- ------------- ----------
1 98 1
1 99 1
1 100 1
1 101 1
--- 4 row(s) selected.
>>
>>-- Should show -1575
>>
>>alter table T025T003 alter column surrogate_key set INCREMENT BY 4294967296;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1576
>>
>>alter table T025T003 alter column surrogate_key set MAXVALUE 4294967296;
*** ERROR[1576] MAXVALUE value is greater than maximum allowed for this sequence.
--- SQL operation failed with errors.
>>
>>-- repeat tests with column type of INT UNSIGNED
>>
>>CREATE TABLE T025T004 (b INT UNSIGNED NOT NULL,
+> surrogate_key smallint unsigned
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 98
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> c INT UNSIGNED,
+> primary key(b) );
--- SQL operation complete.
>>
>>insert into T025T004 values(1,default,1);
--- 1 row(s) inserted.
>>
>>showddl T025T004;
CREATE TABLE TRAFODION.S025.T025T004
(
B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, SURROGATE_KEY SMALLINT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 100 MINVALUE 50
CACHE 3 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (B ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>insert into T025T004 values(2,default,1);
--- 1 row(s) inserted.
>>insert into T025T004 values(3,default,1);
--- 1 row(s) inserted.
>>
>>-- Should fail with -1579
>>
>>insert into T025T004 values(4,default,1);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>select * from T025T004 order by b,surrogate_key,c;
B SURROGATE_KEY C
---------- ------------- ----------
1 98 1
2 99 1
3 100 1
--- 3 row(s) selected.
>>
>>alter table T025T004 alter column surrogate_key set MAXVALUE 900;
--- SQL operation complete.
>>
>>showddl T025T004;
CREATE TABLE TRAFODION.S025.T025T004
(
B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, SURROGATE_KEY SMALLINT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 900 MINVALUE 50
CACHE 3 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (B ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>alter table T025T004 alter column surrogate_key set MAXVALUE 800;
--- SQL operation complete.
>>
>>showddl T025T004;
CREATE TABLE TRAFODION.S025.T025T004
(
B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, SURROGATE_KEY SMALLINT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 800 MINVALUE 50
CACHE 3 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (B ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>
>>-- Should succeed
>>
>>insert into T025T004 values(5,default,1);
--- 1 row(s) inserted.
>>
>>select * from T025T004 order by b,surrogate_key,c;
B SURROGATE_KEY C
---------- ------------- ----------
1 98 1
2 99 1
3 100 1
5 101 1
--- 4 row(s) selected.
>>
>>-- Should show -1575
>>
>>alter table T025T004 alter column surrogate_key set INCREMENT BY 65536;
*** ERROR[1575] INCREMENT BY value cannot be greater than the difference between MINVALUE and MAXVALUE for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- Should show -1576
>>
>>alter table T025T004 alter column surrogate_key set MAXVALUE 65536;
*** ERROR[1576] MAXVALUE value is greater than maximum allowed for this sequence.
--- SQL operation failed with errors.
>>
>>-- Test largeint error handling
>>
>>drop table T025T002 cascade;
--- SQL operation complete.
>>
>>CREATE TABLE T025T002 (surrogate_key LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> (START WITH 9223372036854775800
+> INCREMENT BY 1
+> MAXVALUE 9223372036854775801
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(surrogate_key) )
+>--HASH2 PARTITION BY(surrogate_key)
+> ;
--- SQL operation complete.
>>-- (ADD LOCATION $$partition$$);
>>
>>prepare s1 from
+>insert into T025T002 (b,c) values(1,1);
--- SQL command prepared.
>>
>>-- Both should succeed
>>insert into T025T002 values(default,1,1);
--- 1 row(s) inserted.
>>insert into T025T002 values(default,1,1);
--- 1 row(s) inserted.
>>
>>-- Should fail
>>insert into T025T002 values(default,1,1);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>-- alter the table to a new maximum, but expect failure
>>alter table T025T002 alter column surrogate_key set MAXVALUE 10;
*** ERROR[1573] START WITH value must be greater than or equal to MINVALUE and less than or equal to MAXVALUE for ALTER SEQUENCE.
--- SQL operation failed with errors.
>>
>>-- alter the table to a new maximum, should succeed
>>
>>alter table T025T002 alter column surrogate_key set MAXVALUE 9223372036854775805;
--- SQL operation complete.
>>
>>-- execute should cause a recompilation and it should succeed
>>
>>execute s1;
--- 1 row(s) inserted.
>>
>>select * from T025T002 order by surrogate_key, b,c;
SURROGATE_KEY B C
-------------------- ---------- ----------
9223372036854775800 1 1
9223372036854775801 1 1
9223372036854775802 1 1
--- 3 row(s) selected.
>>
>>-- Test single IDENTITY column table expecting error -3431
>>
>>drop table T025T002 cascade;
--- SQL operation complete.
>>
>>-- Test GENERATED ALWAYS AS single column table
>>
>>CREATE TABLE T025T002 (surrogate_key LARGEINT
+> GENERATED ALWAYS AS IDENTITY
+> NOT NULL NOT DROPPABLE,
+> primary key(surrogate_key) );
--- SQL operation complete.
>>
>>insert into T025T002 values(DEFAULT),(DEFAULT);
--- 2 row(s) inserted.
>>
>>drop table T025T002 cascade;
--- SQL operation complete.
>>
>>-- Test GENERATED BY DEFAULT AS single column table
>>
>>CREATE TABLE T025T002 (surrogate_key LARGEINT
+> GENERATED BY DEFAULT AS IDENTITY
+> NOT NULL NOT DROPPABLE,
+> primary key(surrogate_key) );
--- SQL operation complete.
>>
>>insert into T025T002 values(DEFAULT),(DEFAULT);
--- 2 row(s) inserted.
>>
>>-- CREATE VOLATILE TABLE testing
>>
>>CREATE VOLATILE TABLE T025T00V2 (surrogate_key smallint unsigned
+> GENERATED BY DEFAULT AS IDENTITY
+> (START WITH 98
+> INCREMENT BY 1
+> MAXVALUE 100
+> MINVALUE 50
+> NO CYCLE)
+> NOT NULL NOT DROPPABLE,
+> b INT UNSIGNED NOT NULL,
+> c INT UNSIGNED,
+> primary key(surrogate_key) );
--- SQL operation complete.
>>
>>showddl T025T00V2;
CREATE VOLATILE TABLE T025T00V2
(
SURROGATE_KEY SMALLINT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY ( START WITH 98 INCREMENT BY 1 MAXVALUE 100 MINVALUE 50
CACHE 3 NO CYCLE SMALLINT UNSIGNED ) NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
, PRIMARY KEY (SURROGATE_KEY ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>invoke T025T00V2;
-- Definition of Trafodion volatile table T025T00V2
-- Definition current Tue Jul 2 18:37:56 2019
(
SURROGATE_KEY SMALLINT UNSIGNED GENERATED BY DEFAULT AS
IDENTITY NOT NULL NOT DROPPABLE
, B INT UNSIGNED NO DEFAULT NOT NULL NOT
DROPPABLE
, C INT UNSIGNED DEFAULT NULL
)
PRIMARY KEY (SURROGATE_KEY ASC)
--- SQL operation complete.
>>
>>-- Fourth insert should fail
>>
>>insert into T025T00V2(b,c) values(1,1);
--- 1 row(s) inserted.
>>insert into T025T00V2 values(default,1,1);
--- 1 row(s) inserted.
>>insert into T025T00V2 values(default,1,1);
--- 1 row(s) inserted.
>>insert into T025T00V2 values(default,1,1);
*** ERROR[1579] This sequence has reached its max and cannot provide a new value.
--- 0 row(s) inserted.
>>
>>-- salted identity columns
>>create table t025t009(a largeint generated by default as identity not null primary key, b int) salt using 4 partitions;
--- SQL operation complete.
>>showddl t025t009;
CREATE TABLE TRAFODION.S025.T025T009
(
A LARGEINT GENERATED BY DEFAULT AS IDENTITY
( START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
, B INT DEFAULT NULL
, PRIMARY KEY (A ASC)
)
SALT USING 4 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
>>upsert using load into t025t009 values (default, 1), (default, 2);
--- 2 row(s) inserted.
>>insert into t025t009 values (default, 1), (default, 2);
--- 2 row(s) inserted.
>>upsert into t025t009 values (default, 1), (default, 2);
--- 2 row(s) inserted.
>>select "_SALT_", a, b from t025t009;
_SALT_ A B
---------- -------------------- -----------
0 2 2
1 1 1
1 4 2
3 3 1
3 5 1
3 6 2
--- 6 row(s) selected.
>>
>>-- identity cols and default values
>>create table T025T010 (a largeint generated always as identity not null primary key,
+> b int default 10);
--- SQL operation complete.
>>create table T025T010s (a largeint generated always as identity not null primary key,
+> b int default 10)
+> salt using 4 partitions;
--- SQL operation complete.
>>
>>prepare s from insert into T025T010 default values;
--- SQL command prepared.
>>prepare s from insert into T025T010 values (default, default);
--- SQL command prepared.
>>prepare s from insert into T025T010 values (default, default+1);
--- SQL command prepared.
>>prepare s from insert into T025T010 (a) values (default);
--- SQL command prepared.
>>prepare s from insert into T025T010 (b) values (2);
--- SQL command prepared.
>>
>>prepare s from insert into T025T010s default values;
--- SQL command prepared.
>>prepare s from insert into T025T010s values (default, default);
--- SQL command prepared.
>>prepare s from insert into T025T010 values (default, default+1);
--- SQL command prepared.
>>prepare s from insert into T025T010s (a) values (default);
--- SQL command prepared.
>>prepare s from insert into T025T010s (b) values (2);
--- SQL command prepared.
>>
>>prepare s from insert into T025T010 (b) select b from T025T010s;
--- SQL command prepared.
>>
>>-- identity cols default value negative tests, should return error 3428
>>prepare s from insert into T025T010 (a) values (1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>prepare s from insert into T025T010 values (default+1, default);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>prepare s from insert into T025T010 (a) values (default+1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>prepare s from insert into T025T010s (a) values (1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>prepare s from insert into T025T010s values (default+1, default);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>prepare s from insert into T025T010s (a) values (default+1);
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>prepare s from insert into T025T010 (a) select a from T025T010s;
*** ERROR[3428] IDENTITY column A defined as GENERATED ALWAYS cannot accept values specified by the user.
*** ERROR[8822] The statement was not prepared.
>>
>>-- identity col cannot be added
>>alter table t025t010 add column c largeint generated by default as identity;
*** ERROR[1514] Cannot add an IDENTITY column using ALTER TABLE command.
--- SQL operation failed with errors.
>>
>>-- drop of table with identity cols
>>insert into t025t010 default values;
--- 1 row(s) inserted.
>>select * from t025t010;
A B
-------------------- -----------
1 10
--- 1 row(s) selected.
>>alter table t025t010 drop column b;
--- SQL operation complete.
>>invoke t025t010;
-- Definition of Trafodion table TRAFODION.S025.T025T010
-- Definition current Tue Jul 2 18:40:58 2019
(
A LARGEINT GENERATED ALWAYS AS IDENTITY NOT
NULL NOT DROPPABLE
)
PRIMARY KEY (A ASC)
--- SQL operation complete.
>>select * from t025t010;
A
--------------------
1
--- 1 row(s) selected.
>>
>>-- this alter drop column should fail
>>alter table t025t010 drop column a;
*** ERROR[1420] Column A cannot be dropped or altered as it is part of the table's primary key.
--- SQL operation failed with errors.
>>
>>-- reset option cannot be used during create
>>create table t025t011 (a largeint generated by default as identity (start with 10 reset) not null);
*** ERROR[15001] A syntax error occurred at or before:
create table t025t011 (a largeint generated by default as identity (start with
10 reset) not null);
^ (87 characters from start of SQL statement)
*** ERROR[8822] The statement was not prepared.
>>
>>-- reset option can be used with an alter
>>create table t025t011 (a largeint generated by default as identity(no cache) not null);
--- SQL operation complete.
>>insert into t025t011 default values;
--- 1 row(s) inserted.
>>insert into t025t011 default values;
--- 1 row(s) inserted.
>>select * from t025t011 order by a;
A
--------------------
1
2
--- 2 row(s) selected.
>>alter table t025t011 alter column a reset;
--- SQL operation complete.
>>insert into t025t011 default values;
--- 1 row(s) inserted.
>>-- should return 1,1,2
>>select * from t025t011 order by a;
A
--------------------
1
1
2
--- 3 row(s) selected.
>>
>>
>>-- Clean up test
>>
>>drop table T025T001;
--- SQL operation complete.
>>drop table T025T002;
--- SQL operation complete.
>>drop table T025T00V2;
--- SQL operation complete.
>>drop table T025T003 cascade;
--- SQL operation complete.
>>drop table T025T004 cascade;
--- SQL operation complete.
>>drop table T025T005;
--- SQL operation complete.
>>drop table T025T006;
--- SQL operation complete.
>>drop table T025T007;
--- SQL operation complete.
>>drop table T025T008;
--- SQL operation complete.
>>drop table T025T009;
--- SQL operation complete.
>>drop table T025T010;
--- SQL operation complete.
>>drop table T025T010s;
--- SQL operation complete.
>>drop schema s025 cascade;
--- SQL operation complete.
>>
>>
>>exit;
End of MXCI Session