jira TRAFODION-1782 Auto assignment of NOT NULL attr to primary key columns.
details in jira. regress/seabase/TEST032 has new tests for this enhancement. Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/a5eb0b26 Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/a5eb0b26 Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/a5eb0b26 Branch: refs/heads/master Commit: a5eb0b26465fce538b2b5c1e054cdc42aa7423ff Parents: bc77872 Author: Anoop Sharma <[email protected]> Authored: Wed Jun 13 23:22:18 2018 +0000 Committer: Anoop Sharma <[email protected]> Committed: Wed Jun 13 23:22:18 2018 +0000 ---------------------------------------------------------------------- core/sql/regress/privs2/EXPECTED138 | 19 +- core/sql/regress/privs2/TEST138 | 1 + core/sql/regress/seabase/EXPECTED032 | 248 ++++++++++++++++++++++++++- core/sql/regress/seabase/TEST032 | 56 ++++++ core/sql/sqlcomp/CmpSeabaseDDLtable.cpp | 61 ++++--- 5 files changed, 341 insertions(+), 44 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/a5eb0b26/core/sql/regress/privs2/EXPECTED138 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/EXPECTED138 b/core/sql/regress/privs2/EXPECTED138 index b57b4fa..f8dad07 100644 --- a/core/sql/regress/privs2/EXPECTED138 +++ b/core/sql/regress/privs2/EXPECTED138 @@ -892,14 +892,20 @@ GRANT REFERENCES CREATE TABLE TRAFODION.T138SCH.USER1_T2 ( - C1 INT DEFAULT NULL NOT NULL NOT DROPPABLE + C1 INT DEFAULT NULL , C2 INT DEFAULT NULL - , CONSTRAINT TRAFODION.T138SCH.USER1_PK PRIMARY KEY (C1 ASC) ) ATTRIBUTES ALIGNED FORMAT ; ALTER TABLE TRAFODION.T138SCH.USER1_T2 ADD CONSTRAINT + TRAFODION.T138SCH.USER1_PK UNIQUE + ( + C1 + ) +; + +ALTER TABLE TRAFODION.T138SCH.USER1_T2 ADD CONSTRAINT TRAFODION.T138SCH.USER1_FK FOREIGN KEY ( C2 @@ -967,9 +973,8 @@ GRANT REFERENCES CREATE TABLE TRAFODION.T138SCH.USER1_T2 ( - C1 INT DEFAULT NULL NOT NULL NOT DROPPABLE + C1 INT DEFAULT NULL , C2 INT DEFAULT NULL - , CONSTRAINT TRAFODION.T138SCH.USER1_PK PRIMARY KEY (C1 ASC) ) ATTRIBUTES ALIGNED FORMAT ; @@ -1099,9 +1104,8 @@ GRANT REFERENCES CREATE TABLE TRAFODION.T138SCH.USER1_T2 ( - C1 INT DEFAULT NULL NOT NULL NOT DROPPABLE + C1 INT DEFAULT NULL , C2 INT DEFAULT NULL - , CONSTRAINT TRAFODION.T138SCH.USER1_PK PRIMARY KEY (C1 ASC) ) ATTRIBUTES ALIGNED FORMAT ; @@ -1179,9 +1183,8 @@ GRANT REFERENCES CREATE TABLE TRAFODION.T138SCH.USER1_T2 ( - C1 INT DEFAULT NULL NOT NULL NOT DROPPABLE + C1 INT DEFAULT NULL , C2 INT DEFAULT NULL - , CONSTRAINT TRAFODION.T138SCH.USER1_PK PRIMARY KEY (C1 ASC) ) ATTRIBUTES ALIGNED FORMAT ; http://git-wip-us.apache.org/repos/asf/trafodion/blob/a5eb0b26/core/sql/regress/privs2/TEST138 ---------------------------------------------------------------------- diff --git a/core/sql/regress/privs2/TEST138 b/core/sql/regress/privs2/TEST138 index cb58952..e70c230 100755 --- a/core/sql/regress/privs2/TEST138 +++ b/core/sql/regress/privs2/TEST138 @@ -264,6 +264,7 @@ showddl user1_t2; ?section alter_tbl set schema t138sch; cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'on'; +cqd traf_alter_add_pkey_as_unique_constraint 'ON'; log LOG138; get tables; http://git-wip-us.apache.org/repos/asf/trafodion/blob/a5eb0b26/core/sql/regress/seabase/EXPECTED032 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED032 b/core/sql/regress/seabase/EXPECTED032 index b975865..085bc82 100644 --- a/core/sql/regress/seabase/EXPECTED032 +++ b/core/sql/regress/seabase/EXPECTED032 @@ -15,7 +15,7 @@ >>invoke t032t1; -- Definition of Trafodion table TRAFODION.SCH.T032T1 --- Definition current Tue Jan 17 18:16:14 2017 +-- Definition current Wed Jun 13 23:15:19 2018 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -140,7 +140,7 @@ B (EXPR) ------------------------------ -RANDOMVAL=1210881081 +RANDOMVAL=791360135 --- 1 row(s) selected. >>select random() from (values(1)) x(a); @@ -333,8 +333,8 @@ B (EXPR) ------------ -11 -21 +11 +21 --- 2 row(s) selected. >>insert into t032t1 values ('3', 3, 3, date '2016-08-15', time '10:11:12', @@ -380,6 +380,9 @@ A >> >> >>-- auto create schema +>>drop schema if exists t032sch cascade; + +--- SQL operation complete. >> >>-- should fail, schema doesnt exist >>create table t032sch.t032t2 (a int); @@ -401,7 +404,7 @@ A >>invoke t032t2; -- Definition of Trafodion table TRAFODION.T032SCH.T032T2 --- Definition current Tue Jan 17 18:16:25 2017 +-- Definition current Wed Jun 13 23:15:37 2018 ( SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE @@ -413,6 +416,241 @@ A --- 1 row(s) inserted. >> +>>-- auto NOT NULL attr for pkey columns +>>create schema if not exists t032sch; + +--- SQL operation complete. +>>set schema t032sch; + +--- SQL operation complete. +>>drop table if exists t032t1; + +--- SQL operation complete. +>>drop table if exists t032t2; + +--- SQL operation complete. +>>drop table if exists t032t3; + +--- SQL operation complete. +>>drop table if exists t032t4 cascade; + +--- SQL operation complete. +>>drop table if exists t032t5 cascade; + +--- SQL operation complete. +>> +>>-- NOT NULL attr for pkey columns +>>create table t032t1 (a int primary key); + +--- SQL operation complete. +>>showddl t032t1; + +CREATE TABLE TRAFODION.T032SCH.T032T1 + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , PRIMARY KEY (A ASC) + ) + ATTRIBUTES ALIGNED FORMAT +; + +--- SQL operation complete. +>>drop table t032t1; + +--- SQL operation complete. +>>create table t032t1 (a int, primary key(a)); + +--- SQL operation complete. +>>showddl t032t1; + +CREATE TABLE TRAFODION.T032SCH.T032T1 + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , PRIMARY KEY (A ASC) + ) + ATTRIBUTES ALIGNED FORMAT +; + +--- SQL operation complete. +>>drop table t032t1; + +--- SQL operation complete. +>>create table t032t1 (a int) primary key(a); + +--- SQL operation complete. +>>showddl t032t1; + +CREATE TABLE TRAFODION.T032SCH.T032T1 + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , PRIMARY KEY (A ASC) + ) + ATTRIBUTES ALIGNED FORMAT +; + +--- SQL operation complete. +>>drop table t032t1; + +--- SQL operation complete. +>>create table t032t1 primary key(a) as select cast(1 as nullable) a from dual; + +--- 1 row(s) inserted. +>>showddl t032t1; + +CREATE TABLE TRAFODION.T032SCH.T032T1 + ( + A NUMERIC(1, 0) NO DEFAULT NOT NULL NOT + DROPPABLE + , PRIMARY KEY (A ASC) + ) + ATTRIBUTES ALIGNED FORMAT +; + +--- SQL operation complete. +>> +>>-- no NOT NULL attr if nullable pkey is specified +>>create table t032t2 (a int constraint t2pk primary key nullable); + +--- SQL operation complete. +>>showddl t032t2; + +CREATE TABLE TRAFODION.T032SCH.T032T2 + ( + A INT DEFAULT NULL + , CONSTRAINT TRAFODION.T032SCH.T2PK PRIMARY KEY (A ASC) + ) + ATTRIBUTES ALIGNED FORMAT +; + +--- SQL operation complete. +>>insert into t032t2 values (1), (null); + +--- 2 row(s) inserted. +>>select * from t032t2 order by a; + +A +----------- + + 1 + ? + +--- 2 row(s) selected. +>>drop table t032t2; + +--- SQL operation complete. +>>create table t032t2 (a int, b int); + +--- SQL operation complete. +>>alter table t032t2 add constraint pkt2 primary key nullable (a); + +--- SQL operation complete. +>>showddl t032t2; + +CREATE TABLE TRAFODION.T032SCH.T032T2 + ( + A INT DEFAULT NULL + , B INT DEFAULT NULL + , CONSTRAINT TRAFODION.T032SCH.PKT2 PRIMARY KEY (A ASC) + ) + ATTRIBUTES ALIGNED FORMAT +; + +--- SQL operation complete. +>>-- should return unique violation +>>insert into t032t2 values (null, 1), (null, 2); + +*** ERROR[8102] The operation is prevented by a unique constraint. + +--- 0 row(s) inserted. +>> +>>-- alter/add pkey on empty table +>>create table t032t3 (a int, b int not null); + +--- SQL operation complete. +>>alter table t032t3 add constraint pkt3 primary key(a); + +--- SQL operation complete. +>>invoke t032t3; + +-- Definition of Trafodion table TRAFODION.T032SCH.T032T3 +-- Definition current Wed Jun 13 23:17:19 2018 + + ( + A INT DEFAULT NULL NOT NULL NOT DROPPABLE + , B INT NO DEFAULT NOT NULL NOT DROPPABLE + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>-- alter/add pkey on non-empty table +>>create table t032t4 (a int, b int not null); + +--- SQL operation complete. +>>insert into t032t4 values (1,1); + +--- 1 row(s) inserted. +>>alter table t032t4 add constraint pkt4 primary key(a); + +--- SQL operation complete. +>>invoke t032t4; + +-- Definition of Trafodion table TRAFODION.T032SCH.T032T4 +-- Definition current Wed Jun 13 23:18:03 2018 + + ( + A INT DEFAULT NULL NOT NULL NOT DROPPABLE + , B INT NO DEFAULT NOT NULL NOT DROPPABLE + ) + PRIMARY KEY (A ASC) + +--- SQL operation complete. +>> +>>-- pkey not allowed with dependent objects +>>create table t032t5 (a int, b int); + +--- SQL operation complete. +>>create view v032t5 as select * from t032t5; + +--- SQL operation complete. +>>alter table t032t5 add constraint pkt5 primary key(a); + +*** ERROR[3242] This statement is not supported. Reason: Cannot alter/add primary key constraint on a table with dependencies. Drop all dependent objects (views, indexes, unique and referential constraints) on the specified table and recreate them after adding the primary key. + +--- SQL operation failed with errors. +>> +>>-- pkey created as unique constraint if cqd is set +>>cqd traf_alter_add_pkey_as_unique_constraint 'ON'; + +--- SQL operation complete. +>>alter table t032t5 add constraint pkt5 primary key(a); + +--- SQL operation complete. +>>showddl t032t5; + +CREATE TABLE TRAFODION.T032SCH.T032T5 + ( + A INT DEFAULT NULL + , B INT DEFAULT NULL + ) + ATTRIBUTES ALIGNED FORMAT +; + +-- The following index is a system created index -- +CREATE UNIQUE INDEX PKT5 ON TRAFODION.T032SCH.T032T5 + ( + A ASC + ) +; + +ALTER TABLE TRAFODION.T032SCH.T032T5 ADD CONSTRAINT TRAFODION.T032SCH.PKT5 + UNIQUE + ( + A + ) +; + +--- SQL operation complete. +>> >>drop schema t032sch cascade; --- SQL operation complete. http://git-wip-us.apache.org/repos/asf/trafodion/blob/a5eb0b26/core/sql/regress/seabase/TEST032 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST032 b/core/sql/regress/seabase/TEST032 index 29d32db..41ad3c0 100644 --- a/core/sql/regress/seabase/TEST032 +++ b/core/sql/regress/seabase/TEST032 @@ -101,6 +101,7 @@ select cast(1e0 as interval year) from dual; -- auto create schema +drop schema if exists t032sch cascade; -- should fail, schema doesnt exist create table t032sch.t032t2 (a int); @@ -112,6 +113,61 @@ set schema t032sch; invoke t032t2; insert into t032t2 values (1); +-- auto NOT NULL attr for pkey columns +create schema if not exists t032sch; +set schema t032sch; +drop table if exists t032t1; +drop table if exists t032t2; +drop table if exists t032t3; +drop table if exists t032t4 cascade; +drop table if exists t032t5 cascade; + +-- NOT NULL attr for pkey columns +create table t032t1 (a int primary key); +showddl t032t1; +drop table t032t1; +create table t032t1 (a int, primary key(a)); +showddl t032t1; +drop table t032t1; +create table t032t1 (a int) primary key(a); +showddl t032t1; +drop table t032t1; +create table t032t1 primary key(a) as select cast(1 as nullable) a from dual; +showddl t032t1; + +-- no NOT NULL attr if nullable pkey is specified +create table t032t2 (a int constraint t2pk primary key nullable); +showddl t032t2; +insert into t032t2 values (1), (null); +select * from t032t2 order by a; +drop table t032t2; +create table t032t2 (a int, b int); +alter table t032t2 add constraint pkt2 primary key nullable (a); +showddl t032t2; +-- should return unique violation +insert into t032t2 values (null, 1), (null, 2); + +-- alter/add pkey on empty table +create table t032t3 (a int, b int not null); +alter table t032t3 add constraint pkt3 primary key(a); +invoke t032t3; + +-- alter/add pkey on non-empty table +create table t032t4 (a int, b int not null); +insert into t032t4 values (1,1); +alter table t032t4 add constraint pkt4 primary key(a); +invoke t032t4; + +-- pkey not allowed with dependent objects +create table t032t5 (a int, b int); +create view v032t5 as select * from t032t5; +alter table t032t5 add constraint pkt5 primary key(a); + +-- pkey created as unique constraint if cqd is set +cqd traf_alter_add_pkey_as_unique_constraint 'ON'; +alter table t032t5 add constraint pkt5 primary key(a); +showddl t032t5; + drop schema t032sch cascade; log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/a5eb0b26/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp index 001b338..3e9f1a2 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp @@ -7993,44 +7993,43 @@ void CmpSeabaseDDL::alterSeabaseTableAddPKeyConstraint( dependentObjects = TRUE; } - // if the table has dependent objects return error. - // Users need to drop them before adding primary key // If cqd is set to create pkey as a unique constraint, then do that. - if (dependentObjects) - { - if (CmpCommon::getDefault(TRAF_ALTER_ADD_PKEY_AS_UNIQUE_CONSTRAINT) == DF_OFF) + // otherwise if table has dependent objects, return error. + // Users need to drop them before adding primary key + if (CmpCommon::getDefault(TRAF_ALTER_ADD_PKEY_AS_UNIQUE_CONSTRAINT) == DF_ON) + { + // either dependent objects or cqd set to create unique constraint. + // cannot create clustered primary key constraint. + // create a unique constraint instead. + NAString cliQuery; + cliQuery = "alter table " + extTableName + " add constraint " + uniqueStr + + " unique " + pkeyColsStr + ";"; + cliRC = cliInterface.executeImmediate((char*)cliQuery.data()); + if (cliRC < 0) { - // error - *CmpCommon::diags() << DgSqlCode(-3242) - << DgString0("Cannot alter/add primary key constraint on a table with dependencies. Drop all dependent objects (views, indexes, unique and referential constraints) on the specified table and recreate them after adding the primary key."); + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); } - else + + if (!Get_SqlParser_Flags(INTERNAL_QUERY_FROM_EXEUTIL)) { - // cannot create clustered primary key constraint. - // create a unique constraint instead. - NAString cliQuery; - cliQuery = "alter table " + extTableName + " add constraint " + uniqueStr - + " unique " + pkeyColsStr + ";"; - cliRC = cliInterface.executeImmediate((char*)cliQuery.data()); - if (cliRC < 0) - { - cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); - } - - if (!Get_SqlParser_Flags(INTERNAL_QUERY_FROM_EXEUTIL)) - { - // remove NATable for this table - ActiveSchemaDB()->getNATableDB()->removeNATable - (cn, - ComQiScope::REMOVE_FROM_ALL_USERS, - COM_BASE_TABLE_OBJECT, - alterAddConstraint->ddlXns(), FALSE); - } + // remove NATable for this table + ActiveSchemaDB()->getNATableDB()->removeNATable + (cn, + ComQiScope::REMOVE_FROM_ALL_USERS, + COM_BASE_TABLE_OBJECT, + alterAddConstraint->ddlXns(), FALSE); } - + return; } - + else if (dependentObjects) + { + // error + *CmpCommon::diags() << DgSqlCode(-3242) + << DgString0("Cannot alter/add primary key constraint on a table with dependencies. Drop all dependent objects (views, indexes, unique and referential constraints) on the specified table and recreate them after adding the primary key."); + return; + } + // create a true primary key by drop/create/populate of table. NABoolean isEmpty = FALSE;
