Hi Eric, Thanks for explaining about RPN notation. I am sorry that I forgot after reading your clear description in the JIRA. Can we use the following two statements after the statement has finished execution (with hbase_filter_preds '1' and '2') and see if the number of rows coming of operator 1 are the same. The fact that we got 0 rows updated and no error message in the '2' case makes me think that no row is coming out of that first scan. get statistics for qid current ; get statistics for qid current default;
You may already know this for a fact and are asking how to find out why this is so. If so, I am sorry then we would have to look further and set a breakpoint before the executor predicate is evaluated on node 1 and see if there is any difference there. Thanks Suresh On Mon, Dec 21, 2015 at 11:41 AM, Eric Owhadi <[email protected]> wrote: > No that's because when predicate are pushed down they shown under > pushed_down_rpn in reverse polish form and showing column name as hbase > column name. > The reminder are the predicates that are still needed to be evaluated at > executor side. > Eric > > -----Original Message----- > From: suresh subbiah [mailto:[email protected]] > Sent: Monday, December 21, 2015 11:38 AM > To: [email protected] > Subject: RE: a pointer would be appreciated: > > Hi Eric, Thanks for sharing. The predicates on node 1 (scan that feeds > into > delete) seems different for second and third plans (filter preds 1 and 2). > Third plan has fewer predicates. May be a breakpoint on this scan to see > how > many rows are returned from here will help. Rms can also provide this info. > Thanks suresh > > > Sent via the Samsung Galaxy S™ III, an AT&T 4G LTE smartphone > > <div>-------- Original message --------</div><div>From: Eric Owhadi > <[email protected]> </div><div>Date:12/21/2015 9:50 AM (GMT-06:00) > </div><div>To: [email protected] </div><div>Subject: RE: > a > pointer would be appreciated: </div><div> </div>Hi Suresh, here is the 3 > different explain, first with flter preds off, second with current filter > preds, and last is with the one I am developing. > > > ---- this one is with HBASE_FILTER_PREDS = 'OFF' > >>explain update T29xv3 set r=0,d=0 where r=9; > > ------------------------------------------------------------------ PLAN > SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME > ........... NOT NAMED PLAN_ID .................. 212317471790857530 > ROWS_OUT > ................. 1 EST_TOTAL_COST ........... 0.01 STATEMENT > ................ update T29xv3 set r=0,d=0 where r=9; > > > ------------------------------------------------------------------ NODE > LISTING > ROOT ====================================== SEQ_NO 7 ONLY CHILD 6 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > statement_index ........ 0 > affinity_value ......... 0 > max_max_cardinality .... 1 > total_overflow_size .... 0.00 KB > upd_action_on_error .... xn_rollback > xn_access_mode ......... read_only > xn_autoabort_interval 0 > auto_query_retry ....... enabled > plan_version ....... 2,600 > embedded_arkcmp ........ used > self_referencing_update forced_sort > IS_SQLCI ............... ON > LDAP_USERNAME > GENERATE_EXPLAIN ....... ON > ObjectUIDs ............. 4828507088152246961 > input_variables ........ %(0), %(0), %(0), %(0), %(0), %(9) > > > TUPLE_FLOW ================================ SEQ_NO 6 CHILDREN 4, 5 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_INSERT ========================== SEQ_NO 5 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_insert TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > new_rec_expr ........... (A assign %(0)), (B assign B), (C assign C), > (D assign %(0)), (E assign E), > (SYSKEY assign %(0)) > check_constraint ....... case(if_then_else((comp_decode(A) <> > comp_decode(B)) > is false, RaiseError(TRAFODION.SCH." T29x"), > return_true)) and > case(if_then_else((comp_decode(A > ) > (0 - 99)) and (C >= 'c') and > (comp_decode(A) > > 0) and (comp_decode(A) < 99) and > (comp_decode(A) > <= comp_decode(B)) and (comp_decode(A) < 10) > and > (C < 'j'), return_true, > RaiseError(TRAFODION.SCH.T29XV3))) > > > SORT ====================================== SEQ_NO 4 ONLY CHILD 3 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0.01 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > memory_quota ........... 0 MB > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > sort_type .............. full > self_referencing_update forced_sort > sort_key ............... TRAFODION.SCH.T29X.B > > > NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_DELETE ========================== SEQ_NO 2 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_delete TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > begin_key .............. (SYSKEY = SYSKEY) > end_key ................ (SYSKEY = SYSKEY) > > > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... T29X > 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.SCH.T29X > object_type ............ Trafodion > columns ................ all > begin_keys(incl) > end_keys(incl) > cache_size ........... 100 > probes ................. 1 > rows_accessed ........ 100 > column_retrieved ....... #1:1,#1:2,#1:3,#1:4,#1:6 > key_columns ............ SYSKEY > executor_predicates .... (%(9) <= B) and (A = %(9)) and (%(9) > 0) and > (%(9) > < 10) and (C >= 'c') and (C < 'j') > > --- SQL operation complete. > > > ---- This one is with HBASE_FILTER_PREDS = '1' (meaning existing filter > preds) > --- 0 row(s) updated. > >>explain update T29xv3 set r=0,d=0 where r=9; > > ------------------------------------------------------------------ PLAN > SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME > ........... NOT NAMED PLAN_ID .................. 212317472287837029 > ROWS_OUT > ................. 1 EST_TOTAL_COST ........... 0.01 STATEMENT > ................ update T29xv3 set r=0,d=0 where r=9; > > > ------------------------------------------------------------------ NODE > LISTING > ROOT ====================================== SEQ_NO 7 ONLY CHILD 6 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > statement_index ........ 0 > affinity_value ......... 0 > max_max_cardinality .... 1 > total_overflow_size .... 0.00 KB > upd_action_on_error .... xn_rollback > xn_access_mode ......... read_only > xn_autoabort_interval 0 > auto_query_retry ....... enabled > plan_version ....... 2,600 > embedded_arkcmp ........ used > self_referencing_update forced_sort > IS_SQLCI ............... ON > LDAP_USERNAME > HBASE_FILTER_PREDS ..... 1 > ObjectUIDs ............. 4828507088152246961 > input_variables ........ %(0), %(0), %(0), %(0), %(0), %(9) > > > TUPLE_FLOW ================================ SEQ_NO 6 CHILDREN 4, 5 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_INSERT ========================== SEQ_NO 5 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_insert TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > new_rec_expr ........... (A assign %(0)), (B assign B), (C assign C), > (D assign %(0)), (E assign E), > (SYSKEY assign %(0)) > check_constraint ....... case(if_then_else((comp_decode(A) <> > comp_decode(B)) > is false, RaiseError(TRAFODION.SCH." T29x"), > return_true)) and > case(if_then_else((comp_decode(A > ) > (0 - 99)) and (C >= 'c') and > (comp_decode(A) > > 0) and (comp_decode(A) < 99) and > (comp_decode(A) > <= comp_decode(B)) and (comp_decode(A) < 10) > and > (C < 'j'), return_true, > RaiseError(TRAFODION.SCH.T29XV3))) > > > SORT ====================================== SEQ_NO 4 ONLY CHILD 3 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0.01 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > memory_quota ........... 0 MB > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > sort_type .............. full > self_referencing_update forced_sort > sort_key ............... TRAFODION.SCH.T29X.B > > > NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_DELETE ========================== SEQ_NO 2 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_delete TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > begin_key .............. (SYSKEY = SYSKEY) > end_key ................ (SYSKEY = SYSKEY) > > > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... T29X > 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.SCH.T29X > object_type ............ Trafodion > columns ................ all > begin_keys(incl) > end_keys(incl) > cache_size ........... 100 > probes ................. 1 > rows_accessed ........ 100 > column_retrieved ....... #1:1,#1:2,#1:3,#1:4,#1:6 > pushed_down_rpn ........ (#1:3>=?)(#1:2=?)(#1:4>=?)(#1:4<?) > key_columns ............ SYSKEY > executor_predicates .... (%(9) <= B) and (%(9) > 0) and (%(9) < 10) and > (C > >= > 'c') and (C < 'j') > > --- SQL operation complete. > > --This one is with the new code (HBASE_FILTER_PREDS '2') > >>explain update T29xv3 set r=0,d=0 where r=9; > > ------------------------------------------------------------------ PLAN > SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME > ........... NOT NAMED PLAN_ID .................. 212317472287837029 > ROWS_OUT > ................. 1 EST_TOTAL_COST ........... 0.01 STATEMENT > ................ update T29xv3 set r=0,d=0 where r=9; > > > ------------------------------------------------------------------ NODE > LISTING > ROOT ====================================== SEQ_NO 7 ONLY CHILD 6 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > statement_index ........ 0 > affinity_value ......... 0 > max_max_cardinality .... 1 > total_overflow_size .... 0.00 KB > upd_action_on_error .... xn_rollback > xn_access_mode ......... read_only > xn_autoabort_interval 0 > auto_query_retry ....... enabled > plan_version ....... 2,600 > embedded_arkcmp ........ used > self_referencing_update forced_sort > IS_SQLCI ............... ON > LDAP_USERNAME > HBASE_FILTER_PREDS ..... 1 > ObjectUIDs ............. 4828507088152246961 > input_variables ........ %(0), %(0), %(0), %(0), %(0), %(9) > > > TUPLE_FLOW ================================ SEQ_NO 6 CHILDREN 4, 5 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_INSERT ========================== SEQ_NO 5 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_insert TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > new_rec_expr ........... (A assign %(0)), (B assign B), (C assign C), > (D assign %(0)), (E assign E), > (SYSKEY assign %(0)) > check_constraint ....... case(if_then_else((comp_decode(A) <> > comp_decode(B)) > is false, RaiseError(TRAFODION.SCH." T29x"), > return_true)) and > case(if_then_else((comp_decode(A > ) > (0 - 99)) and (C >= 'c') and > (comp_decode(A) > > 0) and (comp_decode(A) < 99) and > (comp_decode(A) > <= comp_decode(B)) and (comp_decode(A) < 10) > and > (C < 'j'), return_true, > RaiseError(TRAFODION.SCH.T29XV3))) > > > SORT ====================================== SEQ_NO 4 ONLY CHILD 3 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0.01 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > memory_quota ........... 0 MB > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > sort_type .............. full > self_referencing_update forced_sort > sort_key ............... TRAFODION.SCH.T29X.B > > > NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_DELETE ========================== SEQ_NO 2 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_delete TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > begin_key .............. (SYSKEY = SYSKEY) > end_key ................ (SYSKEY = SYSKEY) > > > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... T29X > 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.SCH.T29X > object_type ............ Trafodion > columns ................ all > begin_keys(incl) > end_keys(incl) > cache_size ........... 100 > probes ................. 1 > rows_accessed ........ 100 > column_retrieved ....... #1:1,#1:2,#1:3,#1:4,#1:6 > pushed_down_rpn ........ (#1:3>=?)(#1:2=?)(#1:4>=?)(#1:4<?) > key_columns ............ SYSKEY > executor_predicates .... (%(9) <= B) and (%(9) > 0) and (%(9) < 10) and > (C > >= > 'c') and (C < 'j') > > --- SQL operation complete. > >>cqd hbase_filter_preds '2'; > > --- SQL operation complete. > >>explain update T29xv3 set r=0,d=0 where r=9; > > ------------------------------------------------------------------ PLAN > SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME > ........... NOT NAMED PLAN_ID .................. 212317472636084985 > ROWS_OUT > ................. 1 EST_TOTAL_COST ........... 0.01 STATEMENT > ................ update T29xv3 set r=0,d=0 where r=9; > > > ------------------------------------------------------------------ NODE > LISTING > ROOT ====================================== SEQ_NO 7 ONLY CHILD 6 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > statement_index ........ 0 > affinity_value ......... 0 > max_max_cardinality .... 1 > total_overflow_size .... 0.00 KB > upd_action_on_error .... xn_rollback > xn_access_mode ......... read_only > xn_autoabort_interval 0 > auto_query_retry ....... enabled > plan_version ....... 2,600 > embedded_arkcmp ........ used > self_referencing_update forced_sort > IS_SQLCI ............... ON > LDAP_USERNAME > HBASE_FILTER_PREDS ..... 2 > GENERATE_EXPLAIN ....... ON > ObjectUIDs ............. 4828507088152246961 > input_variables ........ %(0), %(0), %(0), %(0), %(0), %(9) > > > TUPLE_FLOW ================================ SEQ_NO 6 CHILDREN 4, 5 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_INSERT ========================== SEQ_NO 5 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_insert TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > new_rec_expr ........... (A assign %(0)), (B assign B), (C assign C), > (D assign %(0)), (E assign E), > (SYSKEY assign %(0)) > check_constraint ....... case(if_then_else((comp_decode(A) <> > comp_decode(B)) > is false, RaiseError(TRAFODION.SCH." T29x"), > return_true)) and > case(if_then_else((comp_decode(A > ) > (0 - 99)) and (C >= 'c') and > (comp_decode(A) > > 0) and (comp_decode(A) < 99) and > (comp_decode(A) > <= comp_decode(B)) and (comp_decode(A) < 10) > and > (C < 'j'), return_true, > RaiseError(TRAFODION.SCH.T29XV3))) > > > SORT ====================================== SEQ_NO 4 ONLY CHILD 3 > REQUESTS_IN .............. 1 > ROWS_OUT ................. 1 > EST_OPER_COST ............ 0.01 > EST_TOTAL_COST ........... 0.01 > DESCRIPTION > memory_quota ........... 0 MB > max_card_est ........... 1 > fragment_id ............ 0 > parent_frag ............ (none) > fragment_type .......... master > sort_type .............. full > self_referencing_update forced_sort > sort_key ............... TRAFODION.SCH.T29X.B > > > NESTED_JOIN =============================== SEQ_NO 3 CHILDREN 1, 2 > 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 > join_type .............. inner > join_method ............ in-order nested > > > TRAFODION_DELETE ========================== SEQ_NO 2 NO CHILDREN > TABLE_NAME ............... TRAFODION.SCH.T29X 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 > iud_type ............... trafodion_delete TRAFODION.SCH.T29X (implements > update of clustering key or unique index key) > begin_key .............. (SYSKEY = SYSKEY) > end_key ................ (SYSKEY = SYSKEY) > > > TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN > TABLE_NAME ............... T29X > 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.SCH.T29X > object_type ............ Trafodion > columns ................ all > begin_keys(incl) > end_keys(incl) > cache_size ........... 100 > probes ................. 1 > rows_accessed ........ 100 > column_retrieved ....... #1:1,#1:3,#1:4,#1:6 > pushed_down_rpn ........ (#1:3<=.?)(#1:2=?)AND(#1:4>=.?)(#1:4<.?)ANDAND > key_columns ............ SYSKEY > executor_predicates .... (%(9) > 0) and (%(9) < 10) > > --- SQL operation complete. > > As you can see, column A is not retrieved with the new optimization, and I > thought that was it, but I hacked the code to add it back, and that was not > it. Make sense since A is assign o a constant > new_rec_expr ........... (A assign %(0)), (B assign B), (C assign C), > (D assign %(0)), (E assign E), > (SYSKEY assign %(0)) > > And performing the scan statement alone via a select statement is returning > same result with the 3 different version of HBASE_FILTER_PREDS cqd. > > > -----Original Message----- > From: Suresh Subbiah [mailto:[email protected]] > Sent: Friday, December 18, 2015 9:32 PM > To: [email protected] > Subject: Re: a pointer would be appreciated: > > Hi Eric, > > Can you please share the Explain plan we are seeing with and without the > changes? Likely they are the same. > This is currently implemented as a DELETE of matching rows followed by an > UPSERT of the updated row. The check constraint is evaluated during the > UPSERT and is supposed to fail for these parameter values. > From the results we are seeing with the new code is it likely that DELETE > did not remove any rows? > I suppose you are already well past realizing all these statements, I am > still trying to orient myself. > > Does filterPred expression on the delete side look ok? > > Thanks > Suresh > > PS showplan output (with and without) will help too. > PPS This error is likely coming from ExHbaseAccessTcb::evalConstraintExpr() > in ExHBaseIUD.cpp > > > > > On Fri, Dec 18, 2015 at 7:15 PM, Eric Owhadi <[email protected]> > wrote: > > > I am struggling with the last 2 regression issue on the pushdown V2 > > implementation. > > > > I am having issue with core/test029 > > > > I should be aiming at this: > > > > > > > > >>update T29xv3 set r=0,d=0 where r=9; > > > > > > > > *** ERROR[8105] The operation is prevented by the check option on view > > TRAFODION.SCH.T29XV3. > > > > > > > > --- 0 row(s) updated. > > > > >> -- violates v1 cascaded WCO > > > > > > > > But I am getting: > > > > >>update T29xv3 set r=0,d=0 where r=9; > > > > > > > > --- 0 row(s) updated. > > > > >> -- violates v1 cascaded WCO > > > > > > > > When I use old predicate push down, or no predicate push down I am > > getting the right behavior. When I enable my new code I fail to get > > the error message. > > > > A showplan on both would give the exact same thing. And manually > > performing the equivalent select I can see in the explain shows the > > exact same result with my new predicate mode, vs the old one. My code > > is optimized to retrieve less column than previous one, so I was > > suspecting this as being the guilty part, but hacking the code to put > > back same columns in the return set of the scan object still give me > > same problem… > > > > > > > > So I am not sure what to look next, I was thinking putting debugger on > > where this error message is coming from and back trace where the code > > diverge… Any idea where this error message coming from so I can set my > > break point somewhere close to what this code is doing? > > > > > > > > Thanks in advance for the help, > > Eric > > >
