core/sql/regress/executor/EXPECTED140

>>obey TEST140(ddl);
>>cqd traf_aligned_row_format 'OFF';

--- SQL operation complete.
>>
>>create table t140helper (a int not null, primary key(a));

--- SQL operation complete.
>>insert into t140helper values(1);

--- 1 row(s) inserted.
>>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)) ;

--- SQL operation complete.
>>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) ;

--- SQL operation complete.
>>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'
+>  )
+>;

--- SQL operation complete.
>>
>>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;

--- 10 row(s) inserted.
>>
>>insert into t140 values(11,11,12,102,1002,13,103,1003,'nullabs11');

--- 1 row(s) inserted.
>>insert into t140 values(12,12,22,202,2002,NULL,NULL,2003,'nullabs12');

--- 1 row(s) inserted.
>>insert into t140 values(13,13,32,302,3002,33,303,3003,'nullupd13');

--- 1 row(s) inserted.
>>update t140 set an = null, bn=null where uniq=13;

--- 1 row(s) updated.
>>insert into t140 values(14,14,42,402,4002,43,403,4003,NULL);

--- 1 row(s) inserted.
>>insert into t140 values(15,15,52,502,5002,53,503,5003,'asd');

--- 1 row(s) inserted.
>>update t140 set d = null where uniq = 15;

--- 1 row(s) updated.
>>
>>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;

--- 10 row(s) inserted.
>>
>>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;
Task:  LOAD            Status: Started    Object: TRAFODION.SCH.T140C
Task:  CLEANUP         Status: Started    Time: 2017-09-18 15:49:46.789
Task:  CLEANUP         Status: Ended      Time: 2017-09-18 15:49:46.816
Task:  CLEANUP         Status: Ended      Elapsed Time:    00:00:00.027
Task:  LOADING DATA    Status: Started    Time: 2017-09-18 15:49:46.816
       Rows Processed: 2750311 
       Error Rows:     0 
Task:  LOADING DATA    Status: Ended      Time: 2017-09-18 15:50:33.561
Task:  LOADING DATA    Status: Ended      Elapsed Time:    00:00:46.744
Task:  COMPLETION      Status: Started    Time: 2017-09-18 15:50:33.561
       Rows Loaded:    2750311 
Task:  COMPLETION      Status: Ended      Time: 2017-09-18 15:50:35.404
Task:  COMPLETION      Status: Ended      Elapsed Time:    00:00:01.843

--- 2750311 row(s) loaded.
>>update statistics for table t140c on every column sample;

--- SQL operation complete.
>>cqd cache_histograms_in_kb '0';

--- SQL operation complete.
>>cqd cache_histograms_in_kb reset;

