[jira] [Created] (TRAFODION-334) LP Bug: 1324702 - alter table adding a primary key different from the clustering key causes the table to disapear

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-334:
------------------------------------

             Summary: LP Bug: 1324702 - alter table adding a primary key different from the clustering key causes the table to disapear
                 Key: TRAFODION-334
                 URL: https://issues.apache.org/jira/browse/TRAFODION-334
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


Trafodion currently does not support a clustering key that is not the same as the primary key.  But the following alter table statement does not return an error when trying to add a primary key that is different from the clustering key.  What’s worse is that the table disappears afterwards.  It’s not in “_MD_”.objects anymore and dropping the table returns an error saying that the table does not exist.

This is seen on the datalake v40963 build installed on a workstation.

Here is the script to reproduce it:

set schema MYTEST6;
select * from "_MD_".objects where schema_name='MYTEST6';
create table mytab (a int not null not droppable, b int) store by (a);
select * from "_MD_".objects where schema_name='MYTEST6';
alter table mytab add constraint myconst primary key (b);
select * from "_MD_".objects where schema_name='MYTEST6';
drop table mytab;

Here is the execution output shows that the alter table statement ran successfully and the table disappeared afterwards:

>>set schema MYTEST6;

--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYTEST6';

--- 0 row(s) selected.
>>create table mytab (a int not null not droppable, b int) store by (a);

--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYTEST6';

CATALOG_NAME                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                       OBJECT_NAME                                                                                                                                                                                                                                                       OBJECT_TYPE  OBJECT_UID            CREATE_TIME           REDEF_TIME            VALID_DEF  OBJECT_OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  --------------------  --------------------  --------------------  ---------  ------------

TRAFODION                                                                                                                                                                                                                                                         MYTEST6                                                                                                                                                                                                                                                           MYTAB                                                                                                                                                                                                                                                             BT              99115218815271588    212268159452822153    212268159452822153  Y                 33333

--- 1 row(s) selected.
>>alter table mytab add constraint myconst primary key (b);

--- SQL operation complete.
>>select * from "_MD_".objects where schema_name='MYTEST6';

--- 0 row(s) selected.
>>drop table mytab;

*** ERROR[1389] Object TRAFODION.MYTEST6.MYTAB does not exist in Trafodion.

--- SQL operation failed with errors.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)