>>obey TEST071(setup);
>>create schema mtd;

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

--- SQL operation complete.
>>
>>cqd traf_aligned_row_format 'OFF';

--- SQL operation complete.
>>
>>-- table with identity column
>>create table mtd00(id smallint unsigned GENERATED BY DEFAULT AS IDENTITY not null,
+>                   sname varchar(50) not null,
+>                   rname varchar(50) not null,
+>                   bday date not null CONSTRAINT minchk1 CHECK ( bday > date '1850-01-01'),
+>                   dth date,
+>                   num_mv smallint,
+>                   primary key(id, bday))
+>                   division by (DATE_PART('YEARMONTH', bday))  ;

--- SQL operation complete.
>>
>>create table mtd0(store_id integer not null,
+>                  item_id  integer not null,
+>                  sale_date date default date '2000-01-01' not null ,
+>                  sale_amt numeric(10,2),
+>                  primary key (store_id, item_id, sale_date));

--- SQL operation complete.
>>
>>create table mtd1(store_id integer not null,
+>                  item_id  integer not null,
+>                  sale_date date default date '2000-01-01' not null,
+>                  sale_amt numeric(10,2),
+>                  primary key (store_id, item_id, sale_date))
+>division by (date_part('YEARMONTH', sale_date));

--- SQL operation complete.
>>
>>create table mtd2(store_id integer not null,
+>                  item_id  integer not null,
+>                  sale_date date default date '2000-01-01' not null,
+>                  sale_amt numeric(10,2))
+>store by (store_id, item_id, sale_date)
+>division by (date_part('YEARMONTH', sale_date));

--- SQL operation complete.
>>
>>create table mtd3 like mtd1;

--- SQL operation complete.
>>
>>create table mtdTRIGtarget1 like mtd3 without constraints without division;

--- SQL operation complete.
>>alter table mtdTRIGtarget1 add column sale_amt_before numeric(10,2) default 0;

--- SQL operation complete.
>>
>>create table mtdTRIGtarget2d like mtd3;

--- SQL operation complete.
>>alter table mtdTRIGtarget2d add column sale_amt_before numeric(10,2) default 0;

--- SQL operation complete.
>>
>>create table mtd4 
+>   store by (store_id, item_id, sale_date)
+>   division by (date_part('YEARMONTHD', sale_date) desc)
+>   as select * from mtd2;

--- 0 row(s) inserted.
>>
>>create table mtdRItarget1(d_date date default date '2000-01-01' not null primary key,
+>                          quarter_id char(6));

--- SQL operation complete.
>>create table mtdRItarget2d(d_date date default date '2000-01-01' not null primary key,
+>                           quarter_id char(6))
+>division by (date_part('YEARMONTH', d_date));

--- SQL operation complete.
>>
>>set param ?sch 'MTD';
>>set param ?pat '%MTD%';
>>
>>prepare smdquery from
+>select cast(substring(objects.object_name, 1,15) as char(15 bytes) character set utf8)  as table_name,
+>       objects.object_type as typ,
+>       cast(cols.column_number as smallint) colnum,
+>       cast(substring(cols.column_name,1,20) as char(20 bytes) character set utf8) as column_name,
+>       keys.ordering as ord,
+>       cast(column_size as smallint) colsiz,
+>       column_class colclass, default_class defclass,
+>       coalesce(cast(keys.keyseq_number as char(4)),'none') as ckey_col,
+>       coalesce(substring(text.text,1,60),'none') comp_expression
+>from "_MD_".OBJECTS objects
+>     join "_MD_".COLUMNS cols
+>       on objects.object_uid = cols.object_uid
+>     left outer join "_MD_".keys keys
+>       on objects.object_uid = keys.object_uid and
+>          cols.column_number = keys.column_number
+>     left outer join "_MD_".TEXT text
+>       on objects.object_uid = text.text_uid and
+>          text.text_type = 4 and
+>          text.sub_id = cols.column_number
+>where objects.schema_name = ?sch and
+>      objects.object_name like ?pat and
+>      objects.object_type in ('BT', 'VI')
+>order by 1, 2, 3
+>;

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

TABLE_NAME       TYP  COLNUM  COLUMN_NAME           ORD          COLSIZ  COLCLASS  DEFCLASS     CKEY_COL  COMP_EXPRESSION
---------------  ---  ------  --------------------  -----------  ------  --------  -----------  --------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MTD0             BT        0  STORE_ID                        0       4  U                   1  1         none                                                                                                                                                                                                                                            
MTD0             BT        1  ITEM_ID                         0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD0             BT        2  SALE_DATE                       0       4  U                   3  3         none                                                                                                                                                                                                                                            
MTD0             BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD00            BT        0  ID                              0       2  U                   5  2         none                                                                                                                                                                                                                                            
MTD00            BT        1  SNAME                           ?      50  U                   1  none      none                                                                                                                                                                                                                                            
MTD00            BT        2  RNAME                           ?      50  U                   1  none      none                                                                                                                                                                                                                                            
MTD00            BT        3  BDAY                            0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD00            BT        4  DTH                             ?       4  U                   2  none      none                                                                                                                                                                                                                                            
MTD00            BT        5  NUM_MV                          ?       2  U                   2  none      none                                                                                                                                                                                                                                            
MTD00            BT        6  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',BDAY)                                                                                                                                                                                                                     
MTD1             BT        0  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD1             BT        1  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD1             BT        2  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTD1             BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD1             BT        4  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                
MTD2             BT        0  SYSKEY                          0       8  S                   1  5         none                                                                                                                                                                                                                                            
MTD2             BT        1  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD2             BT        2  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD2             BT        3  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTD2             BT        4  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD2             BT        5  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                
MTD3             BT        0  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD3             BT        1  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD3             BT        2  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTD3             BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD3             BT        4  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                
MTD4             BT        0  SYSKEY                          0       8  S                   1  5         none                                                                                                                                                                                                                                            
MTD4             BT        1  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD4             BT        2  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD4             BT        3  SALE_DATE                       0       4  U                   1  4         none                                                                                                                                                                                                                                            
MTD4             BT        4  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD4             BT        5  _DIVISION_1_                    1       4  S                   7  1         DATE_PART('YEARMONTHD',SALE_DATE)                                                                                                                                                                                                               
MTDRITARGET1     BT        0  D_DATE                          0       4  U                   3  1         none                                                                                                                                                                                                                                            
MTDRITARGET1     BT        1  QUARTER_ID                      ?       6  U                   2  none      none                                                                                                                                                                                                                                            
MTDRITARGET2D    BT        0  D_DATE                          0       4  U                   3  2         none                                                                                                                                                                                                                                            
MTDRITARGET2D    BT        1  QUARTER_ID                      ?       6  U                   2  none      none                                                                                                                                                                                                                                            
MTDRITARGET2D    BT        2  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',D_DATE)                                                                                                                                                                                                                   
MTDTRIGTARGET1   BT        0  STORE_ID                        0       4  U                   1  1         none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        1  ITEM_ID                         0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        2  SALE_DATE                       0       4  U                   3  3         none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        4  SALE_AMT_BEFORE                 ?       8  A                   3  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        0  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        1  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        2  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        4  SALE_AMT_BEFORE                 ?       8  A                   3  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        5  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                

--- 49 row(s) selected.
>>
>>set param ?STM 'S';
>>
>>prepare skeyquery from
+>select OPERATOR,
+>       TNAME,
+>       cast(substring(description from begin_key_pos for begin_key_len) as char(600)) bkey,
+>       cast(substring(description from end_key_pos for end_key_len) as char(600)) ekey
+>from
+>  (select operator,
+>          tname,
+>          description,
+>          begin_key_pos,
+>          end_key_pos-begin_key_pos-1 as begin_key_len,
+>          end_key_pos,
+>          case when end_key_len_or_0 = 0 then char_length(description) + 1 - end_key_pos
+>                                         else end_key_len_or_0 + 10 end end_key_len
+>   from
+>     (select operator,
+>             tname,
+>             cast(description as varchar(3000) character set iso88591) as description,
+>             position(' begin_key: ' IN description) + 1 as begin_key_pos,
+>             position(' end_key: '   IN description) + 1 as end_key_pos,
+>             position(': ' IN substring(description from position(' end_key: '   IN description) + 10)) end_key_len_or_0
+>      from table(explain(null,?STM))
+>      where TNAME like '%MTD%') X) Y;

--- SQL command prepared.
>>
>>obey TEST071(tests);
>>
>>set schema mtd;

--- SQL operation complete.
>>
>>insert into mtd0 values (1, 100, date '2011-06-30', 100.00);

--- 1 row(s) inserted.
>>
>>prepare s from
+>insert into mtd1 select * from mtd0;

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

--- 1 row(s) inserted.
>>
>>prepare s from
+>insert into mtd1 values(10,10,default,default);

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

--- 1 row(s) inserted.
>>
>>prepare s from
+>insert into mtd1(item_id, sale_amt, store_id, sale_date)
+>values(11,default,11,default);

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

