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:17:54 UTC
[jira] [Created] (TRAFODION-712) LP Bug: 1384430 - create index
fails because no sort operator is added to plan
Alice Chen created TRAFODION-712:
------------------------------------
Summary: LP Bug: 1384430 - create index fails because no sort operator is added to plan
Key: TRAFODION-712
URL: https://issues.apache.org/jira/browse/TRAFODION-712
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Reporter: Apache Trafodion
Assignee: Qifan Chen
Priority: Blocker
This issue was faced with HammerDB POC
Here are the steps to reproduce. tyhe plan shoud have a sort node in this case (which it does not)
>>cqd hide_indexes 'all';
--- SQL operation complete.
>>set parserflags 1;
--- SQL operation complete.
>>explain options 'f' load transform into table(index_table ORDERS_i2) select O_W_ID, O_D_ID, O_C_ID, O_ID from ORDERS ;
..
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
3 . 4 root 1.00E+002
1 2 3 tuple_flow 1.00E+002
. . 2 trafodion_load_prepa ORDERS_I2 1.00E+000
. . 1 trafodion_scan ORDERS 1.00E+002
--- SQL operation complete.
>>explain load transform into table(index_table ORDERS_i2) select O_W_ID, O_D_ID, O_C_ID, O_ID from ORDERS ;
..
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212280765588587912
ROWS_OUT ............... 100
EST_TOTAL_COST ........... 0.01
STATEMENT ................ load transform into table(index_table ORDERS_i2)
select O_W_ID, O_D_ID, O_C_ID, O_ID from ORDERS;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 4 ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est ......... 100
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinality 100
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
ATTEMPT_ESP_PARALLELISM OFF
SCHEMA ................. HAMMERDB
COMP_BOOL_226 .......... ON
SHOWCONTROL_SHOW_ALL ... OFF
HIDE_INDEXES ........... ALL
GENERATE_EXPLAIN ....... ON
TUPLE_FLOW ================================ SEQ_NO 3 CHILDREN 1, 2
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est ......... 100
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. inner
join_method ............ in-order nested
TRAFODION_LOAD_PREPARATION ================ SEQ_NO 2 NO CHILDREN
TABLE_NAME ............... TRAFODION.HAMMERDB.ORDERS_I2
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0
DESCRIPTION
max_card_est ......... 100
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
iud_type ............... index_trafodion_load_preparation
TRAFODION.HAMMERDB.ORDERS_I2
new_rec_expr ........... ("O_W_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_W_ID),
("O_D_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_D_ID
), ("O_C_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_C
_ID), ("O_ID@" assign TRAFODION.HAMMERDB.ORDERS.O_
ID)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... ORDERS
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est ......... 100
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of table TRAFODION.HAMMERDB.ORDERS
columns ................ all
begin_keys(incl)
end_keys(incl)
cache_size ........... 100
probes ................. 1
rows_accessed ........ 100
key_columns ............ O_W_ID, O_D_ID, O_ID
--- SQL operation complete.
>>showddl orders;
CREATE TABLE TRAFODION.HAMMERDB.ORDERS
(
O_ID NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
DROPPABLE
, O_W_ID NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
DROPPABLE
, O_D_ID NUMERIC(9, 0) NO DEFAULT NOT NULL NOT
DROPPABLE
, O_C_ID NUMERIC(9, 0) DEFAULT NULL
, O_CARRIER_ID NUMERIC(9, 0) DEFAULT NULL
, O_OL_CNT NUMERIC(9, 0) DEFAULT NULL
, O_ALL_LOCAL NUMERIC(9, 0) DEFAULT NULL
, O_ENTRY_D TIMESTAMP(6) DEFAULT NULL
, PRIMARY KEY (O_W_ID ASC, O_D_ID ASC, O_ID ASC)
)
;
CREATE UNIQUE INDEX ORDERS_I2 ON TRAFODION.HAMMERDB.ORDERS
(
O_W_ID ASC
, O_D_ID ASC
, O_C_ID ASC
, O_ID ASC
)
;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)