>>
>>cqd hbase_native_iud 'ON';

--- SQL operation complete.
>>cqd hbase_filter_preds 'ON';

--- SQL operation complete.
>>
>>drop hbase table T022HBT1;

*** WARNING[1004] Object T022HBT1 does not exist or object type is invalid for the current operation.

--- SQL operation complete.
>>create hbase table T022HBT1 (column family 'cf');

--- SQL operation complete.
>>
>>drop hbase table T022HBT2;

*** WARNING[1004] Object T022HBT2 does not exist or object type is invalid for the current operation.

--- SQL operation complete.
>>create hbase table T022HBT2 (column family 'cf');

--- SQL operation complete.
>>
>>drop table if exists t022t1;

--- SQL operation complete.
>>create table if not exists t022t1 (a int not null primary key, b int not null);

--- SQL operation complete.
>>
>>insert into hbase."_CELL_".t022hbt1 values ('1', 'cf', '1a', -1, '101');

--- 1 row(s) inserted.
>>insert into hbase."_ROW_".t022hbt1 values ('2', column_create('cf:2b', '201')),
+>                              ('1', column_create('cf:1b', '102'));

--- 2 row(s) inserted.
>>prepare s from insert into hbase."_ROW_".t022hbt1 values 
+>         (?, column_create((?, ?), ('cf:3b', '302')));

--- SQL command prepared.
>>execute s using '3', 'cf:3a', '301';

--- 1 row(s) inserted.
>>
>>select left(row_id, 10) row_id, column_display(column_details, 40) column_details
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      COLUMN_DETAILS                          
----------  ----------------------------------------

1           cf:1a => 101, cf:1b => 102              
2           cf:2b => 201                            
3           cf:3a => 301, cf:3b => 302              

--- 3 row(s) selected.
>>select left(row_id, 10) row_id, left(column_display(column_details), 40) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                  
----------  ----------------------------------------

1           cf:1a => 101, cf:1b => 102              
2           cf:2b => 201                            
3           cf:3a => 301, cf:3b => 302              

--- 3 row(s) selected.
>>select left(row_id, 10) row_id, left(column_display(column_details, ('cf:2b', 'cf:1b')), 40) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                  
----------  ----------------------------------------

1           cf:1b => 102                            
2           cf:2b => 201                            

--- 2 row(s) selected.
>>
>>-- no rows updated. where pred fails.
>>update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:a', 
+>          (cast(column_lookup(column_details, 'cf:a') as varchar(10)) || '0103'))
+>   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '3021';

--- 0 row(s) updated.
>>
>>-- no rows updated. column not found in set clause.
>>update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:a', 
+>          (cast(column_lookup(column_details, 'cf:a') as varchar(10)) || '0103'))
+>   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302';

--- 0 row(s) updated.
>>
>>begin work;

--- SQL operation complete.
>>-- one row updated
>>update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:3a', 
+>          (cast(column_lookup(column_details, 'cf:3a') as varchar(10)) || '0103'))
+>   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302';

--- 1 row(s) updated.
>>
>>select left(row_id, 10) row_id, left(column_display(column_details), 50) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                            
----------  --------------------------------------------------

1           cf:1a => 101, cf:1b => 102                        
2           cf:2b => 201                                      
3           cf:3a => 3010103, cf:3a => 301, cf:3b => 302      

--- 3 row(s) selected.
>>rollback work;

--- SQL operation complete.
>>
>>select left(row_id, 10) row_id, left(column_display(column_details), 50) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                            
----------  --------------------------------------------------

1           cf:1a => 101, cf:1b => 102                        
2           cf:2b => 201                                      
3           cf:3a => 301, cf:3b => 302                        

--- 3 row(s) selected.
>>
>>begin work;

--- SQL operation complete.
>>-- one row updated
>>update  hbase."_ROW_".t022hbt1 set column_details = column_create ('cf:3a', 
+>          (cast(column_lookup(column_details, 'cf:3a') as varchar(10)) || '0103'))
+>   where row_id = '3' and column_lookup(column_details, 'cf:3b') = '302';

--- 1 row(s) updated.
>>
>>select left(row_id, 10) row_id, left(column_display(column_details), 50) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                            
----------  --------------------------------------------------

1           cf:1a => 101, cf:1b => 102                        
2           cf:2b => 201                                      
3           cf:3a => 3010103, cf:3a => 301, cf:3b => 302      

--- 3 row(s) selected.
>>commit work;

--- SQL operation complete.
>>
>>select left(row_id, 10) row_id, left(column_display(column_details), 50) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                            
----------  --------------------------------------------------

