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
>
|