--- 1 row(s) inserted.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          100  2011-06-30                 100.00        201106
         10           10  2000-01-01                      ?        200001
         11           11  2000-01-01                      ?        200001

--- 3 row(s) selected.
>>
>>prepare s from
+>update mtd1 set sale_date = date '2011-07-01';

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

--- 3 row(s) updated.
>>
>>prepare s from
+>update mtd1 set sale_date = sale_date + interval '1' month
+>where store_id < 5;

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

--- 1 row(s) updated.
>>
>>prepare s from
+>update mtd1 set item_id = item_id+1;

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

--- 3 row(s) updated.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-08-01                 100.00        201108
         10           11  2011-07-01                      ?        201107
         11           12  2011-07-01                      ?        201107

--- 3 row(s) selected.
>>
>>insert into mtd0 values (1, 101, date '2011-08-01', 300.00);

--- 1 row(s) inserted.
>>insert into mtd0 values (2, 102, date '2011-06-30', 300.00);

--- 1 row(s) inserted.
>>
>>prepare s from
+>merge into mtd1
+>  using (select * from mtd0) as src 
+>  -- need query support for SearchKey to avoid adding the computed column pred here
+>  on (src.store_id, src.item_id, src.sale_date, DATE_PART('YEARMONTH',src.sale_date)) =
+>     (mtd1.store_id, mtd1.item_id, mtd1.sale_date, mtd1."_DIVISION_1_")
+>when matched
+>  then update set sale_amt = src.sale_amt
+>when not matched
+>  then insert values (src.store_id, src.item_id, src.sale_date, src.sale_amt*1.1)
+>;

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

--- 3 row(s) updated.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          100  2011-06-30                 110.00        201106
          1          101  2011-08-01                 300.00        201108
          2          102  2011-06-30                 330.00        201106
         10           11  2011-07-01                      ?        201107
         11           12  2011-07-01                      ?        201107