1           cf:1a => 101, cf:1b => 102                        
2           cf:2b => 201                                      
3           cf:3a => 3010103, cf:3b => 302                    

--- 3 row(s) selected.
>>
>>begin work;

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbt1 values 
+>       ('4', column_create(('cf:4a', '301'), ('cf:4b', '302')));

--- 1 row(s) inserted.
>>select left(row_id, 10) row_id, left(column_display(column_details), 40) 
+>           from hbase."_ROW_".t022hbt1 where row_id = '4';

ROW_ID      (EXPR)                                  
----------  ----------------------------------------

4           cf:4a => 301, cf:4b => 302              

--- 1 row(s) selected.
>>rollback work;

--- SQL operation complete.
>>
>>select col_family, col_name, left(col_value, 20) from hbase."_CELL_".t022hbt1;

COL_FAMILY                                                                                            COL_NAME                                                                                              (EXPR)
----------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------  --------------------

cf                                                                                                    1a                                                                                                    101                 
cf                                                                                                    1b                                                                                                    102                 
cf                                                                                                    2b                                                                                                    201                 
cf                                                                                                    3a                                                                                                    3010103             
cf                                                                                                    3b                                                                                                    302                 

--- 5 row(s) selected.
>>select left(row_id, 10) row_id, left(column_display(column_details), 40) 
+>           from hbase."_ROW_".t022hbt1 where row_id = '4';

--- 0 row(s) selected.
>>
>>select left(column_lookup (column_details, 'cf:1a'), 20) from hbase."_ROW_".t022hbt1;

(EXPR)              
--------------------

101                 

--- 1 row(s) selected.
>>
>>select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1
+>  where column_lookup (column_details, 'cf:3b') = '302';

(EXPR)              
--------------------

3010103             

--- 1 row(s) selected.
>>
>>select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1
+>  where column_lookup (column_details, 'cf:3a') = '301' 
+>     and column_lookup (column_details, 'cf:3b') = '302';

--- 0 row(s) selected.
>>
>>select left(column_lookup (column_details, 'cf:3a'), 20) from hbase."_ROW_".t022hbt1
+>  where column_lookup (column_details, 'cf:3a') = '3010103' 
+>     and column_lookup (column_details, 'cf:3b') = '302';

(EXPR)              
--------------------

3010103             

--- 1 row(s) selected.
>>
>>select left(column_lookup (column_details, 'cf:3c'), 20) from hbase."_ROW_".t022hbt1
+>  where column_lookup (column_details, 'cf:3b') = '3020';

--- 0 row(s) selected.
>>
>>select left(column_lookup (column_details, 'cf:3c'), 20) from hbase."_ROW_".t022hbt1
+>  where column_lookup (column_details, 'cf:3b') = '302';

(EXPR)              
--------------------

?                   

--- 1 row(s) selected.
>>
>>select column_lookup (column_details, 'cf:3a', cast as int),
+>       column_lookup (column_details, 'cf:3a', cast as int) + 1 
+>  from hbase."_ROW_".t022hbt1;

(EXPR)       (EXPR)              
-----------  --------------------

    3010103               3010104

--- 1 row(s) selected.
>>
>>select column_lookup (column_details, 'cf:3a', cast as int),
+>       column_lookup (column_details, 'cf:3a', cast as int) + 1 
+>  from hbase."_ROW_".t022hbt1
+>   where 
+>       column_lookup (column_details, 'cf:3b', cast as int) = 302;

(EXPR)       (EXPR)              
-----------  --------------------

    3010103               3010104

--- 1 row(s) selected.
>>
>>begin work;

--- SQL operation complete.
>>delete from hbase."_ROW_".t022hbt1 where row_id = '3';

--- 1 row(s) deleted.
>>select left(row_id, 10) from hbase."_ROW_".t022hbt1;

(EXPR)    
----------

1         
2         

--- 2 row(s) selected.
>>commit work;

--- SQL operation complete.
>>select left(row_id, 10) from hbase."_ROW_".t022hbt1;

(EXPR)    
----------

1         
2         

--- 2 row(s) selected.
>>
>>delete columns ('cf:1a') from hbase."_ROW_".t022hbt1  where row_id  = '1';

--- 1 row(s) deleted.
>>select left(row_id, 10) row_id, column_display(column_details, 40) column_details
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      COLUMN_DETAILS                          
----------  ----------------------------------------

1           cf:1b => 102                            
2           cf:2b => 201                            

--- 2 row(s) selected.
>>
>>delete from hbase."_ROW_".t022hbt1;

--- 2 row(s) deleted.
>>select count(*) from hbase."_ROW_".t022hbt1;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>
>>-- operations between trafodion and hbase tables
>>begin work;

