| >>-- |
| >>?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,4); |
| |
| --- 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, 4); |
| |
| --- 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:4(hash2) 1.00E+004 |
| 6 . 7 esp_exchange 4(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 |
| -------------------- ---------- ---------- |
| |
| 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 b,c; |
| |
| A B C |
| -------------------- ---------- ---------- |
| |
| 10 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_918519353_6685 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_918519353_6685 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 Sun Jan 7 23:18:49 2018 |
| |
| ( |
| 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 Sun Jan 7 23:20:51 2018 |
| |
| ( |
| 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 |
| |