-- Test: TEST025
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- Functionality: Tests identity cols functionality

?section ddl
drop schema s025 cascade;
drop table T025T001;
drop table T025T002;
drop table T025T00V2;
drop table T025T003 cascade;
drop table T025T004 cascade;
drop table T025T005;
drop table T025T006;
drop table T025T007;
drop table T025T008;
drop table T025T009;
drop table T025T010;
drop table T025T010s;

?section prep
cqd TRAF_USE_REGION_XN 'OFF';
create schema S025;
set schema S025;

log LOG025 clear;
--
?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) );
--
SHOWDDL T025T001;

-- 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) )
;

--
SHOWDDL T025T002;

drop table T025T002;

-- 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) )
;
                    
-- 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;                                     
drop table T025T002;

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) )
;
                    
-- 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;                                     
drop table T025T002;

-- 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) )
;
                    
-- 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;
drop table T025T002;

-- 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) )
;
                    
-- 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;
drop table T025T002;

-- 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) )
;
                    
-- 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;
drop table T025T002; 
    
-- 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) )
;
                    
-- 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;
drop table T025T002; 
                                                                     
-- 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) )
;

-- 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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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)
                    ;-- (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);
insert into T025T001 values(DEFAULT,2,2);
insert into T025T001 values(3333,3,3);

-- Insert another row using DEFAULT, this should succeed

insert into T025T001 values(DEFAULT,4,4);

-- Insert another row using the same user supplied value.
-- This should receive -8102 unique constraint error

insert into T025T001 values(3333,5,5);

-- Insert again, with a unique user supplied value.

insert into T025T001 values(6666,6,6);

-- Insert again, using a number assigned by SG.       
-- Should also receive -8102 error

insert into T025T001 values(2,7,7);

select * from T025T001 order by b,c;

-- Should succeed, an IDENTITY column of type
-- GENERATED BY DEFAULT should be updatable

update T025T001 set a = 25 where a = 2;

select * from T025T001 order by b,c;

-- 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)
                    ;-- (ADD LOCATION $$partition$$);

insert into T025T002 (b,c) values(1,1);  
insert into T025T002 (b,c) values(2,2);
insert into T025T002 (b,c) values(3,3);  
insert into T025T002 (b,c) values(4,4);

select * from T025T002 order by b,c;

-- 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)
                    ;-- (ADD LOCATION $$partition$$); 

-- First insert should succeed

insert into T025T003 values(DEFAULT, 1,1),(DEFAULT, 2,2);

-- Should fail with error -3428

insert into T025T003 values(9223372036854775804,1,1);

-- Should fail with error -3428

insert into T025T003 values(9223372036854775804,1,1),(9223372036854775805,1,1);

-- Should fail with error -3428 to an update
-- for an IDENTITY column of type GENERATED ALWAYS

update T025T003 set a=300 where a=9223372036854775802;

-- Should fail with error -3428

insert into T025T003 values(DEFAULT,1,1), (9223372036854775804,1,1);

-- Insert should succeed
insert into T025T003(b,c) values(1,1),(2,2);

-- Insert should fail with MAXVALUE exceeded

insert into T025T003 (b,c) values(1,1),(2,2);

select * from T025T003 order by a,b,c;

-- 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)
                    ;-- (ADD LOCATION $$partition$$); 

insert into T025T004 (b,c) values(1,1);
insert into T025T004 (b,c) values(2,2);
insert into T025T004 (b,c) values(3,3);

select * from T025T004 order by b,c;

-- 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)
                    ;-- (ADD LOCATION $$partition$$); 

-- Insert using DEFAULT for IDENTITY column

insert into T025T005 (b,c) values(1,1),(2,2);
insert into T025T005 (b,c) values(3,3);
insert into T025T005 (b,c) values(4,4);
insert into T025T005 (b,c) values(5,5),(6,6);


select * from T025T005 order by b,c;
drop table t025t005;

-- 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));

-- Insert using DEFAULT for IDENTITY column

insert into T025T005 (b,c) values(1,1),(2,2),(3,3);


select * from T025T005 order by b,c;
drop table t025t005;

-- 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)
                    ;-- (ADD LOCATION $$partition$$); 

insert into T025T005 values(DEFAULT,1,1),(DEFAULT,2,2);
insert into T025T005 (b,c) values(3,3),(4,4),(5,5),(6,6),(7,7),
                                 (8,8),(9,9),(10,10),(11,11);

select * from T025T005 order by b,c;

-- 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);

DROP TABLE T025T005;
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)
                    ;-- (ADD LOCATION $$partition$$); 

-- Execute i1, should cause recompilation and successful execution
execute i1;

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

--control query default def_num_smp_cpus '4';
control query shape esp_exchange(cut,4);

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;
control query shape cut;
--explain options 'f' ins06;

execute ins06;

-- Expect 10,000
select count(*) from T025T006;

-- 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);

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

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
;