--- SQL operation complete.
>>insert into t022t1 values (1, 100);

--- 1 row(s) inserted.
>>insert into hbase."_ROW_".t022hbt1 values ('1', column_create('cf:a', '100'));

--- 1 row(s) inserted.
>>select count(*) from t022t1;

(EXPR)              
--------------------

                   1

--- 1 row(s) selected.
>>select count(*) from hbase."_ROW_".t022hbt1;

(EXPR)              
--------------------

                   1

--- 1 row(s) selected.
>>rollback work;

--- SQL operation complete.
>>select count(*) from t022t1;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>select count(*) from hbase."_ROW_".t022hbt1;

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>
>>insert into hbase."_ROW_".t022hbt1 values 
+>       ('3', column_create(('cf:3a', '301'), ('cf:3b', '302')));

--- 1 row(s) inserted.
>>update hbase."_ROW_".t022hbt1 set column_details = column_create('cf:3b', '3022')
+>  where row_id = '3' and column_lookup(column_details, 'cf:3a', cast as int) = 3011;

--- 0 row(s) updated.
>>update hbase."_ROW_".t022hbt1 set column_details = column_create('cf:3b', '3022')
+>  where row_id = '3' and column_lookup(column_details, 'cf:3a', cast as int) = 301;

--- 1 row(s) updated.
>>select left(row_id, 10) row_id, left(column_display(column_details), 40) 
+>           from hbase."_ROW_".t022hbt1;

ROW_ID      (EXPR)                                  
----------  ----------------------------------------

3           cf:3a => 301, cf:3b => 3022             

--- 1 row(s) selected.
>>
>>-- negative tests
>>delete from hbase."_CELL_".t022hbt1;

*** ERROR[1425] This operation could not be performed on HBASE."_CELL_".T022HBT1. Reason: Cannot delete from an hbase table in CELL format. Use ROW format for this operation.

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

>>insert into hbase."_ROW_".t022hbt1 values ('2', column_create(':b', '201'));

*** ERROR[1426] An invalid HBase column name :b was specified. A valid name must be of the format: <ColumnFamily>:<ColumnName>

--- 0 row(s) inserted.
>>insert into hbase."_ROW_".t022hbt1 values ('2', '100');

*** ERROR[1429] Inserts into _ROW_ format external HBase tables can only use the VALUES clause and must use the column_create function to create values.

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

>>insert into hbase."_ROW_".t022hbt1 select * from hbase."_ROW_".t022hbt2;

*** ERROR[1429] Inserts into _ROW_ format external HBase tables can only use the VALUES clause and must use the column_create function to create values.

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

>>
>>-- tests with large rows
>>invoke hbase."_CELL_".t022hbt2;

