[jira] [Created] (TRAFODION-780) LP Bug: 1394780 - Create table and alter table do not check the default value for char and varchar columns

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

Alice Chen created TRAFODION-780:
------------------------------------

             Summary: LP Bug: 1394780 - Create table and alter table do not check the default value for char and varchar columns
                 Key: TRAFODION-780
                 URL: https://issues.apache.org/jira/browse/TRAFODION-780
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


A ‘create table’ statement or an ‘alter table add column’ statement now fails to return an error if the default value for a char or varchar column does not fit into the column.   However, trying to select from the table returns error 7001 afterwards.  This is seen on the v1115_0830 build installed on a workstation.  It is a regression introduced between v1108_0830 and v1115_0830.

--------------------------------------------------------

Here is the entire script to reproduce this problem:

set schema mytest;
create table t1 (a int, b char (2) default 'aaa');
showddl t1;
select * from t1;
create table t2 (a int, b varchar (2) default 'aaa');
showddl t2;
select * from t2;
create table t3 (a int);
alter table t3 add b char (2) default 'aaa';
showddl t3;
select * from t3;
create table t4 (a int);
alter table t4 add b varchar (2) default 'aaa';
showddl t4;
select * from t4;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop schema mytest cascade;

--------------------------------------------------------

Here is the execution output of the script:

>>set schema mytest;

--- SQL operation complete.
>>create table t1 (a int, b char (2) default 'aaa');

--- SQL operation complete.
>>showddl t1;

CREATE TABLE TRAFODION.MYTEST.T1
  (
    A                                INT DEFAULT NULL
  , B                                CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591'aaa'
  )
;

--- SQL operation complete.
>>select * from t1;

*** ERROR[7001] Default value ??????? is not valid for column TRAFODION.MYTEST.T1.B.

*** ERROR[8822] The statement was not prepared.

>>create table t2 (a int, b varchar (2) default 'aaa');

--- SQL operation complete.
>>showddl t2;

CREATE TABLE TRAFODION.MYTEST.T2
  (
    A                                INT DEFAULT NULL
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591'aaa'
  )
;

--- SQL operation complete.
>>select * from t2;

*** ERROR[7001] Default value ??????? is not valid for column TRAFODION.MYTEST.T2.B.

*** ERROR[8822] The statement was not prepared.

>>create table t3 (a int);

--- SQL operation complete.
>>alter table t3 add b char (2) default 'aaa';

--- SQL operation complete.
>>showddl t3;

CREATE TABLE TRAFODION.MYTEST.T3
  (
    A                                INT DEFAULT NULL
  , B                                CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591'aaa' /* added col */
  )
;

--- SQL operation complete.
>>select * from t3;

*** ERROR[7001] Default value ??????? is not valid for column TRAFODION.MYTEST.T3.B.

*** ERROR[8822] The statement was not prepared.

>>create table t4 (a int);

--- SQL operation complete.
>>alter table t4 add b varchar (2) default 'aaa';

--- SQL operation complete.
>>showddl t4;

CREATE TABLE TRAFODION.MYTEST.T4
  (
    A                                INT DEFAULT NULL
  , B                                VARCHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591'aaa' /* added col */
  )
;

--- SQL operation complete.
>>select * from t4;

*** ERROR[7001] Default value ??????? is not valid for column TRAFODION.MYTEST.T4.B.

*** ERROR[8822] The statement was not prepared.

>>drop table t1;

--- SQL operation complete.
>>drop table t2;

--- SQL operation complete.
>>drop table t3;

--- SQL operation complete.
>>drop table t4;

--- SQL operation complete.
>>drop schema mytest cascade;

--- SQL operation complete.



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