--- 5 row(s) selected.
>>
>>prepare s from
+>merge into mtd1
+>  using (select * from mtd0) as src 
+>  on (src.store_id, src.item_id, src.sale_date) =
+>     (mtd1.store_id, mtd1.item_id, mtd1.sale_date)
+>when matched
+>  then update set sale_amt = src.sale_amt
+>when not matched
+>  then insert values (src.store_id, src.item_id, src.sale_date, src.sale_amt)
+>;

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTD0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    max_card_est:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
TRAFODION_MERGE                 TRAFODION.MTD.MTD1                                            begin_key: (_DIVISION_1_ = DATE_PART('YEARMONTH',TRAFODION.MTD.MTD0.SALE_DATE)) and (STORE_ID = TRAFODION.MTD.MTD0.STORE_ID) and (ITEM_ID = TRAFODION.MTD.MTD0.ITEM_ID) and (SALE_DATE = TRAFODION.MTD.MTD0.SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                    end_key: (_DIVISION_1_ = DATE_PART('YEARMONTH',TRAFODION.MTD.MTD0.SALE_DATE)) and (STORE_ID = TRAFODION.MTD.MTD0.STORE_ID) and (ITEM_ID = TRAFODION.MTD.MTD0.ITEM_ID) and (SALE_DATE = TRAFODION.MTD.MTD0.SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                    

--- 2 row(s) selected.
>>
>>execute s;

--- 3 row(s) updated.
>>
>>prepare s from
+>merge into mtd1
+>  using (select * from mtd0 where item_id = 102) as src 
+>  on (src.store_id, src.item_id, src.sale_date) =
+>     (mtd1.store_id, mtd1.item_id, mtd1.sale_date)
+>when matched
+>  then delete
+>;

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTD0                                                          begin_key: (STORE_ID = <min>), (ITEM_ID = %(102)), (SALE_DATE = <min>)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    end_key: (STORE_ID = <max>), (ITEM_ID = %(102)), (SALE_DATE = <max>)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
TRAFODION_VSBB_SCAN             MTD1                                                          begin_key: (_DIVISION_1_ = DATE_PART('YEARMONTH',TRAFODION.MTD.MTD0.SALE_DATE)), (STORE_ID = TRAFODION.MTD.MTD0.STORE_ID), (ITEM_ID = %(102)), (SALE_DATE = TRAFODION.MTD.MTD0.SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                 end_key: (_DIVISION_1_ = DATE_PART('YEARMONTH',TRAFODION.MTD.MTD0.SALE_DATE)), (STORE_ID = TRAFODION.MTD.MTD0.STORE_ID), (ITEM_ID = %(102)), (SALE_DATE = TRAFODION.MTD.MTD0.SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                 
TRAFODION_VSBB_DELETE           TRAFODION.MTD.MTD1                                            begin_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = TRAFODION.MTD.MTD0.STORE_ID) and (ITEM_ID = %(102)) and (SALE_DATE = TRAFODION.MTD.MTD0.SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                                               end_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = TRAFODION.MTD.MTD0.STORE_ID) and (ITEM_ID = %(102)) and (SALE_DATE = TRAFODION.MTD.MTD0.SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                                               

--- 3 row(s) selected.
>>
>>execute s;

--- 1 row(s) updated.
>>
>>prepare s from
+>delete from mtd1 where store_id = 10;

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

--- 1 row(s) deleted.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          100  2011-06-30                 100.00        201106
          1          101  2011-08-01                 300.00        201108
         11           12  2011-07-01                      ?        201107

--- 3 row(s) selected.
>>
>>prepare s from
+>delete from mtd1 where sale_date between date '2011-06-01' and date '2011-06-30';

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTD1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    max_card_est:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
TRAFODION_VSBB_DELETE           TRAFODION.MTD.MTD1                                            begin_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = STORE_ID) and (ITEM_ID = ITEM_ID) and (SALE_DATE = SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    end_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = STORE_ID) and (ITEM_ID = ITEM_ID) and (SALE_DATE = SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

--- 2 row(s) selected.
>>execute s;

--- 1 row(s) deleted.
>>
>>prepare s from
+>update mtd1 set sale_amt = sale_amt - 1 where sale_date < date '2010-01-01';

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTD1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    max_card_est:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
TRAFODION_VSBB_UPDATE           TRAFODION.MTD.MTD1                                            begin_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = STORE_ID) and (ITEM_ID = ITEM_ID) and (SALE_DATE = SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    end_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = STORE_ID) and (ITEM_ID = ITEM_ID) and (SALE_DATE = SALE_DATE)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

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

--- SQL operation complete.
>>execute s;

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

--- SQL operation complete.
>>
>>prepare s from
+>delete from mtd1;

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

--- 2 row(s) deleted.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

--- 0 row(s) selected.
>>
>>--------- same as above, but with table mtd2 that has a syskey -------
>>
>>delete from mtd0 where item_id > 100;

--- 2 row(s) deleted.
>>
>>prepare s from
+>insert into mtd2 select * from mtd0;

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

--- 1 row(s) inserted.
>>
>>prepare s from
+>insert into mtd2 values(10,10,default,default);

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

--- 1 row(s) inserted.
>>
>>prepare s from
+>insert into mtd2(item_id, sale_amt, store_id, sale_date)
+>values(11,default,11,default);

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

--- 1 row(s) inserted.
>>
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          100  2011-06-30                 100.00        201106
         10           10  2000-01-01                      ?        200001
         11           11  2000-01-01                      ?        200001

--- 3 row(s) selected.
>>
>>prepare s from
+>update mtd2 set sale_date = date '2011-07-01';

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

--- 3 row(s) updated.
>>
>>prepare s from
+>update mtd2 set sale_date = sale_date + interval '1' month
+>where store_id < 5;

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

--- 1 row(s) updated.
>>
>>prepare s from
+>update mtd2 set item_id = item_id+1;

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

--- 3 row(s) updated.
>>
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-08-01                 100.00        201108
         10           11  2011-07-01                      ?        201107
         11           12  2011-07-01                      ?        201107

--- 3 row(s) selected.
>>
>>insert into mtd0 values (1, 101, date '2011-08-01', 300.00);

--- 1 row(s) inserted.
>>insert into mtd0 values (2, 102, date '2011-06-30', 300.00);

--- 1 row(s) inserted.
>>
>>prepare s from
+>merge into mtd2
+>  using (select * from mtd0) as src 
+>  -- need query support for SearchKey to avoid adding the computed column pred here
+>  on (src.store_id, src.item_id, src.sale_date, DATE_PART('YEARMONTH',src.sale_date)) =
+>     (mtd2.store_id, mtd2.item_id, mtd2.sale_date, mtd2."_DIVISION_1_")
+>when matched
+>  then update set sale_amt = src.sale_amt
+>when not matched
+>  then insert values (src.store_id, src.item_id, src.sale_date, src.sale_amt*1.1)
+>;

*** ERROR[3241] This MERGE statement is not supported. Reason:  SYSKEY not allowed.

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

>>-- fails, this unique merge won't work with a SYSKEY table
>>
>>-- simulate the merge with update/insert
>>update mtd2 set sale_amt =
+>   (select sale_amt
+>    from mtd0 src
+>    where (src.store_id, src.item_id, src.sale_date, DATE_PART('YEARMONTH',src.sale_date)) =
+>          (mtd2.store_id, mtd2.item_id, mtd2.sale_date, mtd2."_DIVISION_1_"))
+>where exists (select 1
+>              from mtd0 src
+>              where (src.store_id,  src.item_id,  src.sale_date) =
+>                    (mtd2.store_id, mtd2.item_id, mtd2.sale_date));

--- 1 row(s) updated.
>>execute skeyquery;

--- 0 row(s) selected.
>>
>>insert into mtd2
+>select store_id, item_id, sale_date, sale_amt*1.1
+>from mtd0
+>where NOT exists (select 1
+>                  from mtd2
+>                  where (mtd2.store_id, mtd2.item_id, mtd2.sale_date) =
+>                        (mtd0.store_id, mtd0.item_id, mtd0.sale_date));

--- 2 row(s) inserted.
>>
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          100  2011-06-30                 110.00        201106
          1          101  2011-08-01                 300.00        201108
          2          102  2011-06-30                 330.00        201106
         10           11  2011-07-01                      ?        201107
         11           12  2011-07-01                      ?        201107

--- 5 row(s) selected.
>>
>>prepare s from
+>merge into mtd2
+>  using (select * from mtd0 where item_id = 102) as src 
+>  on (src.store_id, src.item_id, src.sale_date) =
+>     (mtd2.store_id, mtd2.item_id, mtd2.sale_date)
+>when matched
+>  then delete
+>;

*** ERROR[3241] This MERGE statement is not supported. Reason:  SYSKEY not allowed.

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

>>-- currently not supported
>>-- explain s;
>>-- execute skeyquery;
>>-- execute s;
>>
>>prepare s from
+>delete from mtd2 where store_id = 10;

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

--- 1 row(s) deleted.
>>
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          100  2011-06-30                 110.00        201106
          1          101  2011-08-01                 300.00        201108
          2          102  2011-06-30                 330.00        201106
         11           12  2011-07-01                      ?        201107

--- 4 row(s) selected.
>>
>>prepare s from
+>delete from mtd2 where sale_date between date '2011-06-01' and date '2011-06-30';

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTD2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    max_card_est:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
TRAFODION_VSBB_DELETE           TRAFODION.MTD.MTD2                                            begin_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = STORE_ID) and (ITEM_ID = ITEM_ID) and (SALE_DATE = SALE_DATE) and (SYSKEY = SYSKEY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                              end_key: (_DIVISION_1_ = _DIVISION_1_) and (STORE_ID = STORE_ID) and (ITEM_ID = ITEM_ID) and (SALE_DATE = SALE_DATE) and (SYSKEY = SYSKEY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                              

--- 2 row(s) selected.
>>execute s;

--- 2 row(s) deleted.
>>
>>prepare s from
+>delete from mtd2;

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

--- 2 row(s) deleted.
>>
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

--- 0 row(s) selected.
>>
>>-- test elimination of division column for single-division queries with order by
>>explain options 'f'
+>select *
+>from mtd1
+>where sale_date between date '2000-01-01' and date '2000-01-21'
+>order by store_id;

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

1    .    2    root                                                  1.10E+001
.    .    1    trafodion_scan                  MTD1                  1.10E+001

--- SQL operation complete.
>>-- expect no sort operator, for a single division, table is sorted on store_id
>>
>>explain options 'f'
+>select *
+>from mtd2
+>where sale_date between date '2000-01-01' and date '2000-01-21'
+>order by store_id;

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

1    .    2    root                                                  1.10E+001
.    .    1    trafodion_scan                  MTD2                  1.10E+001

--- SQL operation complete.
>>-- expect no sort operator, for a single division, table is sorted on store_id
>>
>>explain options 'f'
+>select *
+>from mtd2
+>where sale_date between date '2000-01-01' and date '2000-02-21'
+>order by store_id;

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

2    .    3    root                                                  1.10E+001
1    .    2    sort                                                  1.10E+001
.    .    1    trafodion_scan                  MTD2                  1.10E+001

--- SQL operation complete.
>>-- expect a sort operator, since we are querying more than one division
>>
>>-- some simple join queries, validate min/max optimization for divisioned table
>>
>>prepare min_max_opt_query from
+>select cast(substring(description from mm_pos for mm_len) as char(200 bytes) character set utf8) mm_cols
+>from
+>  (select description,
+>          mm_pos,
+>          mm_len
+>   from
+>     (select cast(description as varchar(3000 bytes) character set utf8) as description,
+>             position(' min_max_cols: ' IN description) + 1 as mm_pos,
+>             position(' ' IN substring(description
+>                                        from position(' min_max_cols: '
+>                                                      IN description) + 21)) + 19 mm_len
+>      from table(explain(null,'S'))
+>     ) X
+>    where mm_pos > 1
+>  ) Y;

--- SQL command prepared.
>>
>>delete from mtd0;

--- 3 row(s) deleted.
>>delete from mtd1;

--- 0 row(s) deleted.
>>delete from mtd4;

--- 0 row(s) deleted.
>>
>>insert into mtd0(store_id, item_id, sale_date, sale_amt)
+>select st+num/10, it+num, dt + num * interval '1' day, am
+>from (values (1, 100, date '2011-01-01', 100.00)) v(st, it, dt, am),
+>     (select 10*tens+ones as num
+>      from (values (0)) seed(c)
+>      transpose 0,1,2,3,4,5,6,7,8,9 as ones
+>      transpose 0,1,2,3,4,5,6,7,8,9 as tens) t;

--- 100 row(s) inserted.
>>
>>
>>insert into mtd1 select * from mtd0;

--- 100 row(s) inserted.
>>insert into mtd4 select * from mtd0;

--- 100 row(s) inserted.
>>
>>cqd join_order_by_user 'on';

--- SQL operation complete.
>>cqd gen_hshj_min_max_opt 'on';

--- SQL operation complete.
>>
>>prepare s from
+>select *
+>from mtd1 fact join mtd0 dim on fact.sale_date = dim.sale_date
+>where dim.item_id between 135 and 150;

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

--- 0 row(s) selected.
>>-- should do min/max opt on "_DIVISION_1_" col
>>-- NOTE: This is not working at this time, to be fixed later
>>execute s;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT
-----------  -----------  ----------  ---------------------  -----------  -----------  ----------  ---------------------

          4          135  2011-02-05                 100.00            4          135  2011-02-05                 100.00
          4          136  2011-02-06                 100.00            4          136  2011-02-06                 100.00
          4          137  2011-02-07                 100.00            4          137  2011-02-07                 100.00
          4          138  2011-02-08                 100.00            4          138  2011-02-08                 100.00
          4          139  2011-02-09                 100.00            4          139  2011-02-09                 100.00
          5          140  2011-02-10                 100.00            5          140  2011-02-10                 100.00
          5          141  2011-02-11                 100.00            5          141  2011-02-11                 100.00
          5          142  2011-02-12                 100.00            5          142  2011-02-12                 100.00
          5          143  2011-02-13                 100.00            5          143  2011-02-13                 100.00
          5          144  2011-02-14                 100.00            5          144  2011-02-14                 100.00
          5          145  2011-02-15                 100.00            5          145  2011-02-15                 100.00
          5          146  2011-02-16                 100.00            5          146  2011-02-16                 100.00
          5          147  2011-02-17                 100.00            5          147  2011-02-17                 100.00
          5          148  2011-02-18                 100.00            5          148  2011-02-18                 100.00
          5          149  2011-02-19                 100.00            5          149  2011-02-19                 100.00
          6          150  2011-02-20                 100.00            6          150  2011-02-20                 100.00

--- 16 row(s) selected.
>>
>>prepare s from
+>select *
+>from mtd4 fact join mtd0 dim on fact.sale_date = dim.sale_date
+>where dim.item_id between 135 and 180
+>  and fact.sale_date between date '2011-02-01' and date '2011-02-28';

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

--- 0 row(s) selected.
>>-- should do min/max opt on "_DIVISION_1_" col
>>-- NOTE: This is not working at this time, to be fixed later
>>execute s;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT
-----------  -----------  ----------  ---------------------  -----------  -----------  ----------  ---------------------

          4          135  2011-02-05                 100.00            4          135  2011-02-05                 100.00
          4          136  2011-02-06                 100.00            4          136  2011-02-06                 100.00
          4          137  2011-02-07                 100.00            4          137  2011-02-07                 100.00
          4          138  2011-02-08                 100.00            4          138  2011-02-08                 100.00
          4          139  2011-02-09                 100.00            4          139  2011-02-09                 100.00
          5          140  2011-02-10                 100.00            5          140  2011-02-10                 100.00
          5          141  2011-02-11                 100.00            5          141  2011-02-11                 100.00
          5          142  2011-02-12                 100.00            5          142  2011-02-12                 100.00
          5          143  2011-02-13                 100.00            5          143  2011-02-13                 100.00
          5          144  2011-02-14                 100.00            5          144  2011-02-14                 100.00
          5          145  2011-02-15                 100.00            5          145  2011-02-15                 100.00
          5          146  2011-02-16                 100.00            5          146  2011-02-16                 100.00
          5          147  2011-02-17                 100.00            5          147  2011-02-17                 100.00
          5          148  2011-02-18                 100.00            5          148  2011-02-18                 100.00
          5          149  2011-02-19                 100.00            5          149  2011-02-19                 100.00
          6          150  2011-02-20                 100.00            6          150  2011-02-20                 100.00
          6          151  2011-02-21                 100.00            6          151  2011-02-21                 100.00
          6          152  2011-02-22                 100.00            6          152  2011-02-22                 100.00
          6          153  2011-02-23                 100.00            6          153  2011-02-23                 100.00
          6          154  2011-02-24                 100.00            6          154  2011-02-24                 100.00
          6          155  2011-02-25                 100.00            6          155  2011-02-25                 100.00
          6          156  2011-02-26                 100.00            6          156  2011-02-26                 100.00
          6          157  2011-02-27                 100.00            6          157  2011-02-27                 100.00
          6          158  2011-02-28                 100.00            6          158  2011-02-28                 100.00

--- 24 row(s) selected.
>>
>>cqd join_order_by_user reset;

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

--- SQL operation complete.
>>
>>
>>-- test some more division clauses
>>create table mtdn1(pc1 timestamp not null,
+>                   pc2 timestamp(6) not null,
+>                   pc3 char(10) character set ucs2 not null,
+>                   c4 char(20) default null,
+>                   primary key (pc1, pc2, pc3))
+>salt using 2 partitions on (pc3)
+>division by (date_part('yearWEEK', pc1),
+>             date_trunc('century', pc2),
+>             left(pc3, 4),
+>             substring(cast(pc3 as char(10) character set ucs2 not null) from 1 for 3),
+>             substr(pc3, 1, 2));

--- SQL operation complete.
>>
>>create table mtdn2(pc3 timestamp not null,
+>                   pc2 timestamp(6) not null,
+>                   pc1 char(10) not null,
+>                   c4 numeric(8,2) default null,
+>                   primary key (pc1, pc2, pc3))
+>salt using 4 partitions on (pc3)
+>division by (datediff(quarter, date '1999-11-01', pc2),
+>             datediff(week, date '2012-01-01', pc3),
+>             datediff(year, timestamp '2011-03-01 18:24:36', pc3));

--- SQL operation complete.
>>
>>create table mtdn3(pc1 timestamp not null,
+>                   pc2 timestamp(6) not null,
+>                   pc3 char(10) character set ucs2 not null,
+>                   pc4 numeric(14,5) not null,
+>                   primary key (pc1, pc2, pc3, pc4))
+>salt using 3 partitions on (pc3)
+>division by (date_part('yearQuarter', pc1),
+>             left(pc3, 4),
+>             datediff(week, date '1900-01-01', pc2),
+>             substr(pc3, 1, 2),
+>             cast(((pc4 + 32) / 18.0001) as integer not null));

--- SQL operation complete.
>>
>>set param ?pat '%MTDN%';
>>execute smdquery;

TABLE_NAME       TYP  COLNUM  COLUMN_NAME           ORD          COLSIZ  COLCLASS  DEFCLASS     CKEY_COL  COMP_EXPRESSION
---------------  ---  ------  --------------------  -----------  ------  --------  -----------  --------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MTDN1            BT        0  PC1                             0      11  U                   1  7         none                                                                                                                                                                                                                                            
MTDN1            BT        1  PC2                             0      11  U                   1  8         none                                                                                                                                                                                                                                            
MTDN1            BT        2  PC3                             0      20  U                   1  9         none                                                                                                                                                                                                                                            
MTDN1            BT        3  C4                              ?      20  U                   2  none      none                                                                                                                                                                                                                                            
MTDN1            BT        4  _SALT_                          0       4  S                   7  1         HASH2PARTFUNC(CAST("PC3" AS CHAR(10) CHARACTER SET UCS2 COLL                                                                                                                                                                                    
MTDN1            BT        5  _DIVISION_1_                    0       4  S                   7  2         DATE_PART('YEARWEEK',PC1)                                                                                                                                                                                                                       
MTDN1            BT        6  _DIVISION_2_                    0      11  S                   7  3         DATE_TRUNC('CENTURY',PC2)                                                                                                                                                                                                                       
MTDN1            BT        7  _DIVISION_3_                    0       8  S                   7  4         left(PC3, 4)                                                                                                                                                                                                                                    
MTDN1            BT        8  _DIVISION_4_                    0       6  S                   7  5         substring(cast(PC3 AS CHAR(10) CHARACTER SET UCS2 NOT NULL),                                                                                                                                                                                    
MTDN1            BT        9  _DIVISION_5_                    0       4  S                   7  6         substring(PC3, 1, 2)                                                                                                                                                                                                                            
MTDN2            BT        0  PC3                             0      11  U                   1  7         none                                                                                                                                                                                                                                            
MTDN2            BT        1  PC2                             0      11  U                   1  6         none                                                                                                                                                                                                                                            
MTDN2            BT        2  PC1                             0      10  U                   1  5         none                                                                                                                                                                                                                                            
MTDN2            BT        3  C4                              ?       4  U                   2  none      none                                                                                                                                                                                                                                            
MTDN2            BT        4  _SALT_                          0       4  S                   7  1         HASH2PARTFUNC(CAST("PC3" AS TIMESTAMP(6) NOT NULL) FOR 4)                                                                                                                                                                                       
MTDN2            BT        5  _DIVISION_1_                    0       4  S                   7  2         DATEDIFF(QUARTER, DATE '1999-11-01', PC2)                                                                                                                                                                                                       
MTDN2            BT        6  _DIVISION_2_                    0       4  S                   7  3         DATEDIFF(WEEK, DATE '2012-01-01', PC3)                                                                                                                                                                                                          
MTDN2            BT        7  _DIVISION_3_                    0       4  S                   7  4         DATEDIFF(YEAR, TIMESTAMP '2011-03-01 18:24:36', PC3)                                                                                                                                                                                            
MTDN3            BT        0  PC1                             0      11  U                   1  7         none                                                                                                                                                                                                                                            
MTDN3            BT        1  PC2                             0      11  U                   1  8         none                                                                                                                                                                                                                                            
MTDN3            BT        2  PC3                             0      20  U                   1  9         none                                                                                                                                                                                                                                            
MTDN3            BT        3  PC4                             0       8  U                   1  10        none                                                                                                                                                                                                                                            
MTDN3            BT        4  _SALT_                          0       4  S                   7  1         HASH2PARTFUNC(CAST("PC3" AS CHAR(10) CHARACTER SET UCS2 COLL                                                                                                                                                                                    
MTDN3            BT        5  _DIVISION_1_                    0       4  S                   7  2         DATE_PART('YEARQUARTER',PC1)                                                                                                                                                                                                                    
MTDN3            BT        6  _DIVISION_2_                    0       8  S                   7  3         left(PC3, 4)                                                                                                                                                                                                                                    
MTDN3            BT        7  _DIVISION_3_                    0       4  S                   7  4         DATEDIFF(WEEK, DATE '1900-01-01', PC2)                                                                                                                                                                                                          
MTDN3            BT        8  _DIVISION_4_                    0       4  S                   7  5         substring(PC3, 1, 2)                                                                                                                                                                                                                            
MTDN3            BT        9  _DIVISION_5_                    0       4  S                   7  6         cast(((PC4 + 32) / 18.0001) AS INTEGER SIGNED NOT NULL)                                                                                                                                                                                         

--- 28 row(s) selected.
>>set param ?pat '%MTD%';
>>
>>insert into mtdn1 values (timestamp '2009-12-06 12:13:14',
+>                          timestamp '2099-01-06 12:13:14.555555',
+>                          'abcdefg',
+>                          'whatever'),
+>                         (timestamp '2009-01-01 12:13:14',
+>                          timestamp '1999-01-06 12:13:14.555555',
+>                          '   xyz***',
+>                          'whatever');

--- 2 row(s) inserted.
>>
>>insert into mtdn2 select pc1, pc2, pc3, month(pc1)+day(pc1)/100 from mtdn1;

--- 2 row(s) inserted.
>>insert into mtdn3 select * from mtdn2;

--- 2 row(s) inserted.
>>
>>select "_DIVISION_1_", pc1, "_DIVISION_2_", pc2, "_DIVISION_3_", "_DIVISION_4_", "_DIVISION_5_", pc3, c4
+>from mtdn1;

_DIVISION_1_  PC1                         _DIVISION_2_                PC2                         _DIVISION_3_  _DIVISION_4_  _DIVISION_5_  PC3                   C4
------------  --------------------------  --------------------------  --------------------------  ------------  ------------  ------------  --------------------  --------------------

      200950  2009-12-06 12:13:14.000000  2000-01-01 00:00:00.000000  2099-01-06 12:13:14.555555  abcd          abc           ab            abcdefg               whatever            
      200901  2009-01-01 12:13:14.000000  1900-01-01 00:00:00.000000  1999-01-06 12:13:14.555555     x                                         xyz***             whatever            

--- 2 row(s) selected.
>>select "_DIVISION_1_", pc2, "_DIVISION_2_", "_DIVISION_3_", pc3, pc1, c4
+>from mtdn2;

_DIVISION_1_  PC2                         _DIVISION_2_  _DIVISION_3_  PC3                         PC1         C4
------------  --------------------------  ------------  ------------  --------------------------  ----------  ------------

          -3  1999-01-06 12:13:14.555555          -157            -2  2009-01-01 12:13:14.000000     xyz***           1.01
         397  2099-01-06 12:13:14.555555          -108            -2  2009-12-06 12:13:14.000000  abcdefg            12.06

--- 2 row(s) selected.
>>select "_SALT_", "_DIVISION_1_", pc1, "_DIVISION_2_", "_DIVISION_4_", pc3, "_DIVISION_3_", pc2, "_DIVISION_5_", pc4
+>from mtdn3;

_SALT_      _DIVISION_1_  PC1                         _DIVISION_2_  _DIVISION_4_  PC3                   _DIVISION_3_  PC2                         _DIVISION_5_  PC4
----------  ------------  --------------------------  ------------  ------------  --------------------  ------------  --------------------------  ------------  ---------------------

         1         20094  2009-12-06 12:13:14.000000  abcd          ab            abcdefg                      10384  2099-01-06 12:13:14.555555             2               12.06000
         2         20091  2009-01-01 12:13:14.000000     x                           xyz***                     5166  1999-01-06 12:13:14.555555             1                1.01000

--- 2 row(s) selected.
>>
>>prepare s from
+>select * from mtdn1 where pc1 < cast(date '2011-08-15' as timestamp);

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTDN1                                                         begin_key: (_SALT_ = <min>), (_DIVISION_1_ = <min>), (_DIVISION_2_ = <min>), (_DIVISION_3_ = '洼湩'), (_DIVISION_4_ = '洼湩'), (_DIVISION_5_ = '洼湩'), (PC1 = <min>), (PC2 = <min>), (PC3 = '洼湩')                                                                                                                                                                                                                                                                                                                                                                                                              end_key: (_SALT_ = <max>), (_DIVISION_1_ =      201134), (_DIVISION_2_ = <max>), (_DIVISION_3_ = '洼硡'), (_DIVISION_4_ = '洼硡'), (_DIVISION_5_ = '洼硡'), (PC1 = 2011-08-15 00:00:00.000000), (PC2 = <max>), (PC3 = '洼硡')                                                                                                                                                                                                                                                                                                                                                                                   

--- 1 row(s) selected.
>>
>>prepare s from
+>select * from mtdn3
+>where pc1 = timestamp '2009-12-06 12:13:14'
+>  and pc3 > 'ab'
+>  and pc3 < 'abcf'
+>  and pc2 = timestamp '2099-01-06 12:13:14.555555';

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTDN3                                                         begin_key: (_SALT_ = <min>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'ab  '), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(001)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <min>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'ab'), (PC4 = <min>)                                                                   end_key: (_SALT_ = <max>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'abcf'), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(001)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <max>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'abcf'), (PC4 = <max>)                                                                 

--- 1 row(s) selected.
>>-- ("_DIVISION_1_", "_DIVISION_2_", "_DIVISION_3_", "_DIVISION_4_") has (=, between, = between, none) predicates
>>
>>prepare s from
+>select * from mtdn3
+>where pc1 = timestamp '2009-12-06 12:13:14'
+>  and pc3 > 'ab'
+>  and pc3 < 'abcf'
+>  and pc2 = timestamp '2099-01-06 12:13:14.555555';

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

OPERATOR                        TNAME                                                         BKEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      EKEY
------------------------------  ------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRAFODION_SCAN                  MTDN3                                                         begin_key: (_SALT_ = <min>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'ab  '), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(001)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <min>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'ab'), (PC4 = <min>)                                                                   end_key: (_SALT_ = <max>), (_DIVISION_1_ = DATE_PART('YEARQUARTER',%(2009-12-06 12:13:14))), (_DIVISION_2_ = 'abcf'), (_DIVISION_3_ = cast((cast(((cast((cast(((cast(%(2099-01-06 12:13:14.555555)) - cast((cast(dayofweek(%(2099-01-06 12:13:14.555555))) - cast(1)))) - (cast(1900-01-01) - cast((cast(dayofweek(1900-01-01)) - cast(1)))))) * cast(10))) / cast(7)) / cast(10))) / cast(001)))), (_DIVISION_4_ = 'ab'), (_DIVISION_5_ = <max>), (PC1 = %(2009-12-06 12:13:14)), (PC2 = %(2099-01-06 12:13:14.555555)), (PC3 = 'abcf'), (PC4 = <max>)                                                                 

--- 1 row(s) selected.
>>
>>drop table mtdn1;

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

--- SQL operation complete.
>>
>>-- negative tests:
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id))
+>division by (date_part('YEARMONTH', sale_date));

*** ERROR[4240] Expression SALE_DATE in the DIVISION BY clause references columns other than clustering key columns.

--- SQL operation failed with errors.
>>-- 4240, non-key column
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (date_part('YEARMONTH', nonexistingcolumn));

*** ERROR[4240] Expression NONEXISTINGCOLUMN in the DIVISION BY clause references columns other than clustering key columns.

--- SQL operation failed with errors.
>>-- 4001, column not found
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (cast(date_part('YEARMONTH', sale_date) as integer));

*** ERROR[4243] The DIVISION BY clause only supports very limited types of expressions. Expression cast(DATE_PART('YEARMONTH',SALE_DATE) AS INTEGER SIGNED) is not supported.

--- SQL operation failed with errors.
>>-- 4243, expression (with extra cast) not supported
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (cos(store_id));

*** ERROR[4243] The DIVISION BY clause only supports very limited types of expressions. Expression cos(STORE_ID) is not supported.

*** ERROR[4257] DIVISION BY with an approximate numeric data type is not supported.

--- SQL operation failed with errors.
>>-- 4243, expression not supported
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (store_id);

*** ERROR[4243] The DIVISION BY clause only supports very limited types of expressions. Expression STORE_ID is not supported.

--- SQL operation failed with errors.
>>-- 4243, division by column is not - yet - supported
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (store_id / item_id);

*** ERROR[4241] The value ITEM_ID is not supported at this place in the DIVISION BY clause, only constants are allowed.

--- SQL operation failed with errors.
>>-- 4241, expect constant instead of item_id
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by ((store_id + 5)/ item_id);

*** ERROR[4241] The value ITEM_ID is not supported at this place in the DIVISION BY clause, only constants are allowed.

--- SQL operation failed with errors.
>>-- 4241, expect constant instead of item_id
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (cast((item_id + store_id) / 6 as integer));

*** ERROR[4241] The value STORE_ID is not supported at this place in the DIVISION BY clause, only constants are allowed.

--- SQL operation failed with errors.
>>-- 4241, expect constant instead of item_id
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (cast((item_id + 55) / 6 as char(10) character set utf8));

*** ERROR[4243] The DIVISION BY clause only supports very limited types of expressions. Expression cast(((ITEM_ID + 55) / 6) AS CHAR(10 CHARS) CHARACTER SET UTF8) is not supported.

--- SQL operation failed with errors.
>>-- 4243, cast must be to a numeric type
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (2 / 3);

*** ERROR[4242] The value 2 is not supported at this place in the DIVISION BY clause, only key columns are allowed.

--- SQL operation failed with errors.
>>-- 4242, expect column instead of 2
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (date_part('MONTH', sale_date));

*** ERROR[4244] When using the DATE_PART function in DIVISION BY, only leading parts of the date, including the year, can be extracted.

--- SQL operation failed with errors.
>>-- 4244, this type of date_part function not supported
>>
>>create table mtderr1(store_id integer not null,
+>                     item_id  integer not null,
+>                     sale_date date default date '2000-01-01' not null,
+>                     sale_amt numeric(10,2),
+>                     primary key (store_id, item_id, sale_date))
+>division by (date_part('YEARQUARTER', date '2011-08-02'));

*** ERROR[4242] The value  DATE '2011-08-02' is not supported at this place in the DIVISION BY clause, only key columns are allowed.

--- SQL operation failed with errors.
>>-- 4242, expect key column
>>
>>
>>prepare s from
+>insert into mtd1(store_id, item_id, sale_date, sale_amt, "_DIVISION_1_")
+>  select *, DATE_PART('YEARMONTH', SALE_DATE) from mtd0;

*** ERROR[4013] Column _DIVISION_1_ is a system column and cannot be updated or inserted into.

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

>>-- error 4013, column "_DIVISION_1_" is a system column and cannot be updated
>>
>>prepare s from
+>insert into mtd1 select *, DATE_PART('YEARMONTH', SALE_DATE) from mtd0;

*** ERROR[4023] The degree of each row value constructor (5) must equal the degree of the target table column list (4).

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

>>-- error 4023, degree of row value constructor doesn't match
>>
>>prepare s from
+>update mtd1 set "_DIVISION_1_" = DATE_PART('YEARMONTH', SALE_DATE) ;

*** ERROR[4013] Column _DIVISION_1_ is a system column and cannot be updated or inserted into.

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

>>-- error 4013 again
>>
>>prepare s from
+>update mtd1 set "_DIVISION_1_" = 999999;

*** ERROR[4013] Column _DIVISION_1_ is a system column and cannot be updated or inserted into.

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

>>-- error 4013, column "_DIVISION_1_" is a system column and cannot be updated
>>
>>
>>--- same with mtd2
>>prepare s from
+>insert into mtd2(store_id, item_id, sale_date, sale_amt, "_DIVISION_1_")
+>  select *, DATE_PART('YEARMONTH', SALE_DATE) from mtd0;

*** ERROR[4013] Column _DIVISION_1_ is a system column and cannot be updated or inserted into.

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

>>-- error 4013, column "_DIVISION_1_" is a system column and cannot be updated
>>
>>prepare s from
+>insert into mtd2 select *, DATE_PART('YEARMONTH', SALE_DATE) from mtd0;

*** ERROR[4023] The degree of each row value constructor (5) must equal the degree of the target table column list (4).

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

>>-- error 4023, degree of row value constructor doesn't match
>>
>>prepare s from
+>update mtd2 set "_DIVISION_1_" = DATE_PART('YEARMONTH', SALE_DATE) ;

*** ERROR[4013] Column _DIVISION_1_ is a system column and cannot be updated or inserted into.

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

>>-- error 4013 again
>>
>>prepare s from
+>update mtd2 set "_DIVISION_1_" = 999999;

*** ERROR[4013] Column _DIVISION_1_ is a system column and cannot be updated or inserted into.

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

>>-- error 4013, column "_DIVISION_1_" is a system column and cannot be updated
>>
>>
>>------------- Testing indexes --------------------------
>>
>>delete from mtd0;

--- 100 row(s) deleted.
>>delete from mtd1;

--- 100 row(s) deleted.
>>delete from mtd2;

--- 0 row(s) deleted.
>>
>>insert into mtd0 values (1, 101, date '2011-06-30', 100.00),
+>                        (2, 102, date '2011-08-02', 200.00);

--- 2 row(s) inserted.
>>insert into mtd1 select * from mtd0;

--- 2 row(s) inserted.
>>insert into mtd2 select * from mtd1;

--- 2 row(s) inserted.
>>
>>create view mtd0v as (select sale_date, item_id, store_id, sale_amt from mtd0);

--- SQL operation complete.
>>create view mtd1v as (select sale_date, item_id, store_id, sale_amt from mtd1);

--- SQL operation complete.
>>create view mtd2v as (select sale_date, store_id, sale_amt from mtd2);

--- SQL operation complete.
>>
>>create index mtd1x1 on mtd1(item_id);

--- SQL operation complete.
>>create index mtd1x2 on mtd1(item_id, sale_amt) division like table;

--- SQL operation complete.
>>create index mtd2x1 on mtd2(item_id);

--- SQL operation complete.
>>create index mtd2x2 on mtd2(item_id, sale_amt) division like table;

--- SQL operation complete.
>>
>>create unique index mtd1ux3 on mtd1(item_id, store_id);

--- SQL operation complete.
>>create unique index mtd2ux3 on mtd2(sale_amt);

--- SQL operation complete.
>>create unique index mtd1ux4 on mtd1(item_id, sale_date) division like table;

*** ERROR[1402] Unique index TRAFODION.MTD.MTD1UX4 could not be created with the DIVISION LIKE TABLE option. Only non-unique indexes are supported with this option.

--- SQL operation failed with errors.
>>-- currently not supported although we could allow this one
>>
>>create unique index mtd1ux8 on mtd1(item_id, store_id) division like table;

*** ERROR[1402] Unique index TRAFODION.MTD.MTD1UX8 could not be created with the DIVISION LIKE TABLE option. Only non-unique indexes are supported with this option.

--- SQL operation failed with errors.
>>-- 1402, can't division a unique index unless it contains the underlying cols of division by
>>create unique index mtd1ux9 on mtd1(sale_amt) division like table;

*** ERROR[1402] Unique index TRAFODION.MTD.MTD1UX9 could not be created with the DIVISION LIKE TABLE option. Only non-unique indexes are supported with this option.

--- SQL operation failed with errors.
>>-- 1402, can't division a unique index unless it contains the underlying cols of division by
>>
>>execute smdquery;

TABLE_NAME       TYP  COLNUM  COLUMN_NAME           ORD          COLSIZ  COLCLASS  DEFCLASS     CKEY_COL  COMP_EXPRESSION
---------------  ---  ------  --------------------  -----------  ------  --------  -----------  --------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MTD0             BT        0  STORE_ID                        0       4  U                   1  1         none                                                                                                                                                                                                                                            
MTD0             BT        1  ITEM_ID                         0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD0             BT        2  SALE_DATE                       0       4  U                   3  3         none                                                                                                                                                                                                                                            
MTD0             BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD00            BT        0  ID                              0       2  U                   5  2         none                                                                                                                                                                                                                                            
MTD00            BT        1  SNAME                           ?      50  U                   1  none      none                                                                                                                                                                                                                                            
MTD00            BT        2  RNAME                           ?      50  U                   1  none      none                                                                                                                                                                                                                                            
MTD00            BT        3  BDAY                            0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD00            BT        4  DTH                             ?       4  U                   2  none      none                                                                                                                                                                                                                                            
MTD00            BT        5  NUM_MV                          ?       2  U                   2  none      none                                                                                                                                                                                                                                            
MTD00            BT        6  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',BDAY)                                                                                                                                                                                                                     
MTD0V            VI        0  SALE_DATE                       ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD0V            VI        1  ITEM_ID                         ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD0V            VI        2  STORE_ID                        ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD0V            VI        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD1             BT        0  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD1             BT        1  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD1             BT        2  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTD1             BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD1             BT        4  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                
MTD1V            VI        0  SALE_DATE                       ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD1V            VI        1  ITEM_ID                         ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD1V            VI        2  STORE_ID                        ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD1V            VI        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD2             BT        0  SYSKEY                          0       8  S                   1  5         none                                                                                                                                                                                                                                            
MTD2             BT        1  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD2             BT        2  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD2             BT        3  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTD2             BT        4  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD2             BT        5  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                
MTD2V            VI        0  SALE_DATE                       ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD2V            VI        1  STORE_ID                        ?       4  U                   1  none      none                                                                                                                                                                                                                                            
MTD2V            VI        2  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD3             BT        0  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD3             BT        1  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD3             BT        2  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTD3             BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD3             BT        4  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                
MTD4             BT        0  SYSKEY                          0       8  S                   1  5         none                                                                                                                                                                                                                                            
MTD4             BT        1  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTD4             BT        2  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTD4             BT        3  SALE_DATE                       0       4  U                   1  4         none                                                                                                                                                                                                                                            
MTD4             BT        4  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTD4             BT        5  _DIVISION_1_                    1       4  S                   7  1         DATE_PART('YEARMONTHD',SALE_DATE)                                                                                                                                                                                                               
MTDN3            BT        0  PC1                             0      11  U                   1  7         none                                                                                                                                                                                                                                            
MTDN3            BT        1  PC2                             0      11  U                   1  8         none                                                                                                                                                                                                                                            
MTDN3            BT        2  PC3                             0      20  U                   1  9         none                                                                                                                                                                                                                                            
MTDN3            BT        3  PC4                             0       8  U                   1  10        none                                                                                                                                                                                                                                            
MTDN3            BT        4  _SALT_                          0       4  S                   7  1         HASH2PARTFUNC(CAST("PC3" AS CHAR(10) CHARACTER SET UCS2 COLL                                                                                                                                                                                    
MTDN3            BT        5  _DIVISION_1_                    0       4  S                   7  2         DATE_PART('YEARQUARTER',PC1)                                                                                                                                                                                                                    
MTDN3            BT        6  _DIVISION_2_                    0       8  S                   7  3         left(PC3, 4)                                                                                                                                                                                                                                    
MTDN3            BT        7  _DIVISION_3_                    0       4  S                   7  4         DATEDIFF(WEEK, DATE '1900-01-01', PC2)                                                                                                                                                                                                          
MTDN3            BT        8  _DIVISION_4_                    0       4  S                   7  5         substring(PC3, 1, 2)                                                                                                                                                                                                                            
MTDN3            BT        9  _DIVISION_5_                    0       4  S                   7  6         cast(((PC4 + 32) / 18.0001) AS INTEGER SIGNED NOT NULL)                                                                                                                                                                                         
MTDRITARGET1     BT        0  D_DATE                          0       4  U                   3  1         none                                                                                                                                                                                                                                            
MTDRITARGET1     BT        1  QUARTER_ID                      ?       6  U                   2  none      none                                                                                                                                                                                                                                            
MTDRITARGET2D    BT        0  D_DATE                          0       4  U                   3  2         none                                                                                                                                                                                                                                            
MTDRITARGET2D    BT        1  QUARTER_ID                      ?       6  U                   2  none      none                                                                                                                                                                                                                                            
MTDRITARGET2D    BT        2  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',D_DATE)                                                                                                                                                                                                                   
MTDTRIGTARGET1   BT        0  STORE_ID                        0       4  U                   1  1         none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        1  ITEM_ID                         0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        2  SALE_DATE                       0       4  U                   3  3         none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET1   BT        4  SALE_AMT_BEFORE                 ?       8  A                   3  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        0  STORE_ID                        0       4  U                   1  2         none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        1  ITEM_ID                         0       4  U                   1  3         none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        2  SALE_DATE                       0       4  U                   3  4         none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        3  SALE_AMT                        ?       8  U                   2  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        4  SALE_AMT_BEFORE                 ?       8  A                   3  none      none                                                                                                                                                                                                                                            
MTDTRIGTARGET2D  BT        5  _DIVISION_1_                    0       4  S                   7  1         DATE_PART('YEARMONTH',SALE_DATE)                                                                                                                                                                                                                

--- 70 row(s) selected.
>>prepare smdviewquery from
+>select cast(objects.object_name as char(10)) as table_name,
+>       vws.is_updatable, vws.is_insertable
+>from "_MD_".OBJECTS objects
+>     left outer join "_MD_".VIEWS vws
+>       on objects.object_uid = vws.view_uid
+>where objects.schema_name = 'MTD' and
+>      objects.object_name in ('MTD0V', 'MTD1V', 'MTD2V') and
+>      objects.object_type = 'VI' 
+>order by 1, 3
+>;

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

TABLE_NAME                                IS_UPDATABLE  IS_INSERTABLE
----------------------------------------  ------------  -------------

MTD0V                                                1              1
MTD1V                                                1              1
MTD2V                                                1              0

--- 3 row(s) selected.
>>-- view MTD2V is not insertable, all others have "Y" for updatable/insertable
>>
>>prepare smdixquery from
+>select cast(tables.object_name as char(10 bytes) character set utf8) as table_name,
+>       cast(ix_obj.object_name as char(10 bytes) character set utf8) as index_name,
+>       ap.keyseq_number as ix_colnum,
+>       cast(cols.column_number as smallint) colnum,
+>       cast(ap.nonkeycol as smallint) nonkeyc,
+>       cols.column_class syscol,
+>       cast(cols.column_name as char(16 bytes) character set utf8) as column_name
+>from "_MD_".OBJECTS tables
+>     join "_MD_".INDEXES indexes
+>       on tables.object_uid = indexes.base_table_uid
+>     join "_MD_".KEYS ap
+>       on indexes.index_uid = ap.object_uid
+>     join "_MD_".COLUMNS cols
+>       on tables.object_uid = cols.object_uid and
+>          ap.column_number = cols.column_number
+>     join "_MD_".objects ix_obj
+>       on indexes.index_uid = ix_obj.object_uid
+>where tables.schema_name = 'MTD' and
+>      tables.object_name in ('MTD0', 'MTD1', 'MTD2') and
+>      tables.object_type = 'BT'
+>order by 1, 2, 3
+>;

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

TABLE_NAME  INDEX_NAME  IX_COLNUM    COLNUM  NONKEYC  SYSCOL  COLUMN_NAME
----------  ----------  -----------  ------  -------  ------  ----------------

MTD1        MTD1UX3               1       1        0  U       ITEM_ID         
MTD1        MTD1UX3               2       0        0  U       STORE_ID        
MTD1        MTD1UX3               3       4        1  S       _DIVISION_1_    
MTD1        MTD1UX3               4       2        1  U       SALE_DATE       
MTD1        MTD1X1                1       1        0  U       ITEM_ID         
MTD1        MTD1X1                2       4        0  S       _DIVISION_1_    
MTD1        MTD1X1                3       0        0  U       STORE_ID        
MTD1        MTD1X1                4       2        0  U       SALE_DATE       
MTD1        MTD1X2                1       4        0  S       _DIVISION_1_    
MTD1        MTD1X2                2       1        0  U       ITEM_ID         
MTD1        MTD1X2                3       3        0  U       SALE_AMT        
MTD1        MTD1X2                4       0        0  U       STORE_ID        
MTD1        MTD1X2                5       2        0  U       SALE_DATE       
MTD2        MTD2UX3               1       4        0  U       SALE_AMT        
MTD2        MTD2UX3               2       5        1  S       _DIVISION_1_    
MTD2        MTD2UX3               3       1        1  U       STORE_ID        
MTD2        MTD2UX3               4       2        1  U       ITEM_ID         
MTD2        MTD2UX3               5       3        1  U       SALE_DATE       
MTD2        MTD2UX3               6       0        1  S       SYSKEY          
MTD2        MTD2X1                1       2        0  U       ITEM_ID         
MTD2        MTD2X1                2       5        0  S       _DIVISION_1_    
MTD2        MTD2X1                3       1        0  U       STORE_ID        
MTD2        MTD2X1                4       3        0  U       SALE_DATE       
MTD2        MTD2X1                5       0        0  S       SYSKEY          
MTD2        MTD2X2                1       5        0  S       _DIVISION_1_    
MTD2        MTD2X2                2       2        0  U       ITEM_ID         
MTD2        MTD2X2                3       4        0  U       SALE_AMT        
MTD2        MTD2X2                4       1        0  U       STORE_ID        
MTD2        MTD2X2                5       3        0  U       SALE_DATE       
MTD2        MTD2X2                6       0        0  S       SYSKEY          

--- 30 row(s) selected.
>>
>>update statistics for table mtd1 on every column;

--- SQL operation complete.
>>
>>select store_id, item_id, sale_date, sale_amt
+>from mtd1
+>where item_id = 101 and sale_amt = 100.0 and sale_date between date '2011-06-01' and date '2011-06-30';

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT             
-----------  -----------  ----------  ---------------------

          1          101  2011-06-30                 100.00

--- 1 row(s) selected.
>>
>>select store_id, item_id, sale_date, sale_amt
+>from mtd1
+>where item_id = 102 and sale_amt = 200.0 and sale_date between date '2011-07-01' and date '2011-08-31';

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT             
-----------  -----------  ----------  ---------------------

          2          102  2011-08-02                 200.00

--- 1 row(s) selected.
>>
>>set parserflags 1;

--- SQL operation complete.
>>-- validate contents of index against base table after create index
>>select count(*) from mtd1;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd1x1) natural join mtd1;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd1x2) natural join mtd1;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd1ux3) natural join mtd1;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd1ux4) natural join mtd1;

*** ERROR[4082] Object TRAFODION.MTD.MTD1UX4 does not exist or is inaccessible.

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

>>-- currently not supported
>>
>>select count(*) from mtd2;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd2x1) natural join mtd2;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd2x2) natural join mtd2;

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

                   2

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd2ux3) natural join mtd2;

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

                   2

