core/sql/regress/seabase/EXPECTED040

>>
>>?section set_up
>>
>>-- set-up
>>
>>drop schema if exists t040sch cascade;

--- SQL operation complete.
>>
>>create schema t040sch;

--- SQL operation complete.
>>
>>set schema t040sch;

--- SQL operation complete.
>>
>>create table t040salt(a int not null, b int not null, c int, primary key (a,b))
+> salt using 4 partitions;

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

CREATE TABLE TRAFODION.T040SCH.T040SALT
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 4 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040nosalt(a int not null, b int not null, c int, primary key (a,b));

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

CREATE TABLE TRAFODION.T040SCH.T040NOSALT
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040bigcols(a int not null,
+>                         b char(1000) character set iso88591,
+>                         c varchar(1001) character set iso88591,
+>                         d char(1002) character set utf8,
+>                         e varchar(1003) character set utf8,
+>                         f char(1004) character set ucs2,
+>                         g varchar(1005) character set ucs2,
+>                         primary key (a));

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

CREATE TABLE TRAFODION.T040SCH.T040BIGCOLS
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(1000) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(1001) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , D                                CHAR(1002 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , E                                VARCHAR(1003 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , F                                CHAR(1004) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , G                                VARCHAR(1005) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>?section positive_tests
>>
>>-- positive tests
>>
>>create table t040salt1 like t040salt without salt;

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

CREATE TABLE TRAFODION.T040SCH.T040SALT1
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040salt5 like t040salt salt using 5 partitions;

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

CREATE TABLE TRAFODION.T040SCH.T040SALT5
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 5 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040salt6 like t040salt salt using 6 partitions on (a);

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

CREATE TABLE TRAFODION.T040SCH.T040SALT6
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 6 PARTITIONS
       ON (A)
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040salt7 like t040salt salt using 7 partitions on (b);

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

CREATE TABLE TRAFODION.T040SCH.T040SALT7
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 7 PARTITIONS
       ON (B)
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040salt8 like t040salt salt using 8 partitions on (a,b);

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

CREATE TABLE TRAFODION.T040SCH.T040SALT8
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 8 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040nosalt1 like t040nosalt without salt;

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

CREATE TABLE TRAFODION.T040SCH.T040NOSALT1
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040nosalt4 like t040nosalt salt using 4 partitions;

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

CREATE TABLE TRAFODION.T040SCH.T040NOSALT4
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 4 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040saltsame like t040salt;

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

CREATE TABLE TRAFODION.T040SCH.T040SALTSAME
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
  SALT USING 4 PARTITIONS
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040nosaltsame like t040nosalt;

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

CREATE TABLE TRAFODION.T040SCH.T040NOSALTSAME
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  , PRIMARY KEY (A ASC, B ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040limitedcols like t040bigcols 
+>limit column length to 256;

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

CREATE TABLE TRAFODION.T040SCH.T040LIMITEDCOLS
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(256) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(256) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , D                                CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT DEFAULT NULL
  , E                                VARCHAR(256 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , F                                CHAR(128) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , G                                VARCHAR(128) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040somelimitedcols like t040bigcols 
+>limit column length to 1002;

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

CREATE TABLE TRAFODION.T040SCH.T040SOMELIMITEDCOLS
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(1000) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(1001) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , D                                CHAR(1002 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , E                                VARCHAR(1002 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , F                                CHAR(501) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , G                                VARCHAR(501) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040fewerlimitedcols like t040bigcols 
+>limit column length to 2010;

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

CREATE TABLE TRAFODION.T040SCH.T040FEWERLIMITEDCOLS
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(1000) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(1001) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , D                                CHAR(2010 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , E                                VARCHAR(2010 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , F                                CHAR(1004) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , G                                VARCHAR(1005) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>create table t040nolimitedcols like t040bigcols 
+>limit column length to 4012;

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

CREATE TABLE TRAFODION.T040SCH.T040NOLIMITEDCOLS
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                CHAR(1000) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , C                                VARCHAR(1001) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , D                                CHAR(1002 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , E                                VARCHAR(1003 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , F                                CHAR(1004) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , G                                VARCHAR(1005) CHARACTER SET UCS2 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>?section negative_tests
>>
>>-- negative tests
>>
>>-- non-key column, should fail
>>create table t040saltbad1 like t040salt salt using 5 partitions on (c);

*** ERROR[1195] Column C is not allowed as a salt column. Only primary key columns or STORE BY columns are allowed.

--- SQL operation failed with errors.
>>
>>-- non-existent column, should fail
>>create table t040saltbad2 like t040salt salt using 5 partitions on (d);

*** ERROR[1195] Column D is not allowed as a salt column. Only primary key columns or STORE BY columns are allowed.

--- SQL operation failed with errors.
>>
>>-- should fail with error 3154
>>create table t040saltbad3 like t040salt without salt salt using 4 partitions;

*** ERROR[3154] The WITHOUT SALT clause is not allowed with the SALT clause.

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

>>
>>-- should fail, duplicate clauses
>>create table t040saltbad4 like t040salt without salt without salt;

*** ERROR[3152] Duplicate WITHOUT SALT phrases were specified in LIKE clause in CREATE TABLE statement.

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

>>
>>-- and now an extravaganza of errors
>>create table t040saltbad5 like t040salt salt using 4 partitions without salt salt using 7 partitions;

*** ERROR[3154] The WITHOUT SALT clause is not allowed with the SALT clause.

*** ERROR[3183] Duplicate SALT clauses were specified.

*** ERROR[3154] The WITHOUT SALT clause is not allowed with the SALT clause.

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

>>
>>-- syntax error; clause not supported with LIKE
>>create table t040saltbad6 like t040salt store by (b);

*** ERROR[15001] A syntax error occurred at or before: 
create table t040saltbad6 like t040salt store by (b);
                                            ^ (45 characters from start of SQL statement)

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

>>
>>log;