Github user anoopsharma00 commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/344#discussion_r54324162
--- Diff: core/sql/regress/seabase/EXPECTED027 ---
@@ -756,8 +756,381 @@ create index t027t01i2 on t027t01("cf2".b);
--- SQL operation failed with errors.
>>
+>>-- tests for alter varchar column
+>>drop table if exists t027t7;
+
+--- SQL operation complete.
+>>create table t027t7(a int not null, b varchar(2),
++> c varchar(4) character set utf8 not null, z int not null primary key)
++>salt using 2 partitions;
+
+--- SQL operation complete.
+>>
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:39:06 2016
+
+ (
+ A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(2) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>insert into t027t7 values (1, 'ab', 'cd', 10);
+
+--- 1 row(s) inserted.
+>>select * from t027t7;
+
+A B C Z
+----------- -- ---------------- -----------
+
+ 1 ab cd 10
+
+--- 1 row(s) selected.
+>>alter table t027t7 alter column b varchar(3);
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:39:13 2016
+
+ (
+ A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(3) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column c varchar(5) character set utf8;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:39:35 2016
+
+ (
+ A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(3) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(5 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column c varchar(4) character set utf8;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:39:50 2016
+
+ (
+ A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(3) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>
+>>alter table t027t7 alter column a largeint;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:40:08 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , B VARCHAR(3) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column b varchar(4) character set utf8;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:40:26 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , B VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column c varchar(6);
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:40:43 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , B VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(6) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column b varchar(2);
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:40:58 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , B VARCHAR(2) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(6) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column a int;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:41:12 2016
+
+ (
+ A INT DEFAULT NULL /*altered_col*/
+ , B VARCHAR(2) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(6) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>alter table t027t7 alter column a smallint default 0 not null;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:41:30 2016
+
+ (
+ A SMALLINT DEFAULT 0 NOT NULL NOT
DROPPABLE
+ /*altered_col*/
+ , B VARCHAR(2) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(6) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL /*altered_col*/
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>select * from t027t7;
+
+A B C Z
+------ -- ------ -----------
+
+ 1 ab cd 10
+
+--- 1 row(s) selected.
+>>
+>>-- aligned format
+>>drop table if exists t027t7;
+
+--- SQL operation complete.
+>>create table t027t7(a int not null, b varchar(2),
++> c varchar(4) character set utf8 not null, z int not null primary key)
++>salt using 2 partitions attribute aligned format;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:41:52 2016
+
+ (
+ A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(2) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>insert into t027t7 values (1, 'ab', 'cd', 10);
+
+--- 1 row(s) inserted.
+>>select * from t027t7;
+
+A B C Z
+----------- -- ---------------- -----------
+
+ 1 ab cd 10
+
+--- 1 row(s) selected.
+>>alter table t027t7 alter column a largeint;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:42:09 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , B VARCHAR(2) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT NULL
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>select * from t027t7;
+
+A B C Z
+-------------------- -- ---------------- -----------
+
+ 1 ab cd 10
+
+--- 1 row(s) selected.
+>>alter table t027t7 drop column b;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:42:35 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>select * from t027t7;
+
+A C Z
+-------------------- ---------------- -----------
+
+ 1 cd 10
+
+--- 1 row(s) selected.
+>>alter table t027t7 add column b char(10) default 'abc' not null;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:42:38 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B CHAR(10) CHARACTER SET ISO88591
COLLATE
+ DEFAULT DEFAULT _ISO88591'abc' NOT NULL NOT DROPPABLE /*added_col*/
+ )
+
+--- SQL operation complete.
+>>select * from t027t7;
+
+A C Z B
+-------------------- ---------------- ----------- ----------
+
+ 1 cd 10 abc
+
+--- 1 row(s) selected.
+>>alter table t027t7 drop column b;
+
+--- SQL operation complete.
+>>invoke t027t7;
+
+-- Definition of Trafodion table TRAFODION.SCH027.T027T7
+-- Definition current Fri Feb 26 20:43:02 2016
+
+ (
+ A LARGEINT DEFAULT NULL /*altered_col*/
+ , C VARCHAR(4 CHARS) CHARACTER SET UTF8
+ COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+ , Z INT NO DEFAULT NOT NULL NOT DROPPABLE
+ )
+
+--- SQL operation complete.
+>>select * from t027t7;
+
+A C Z
+-------------------- ---------------- -----------
+
+ 1 cd 10
+
+--- 1 row(s) selected.
+>>
+>>-- negative tests for alter/drop column
+>>alter table t027t7 alter column b int;
+
+*** ERROR[1009] Column B does not exist in the specified table.
+
+--- SQL operation failed with errors.
+>>alter table t027t7 alter column a char(10);
+
+*** ERROR[1404] Column A cannot be altered. Reason: Old and New datatypes
must be compatible.
+
+--- SQL operation failed with errors.
+>>alter table t027t7 alter column c char(1);
+
+*** ERROR[8402] A string overflow occurred during the evaluation of a
character expression. Conversion of Source Type:VARCHAR(REC_BYTE_V_ASCII,2
BYTES,ISO88591) Source Value:cd to Target Type:CHAR(REC_BYTE_F_ASCII,1
BYTES,ISO88591).
+
+*** ERROR[8839] Transaction was aborted.
+
+*** ERROR[1404] Column C cannot be altered. Reason: Old data could not be
updated using the altered column definition.
+
+--- SQL operation failed with errors.
+>>alter table t027t7 drop column e;
+
+*** ERROR[1009] Column E does not exist in the specified table.
+
+--- SQL operation failed with errors.
--- End diff --
Yes, it is ok. During alter, values are update from old to the new column
definition. If a character set has changed, then an internally generated
TRANSLATE function is used to convert src to tgt. This is the same behavior as
in any insert...select or update stmt. If an error occurs during this
conversion, then the alter stmt fails.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