--- 1 row(s) selected.
>>
>>drop index mtd1ux3;

--- SQL operation complete.
>>drop index mtd2ux3;

--- SQL operation complete.
>>drop index mtd1ux4;

*** ERROR[1389] Object TRAFODION.MTD.MTD1UX4 does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>set parserflags 0;

--- SQL operation complete.
>>
>>delete from mtd1;

--- 2 row(s) deleted.
>>delete from mtd2;

--- 2 row(s) deleted.
>>
>>select count(*) from table(index_table mtd1x1);

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

                   0

--- 1 row(s) selected.
>>select count(*) from table(index_table mtd1x2);

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

                   0

--- 1 row(s) selected.
>>select count(*) from table(index_table mtd2x1);

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

                   0

--- 1 row(s) selected.
>>select count(*) from table(index_table mtd2x2);

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

                   0

--- 1 row(s) selected.
>>
>>insert into mtd1 select * from mtd0;

--- 2 row(s) inserted.
>>insert into mtd1(item_id, sale_amt, store_id) values(110,default,10);

--- 1 row(s) inserted.
>>insert into mtd2 select * from mtd1;

--- 3 row(s) inserted.
>>insert into mtd2(item_id, store_id, sale_amt) values(111,11, default);

--- 1 row(s) inserted.
>>insert into mtd2(item_id, store_id, sale_date) values(112,12, default);

