core/sql/regress/executor/TEST140

-- ============================================================================
-- Test: TEST0140 (Executor)
-- @@@ 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 @@@
--
-- Functionality: Advanced predicate pushdown (V2) and parallelScanner
-- Expected files: EXPECTED140
-- Table created: t140 t140b t140helper
-- Limitations:
-- To do:
-- Revision history:
-- ============================================================================
obey TEST140(clnup);
log LOG140 clear;
obey TEST140(ddl);
obey TEST140(run);
obey TEST140(clnup);
exit;

?section clnup
drop table t140helper;
drop table t140;
drop table t140b;
drop table t140c;

?section ddl
cqd traf_aligned_row_format 'OFF';

create table t140helper (a int not null, primary key(a));
insert into t140helper values(1);
create table t140 (uniq int not null, uniq2 int not null , a int not null, b int not null,
    c int not null, an int, bn int, cn int, d varchar(10),                  primary key (uniq,uniq2)) ;
create table t140b (uniq int not null, uniq2 int not null , a int not null, b int not null,
    c int not null, an int, bn int, cn int, d varchar(10),                  primary key (uniq,uniq2)) salt using 2 partitions on (uniq,uniq2) ;
CREATE TABLE t140c
  (
    SS_SOLD_DATE_SK                  INT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
  , SS_ITEM_SK                       INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , SS_TICKET_NUMBER                 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , SS_SOLD_TIME_SK                  INT DEFAULT NULL NOT SERIALIZED
  , SS_CUSTOMER_SK                   INT DEFAULT NULL NOT SERIALIZED
  , SS_CDEMO_SK                      INT DEFAULT NULL NOT SERIALIZED
  , SS_HDEMO_SK                      INT DEFAULT NULL NOT SERIALIZED
  , SS_ADDR_SK                       INT DEFAULT NULL NOT SERIALIZED
  , SS_STORE_SK                      INT DEFAULT NULL NOT SERIALIZED
  , SS_PROMO_SK                      INT DEFAULT NULL NOT SERIALIZED
  , SS_QUANTITY                      INT DEFAULT NULL NOT SERIALIZED
  , SS_WHOLESALE_COST                REAL DEFAULT NULL NOT SERIALIZED
  , SS_LIST_PRICE                    REAL DEFAULT NULL NOT SERIALIZED
  , SS_SALES_PRICE                   REAL DEFAULT NULL NOT SERIALIZED
  , SS_EXT_DISCOUNT_AMT              REAL DEFAULT NULL NOT SERIALIZED
  , SS_EXT_SALES_PRICE               REAL DEFAULT NULL NOT SERIALIZED
  , SS_EXT_WHOLESALE_COST            REAL DEFAULT NULL NOT SERIALIZED
  , SS_EXT_LIST_PRICE                REAL DEFAULT NULL NOT SERIALIZED
  , SS_EXT_TAX                       REAL DEFAULT NULL NOT SERIALIZED
  , SS_COUPON_AMT                    REAL DEFAULT NULL NOT SERIALIZED
  , SS_NET_PAID                      REAL DEFAULT NULL NOT SERIALIZED
  , SS_NET_PAID_INC_TAX              REAL DEFAULT NULL NOT SERIALIZED
  , SS_NET_PROFIT                    REAL DEFAULT NULL NOT SERIALIZED
  , PRIMARY KEY (SS_SOLD_DATE_SK ASC, SS_ITEM_SK ASC, SS_TICKET_NUMBER ASC)
  )
  SALT USING 8 PARTITIONS
       ON (SS_ITEM_SK, SS_TICKET_NUMBER)
 ATTRIBUTES ALIGNED FORMAT
  HBASE_OPTIONS
  (
    DATA_BLOCK_ENCODING = 'FAST_DIFF',
    BLOCKSIZE = '131072'
  )
;

upsert using load 
into t140
  select  x1,x1,10*x1,100*x1,1000*x1,10*x1+1,100*x1+1, 1000*x1+1, 'aaa'||cast(x1 as varchar(10)) 
         
  from t140helper
transpose 0,1,2,3,4,5,6,7,8,9 as x1;

