-- Tests for Hbase - Populate all indexes and Load/Extract 
-- Added April 2014
--
-- @@@ 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 @@@

create schema trafodion.hbase;
set schema trafodion.hbase;

prepare indexInfo from
select left(o1.object_name,20),o1.object_type  , o1.valid_def Index_valid_def, o2.valid_def     table_valid_def
from trafodion."_MD_".objects o1 
join  trafodion."_MD_".indexes i on  o1.object_uid=i.index_uid
join   trafodion."_MD_".objects o2 on i.base_table_uid=o2.object_uid
where o1.object_type='IX' and o2.Object_Name=? and o2.schema_name='HBASE' and O2.catalog_name='TRAFODION';


cqd comp_bool_226 'on';

obey TEST017(clnup);
log LOG017 clear;


obey TEST017(setup);

obey TEST017(tests_simple);

log;


obey TEST017(clnup);
exit;

?section clnup
--------------
drop index T017TTBIDXb;
drop  index T017TTBIDXc;
drop  index T017TTCIDXb;
drop  index T017TTCIDXc;
drop  index T017TTDIDXb;
drop  index T017TTDIDXc;
drop  index T017TTEIDXb;

drop table T017TTB cascade;
drop table T017TTA cascade;
drop table T017TTC cascade;
drop table T017TTD cascade;
drop table T017TTE cascade;
drop table "T017_Delimit" cascade;

drop index a5iraea1;
drop index a5iraee1;

drop table   a5table1;


?section setup
--------------
create  table T017TTB ( a int not null primary key, b int, c int);

create  table T017TTA ( a int , b int, c int);

create  table T017TTC ( a int not null primary key, b int, c int) SALT using 2 partitions on (a);

create  table T017TTD ( a int not null not droppable, b int, c int) store by (a); 

create  table T017TTE ( a int , b int, c int); 

create table "T017_Delimit" ( a int not null primary key, b int, c int);

load with no output, no recovery into T017TTA
  select
    0 +   (10 * x10) + x1,
    1000 +   (10 * x10) + x1,
    10000 +   (10 * x10) + x1
  from (values(1)) as starter
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1 ;

 
create table a5table1
 ( ref_num largeint
 , z_text char(3)
 , emp_num smallint
 ) no partition
 ;

insert into a5table1 values
 (100,'abc',99), (100,'abd',99), (100,'abe',99), (100,'abc',100),
 (100,'abd',100), (100,'abe',100), (100,'abc',101), (100,'abd',101),
 (100,'abe',101), (100,'abb',null), (100,'abc',null), (100,'abd',null),
 (100,'abe',null), (100,null,99), (100,null,100), (100,null,101),
 (200,'abc',200), (200,'abd',200), (200,'abe',200);                                                                   



?section  tests_simple
----------------------
create index T017TTBIDXb on T017TTB(b) no populate;
create unique index T017TTBIDXc on T017TTB(c) no populate;

create index T017TTCIDXb on T017TTC(b) no populate;
create unique index T017TTCIDXc on T017TTC(c) no populate;

create index T017TTDIDXb on T017TTD(b) no populate;
create unique index T017TTDIDXc on T017TTD(c) no populate;

create index T017TTEIDXb on T017TTE(b) no populate;

execute indexinfo using 'T017TTB';
execute indexinfo using 'T017TTC';
execute indexinfo using 'T017TTD';
execute indexinfo using 'T017TTE';

set parserflags 1;
select count(*) from table(index_table T017TTBIDXb);
select count(*) from table(index_table T017TTBIDXc);
select count(*) from table(index_table T017TTCIDXb);
select count(*) from table(index_table T017TTCIDXc);

alter table T017TTB enable all indexes;
alter table T017TTC enable all indexes;
alter table T017TTD enable all indexes;

execute indexinfo using 'T017TTB';
execute indexinfo using 'T017TTC';
execute indexinfo using 'T017TTD';

alter table T017TTB disable all indexes;
alter table T017TTC disable all indexes;
alter table T017TTD disable all indexes;