--- 1 row(s) inserted.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-06-30                 100.00        201106
          2          102  2011-08-02                 200.00        201108
         10          110  2000-01-01                      ?        200001

--- 3 row(s) selected.
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-06-30                 100.00        201106
          2          102  2011-08-02                 200.00        201108
         10          110  2000-01-01                      ?        200001
         11          111  2000-01-01                      ?        200001
         12          112  2000-01-01                      ?        200001

--- 5 row(s) selected.
>>
>>insert into mtd1v select sale_date, item_id+1000, store_id, sale_amt from mtd2;

--- 5 row(s) inserted.
>>insert into mtd1v(store_id, item_id, sale_date) values (41, 141, default);

--- 1 row(s) inserted.
>>insert into mtd1v(store_id, sale_amt, item_id, sale_date) values (42, default, 142, default);

--- 1 row(s) inserted.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-06-30                 100.00        201106
          1         1101  2011-06-30                 100.00        201106
          2          102  2011-08-02                 200.00        201108
          2         1102  2011-08-02                 200.00        201108
         10          110  2000-01-01                      ?        200001
         10         1110  2000-01-01                      ?        200001
         11         1111  2000-01-01                      ?        200001
         12         1112  2000-01-01                      ?        200001
         41          141  2000-01-01                      ?        200001
         42          142  2000-01-01                      ?        200001

