| ------------------------------------------------------------------------- |
| -- Multi-temperate data |
| -- |
| -- Simple test case to set up a table with computed col key prefix, |
| -- do simple inserts, updates, deletes, merges. |
| ------------------------------------------------------------------------- |
| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, |
| -- software distributed under the License is distributed on an |
| -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| -- KIND, either express or implied. See the License for the |
| -- specific language governing permissions and limitations |
| -- under the License. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| |
| obey TEST071(clean_up); |
| log LOG071 clear; |
| obey TEST071(setup); |
| obey TEST071(tests); |
| obey TEST071(clean_up); |
| log; |
| exit; |
| |
| ?section clean_up |
| drop schema mtd cascade; |
| |
| ?section setup |
| create schema mtd; |
| set schema mtd; |
| |
| cqd traf_aligned_row_format 'OFF'; |
| |
| -- 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)) ; |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| create table mtd3 like mtd1; |
| |
| create table mtdTRIGtarget1 like mtd3 without constraints without division; |
| alter table mtdTRIGtarget1 add column sale_amt_before numeric(10,2) default 0; |
| |
| create table mtdTRIGtarget2d like mtd3; |
| alter table mtdTRIGtarget2d add column sale_amt_before numeric(10,2) default 0; |
| |
| create table mtd4 |
| store by (store_id, item_id, sale_date) |
| division by (date_part('YEARMONTHD', sale_date) desc) |
| as select * from mtd2; |
| |
| create table mtdRItarget1(d_date date default date '2000-01-01' not null primary key, |
| quarter_id char(6)); |
| 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)); |
| |
| 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 |
| ; |
| |
| execute smdquery; |
| |
| 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; |
| |
| ?section tests |
| |
| set schema mtd; |
| |
| insert into mtd0 values (1, 100, date '2011-06-30', 100.00); |
| |
| prepare s from |
| insert into mtd1 select * from mtd0; |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| insert into mtd1 values(10,10,default,default); |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| insert into mtd1(item_id, sale_amt, store_id, sale_date) |
| values(11,default,11,default); |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| prepare s from |
| update mtd1 set sale_date = date '2011-07-01'; |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| update mtd1 set sale_date = sale_date + interval '1' month |
| where store_id < 5; |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| update mtd1 set item_id = item_id+1; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| insert into mtd0 values (1, 101, date '2011-08-01', 300.00); |
| insert into mtd0 values (2, 102, date '2011-06-30', 300.00); |
| |
| 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) |
| ; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| 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) |
| ; |
| |
| -- explain s; |
| execute skeyquery; |
| |
| execute s; |
| |
| 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 |
| ; |
| |
| -- explain s; |
| execute skeyquery; |
| |
| execute s; |
| |
| prepare s from |
| delete from mtd1 where store_id = 10; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| prepare s from |
| delete from mtd1 where sale_date between date '2011-06-01' and date '2011-06-30'; |
| -- explain s; |
| execute skeyquery; |
| execute s; |
| |
| prepare s from |
| update mtd1 set sale_amt = sale_amt - 1 where sale_date < date '2010-01-01'; |
| execute skeyquery; |
| |
| begin work; |
| execute s; |
| rollback work; |
| |
| prepare s from |
| delete from mtd1; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| --------- same as above, but with table mtd2 that has a syskey ------- |
| |
| delete from mtd0 where item_id > 100; |
| |
| prepare s from |
| insert into mtd2 select * from mtd0; |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| insert into mtd2 values(10,10,default,default); |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| insert into mtd2(item_id, sale_amt, store_id, sale_date) |
| values(11,default,11,default); |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| prepare s from |
| update mtd2 set sale_date = date '2011-07-01'; |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| update mtd2 set sale_date = sale_date + interval '1' month |
| where store_id < 5; |
| -- explain s; |
| execute s; |
| |
| prepare s from |
| update mtd2 set item_id = item_id+1; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| insert into mtd0 values (1, 101, date '2011-08-01', 300.00); |
| insert into mtd0 values (2, 102, date '2011-06-30', 300.00); |
| |
| 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) |
| ; |
| -- 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)); |
| execute skeyquery; |
| |
| 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)); |
| |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| 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 |
| ; |
| -- currently not supported |
| -- explain s; |
| -- execute skeyquery; |
| -- execute s; |
| |
| prepare s from |
| delete from mtd2 where store_id = 10; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| prepare s from |
| delete from mtd2 where sale_date between date '2011-06-01' and date '2011-06-30'; |
| -- explain s; |
| execute skeyquery; |
| execute s; |
| |
| prepare s from |
| delete from mtd2; |
| -- explain s; |
| execute s; |
| |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| -- 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; |
| -- 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; |
| -- 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; |
| -- 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; |
| |
| delete from mtd0; |
| delete from mtd1; |
| delete from mtd4; |
| |
| 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; |
| |
| |
| insert into mtd1 select * from mtd0; |
| insert into mtd4 select * from mtd0; |
| |
| cqd join_order_by_user 'on'; |
| cqd gen_hshj_min_max_opt 'on'; |
| |
| 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; |
| -- explain s; |
| execute min_max_opt_query; |
| -- should do min/max opt on "_DIVISION_1_" col |
| -- NOTE: This is not working at this time, to be fixed later |
| execute s; |
| |
| 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'; |
| -- explain s; |
| execute min_max_opt_query; |
| -- should do min/max opt on "_DIVISION_1_" col |
| -- NOTE: This is not working at this time, to be fixed later |
| execute s; |
| |
| cqd join_order_by_user reset; |
| cqd gen_hshj_min_max_opt reset; |
| |
| |
| -- 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| set param ?pat '%MTDN%'; |
| execute smdquery; |
| 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'); |
| |
| insert into mtdn2 select pc1, pc2, pc3, month(pc1)+day(pc1)/100 from mtdn1; |
| insert into mtdn3 select * from mtdn2; |
| |
| select "_DIVISION_1_", pc1, "_DIVISION_2_", pc2, "_DIVISION_3_", "_DIVISION_4_", "_DIVISION_5_", pc3, c4 |
| from mtdn1; |
| select "_DIVISION_1_", pc2, "_DIVISION_2_", "_DIVISION_3_", pc3, pc1, c4 |
| from mtdn2; |
| select "_SALT_", "_DIVISION_1_", pc1, "_DIVISION_2_", "_DIVISION_4_", pc3, "_DIVISION_3_", pc2, "_DIVISION_5_", pc4 |
| from mtdn3; |
| |
| prepare s from |
| select * from mtdn1 where pc1 < cast(date '2011-08-15' as timestamp); |
| -- explain s; |
| execute skeyquery; |
| |
| 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'; |
| execute skeyquery; |
| -- ("_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'; |
| execute skeyquery; |
| |
| drop table mtdn1; |
| drop table mtdn2; |
| |
| -- 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)); |
| -- 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)); |
| -- 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)); |
| -- 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)); |
| -- 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); |
| -- 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); |
| -- 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); |
| -- 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)); |
| -- 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)); |
| -- 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); |
| -- 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)); |
| -- 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')); |
| -- 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 |
| |
| prepare s from |
| insert into mtd1 select *, DATE_PART('YEARMONTH', SALE_DATE) from mtd0; |
| -- 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 again |
| |
| prepare s from |
| update mtd1 set "_DIVISION_1_" = 999999; |
| -- 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 |
| |
| prepare s from |
| insert into mtd2 select *, DATE_PART('YEARMONTH', SALE_DATE) from mtd0; |
| -- 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 again |
| |
| prepare s from |
| update mtd2 set "_DIVISION_1_" = 999999; |
| -- error 4013, column "_DIVISION_1_" is a system column and cannot be updated |
| |
| |
| ------------- Testing indexes -------------------------- |
| |
| delete from mtd0; |
| delete from mtd1; |
| delete from mtd2; |
| |
| insert into mtd0 values (1, 101, date '2011-06-30', 100.00), |
| (2, 102, date '2011-08-02', 200.00); |
| insert into mtd1 select * from mtd0; |
| insert into mtd2 select * from mtd1; |
| |
| create view mtd0v as (select sale_date, item_id, store_id, sale_amt from mtd0); |
| create view mtd1v as (select sale_date, item_id, store_id, sale_amt from mtd1); |
| create view mtd2v as (select sale_date, store_id, sale_amt from mtd2); |
| |
| create index mtd1x1 on mtd1(item_id); |
| create index mtd1x2 on mtd1(item_id, sale_amt) division like table; |
| create index mtd2x1 on mtd2(item_id); |
| create index mtd2x2 on mtd2(item_id, sale_amt) division like table; |
| |
| create unique index mtd1ux3 on mtd1(item_id, store_id); |
| create unique index mtd2ux3 on mtd2(sale_amt); |
| create unique index mtd1ux4 on mtd1(item_id, sale_date) division like table; |
| -- currently not supported although we could allow this one |
| |
| create unique index mtd1ux8 on mtd1(item_id, store_id) division like table; |
| -- 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; |
| -- 1402, can't division a unique index unless it contains the underlying cols of division by |
| |
| execute smdquery; |
| 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 |
| ; |
| |
| execute smdviewquery; |
| -- 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 |
| ; |
| |
| execute smdixquery; |
| |
| set parserflags 1; |
| -- validate contents of index against base table after create index |
| select count(*) from mtd1; |
| |
| select count(*) |
| from table(index_table mtd1x1) natural join mtd1; |
| |
| select count(*) |
| from table(index_table mtd1x2) natural join mtd1; |
| |
| select count(*) |
| from table(index_table mtd1ux3) natural join mtd1; |
| |
| select count(*) |
| from table(index_table mtd1ux4) natural join mtd1; |
| -- currently not supported |
| |
| select count(*) from mtd2; |
| |
| select count(*) |
| from table(index_table mtd2x1) natural join mtd2; |
| |
| select count(*) |
| from table(index_table mtd2x2) natural join mtd2; |
| |
| select count(*) |
| from table(index_table mtd2ux3) natural join mtd2; |
| |
| drop index mtd1ux3; |
| drop index mtd2ux3; |
| drop index mtd1ux4; |
| |
| set parserflags 0; |
| |
| delete from mtd1; |
| delete from mtd2; |
| |
| select count(*) from table(index_table mtd1x1); |
| select count(*) from table(index_table mtd1x2); |
| select count(*) from table(index_table mtd2x1); |
| select count(*) from table(index_table mtd2x2); |
| |
| insert into mtd1 select * from mtd0; |
| insert into mtd1(item_id, sale_amt, store_id) values(110,default,10); |
| insert into mtd2 select * from mtd1; |
| insert into mtd2(item_id, store_id, sale_amt) values(111,11, default); |
| insert into mtd2(item_id, store_id, sale_date) values(112,12, default); |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| insert into mtd1v select sale_date, item_id+1000, store_id, sale_amt from mtd2; |
| insert into mtd1v(store_id, item_id, sale_date) values (41, 141, default); |
| insert into mtd1v(store_id, sale_amt, item_id, sale_date) values (42, default, 142, default); |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| update mtd1v set store_id = store_id + 1 where item_id = 1112; |
| --explain |
| update mtd1v set sale_date = sale_date + interval '1' month where store_id <= 11; |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| |
| alter table mtd1 add column ac1 integer default 33; |
| insert into mtd1 values (113,13, default,100.00, default); |
| insert into mtd1 values (114,14, date '2011-07-04', 100.00, 44); |
| insert into mtd1v values (date '2011-07-05', 15, 115, 111.00); |
| alter table mtd1 add column ac2 float default null; |
| insert into mtd1 values (116,16, date '2011-07-04', 100.00, default, 66); |
| insert into mtd1v values (date '2011-07-05', 17, 117, 111.00); |
| insert into mtd1v (store_id, sale_amt, item_id) values (17, 100.00, 117); |
| |
| -- negative test cases |
| insert into mtd1v(store_id, sale_date) values (40, date '2011-07-08'); |
| -- 4024, item_id is missing |
| |
| select *, "_DIVISION_1_" from mtd1 order by 1, 2, 3; |
| select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3; |
| |
| set parserflags 1; |
| select * from table(index_table mtd1x1) order by 1, 2, 3, 4; |
| select * from table(index_table mtd1x2) order by 1, 2, 3, 4, 5; |
| select "ITEM_ID@", "_DIVISION_1_", STORE_ID, SALE_DATE |
| from table(index_table mtd2x1) order by 1, 2, 3, 4; |
| select "_DIVISION_1_@", "ITEM_ID@", "SALE_AMT@", STORE_ID, SALE_DATE |
| from table(index_table mtd2x2) order by 1, 2, 3, 4, 5; |
| |
| -- validate contents of index against base table |
| update mtd1 set sale_amt = 0.00 where sale_amt is null; |
| select count(*) from mtd1; |
| |
| select count(*) |
| from table(index_table mtd1x1) natural join mtd1 |
| where "ITEM_ID@" = item_id; |
| |
| select count(*) |
| from table(index_table mtd1x2) natural join mtd1 |
| where "ITEM_ID@" = item_id |
| and "SALE_AMT@" = sale_amt; |
| |
| update mtd2 set sale_amt = 0.00 where sale_amt is null; |
| select count(*) from mtd2; |
| |
| select count(*) |
| from table(index_table mtd2x1) natural join mtd2; |
| |
| select count(*) |
| from table(index_table mtd2x2) natural join mtd2; |
| |
| set parserflags 0; |