execute indexinfo using 'T017TTB';
execute indexinfo using 'T017TTC';
execute indexinfo using 'T017TTD';

load with no output, no recovery into T017TTB select * from T017TTA;
load with no output, no recovery into T017TTC select * from T017TTA;
load with no output, no recovery into T017TTD select * from T017TTA;
load with no output, no recovery into T017TTE select * from T017TTA;
---------------------------------------------------
populate all indexes on T017TTB;
execute indexinfo using 'T017TTB';
set parserflags 1;
select count(*) from table(index_table T017TTBIDXb);
select count(*) from table(index_table T017TTBIDXc);


populate all indexes on T017TTC;
execute indexinfo using 'T017TTC';
set parserflags 1;
select count(*) from table(index_table T017TTCIDXb);
select count(*) from table(index_table T017TTCIDXc);
--verify contents of index table defined on table with Salt column 
showddl table(index_table T017TTcIDXb);
select  "B@" , I.A ,t.b,t.c ,case when I."_SALT_"<>T."_SALT_" THEN 1 else 0 end  from table(index_table T017TTcIDXb) I join T017TTc t on I.A=t.A order by "B@";

populate all indexes on T017TTD;
execute indexinfo using 'T017TTD';
set parserflags 1;
select count(*) from table(index_table T017TTDIDXb);
select count(*) from table(index_table T017TTDIDXc);
--verify contents of index table defined on table with syskey and store by clause
showddl table(index_table T017TTdIDXb);
select  "B@" , I.A ,t.b,t.c ,case when I.SYSKEY<>T.SYSKEY THEN 1 else 0 end  from table(index_table T017TTdIDXb) I join T017TTd t on I.A=t.A order by "B@";

populate all indexes on T017TTE;
execute indexinfo using 'T017TTE';
set parserflags 1;
select count(*) from table(index_table T017TTEIDXb);
--verify contents of index table defined on table with syskey 
showddl table(index_table T017TTeIDXb);
select  "B@" , t.b,t.c ,case when I.SYSKEY<>T.SYSKEY THEN 1 else 0 end  from table(index_table T017TTeIDXb) I join T017TTe t on I."B@"=t.B order by "B@";

--case where no indexes
drop index T017TTBIDXb;
drop index T017TTBIDXc;
drop table T017TTB;
create  table T017TTB ( a int not null primary key, b int, c int);

load with no output, no recovery into T017TTB select * from T017TTA;

populate all indexes on T017TTB;
alter table T017TTB enable all indexes;

--shoud give error
update T017TTB set c = 1 ;
create unique index T017TTBIDXc on T017TTB(c) no populate;
populate all indexes on T017TTB;

--delimited table name

insert into "T017_Delimit" values (1,1,1),(2,2,2); 

create index idxdelim on "T017_Delimit"(b) no populate;
populate all indexes on "T017_Delimit";
-------

--verifying fix for Bug 1359872
--
select count(*) from a5table1;
 create index a5iraea10 on a5table1 (ref_num asc, z_text, emp_num asc) ; 
 
 create index a5iraey10 on a5table1 (ref_num asc, z_text, emp_num desc) ;

select count(*) from table(index_table a5iraea10);
select count(*) from table(index_table a5iraey10);

drop  index T017TTCIDXb;

create index T017TTCIDXb on T017TTC (b) HBASE_OPTIONS (DATA_BLOCK_ENCODING = 'FAST_DIFF', COMPRESSION = 'GZ') SALT LIKE TABLE;

select * from table (index_table T017TTCIDXb) order by "_SALT_@","B@","A";

explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "_SALT_","B","A" from T017TTC for read uncommitted access;

explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "_SALT_","B","A" from T017TTC <<+ cardinality 10e1 >> for read uncommitted access;


drop  index T017TTCIDXb;

create index T017TTCIDXb on T017TTC (b);

select * from table (index_table T017TTCIDXb) order by "B@","_SALT_","A";

explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "B","_SALT_","A" from T017TTC for read uncommitted access;

explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "B","_SALT_","A" from T017TTC <<+ cardinality 10e1 >> for read uncommitted access;