--- SQL operation complete.
>>
>>obey TEST140(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;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select a from t140 where b>500;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.A


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:3
  pushed_down_rpn ........ (#1:4>?)
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select a from t140 where b>=500;

A          
-----------

         50
         60
         70
         80
         90
         52

--- 6 row(s) selected.
>>--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;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an from t140b where b<=200;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140B.AN


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140B
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140B
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:2,#1:6
  pushed_down_rpn ........ (#1:4<=?)
  key_columns ............ _SALT_, UNIQ, UNIQ2

--- SQL operation complete.
>>-- we should have 2 columns retrieved since an is nullable
>>explain options 'p' select an from t140 where b<=200;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an from t140 where b<=200;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.AN


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:2,#1:6
  pushed_down_rpn ........ (#1:4<=?)
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select an from t140 where b<=200;

AN         
-----------

          1
         11
         21
         13

--- 4 row(s) selected.
>>-- 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;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an from t140 where b=200 and an is not null;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.AN
  input_variables ........ %(200)


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:6
  pushed_down_rpn ........ (#1:4=?)(#1:6 is_not_null.)AND
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select an from t140 where b=200 and an is not null;

AN         
-----------

         21

--- 1 row(s) selected.
>>-- 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;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an, a from t140 where b!=500;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.AN, TRAFODION.SCH.T140.A


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:3,#1:6
  pushed_down_rpn ........ (#1:4<?)(#1:4>?)OR
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select an, a from t140 where b!=500;

AN           A          
-----------  -----------

          1            0
         11           10
         21           20
         31           30
         41           40
         61           60
         71           70
         81           80
         91           90
         13           12
          ?           22
          ?           32
         43           42
         53           52

--- 14 row(s) selected.
>>-- see if we handle null logic correctly
>>select a from t140 where bn>=501;

A          
-----------

         50
         60
         70
         80
         90
         52

--- 6 row(s) selected.
>>select an from t140 where bn<=201;

AN         
-----------

          1
         11
         21
         13

--- 4 row(s) selected.
>>explain options 'p' select an from t140 where bn=201 and an is not null;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an from t140 where bn=201 and an is not null;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.AN
  input_variables ........ %(201)


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:6
  pushed_down_rpn ........ (#1:7=.?)(#1:6 is_not_null.)AND
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select an from t140 where bn=201 and an is not null;

AN         
-----------

         21

--- 1 row(s) selected.
>>explain options 'p' select an, a from t140 where bn!=501;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an, a from t140 where bn!=501;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.AN, TRAFODION.SCH.T140.A


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:3,#1:6
  pushed_down_rpn ........ (#1:7<.?)(#1:7>.?)OR
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select an, a from t140 where bn!=501;

AN           A          
-----------  -----------

          1            0
         11           10
         21           20
         31           30
         41           40
         61           60
         71           70
         81           80
         91           90
         13           12
         43           42
         53           52

--- 12 row(s) selected.
>>select a from t140 where an is null;

A          
-----------

         22
         32

--- 2 row(s) selected.
>>select a from t140 where an is not null;

A          
-----------

          0
         10
         20
         30
         40
         50
         60
         70
         80
         90
         12
         42
         52

--- 13 row(s) selected.
>>select a from t140 where a between 20 and 40;

A          
-----------

         20
         30
         40
         22
         32

--- 5 row(s) selected.
>>-- 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;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select an from t140 where an between 20 and 40;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_SMALL_SCANNER .... SYSTEM
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  GENERATE_EXPLAIN ....... ON
  select_list ............ TRAFODION.SCH.T140.AN


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  small_scanner .......... ON
  column_retrieved ....... #1:6
  pushed_down_rpn ........ (#1:6>=.?)(#1:6<=.?)AND
  key_columns ............ UNIQ, UNIQ2

--- SQL operation complete.
>>select an from t140 where an between 20 and 40;

AN         
-----------

         21
         31

--- 2 row(s) selected.
>>select an from t140 where an in (21,41,51,61,10);

AN         
-----------

         21
         41
         51
         61

--- 4 row(s) selected.
>>
>>-- test parallel scanner
>>-- turn off small scanner as it will force single scanner
>>cqd hbase_small_scanner 'OFF';

--- SQL operation complete.
>>cqd parallel_num_esps '1';

--- SQL operation complete.
>>-- force 2 threads
>>cqd hbase_dop_parallel_scanner '2.0';

--- SQL operation complete.
>>explain options 'p' select avg(a) from t140b;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select avg(a) from t140b;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 3        ONLY CHILD 2
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  HBASE_SMALL_SCANNER .... OFF
  PARALLEL_NUM_ESPS ...... 1
  HBASE_DOP_PARALLEL_SCAN  2
  GENERATE_EXPLAIN ....... ON
  select_list ............ cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T140B.
                             A)) * 10000 ...0)) / cast(count(1 )))) / 10000
                             ...0)))


SORT_SCALAR_AGGR ==========================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  aggregates ............. sum(TRAFODION.SCH.T140B.A), count(1 )


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140B
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140B
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  parallel_scanner ....... 2
  column_retrieved ....... #1:3
  key_columns ............ _SALT_, UNIQ, UNIQ2

--- SQL operation complete.
>>select avg(a) from t140b;

(EXPR)              
--------------------

                  45