-- Definition of Trafodion table HBASE."_CELL_".T022HBT2
-- Definition current  Thu May 10 20:58:57 2018

  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(1000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (ROW_ID ASC)

--- SQL operation complete.
>>invoke hbase."_ROW_".t022hbt2;

-- Definition of Trafodion table HBASE."_ROW_".T022HBT2
-- Definition current  Thu May 10 20:58:58 2018

  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_DETAILS                   VARCHAR(10000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (ROW_ID ASC)

--- SQL operation complete.
>>cqd hbase_max_column_val_length '100000';

--- SQL operation complete.
>>cqd hbase_max_column_info_length '100000';

--- SQL operation complete.
>>invoke hbase."_CELL_".t022hbt2;

-- Definition of Trafodion table HBASE."_CELL_".T022HBT2
-- Definition current  Thu May 10 20:58:58 2018

  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (ROW_ID ASC)

--- SQL operation complete.
>>invoke hbase."_ROW_".t022hbt2;

-- Definition of Trafodion table HBASE."_ROW_".T022HBT2
-- Definition current  Thu May 10 20:58:59 2018

  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_DETAILS                   VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (ROW_ID ASC)

--- SQL operation complete.
>>insert into hbase."_CELL_".t022hbt2 values ('1', 'cf', 'a', -1, repeat('a', 100000));

--- 1 row(s) inserted.
>>insert into hbase."_ROW_".t022hbt2 values 
+>                              ('2', column_create(
+>                                       ('cf:1a', repeat('a', 40000)),
+>                                       ('cf:1b', repeat('z', 40000))));

--- 1 row(s) inserted.
>>select count(*) from hbase."_CELL_".t022hbt2;

(EXPR)              
--------------------

                   3

--- 1 row(s) selected.
>>select char_length(col_value) from hbase."_CELL_".t022hbt2 order by 1;

(EXPR)    
----------

     40000
     40000
    100000

--- 3 row(s) selected.
>>select count(*) from hbase."_CELL_".t022hbt2;

(EXPR)              
--------------------

                   3

--- 1 row(s) selected.
>>select char_length(col_value) from hbase."_CELL_".t022hbt2 order by 1;

(EXPR)    
----------

     40000
     40000
    100000

--- 3 row(s) selected.
>>select count(*) from hbase."_ROW_".t022hbt2;

(EXPR)              
--------------------

                   2

--- 1 row(s) selected.
>>select char_length(column_details) from hbase."_ROW_".t022hbt2 order by 1;

(EXPR)    
----------

     80022
    100000

--- 2 row(s) selected.
>>select left(row_id, 10) row_id, left(column_display(column_details), 40) 
+>           from hbase."_ROW_".t022hbt2;

ROW_ID      (EXPR)                                  
----------  ----------------------------------------

1           cf:a => aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2           cf:1a => aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

--- 2 row(s) selected.
>>
>>
>>-- tests to map hbase tables to relational traf tables
>>cqd traf_hbase_mapped_tables 'ON';

--- SQL operation complete.
>>
>>drop hbase table t022hbm1;

*** WARNING[1004] Object T022HBM1 does not exist or object type is invalid for the current operation.

--- SQL operation complete.
>>create hbase table t022hbm1 (column family 'cf');

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbm1 values ('a1', 
+>                 column_create(('cf:B', '100 ')));

--- 1 row(s) inserted.
>>insert into hbase."_ROW_".t022hbm1 values ('a2', column_create(('cf:A', 'a2')));

--- 1 row(s) inserted.
>>
>>drop table if exists t022hbm1 cascade;

--- SQL operation complete.
>>drop external table if exists t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b char(4))
+>        primary key (a)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 20:59:13 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>-- if no schema is specified, table is looked in regular and then mapped schema
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 20:59:14 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>create table t022hbm1 (a int);

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion table TRAFODION.SCH.T022HBM1
-- Definition current  Thu May 10 20:59:20 2018

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT DEFAULT NULL
  )

--- SQL operation complete.
>>
>>-- join between traf and hbase table with the same name.
>>prepare s from select * from t022hbm1, hbase."_MAP_".t022hbm1;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+004
2    1    3    hybrid_hash_join                                      1.00E+004
.    .    2    trafodion_scan                  T022HBM1              1.00E+002
.    .    1    trafodion_scan                  T022HBM1              1.00E+002

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

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 20:59:30 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>-- should return error 4056
>>prepare s from select * from t022hbm1, hbase."_MAP_".t022hbm1;

*** ERROR[4056] Exposed name TRAFODION."_HB_MAP_".T022HBM1 appears more than once.

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

>>
>>prepare s from select * from t022hbm1 x, hbase."_MAP_".t022hbm1 y;

--- SQL command prepared.
>>
>>prepare s from select * from t022hbm1;

--- SQL command prepared.
>>execute s;

A     B   
----  ----

a1    100 
a2    ?   

--- 2 row(s) selected.
>>select * from t022hbm1 where a = 'a1';

A     B   
----  ----

a1    100 

--- 1 row(s) selected.
>>select a, cast(b as int) from t022hbm1;

A     (EXPR)     
----  -----------

a1            100
a2              ?

--- 2 row(s) selected.
>>
>>alter table t022hbm1 add column "cf".c int;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 20:59:39 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , "cf".C                           INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>-- create a traf table like a mapped table
>>cqd schema reset;

--- SQL operation complete.
>>create table t022hbm1_like like t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1_like;

-- Definition of Trafodion table TRAFODION.SCH.T022HBM1_LIKE
-- Definition current  Thu May 10 20:59:49 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , "cf".C                           INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>
>>insert into t022hbm1_like select * from t022hbm1;

--- 2 row(s) inserted.
>>select * from t022hbm1_like;

A     B     C          
----  ----  -----------

a1    100             ?
a2    ?               ?

--- 2 row(s) selected.
>>
>>create table t022hbm1_ctas as select * from t022hbm1;

--- 2 row(s) inserted.
>>select * from t022hbm1_ctas;

A     B     C          
----  ----  -----------

a1    100             ?
a2    ?               ?

--- 2 row(s) selected.
>>
>>-- create view on mapped table
>>create view t022v1 as select * from t022hbm1;

--- SQL operation complete.
>>
>>drop external table t022hbm1 cascade;

--- SQL operation complete.
>>invoke t022hbm1;

*** ERROR[4082] Object TRAFODION.SCH.T022HBM1 does not exist or is inaccessible.

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

