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