Major Description
In 2.2, alter table add a LOB column was not supported. It returned a 1100 error. In 2.4, it appears to be supported. As shown below, alter table add column c2 works fine. However, this only works once. Adding a second column c3 would start to return error 1390 complaining that an internal object already exists.
It's not clear if we really intend to support alter table add lob columns in 2.4 or if this feature is turned on by accident in 2.4. If we intend to support it, the 1390 error for alter table add column c3 needs to be fixed. If we don't intend to support it, both alter table add column c2 and alter table add column c3 should be made to both return the 1100 error just like in R2.3.
This is seen on the latest daily build.
>>cqd TRAF_BLOB_AS_VARCHAR 'OFF';
— SQL operation complete.
>>cqd TRAF_CLOB_AS_VARCHAR 'OFF';
— SQL operation complete.
>>drop table if exists t;
— SQL operation complete.
>>create table t (c1 int);
— SQL operation complete.
>>alter table t add column c2 blob;
— SQL operation complete.
>>alter table t add column c3 blob;
-
-
- ERROR[1390] Object TRAFODION.SEABASE.LOBMD__05049280011681089346 already exists in Trafodion.
-
— SQL operation failed with errors.
>>showddl t;
CREATE TABLE TRAFODION.SEABASE.T
(
C1 INT DEFAULT NULL NOT SERIALIZED
, C2 BLOB DEFAULT NULL NOT SERIALIZED
/added_col/
)
ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3'
;
– GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T TO DB__ROOT WITH GRANT OPTION;
— SQL operation complete.
=======================================================================
As a reference, this is the old behavior (returning the 1100 error) :
>>cqd TRAF_BLOB_AS_VARCHAR 'OFF';
— SQL operation complete.
>>cqd TRAF_CLOB_AS_VARCHAR 'OFF';
— SQL operation complete.
>>drop table if exists t;
— SQL operation complete.
>>create table t (c1 int);
— SQL operation complete.
>>alter table t add column c2 blob;
-
-
- ERROR[1100] LOB column C2 cannot be specified in an alter operation.
-
— SQL operation failed with errors.
>>alter table t add column c3 blob;
-
-
- ERROR[1100] LOB column C3 cannot be specified in an alter operation.
-
— SQL operation failed with errors.
>>showddl t;
CREATE TABLE TRAFODION.SEABASE.T
(
C1 INT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3'
;
– GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T TO DB__ROOT WITH GRANT OPTION;
— SQL operation complete.