[jira] [Created] (TRAFODION-313) LP Bug: 1324172 - create table as doesn't support constraint

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

Alice Chen created TRAFODION-313:
------------------------------------

             Summary: LP Bug: 1324172 - create table as doesn't support constraint
                 Key: TRAFODION-313
                 URL: https://issues.apache.org/jira/browse/TRAFODION-313
             Project: Apache Trafodion
          Issue Type: Bug
            Reporter: Apache Trafodion
            Assignee: Anoop Sharma


1) when adding constraint to create table as failed with error 8603
2) create table successful if constraint was removed

SQL>create table ctas018 (pkey largeint not null not droppable,
  st_name varchar (30),r_realname varchar(50), b_birthday date
  constraint md18 check (b_birthday > date '1900-01-01')) store by (pkey)
  as select * from Female_actors union select * from Male_actors;

*** ERROR[8603] Trying to begin a transaction that has already been started. [2014-05-28 08:18:31]

SQL>create table ctast2 (pkey largeint not null not droppable,
  st_name varchar (30),r_realname varchar(50), b_birthday date)
  store by (pkey)
  as select * from Female_actors union select * from Male_actors;

--- 9 row(s) inserted.

-- test script
create schema debug_ctas;
set schema debug_ctas;
get tables;

create table Female_actors (
f_no          int not null not droppable,
f_name        varchar(30) not null,
f_realname    varchar(50) default null,
f_birthday    date  constraint md1 check (f_birthday > date '1900-01-01'),
primary key (f_no)
)
;

create table Male_actors (
m_no          int not null not droppable unique,
m_name        varchar(30) not null,
m_realname    varchar(50) default null,
m_birthday    date  constraint md2 check (m_birthday > date '1900-01-01')
) no partition;

-- #expect any *4 row(s) inserted*
insert into Male_actors values
    (0, 'No male actor','No male actor', current_date)
   ,(1111, 'Cary Grant','Archibald Alec Leach',date '1904-01-18')
   ,(1222, 'Gary Cooper','Frank James Cooper', date '1901-05-07')
   ,(1333, 'Clint Eastwood','Clinton Eastwood Jr.', date '1930-05-31')
   ;

-- #expect any *5 row(s) inserted*
insert into Female_actors values
    (0, 'No female actor','No female actor', current_date),
    (6111, 'Grace Kelly', 'Grace Patricia Kelly', date '1929-11-12'),
    (6123, 'Katherine Hepburn','Katharine Houghton Hepburn', date '1907-05-12'),
    (6124, 'Joan Crawford','Lucille Fay LeSueur', date '1904-03-23'),
    (6125, 'Ingrid Bergman', 'Ingrid Bergman', date '1915-08-29');

set schema debug_ctas;
drop table ctas018 cascade;
get tables;
create table ctas018 (pkey largeint not null not droppable,
  st_name varchar (30),r_realname varchar(50), b_birthday date
  constraint md18 check (b_birthday > date '1900-01-01')) store by (pkey)
  as select * from Female_actors union select * from Male_actors;



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