Sandhya Sundaresan created TRAFODION-3269:
---------------------------------------------
Summary: LOB: Alter table add a LOB column returns error 1390
Key: TRAFODION-3269
URL: https://issues.apache.org/jira/browse/TRAFODION-3269
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Affects Versions: 2.4
Reporter: Sandhya Sundaresan
Assignee: Sandhya Sundaresan
Fix For: 2.4
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.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
