You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:16:07 UTC
[jira] [Created] (TRAFODION-460) LP Bug: 1343615 - duplicated rows
may return in a singleton select query against a HASH2 partitioned Hbase
table
Alice Chen created TRAFODION-460:
------------------------------------
Summary: LP Bug: 1343615 - duplicated rows may return in a singleton select query against a HASH2 partitioned Hbase table
Key: TRAFODION-460
URL: https://issues.apache.org/jira/browse/TRAFODION-460
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp, sql-exe
Reporter: Qifan Chen
Assignee: Hans Zeller
Priority: Critical
Fix For: 0.9 (pre-incubation)
Potential problems in Hbase scan layer:
1. Each ESP scans the whole table, not a portion of table it is assigned to.
2. All four ESPs are involved in the scan, although the plan calls for only one of them involved.
===================================================================
How to reproduce
prepare xx from
--select * from warehouse <<+ cardinality 10e8 >> where w_id = ?x;
select * from item <<+ cardinality 10e8 >> where
i_id = 1;
execute xx;
-----------------
DDL
CREATE TABLE TRAFODION.ORDERENTRY.CUSTOMER
(
C_ID INT NO DEFAULT NOT NULL NOT DROPPABLE
, C_D_ID INT NO DEFAULT NOT NULL NOT DROPPABLE
, C_W_ID INT NO DEFAULT NOT NULL NOT DROPPABLE
, C_FIRST VARCHAR(16) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_MIDDLE CHAR(2) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_LAST VARCHAR(16) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_STREET_1 VARCHAR(20) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_STREET_2 VARCHAR(20) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_CITY VARCHAR(20) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_STATE CHAR(2) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_ZIP CHAR(9) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_PHONE CHAR(16) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_SINCE TIMESTAMP(6) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_CREDIT CHAR(2) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, C_CREDIT_LIM NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_DISCOUNT NUMERIC(4, 4) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_BALANCE NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_YTD_PAYMENT NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_PAYMENT_CNT NUMERIC(4, 0) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_DELIVERY_CNT NUMERIC(4, 0) NO DEFAULT NOT NULL NOT
DROPPABLE
, C_DATA VARCHAR(500) CHARACTER SET ISO88591
COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (C_W_ID ASC, C_D_ID ASC, C_ID ASC)
)
SALT USING 8 PARTITIONS
ON (C_W_ID)
;
------------
Data: TPCC order-entry
------------ Run-time result
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... ITEM
REQUESTS_IN .............. 1
ROWS_OUT ..... 1,000,000,000
EST_OPER_COST ........... 29.5
EST_TOTAL_COST .......... 29.5
DESCRIPTION
max_card_est ........... 1e+09
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
scan_type .............. subset scan of table TRAFODION.ORDERENTRY.ITEM
key_columns ............ _SALT_, I_ID
part_key_predicates .... (I_ID = ?x)
begin_key .............. (_SALT_ = (HashDistPartHash(cast(cast((cast(?x) /
cast(1000000))))) Hash2Distrib cast(8))),
(I_ID = ?x)
end_key ................ (_SALT_ = (HashDistPartHash(cast(?x)) Hash2Distrib
cast(8))), (I_ID = ?x)
--- SQL operation complete.
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
2 . 3 root 1.00E+009
1 . 2 esp_exchange 1:4(hash2) 1.00E+009
. . 1 trafodion_scan ITEM 1.00E+009
--- SQL operation complete.
>>
>>set param ?x 1;
>>
>>execute xx;
I_ID I_IM_ID I_NAME I_PRICE I_DATA
----------- ----------- ------------------------ ------------ --------------------------------------------------
1 0 PfFPDKz[tuQPbZG[z[xXI 60.01 QnCXdGpRorwsFuZwPxTJiCtrKV{{EtNTBTMPqpIRRx
1 0 PfFPDKz[tuQPbZG[z[xXI 60.01 QnCXdGpRorwsFuZwPxTJiCtrKV{{EtNTBTMPqpIRRx
1 0 PfFPDKz[tuQPbZG[z[xXI 60.01 QnCXdGpRorwsFuZwPxTJiCtrKV{{EtNTBTMPqpIRRx
1 0 PfFPDKz[tuQPbZG[z[xXI 60.01 QnCXdGpRorwsFuZwPxTJiCtrKV{{EtNTBTMPqpIRRx
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)