>>create external table t022hbm1 ("cf".a varchar(4) not null,
+>            b int)
+>        primary key (a)
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:00:16 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>alter table t022hbm1 drop column b;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:00:24 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>alter table t022hbm1 add column "cf".b int;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:00:29 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL /*added_col*/
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>-- IUD operations on mapped tables
>>cqd traf_hbase_mapped_tables_iud 'ON';

--- SQL operation complete.
>>
>>cqd schema reset;

--- SQL operation complete.
>>delete from hbase."_ROW_".t022hbm1;

--- 2 row(s) deleted.
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int) 
+>        primary key (a)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1
+>        data format native;

--- SQL operation complete.
>>--This part of the test is being commented out due to non deterministic results
>>--It needs to be renabled after this JIRA is fixed : TRAFODION-2613
>>--insert into t022hbm1 values ('a', 1);
>>--select * from t022hbm1;
>>--update t022hbm1 set b = b + 1;
>>--select * from t022hbm1;
>>--insert into t022hbm1 values ('a', 1); -- should fail
>>--insert into t022hbm1 values ('b', null);
>>--select * from t022hbm1;
>>--delete from t022hbm1 where a = 'a';
>>--select * from t022hbm1;
>>--update t022hbm1 set b = 10;
>>--select * from t022hbm1;
>>--update t022hbm1 set b = null;
>>--select * from t022hbm1;
>>--delete from t022hbm1;
>>--select * from t022hbm1;
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a int not null, b int not null, c int) 
+>        primary key (a, b)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1
+>        data format native;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:00:41 2018

  (
    "cf".A                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".C                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC, B ASC)

--- SQL operation complete.
>>insert into t022hbm1 values (1,2,3);

--- 1 row(s) inserted.
>>insert into t022hbm1 values (1,2,3);

*** ERROR[8102] The operation is prevented by a unique constraint.

--- 0 row(s) inserted.
>>insert into t022hbm1 values (1, 1, 1);

--- 1 row(s) inserted.
>>insert into t022hbm1 values (-1, -2, -3);

--- 1 row(s) inserted.
>>select * from t022hbm1 order by 1;

A            B            C          
-----------  -----------  -----------

          1            1            1
          1            2            3
         -1           -2           -3

--- 3 row(s) selected.
>>upsert into t022hbm1 values (1,2,4);

--- 1 row(s) inserted.
>>select * from t022hbm1 order by 1;

A            B            C          
-----------  -----------  -----------

          1            1            1
          1            2            4
         -1           -2           -3

--- 3 row(s) selected.
>>
>>-- various serialization options
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, primary key not serialized (a), b int) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:00:47 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a char(4) not null, primary key serialized (a), b int) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1
+>        data format native;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:00:54 2018

  (
    "cf".A                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, primary key(a), b int) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:01:01 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null primary key, b int) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:01:07 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int) 
+>        primary key not serialized (a)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:01:16 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a char(4) not null, b int) 
+>        primary key serialized (a) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1
+>        data format native;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:01:25 2018

  (
    "cf".A                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>
>>drop external table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int) 
+>        primary key (a)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:01:33 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>drop hbase table t022hbm1;

--- SQL operation complete.
>>create hbase table t022hbm1 (column family 'cf');

--- SQL operation complete.
>>drop external table if exists t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int) 
+>        primary key (a) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbm1 values ('a1', column_create(('cf:B', '10')));

--- 1 row(s) inserted.
>>
>>-- multi column families in mapped table
>>drop external table if exists t022hbm2;

--- SQL operation complete.
>>drop hbase table t022hbm2;

*** WARNING[1004] Object T022HBM2 does not exist or object type is invalid for the current operation.

--- SQL operation complete.
>>create hbase table t022hbm2 (column family 'cf1', column family 'cf2');

--- SQL operation complete.
>>create external table t022hbm2 ("cf1".A int, "cf2".B int, 
+>                           "cf1".Z varchar(4) not null primary key) 
+>     map to hbase table t022hbm2;

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbm2 values ('a11', 
+>           column_create(('cf1:A', '10'), ('cf2:B', '20')));

--- 1 row(s) inserted.
>>invoke t022hbm2;