--control query default def_num_smp_cpus '4';
control query shape esp_exchange (cut, 4);

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
;
control query shape cut;
explain options 'f' ins07;

execute ins07;

-- Check for gaps - Expect 3.
select count(*)
from (select a, offset(a, 1) a1 from t025t007 sequence by a) T
where a - a1 > 2;

control query shape esp_exchange (cut, 4);
prepare ins08 from
upsert using load into t025t008(b,c) select b,c from t025t007;
control query shape cut;
explain options 'f' ins08;

execute ins08;

-- Check for gaps - Expect 3
select count(*)
from (select a, offset(a, 1) a1 from t025t008 sequence by a) T
where a - a1 > 2;

-- Utilities testing

drop table T025T003 cascade;
drop table T025T004 cascade;

-- 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) );

insert into T025T003 values(DEFAULT,1,1);
select * from T025T003;

CREATE TABLE T025T004 LIKE T025T003;

insert into T025T004 values(DEFAULT,1,1);
select * from T025T004 order by a;

showddl T025T003;
showddl T025T004;

drop table T025T003 cascade;
drop table T025T004 cascade;

-- 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) )
                       ;

CREATE UNIQUE INDEX IT025T003 ON T025T003(A) no populate;

insert into T025T003 values(DEFAULT,1,1);
select * from T025T003 order by b,c;

CREATE TABLE T025T004 LIKE T025T003;

insert into T025T004 values(DEFAULT,1,1);
select * from T025T004 order by b,c;

showddl T025T003;
showddl T025T004;

-- Test populate index
populate index it025t003 on t025t003;

-- Constraint test looking for orphan entries

drop table T025T003 cascade;

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 )	
  ;	
	
-- Should show 8101 check constr violation
	
insert into T025T003 values  	
(DEFAULT,-3,0,1,'dasf',2.1,'dfaf','dfa','dfa','d');
	
-- 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');	

-- Should show zero rows

select * from T025T003;

drop table T025T002 cascade;
drop table T025T003 cascade;
drop table T025T004 cascade;

-- 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) );


showddl T025T002;

-- Fourth insert should fail with -1579 MAXVALUE exceeded

insert into T025T002 values(default,1,1);
insert into T025T002 values(default,1,1);
insert into T025T002 values(default,1,1);
insert into T025T002 values(default,1,1);

select * from T025T002 order by surrogate_key,b,c;

-- Alter the table to allow a new MAXVALUE

alter table T025T002 alter column surrogate_key set MAXVALUE 900;

showddl T025T002;

-- Alter the table to again allow a new MAXVALUE

alter table T025T002 alter column surrogate_key set MAXVALUE 800;

showddl T025T002;

-- Alter the table to again allow a new INCREMENT BY

alter table T025T002 alter column surrogate_key set INCREMENT BY 2;

showddl T025T002;

-- New insert should succeed

insert into T025T002 values(default,1,1);

select * from T025T002 order by surrogate_key,b,c;

-- Negative testing

-- Should show -1592

alter table T025T002 alter column surrogate_key set MINVALUE 900;

-- Should show -1592

alter table T025T002 alter column surrogate_key set MINVALUE 900 MAXVALUE 900;

-- Should show -1577

alter table T025T002 alter column surrogate_key set MAXVALUE 99;

-- Should show -1572

alter table T025T002 alter column surrogate_key set MAXVALUE -1;

-- Should show -1576

alter table T025T002 alter column surrogate_key set MAXVALUE 9223372036854775808;

-- Should show -1590

alter table T025T002 alter column b set INCREMENT BY 900;

-- Should show -1009

alter table T025T002 alter column f set INCREMENT BY 900;

-- Should show -1572

alter table T025T002 alter column surrogate_key set INCREMENT BY -1;

-- Should show -1576

alter table T025T002 alter column surrogate_key set INCREMENT BY 9223372036854775808;

-- Should show -1575

alter table T025T002 alter column surrogate_key set INCREMENT BY 801;

-- Should show error -1571

alter table T025T002 alter column surrogate_key set INCREMENT BY 0;

-- Should same results as before negative testing

showddl T025T002;

alter table T025T002 alter column surrogate_key set NO MAXVALUE;
showddl t025t002;

-- 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) );

insert into T025T003 values(1,default,1);

showddl T025T003;

insert into T025T003 values(1,default,1);
insert into T025T003 values(1,default,1);

-- Should fail with -1579

insert into T025T003 values(1,default,1);

select * from T025T003 order by b,surrogate_key,c;

alter table T025T003 alter column surrogate_key set MAXVALUE 900;

showddl T025T003;

alter table T025T003 alter column surrogate_key set MAXVALUE 800;

showddl T025T003;

-- Should succeed

insert into T025T003 values(1,default,1);

select * from T025T003 order by b,surrogate_key,c;

-- Should show -1575

alter table T025T003 alter column surrogate_key set INCREMENT BY 4294967296;

-- Should show -1576