--- 10 row(s) selected.
>>
>>update mtd1v set store_id = store_id + 1 where item_id = 1112;

--- 1 row(s) updated.
>>--explain
>>update mtd1v set sale_date = sale_date + interval '1' month where store_id <= 11;

--- 7 row(s) updated.
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-07-30                 100.00        201107
          1         1101  2011-07-30                 100.00        201107
          2          102  2011-09-02                 200.00        201109
          2         1102  2011-09-02                 200.00        201109
         10          110  2000-02-01                      ?        200002
         10         1110  2000-02-01                      ?        200002
         11         1111  2000-02-01                      ?        200002
         13         1112  2000-01-01                      ?        200001
         41          141  2000-01-01                      ?        200001
         42          142  2000-01-01                      ?        200001

--- 10 row(s) selected.
>>
>>alter table mtd1 add column ac1 integer default 33;

--- SQL operation complete.
>>insert into mtd1 values (113,13, default,100.00, default);

--- 1 row(s) inserted.
>>insert into mtd1 values (114,14, date '2011-07-04', 100.00, 44);

--- 1 row(s) inserted.
>>insert into mtd1v values (date '2011-07-05', 15, 115, 111.00);

--- 1 row(s) inserted.
>>alter table mtd1 add column ac2 float default null;

--- SQL operation complete.
>>insert into mtd1 values (116,16, date '2011-07-04', 100.00, default, 66);