-- Definition of Trafodion HBase mapped table T022HBM2
-- Definition current  Thu May 10 21:02:06 2018

  (
    "cf1".A                          INT DEFAULT NULL
  , "cf2".B                          INT DEFAULT NULL
  , "cf1".Z                          VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY NOT SERIALIZED (Z ASC)

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

A            B            Z   
-----------  -----------  ----

         10           20  a11 

--- 1 row(s) selected.
>>cleanup hbase table t022hbm2;

--- SQL operation complete.
>>
>>-- registration of external hbase tables
>>drop external table if exists t022hbm2;

--- SQL operation complete.
>>drop hbase table t022hbm2;

--- SQL operation complete.
>>create hbase table t022hbm2 (column family 'cf1', column family 'cf2');

--- SQL operation complete.
>>create external table t022hbm2 ("cf1".A int, "cf2".B int, 
+>                           "cf1".Z varchar(4) not null primary key) 
+>     map to hbase table t022hbm2;

--- SQL operation complete.
>>
>>insert into hbase."_ROW_".t022hbm2 values ('a11', 
+>           column_create(('cf1:A', '10'), ('cf2:B', '20')));

--- 1 row(s) inserted.
>>invoke t022hbm2;

-- Definition of Trafodion HBase mapped table T022HBM2
-- Definition current  Thu May 10 21:02:32 2018

  (
    "cf1".A                          INT DEFAULT NULL
  , "cf2".B                          INT DEFAULT NULL
  , "cf1".Z                          VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY NOT SERIALIZED (Z ASC)

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

A            B            Z   
-----------  -----------  ----

         10           20  a11 

--- 1 row(s) selected.
>>
>>showddl hbase."_CELL_".t022hbm2;

/*
CREATE TABLE HBASE."_CELL_".T022HBM2
  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (ROW_ID ASC)
  )
;
*/

/* HBase DDL */
CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1')

REGISTER /*INTERNAL*/ HBASE TABLE T022HBM2;
/* ObjectUID = 7325828357327866088 */

--- SQL operation complete.
>>get hbase registered tables in catalog trafodion, match '%T022HBM2%';

HBase Registered Tables in Catalog TRAFODION
============================================

"_CELL_".T022HBM2
"_ROW_".T022HBM2

--- SQL operation complete.
>>unregister hbase table t022hbm2;

--- SQL operation complete.
>>register hbase table t022hbm2;

--- SQL operation complete.
>>get hbase registered tables in catalog trafodion, match '%T022HBM2%';

HBase Registered Tables in Catalog TRAFODION
============================================

"_CELL_".T022HBM2
"_ROW_".T022HBM2

--- SQL operation complete.
>>showddl hbase."_CELL_".t022hbm2;

/*
CREATE TABLE HBASE."_CELL_".T022HBM2
  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (ROW_ID ASC)
  )
;
*/

/* HBase DDL */
CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1')

REGISTER HBASE TABLE T022HBM2;
/* ObjectUID = 7247015363844883999 */

--- SQL operation complete.
>>showddl hbase."_ROW_".t022hbm2;

/*
CREATE TABLE HBASE."_ROW_".T022HBM2
  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COLUMN_DETAILS                   VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (ROW_ID ASC)
  )
;
*/

/* HBase DDL */
CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1')

REGISTER HBASE TABLE T022HBM2;
/* ObjectUID = 7247015363844884009 */

--- SQL operation complete.
>>unregister hbase table t022hbm2;

--- SQL operation complete.
>>get hbase registered tables in catalog trafodion, match '%T022HBM2%';

--- SQL operation complete.
>>showddl hbase."_CELL_".t022hbm2;

/*
CREATE TABLE HBASE."_CELL_".T022HBM2
  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (ROW_ID ASC)
  )
;
*/

/* HBase DDL */
CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1')

--- SQL operation complete.
>>
>>--showstats for table hbase."_CELL_".t022hbm2 on every column;
>>update statistics for table hbase."_CELL_".t022hbm2 on every column;

--- SQL operation complete.
>>showstats for table hbase."_CELL_".t022hbm2 on every column;

Histogram data for Table HBASE."_CELL_".T022HBM2
Table ID: 7590977785403306847

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1292582475      1           2           1 ROW_ID
1292582470      2           2           2 COL_FAMILY
1292582465      2           2           2 COL_NAME
1292582460      1           2           1 COL_TIMESTAMP
1292582455      2           2           2 COL_VALUE


--- SQL operation complete.
>>get hbase registered tables in catalog trafodion, match '%T022HBM2%';

HBase Registered Tables in Catalog TRAFODION
============================================

"_CELL_".T022HBM2

--- SQL operation complete.
>>showddl hbase."_CELL_".t022hbm2;

/*
CREATE TABLE HBASE."_CELL_".T022HBM2
  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (ROW_ID ASC)
  )
;
*/

/* HBase DDL */
CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1')

REGISTER /*INTERNAL*/ HBASE TABLE T022HBM2;
/* ObjectUID = 7590977785403306847 */

--- SQL operation complete.
>>
>>showstats for table hbase."_ROW_".t022hbm2 on every column;

Histogram data for Table HBASE."_ROW_".T022HBM2
Table ID: 0

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>update statistics for table hbase."_CELL_".t022hbm2 on every column;

--- SQL operation complete.
>>showstats for table hbase."_CELL_".t022hbm2 on every column;

Histogram data for Table HBASE."_CELL_".T022HBM2
Table ID: 7590977785403306847

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1292582474      1           2           1 ROW_ID
1292582471      2           2           2 COL_FAMILY
1292582464      2           2           2 COL_NAME
1292582461      1           2           1 COL_TIMESTAMP
1292582454      2           2           2 COL_VALUE


--- SQL operation complete.
>>get hbase registered tables in catalog trafodion, match '%T022HBM2%';

HBase Registered Tables in Catalog TRAFODION
============================================

"_CELL_".T022HBM2

--- SQL operation complete.
>>showddl hbase."_CELL_".t022hbm2;

/*
CREATE TABLE HBASE."_CELL_".T022HBM2
  (
    ROW_ID                           VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_FAMILY                       VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_NAME                         VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_TIMESTAMP                    LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , COL_VALUE                        VARCHAR(100000) CHARACTER SET ISO88591
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (ROW_ID ASC)
  )
;
*/

/* HBase DDL */
CREATE HBASE TABLE T022HBM2 ( COLUMN FAMILY '#1')

REGISTER /*INTERNAL*/ HBASE TABLE T022HBM2;
/* ObjectUID = 7590977785403306847 */

--- SQL operation complete.
>>
>>showstats for table hbase."_MAP_".t022hbm2 on every column;

Histogram data for Table HBASE."_MAP_".T022HBM2
Table ID: 7247015363844882861

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>update statistics for table hbase."_MAP_".t022hbm2 on every column;

--- SQL operation complete.
>>showstats for table hbase."_MAP_".t022hbm2 on every column;

Histogram data for Table HBASE."_MAP_".T022HBM2
Table ID: 7247015363844882861

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1320902189      1           1           1 A
1320902184      1           1           1 B
1320902179      1           1           1 Z


--- SQL operation complete.
>>showddl hbase."_MAP_".t022hbm2;

CREATE EXTERNAL TABLE T022HBM2
  (
    "cf1".A                          INT DEFAULT NULL
  , "cf2".B                          INT DEFAULT NULL
  , "cf1".Z                          VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY NOT SERIALIZED (Z ASC)
  )
  MAP TO HBASE TABLE T022HBM2 DATA FORMAT VARCHAR
;

--- SQL operation complete.
>>
>>-- error cases
>>
>>-- primary key cannot be missing
>>select * from t022hbm1;

A     B          
----  -----------

a1             10

--- 1 row(s) selected.
>>
>>-- operations not allowed
>>alter table t022hbm1 alter column "cf".b largeint;

*** ERROR[3242] This statement is not supported. Reason: This feature not available for an HBase mapped table.

--- SQL operation failed with errors.
>>invoke t022hbm1;

-- Definition of Trafodion HBase mapped table T022HBM1
-- Definition current  Thu May 10 21:03:39 2018

  (
    "cf".A                           VARCHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , "cf".B                           INT DEFAULT NULL
  )
  PRIMARY KEY NOT SERIALIZED (A ASC)

--- SQL operation complete.
>>
>>set schema trafodion."_HB_MAP_";

--- SQL operation complete.
>>
>>-- cannot invoke using map schema name
>>invoke "_HB_MAP_".t022hbm1;

*** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified.

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

>>
>>-- cannot use "_HB_MAP_" in a table name
>>prepare s from select * from t022hbm1;

*** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified.

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

>>prepare s from select * from "_HB_MAP_".t022hbm1;

*** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified.

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

>>
>>drop table "_HB_MAP_".t022hbm1;

*** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified.

--- SQL operation failed with errors.
>>alter table trafodion."_HB_MAP_".t022hbm1 drop column b;

*** ERROR[4261] The schema name _HB_MAP_ is reserved for HBase mapped tables and cannot be explicitly specified.

--- SQL operation failed with errors.
>>
>>set schema trafodion.sch;

--- SQL operation complete.
>>
>>drop external table if exists t022hbm1;

--- SQL operation complete.
>>
>>-- cannot specify serialized option
>>create external table t022hbm1 (a varchar(4) not null, b int) 
+>        primary key serialized (a) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

*** ERROR[3242] This statement is not supported. Reason: SERIALIZED option cannot be specified for primary key of this table.

--- SQL operation failed with errors.
>>
>>-- cannot be aligned format
>>create external table t022hbm1 (a varchar(4) not null primary key) 
+>  attribute aligned format map to hbase table t022hbm1;

*** ERROR[3242] This statement is not supported. Reason: Aligned format cannot be specified for an HBase mapped table.

--- SQL operation failed with errors.
>>
>>-- must specify pkey
>>create external table t022hbm1 (a char(4)) map to hbase table t022hbm1;

*** ERROR[4259] Primary Key clause must be specified for an HBase mapped table.

--- SQL operation failed with errors.
>>
>>-- external and hbase table names must be the same
>>create external table t022hbm11 (a char(4) not null primary key) 
+>        map to hbase table t022hbm1;

*** ERROR[1180] Trying to create an external HBASE table with a different schema or table name (T022HBM11) than the source table (T022HBM1).  The external schema and table name must be the same as the source.

--- SQL operation failed with errors.
>>
>>-- all non-pkey columns must be nullable
>>create external table t022hbm1 (a varchar(4) not null primary key,
+>              b int not null)
+>              map to hbase table t022hbm1;

*** ERROR[3242] This statement is not supported. Reason: Non-key columns of an HBase mapped table must be nullable with default value of NULL.

--- SQL operation failed with errors.
>>
>>-- all non-pkey columns must have default value of null
>>create external table t022hbm1 (a varchar(4) not null primary key,
+>              b int default 10)
+>              map to hbase table t022hbm1;

*** ERROR[3242] This statement is not supported. Reason: Non-key columns of an HBase mapped table must be nullable with default value of NULL.

--- SQL operation failed with errors.
>>
>>-- mapped table already exist
>>create external table t022hbm1 (a varchar(4) not null primary key) 
+>              map to hbase table t022hbm1;

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null primary key) 
+>              map to hbase table t022hbm1;

*** ERROR[1390] Object TRAFODION."_HB_MAP_".T022HBM1 already exists in Trafodion.

--- SQL operation failed with errors.
>>
>>-- hbase table doesn't exist
>>create external table t022hbm11 (a char(4) not null primary key) 
+>                map to hbase table t022hbm11;

*** ERROR[4260] HBase table(T022HBM11) to be mapped as an external table does not exist.

--- SQL operation failed with errors.
>>
>>-- cannot create view in HB_MAP schema
>>create view "_HB_MAP_".v as select * from t022hbm1;

*** ERROR[1118] Creating object TRAFODION."_HB_MAP_".V is not allowed in a reserved system schema.

--- SQL operation failed with errors.
>>
>>-- cannot create index on an hbase external table
>>create index ti on t022hbm1 (a);

*** ERROR[3242] This statement is not supported. Reason: Cannot create index on an HBase mapped table.

--- SQL operation failed with errors.
>>
>>drop external table if exists t022hbm1;

--- SQL operation complete.
>>drop hbase table t022hbm1;

--- SQL operation complete.
>>create hbase table t022hbm1 (column family 'cf');

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int) 
+>        primary key (a) 
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbm1 values ('a1', 
+>                               column_create(('cf:A', '10')));