alter table T025T003 alter column surrogate_key set MAXVALUE 4294967296;

-- 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) );

insert into T025T004 values(1,default,1);

showddl T025T004;

insert into T025T004 values(2,default,1);
insert into T025T004 values(3,default,1);

-- Should fail with -1579

insert into T025T004 values(4,default,1);

select * from T025T004 order by b,surrogate_key,c;

alter table T025T004 alter column surrogate_key set MAXVALUE 900;

showddl T025T004;

alter table T025T004 alter column surrogate_key set MAXVALUE 800;

showddl T025T004;

-- Should succeed

insert into T025T004 values(5,default,1);

select * from T025T004 order by b,surrogate_key,c;

-- Should show -1575

alter table T025T004 alter column surrogate_key set INCREMENT BY 65536;

-- Should show -1576

alter table T025T004 alter column surrogate_key set MAXVALUE 65536;

-- Test largeint error handling

drop table T025T002 cascade;

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)
                    ;-- (ADD LOCATION $$partition$$); 

prepare s1 from
insert into T025T002 (b,c) values(1,1);

-- Both should succeed
insert into T025T002 values(default,1,1);
insert into T025T002 values(default,1,1);

-- Should fail
insert into T025T002 values(default,1,1);

-- alter the table to a new maximum, but expect failure
alter table T025T002 alter column surrogate_key set MAXVALUE 10;

-- alter the table to a new maximum, should succeed

alter table T025T002 alter column surrogate_key set MAXVALUE 9223372036854775805; 

-- execute should cause a recompilation and it should succeed

execute s1;

select * from T025T002 order by surrogate_key, b,c;

-- Test single IDENTITY column table expecting error -3431

drop table T025T002 cascade;

-- 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) );

insert into T025T002 values(DEFAULT),(DEFAULT);

drop table T025T002 cascade;

-- 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) );

insert into T025T002 values(DEFAULT),(DEFAULT);

-- 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) );

showddl T025T00V2;
invoke T025T00V2;

-- Fourth insert should fail

insert into T025T00V2(b,c) values(1,1);
insert into T025T00V2 values(default,1,1);
insert into T025T00V2 values(default,1,1);
insert into T025T00V2 values(default,1,1);

-- salted identity columns
create table t025t009(a largeint generated by default as identity not null primary key, b int) salt using 4 partitions;
showddl t025t009;
upsert using load into t025t009 values (default, 1), (default, 2);
insert into t025t009 values (default, 1), (default, 2);
upsert into t025t009 values (default, 1), (default, 2);
select "_SALT_", a, b from t025t009;

-- identity cols and default values
create table T025T010 (a largeint generated always as identity not null primary key, 
                         b int default 10);
create table T025T010s (a largeint generated always as identity not null primary key, 
                         b int default 10) 
  salt using 4 partitions;

prepare s from insert into T025T010 default values;
prepare s from insert into T025T010 values (default, default);
prepare s from insert into T025T010 values (default, default+1);
prepare s from insert into T025T010 (a) values (default);
prepare s from insert into T025T010 (b) values (2);

prepare s from insert into T025T010s default values;
prepare s from insert into T025T010s values (default, default);
prepare s from insert into T025T010 values (default, default+1);
prepare s from insert into T025T010s (a) values (default);
prepare s from insert into T025T010s (b) values (2);

prepare s from insert into T025T010 (b) select b from T025T010s;

-- identity cols default value negative tests, should return error 3428
prepare s from insert into T025T010 (a) values (1);
prepare s from insert into T025T010 values (default+1, default);
prepare s from insert into T025T010 (a) values (default+1);
prepare s from insert into T025T010s (a) values (1);
prepare s from insert into T025T010s values (default+1, default);
prepare s from insert into T025T010s (a) values (default+1);
prepare s from insert into T025T010 (a) select a from T025T010s;

-- identity col cannot be added
alter table t025t010 add column c largeint generated by default as identity;

-- drop of table with identity cols
insert into t025t010 default values;
select * from t025t010;
alter table t025t010 drop column b;
invoke t025t010;
select * from t025t010;

-- this alter drop column should fail
alter table t025t010 drop column a;

-- reset option cannot be used during create
create table t025t011 (a largeint generated by default as identity (start with 10 reset) not null);

-- reset option can be used with an alter
create table t025t011 (a largeint generated by default as identity(no cache) not null);
insert into t025t011 default values;
insert into t025t011 default values;
select * from t025t011 order by a;
alter table t025t011 alter column a reset;
insert into t025t011 default values;
-- should return 1,1,2
select * from t025t011 order by a;


-- Clean up test

drop table T025T001;
drop table T025T002;
drop table T025T00V2;
drop table T025T003 cascade;
drop table T025T004 cascade;
drop table T025T005;
drop table T025T006;
drop table T025T007;
drop table T025T008;
drop table T025T009;
drop table T025T010;
drop table T025T010s;
drop schema s025 cascade;