core/sql/regress/executor/TEST131

-- Test: TEST131 (Executor)
-- @@@ START COPYRIGHT @@@
--
--  Licensed 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 @@@
--
-- Functionality: Small Scanner and non regression for JIRA 1446 End key missing for simple scan scenario
-- Expected files: EXPECTED131
-- Table created: t131helper, t1311k, t1311kbis
-- Limitations: 
-- 
-- To Do:   
--      
-- Revision history:
--     (08/07/2015) - Created.
-- 


obey TEST131(clnup);

log LOG131 clear;
obey TEST131(setup);
--reload new session to make sure we are not using cached histogram
sh sh runmxci.ksh -i "TEST131(dml)";
obey TEST131(clnup);
log;
exit;


?section clnup
set schema t131sch;
drop table t131helper cascade;
drop table t1311k cascade;
drop table t1311kbis cascade;
drop schema t131sch cascade;


?section setup
create schema t131sch;
set schema t131sch;
create table t131helper (a int not null, primary key(a));
insert into t131helper values(1);
create table t1311k (uniq int not null,
					c1000 int,
					str1 varchar(4000),
					primary key (uniq));
insert into t1311k
  select (100*x100)+(10*x10)+x1,
  		 (100*x100)+(10*x10)+x1,
  		 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
  from t131helper
  		transpose 0,1,2,3,4,5,6,7,8,9 as x100
  		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;

update statistics for table t1311k on every column sample 10 rows;

--t1311kbis has twice the block size as t1311k that has default hbase block size of 64K  				 					      
create table t1311kbis (uniq int not null,
					c1000 int,
					str1 varchar (4000),
					primary key (uniq))
					HBASE_OPTIONS (BLOCKSIZE='131072');
insert into t1311kbis
  select (100*x100)+(10*x10)+x1,
  		 (100*x100)+(10*x10)+x1,
  		 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
  from t131helper
  		transpose 0,1,2,3,4,5,6,7,8,9 as x100
  		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;

update statistics for table t1311kbis on every column sample 10 rows;

?section dml
log LOG131;
set schema t131sch;
-- need set CQD since invoking this section using a new sh session to force statistic to be taken into account
-- suspect this is because histogram is cached per session and we are starting a new session, reloading Histogram...
cqd hbase_small_scanner 'SYSTEM';
-- should have small scanner on and endKey populated (JIRA 1446)
explain options 'p' select * from t1311k where uniq >2 and uniq <5;
explain options 'p' select * from t1311k where uniq >2 and uniq <99;
select uniq, c1000 from t1311k where uniq >2 and uniq <5;
select uniq, c1000 from t1311k where uniq >2 and uniq <99;
-- should get Small scanner off since the scanned rows do not fit in 64K block
explain options 'p' select * from t1311k where uniq >2 and uniq <30;
-- should get Small scanner on since the scanned rows fit in 128K block
explain options 'p' select * from t1311kbis where uniq >2 and uniq <30;