--- 1 row(s) inserted.
>>-- rowID must match pkey col contents
>>select * from t022hbm1;

*** ERROR[8556] An error occurred while accessing HBase table T022HBM1.  Details: HBase rowID content must match the primary key column content.

--- 0 row(s) selected.
>>
>>drop hbase table t022hbm1;

--- SQL operation complete.
>>create hbase table t022hbm1 (column family 'cf');

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int)
+>        primary key (a)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbm1 values ('a1234567', 
+>                               column_create(('cf:B', '10')));

--- 1 row(s) inserted.
>>-- primary key col length must be big enough to hold rowID
>>select * from t022hbm1;

*** ERROR[8556] An error occurred while accessing HBase table T022HBM1.  Details: retrieved rowID of length 8 is larger than the specified key size of 4.

--- 0 row(s) selected.
>>
>>drop hbase table t022hbm1;

--- SQL operation complete.
>>create hbase table t022hbm1 (column family 'cf');

--- SQL operation complete.
>>create external table t022hbm1 (a varchar(4) not null, b int)
+>        primary key (a)
+>        attribute default column family 'cf'
+>        map to hbase table t022hbm1;

--- SQL operation complete.
>>insert into hbase."_ROW_".t022hbm1 values ('a1', 
+>                               column_create(('cf:B', '1000000')));

--- 1 row(s) inserted.
>>-- buffer to retrieve column value must be big enough
>>cqd hbase_max_column_val_length '5';

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

*** ERROR[8556] An error occurred while accessing HBase table T022HBM1.  Details: actual column value length of 7 is greater than the expected max buffer size of 5.

--- 0 row(s) selected.
>>
>>log;