--- 1 row(s) inserted.
>>insert into mtd1v values (date '2011-07-05', 17, 117, 111.00);

--- 1 row(s) inserted.
>>insert into mtd1v (store_id, sale_amt, item_id) values (17, 100.00, 117);

--- 1 row(s) inserted.
>>
>>-- negative test cases
>>insert into mtd1v(store_id, sale_date) values (40, date '2011-07-08');

*** ERROR[4024] Column ITEM_ID has no default value, so it must be explicitly specified in the insert column list.

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

>>-- 4024, item_id is missing
>>
>>select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               AC1          AC2                        _DIVISION_1_
-----------  -----------  ----------  ---------------------  -----------  -------------------------  ------------

          1          101  2011-07-30                 100.00           33                          ?        201107
          1         1101  2011-07-30                 100.00           33                          ?        201107
          2          102  2011-09-02                 200.00           33                          ?        201109
          2         1102  2011-09-02                 200.00           33                          ?        201109
         10          110  2000-02-01                      ?           33                          ?        200002
         10         1110  2000-02-01                      ?           33                          ?        200002
         11         1111  2000-02-01                      ?           33                          ?        200002
         13         1112  2000-01-01                      ?           33                          ?        200001
         17          117  2000-01-01                 100.00           33                          ?        200001
         41          141  2000-01-01                      ?           33                          ?        200001
         42          142  2000-01-01                      ?           33                          ?        200001
        113           13  2000-01-01                 100.00           33                          ?        200001
        114           14  2011-07-04                 100.00           44                          ?        201107
        115           15  2011-07-05                 111.00           33                          ?        201107
        116           16  2011-07-04                 100.00           33   6.60000000000000000E+001        201107
        117           17  2011-07-05                 111.00           33                          ?        201107

