>>--
>>?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