insert into t140 values(11,11,12,102,1002,13,103,1003,'nullabs11');
insert into t140 values(12,12,22,202,2002,NULL,NULL,2003,'nullabs12');
insert into t140 values(13,13,32,302,3002,33,303,3003,'nullupd13');
update t140 set an = null, bn=null where uniq=13;
insert into t140 values(14,14,42,402,4002,43,403,4003,NULL);
insert into t140 values(15,15,52,502,5002,53,503,5003,'asd');
update t140 set d = null where uniq = 15;

upsert using load 
into t140b
  select  x1,x1,10*x1,100*x1,1000*x1,10*x1+1,100*x1+1, 1000*x1+1, 'aaa'||cast(x1 as varchar(10)) 
         
  from t140helper
transpose 0,1,2,3,4,5,6,7,8,9 as x1;

load into t140c select
   SS_SOLD_DATE_SK 
  , SS_ITEM_SK       
  , SS_TICKET_NUMBER
  , SS_SOLD_TIME_SK
  , SS_CUSTOMER_SK 
  , SS_CDEMO_SK  
  , SS_HDEMO_SK    
  , SS_ADDR_SK    
  , SS_STORE_SK   
  , SS_PROMO_SK  
  , SS_QUANTITY     
  , SS_WHOLESALE_COST  
  , SS_LIST_PRICE   
  , SS_SALES_PRICE  
  , SS_EXT_DISCOUNT_AMT  
  , SS_EXT_SALES_PRICE  
  , SS_EXT_WHOLESALE_COST   
  , SS_EXT_LIST_PRICE    
  , SS_EXT_TAX       
  , SS_COUPON_AMT    
  , SS_NET_PAID      
  , SS_NET_PAID_INC_TAX  
  , SS_NET_PROFIT     
from hive.hive.store_sales where ss_sold_date_sk is not null;
update statistics for table t140c on every column sample;
cqd cache_histograms_in_kb '0';
cqd cache_histograms_in_kb reset;

?section run
-- test returned rows with or without adding key column and test of all pushdown functions with null or non null column
-- only one column retrieved
explain options 'p' select a from t140 where b>500;
select a from t140 where b>=500;
--verify that the column retrieved for null handling is not the _SALT_ for optimization of skips, salt being the last physical column
explain options 'p' select an from t140b where b<=200;
-- we should have 2 columns retrieved since an is nullable
explain options 'p' select an from t140 where b<=200;
select an from t140 where b<=200;
-- should not get back 2 column, only one since predicate says an is not null
explain options 'p' select an from t140 where b=200 and an is not null;
select an from t140 where b=200 and an is not null;
-- should only get 2 column back since a is not null, no need to add key column
explain options 'p' select an, a from t140 where b!=500;
select an, a from t140 where b!=500;
-- see if we handle null logic correctly
select a from t140 where bn>=501;
select an from t140 where bn<=201;
explain options 'p' select an from t140 where bn=201 and an is not null;
select an from t140 where bn=201 and an is not null;
explain options 'p' select an, a from t140 where bn!=501;
select an, a from t140 where bn!=501;
select a from t140 where an is null;
select a from t140 where an is not null;
select a from t140 where a between 20 and 40;
-- make sure that we only retrieve one column as an cannot be null in the result set.
explain options 'p' select an from t140 where an between 20 and 40;
select an from t140 where an between 20 and 40;
select an from t140 where an in (21,41,51,61,10);

-- test parallel scanner
-- turn off small scanner as it will force single scanner
cqd hbase_small_scanner 'OFF';
cqd parallel_num_esps '1';
-- force 2 threads
cqd hbase_dop_parallel_scanner '2.0';
explain options 'p' select avg(a) from t140b;
select avg(a) from t140b;
-- force 100% of threads (with 2 partition this is 2 threads)
cqd hbase_dop_parallel_scanner '1.0';
explain options 'p' select avg(a) from t140b;
select avg(a) from t140b;
explain options 'p' select avg(a) from t140b;
select avg(a) from t140b;
cqd parallel_num_esps '1';
cqd hbase_dop_parallel_scanner '1.0';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
cqd hbase_dop_parallel_scanner '2';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
cqd hbase_dop_parallel_scanner '3';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
cqd hbase_dop_parallel_scanner '4';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
cqd hbase_dop_parallel_scanner '5';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
cqd hbase_dop_parallel_scanner '6';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
cqd hbase_dop_parallel_scanner '7';
prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;
execute xx;
-- reset to regular scanner
cqd hbase_dop_parallel_scanner reset;
cqd hbase_small_scanner reset;
cqd parallel_num_esps reset;