Thanks for the help,
I found the root cause... a bug in my filter code :-).
This regression test suite is really great ...
Eric
-----Original Message-----
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Tuesday, December 22, 2015 3:12 AM
To: dev@trafodion.incubator.apache.org
Subject: Re: a pointer would be appreciated:
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 <eric.owhadi@esgyn.com> 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:suresh.subbiah60@gmail.com]
> Sent: Monday, December 21, 2015 11:38 AM
> To: dev@trafodion.incubator.apache.org
> 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
> <eric.owhadi@esgyn.com> </div><div>Date:12/21/2015 9:50 AM
> (GMT-06:00)
> </div><div>To: dev@trafodion.incubator.apache.org </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:suresh.subbiah60@gmail.com]
> Sent: Friday, December 18, 2015 9:32 PM
> To: dev@trafodion.incubator.apache.org
> 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 <eric.owhadi@esgyn.com>
> 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
> >
>
|