--- 1 row(s) selected.
>>-- force 100% of threads (with 2 partition this is 2 threads)
>>cqd hbase_dop_parallel_scanner '1.0';

--- SQL operation complete.
>>explain options 'p' select avg(a) from t140b;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select avg(a) from t140b;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 3        ONLY CHILD 2
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  HBASE_SMALL_SCANNER .... OFF
  PARALLEL_NUM_ESPS ...... 1
  HBASE_DOP_PARALLEL_SCAN  1
  GENERATE_EXPLAIN ....... ON
  select_list ............ cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T140B.
                             A)) * 10000 ...0)) / cast(count(1 )))) / 10000
                             ...0)))


SORT_SCALAR_AGGR ==========================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  aggregates ............. sum(TRAFODION.SCH.T140B.A), count(1 )


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140B
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140B
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  parallel_scanner ....... 1
  column_retrieved ....... #1:3
  key_columns ............ _SALT_, UNIQ, UNIQ2

--- SQL operation complete.
>>select avg(a) from t140b;

(EXPR)              
--------------------

                  45

--- 1 row(s) selected.
>>explain options 'p' select avg(a) from t140b;

------------------------------------------------------------------ PLAN SUMMARY
STATEMENT_NAME ........... NOT NAMED
STATEMENT ................ select avg(a) from t140b;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 3        ONLY CHILD 2
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  xn_access_mode ......... read_only
  auto_query_retry ....... enabled
  embedded_arkcmp ........ used
  IS_SQLCI ............... ON
  LDAP_USERNAME
  MODE_SEABASE ........... ON
  SEABASE_VOLATILE_TABLES  ON
  HBASE_ASYNC_DROP_TABLE   OFF
  HBASE_SERIALIZATION .... ON
  HBASE_FILTER_PREDS ..... 2
  TRAF_INDEX_CREATE_OPT    ON
  SCHEMA ................. TRAFODION.SCH
  TRAF_ALIGNED_ROW_FORMAT  OFF
  HBASE_SMALL_SCANNER .... OFF
  PARALLEL_NUM_ESPS ...... 1
  HBASE_DOP_PARALLEL_SCAN  1
  select_list ............ cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T140B.
                             A)) * 10000 ...0)) / cast(count(1 )))) / 10000
                             ...0)))


SORT_SCALAR_AGGR ==========================  SEQ_NO 2        ONLY CHILD 1
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  aggregates ............. sum(TRAFODION.SCH.T140B.A), count(1 )


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... T140B
DESCRIPTION
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SCH.T140B
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_blocks ........... ON
  parallel_scanner ....... 1
  column_retrieved ....... #1:3
  key_columns ............ _SALT_, UNIQ, UNIQ2

--- SQL operation complete.
>>select avg(a) from t140b;

(EXPR)              
--------------------

                  45

--- 1 row(s) selected.
>>cqd parallel_num_esps '1';

--- SQL operation complete.
>>cqd hbase_dop_parallel_scanner '1.0';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>cqd hbase_dop_parallel_scanner '2';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>cqd hbase_dop_parallel_scanner '3';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>cqd hbase_dop_parallel_scanner '4';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>cqd hbase_dop_parallel_scanner '5';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>cqd hbase_dop_parallel_scanner '6';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>cqd hbase_dop_parallel_scanner '7';

--- SQL operation complete.
>>prepare xx from select count(*) from t140c where ss_customer_sk between 1000 and 20000;

--- SQL command prepared.
>>execute xx;

(EXPR)              
--------------------

              510964

--- 1 row(s) selected.
>>-- reset to regular scanner
>>cqd hbase_dop_parallel_scanner reset;

--- SQL operation complete.
>>cqd hbase_small_scanner reset;

--- SQL operation complete.
>>cqd parallel_num_esps reset;

--- SQL operation complete.
>>
>>obey TEST140(clnup);
>>drop table t140helper;

--- SQL operation complete.
>>drop table t140;

--- SQL operation complete.
>>drop table t140b;

--- SQL operation complete.
>>drop table t140c;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session