Google Git
Sign in
apache/trafodion/master/./core/sql/regress/compGeneral/EXPECTED071
blob: 8a5774dcaac34d3b842c49d081af34f5afac319a [file] [log] [blame]
>>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(((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(cast(10)))) / cast(cast(7))) / cast(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(((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(cast(10)))) / cast(cast(7))) / cast(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(((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(cast(10)))) / cast(cast(7))) / cast(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(((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(cast(10)))) / cast(cast(7))) / cast(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;