--- 16 row(s) selected.
>>select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;

STORE_ID     ITEM_ID      SALE_DATE   SALE_AMT               _DIVISION_1_
-----------  -----------  ----------  ---------------------  ------------

          1          101  2011-06-30                 100.00        201106
          2          102  2011-08-02                 200.00        201108
         10          110  2000-01-01                      ?        200001
         11          111  2000-01-01                      ?        200001
         12          112  2000-01-01                      ?        200001

--- 5 row(s) selected.
>>
>>set parserflags 1;

--- SQL operation complete.
>>select * from table(index_table mtd1x1) order by 1, 2, 3, 4;

ITEM_ID@     _DIVISION_1_  STORE_ID     SALE_DATE 
-----------  ------------  -----------  ----------

         13        200001          113  2000-01-01
         14        201107          114  2011-07-04
         15        201107          115  2011-07-05
         16        201107          116  2011-07-04
         17        201107          117  2011-07-05
        101        201107            1  2011-07-30
        102        201109            2  2011-09-02
        110        200002           10  2000-02-01
        117        200001           17  2000-01-01
        141        200001           41  2000-01-01
        142        200001           42  2000-01-01
       1101        201107            1  2011-07-30
       1102        201109            2  2011-09-02
       1110        200002           10  2000-02-01
       1111        200002           11  2000-02-01
       1112        200001           13  2000-01-01

--- 16 row(s) selected.
>>select * from table(index_table mtd1x2) order by 1, 2, 3, 4, 5;

_DIVISION_1_@  ITEM_ID@     SALE_AMT@              STORE_ID     SALE_DATE
-------------  -----------  ---------------------  -----------  ----------

       200001           13                 100.00          113  2000-01-01
       200001          117                 100.00           17  2000-01-01
       200001          141                      ?           41  2000-01-01
       200001          142                      ?           42  2000-01-01
       200001         1112                      ?           13  2000-01-01
       200002          110                      ?           10  2000-02-01
       200002         1110                      ?           10  2000-02-01
       200002         1111                      ?           11  2000-02-01
       201107           14                 100.00          114  2011-07-04
       201107           15                 111.00          115  2011-07-05
       201107           16                 100.00          116  2011-07-04
       201107           17                 111.00          117  2011-07-05
       201107          101                 100.00            1  2011-07-30
       201107         1101                 100.00            1  2011-07-30
       201109          102                 200.00            2  2011-09-02
       201109         1102                 200.00            2  2011-09-02

--- 16 row(s) selected.
>>select "ITEM_ID@", "_DIVISION_1_", STORE_ID, SALE_DATE
+>from table(index_table mtd2x1) order by 1, 2, 3, 4;

ITEM_ID@     _DIVISION_1_  STORE_ID     SALE_DATE 
-----------  ------------  -----------  ----------

        101        201106            1  2011-06-30
        102        201108            2  2011-08-02
        110        200001           10  2000-01-01
        111        200001           11  2000-01-01
        112        200001           12  2000-01-01

--- 5 row(s) selected.
>>select "_DIVISION_1_@", "ITEM_ID@", "SALE_AMT@", STORE_ID, SALE_DATE
+>from table(index_table mtd2x2) order by 1, 2, 3, 4, 5;

_DIVISION_1_@  ITEM_ID@     SALE_AMT@              STORE_ID     SALE_DATE
-------------  -----------  ---------------------  -----------  ----------

       200001          110                      ?           10  2000-01-01
       200001          111                      ?           11  2000-01-01
       200001          112                      ?           12  2000-01-01
       201106          101                 100.00            1  2011-06-30
       201108          102                 200.00            2  2011-08-02

--- 5 row(s) selected.
>>
>>-- validate contents of index against base table
>>update mtd1 set sale_amt = 0.00 where sale_amt is null;

--- 6 row(s) updated.
>>select count(*) from mtd1;

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

                  16

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd1x1) natural join mtd1
+>where "ITEM_ID@" = item_id;

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

                  16

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd1x2) natural join mtd1
+>where "ITEM_ID@" = item_id
+>  and "SALE_AMT@" = sale_amt;

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

                  16

--- 1 row(s) selected.
>>
>>update mtd2 set sale_amt = 0.00 where sale_amt is null;

--- 3 row(s) updated.
>>select count(*) from mtd2;

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

                   5

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd2x1) natural join mtd2;

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

                   5

--- 1 row(s) selected.
>>
>>select count(*)
+>from table(index_table mtd2x2) natural join mtd2;

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

                   5

--- 1 row(s) selected.
>>
>>set parserflags 0;

--- SQL operation complete.
>>obey TEST071(clean_up);
>>drop schema mtd cascade;

--- SQL operation complete.
>>
>>log;