[jira] [Created] (TRAFODION-380) LP Bug: 1329965 - compiler doesn't set correct beginKey and endKey for varchar key predicate

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-380:
------------------------------------

             Summary: LP Bug: 1329965 - compiler doesn't set correct beginKey and endKey for varchar key predicate
                 Key: TRAFODION-380
                 URL: https://issues.apache.org/jira/browse/TRAFODION-380
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Ravisha Neelakanthappa
            Assignee: Ravisha Neelakanthappa
            Priority: Critical


For a simple select query on a salted table with an eauqality predicate on a varchar key column, compiler produces an inefficient plan.

For example:
set schema trafodion.hbase;
drop table tpet;

CREATE TABLE tpet
  (
    c1          VARCHAR(45) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , c2          INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (c1 ASC)
  )
  SALT USING 4 PARTITIONS
;

insert into tpet values ('abc', 10);
insert into tpet values ('def', 10);
insert into tpet values ('xyz', 10);

update statistics for table tpet on c1;

>>prepare xx from
+>select * from tpet where c1 = 'xyz';

--- SQL command prepared.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  1.00E+000
.    .    1    trafodion_scan                  TPET                  1.00E+000

--- SQL operation complete.


TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... TPET
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ........... 1
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.HBASE.TPET
  key_columns ............ _SALT_, C1
  executor_predicates .... (C1 = %('xyz'))
  part_key_predicates .... (C1 = %('xyz'))
  begin_key .............. (_SALT_ = <min>), (C1 = %('xyz'))
  end_key ................ (_SALT_ = <max>), (C1 = %('xyz'))


The correct plan should be :
TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... TPET
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ........... 1
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.HBASE.TPET
  key_columns ............ _SALT_, C1
  part_key_predicates .... (C1 = %('xyz')) and ("_SALT_" =
                             (HashDistPartHash(cast(C1)) Hash2Distrib cast(4)))
  begin_key .............. (_SALT_ = (HashDistPartHash(cast(C1)) Hash2Distrib
                             cast(4))), (C1 = %('xyz'))
  end_key ................ (_SALT_ = (HashDistPartHash(cast(C1)) Hash2Distrib
                             cast(4))), (C1 = %('xyz'))

--- SQL operation complete.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)