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 Mon, 21 Dec 2015 17:41:43 GMT
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