trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Owhadi <eric.owh...@esgyn.com>
Subject RE: a pointer would be appreciated:
Date Tue, 22 Dec 2015 15:44:49 GMT
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
> >
>

Mime
View raw message