[jira] [Commented] (TRAFODION-1575) Self-referencing update updates the column to a wrong value
[
https://issues.apache.org/jira/browse/TRAFODION-1575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15000761#comment-15000761
]
ASF GitHub Bot commented on TRAFODION-1575:
---
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-trafodion/pull/167
> Self-referencing update updates the column to a wrong value
> ---
>
> Key: TRAFODION-1575
> URL: https://issues.apache.org/jira/browse/TRAFODION-1575
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.3-incubating
> Environment: Can be reproduced on a workstation
>Reporter: David Wayne Birdsall
>Assignee: Selvaganesan Govindarajan
> Fix For: 2.0-incubating
>
>
> As shown in the following execution output, the update statement tries to
> update c2 with count(distinct c2) from the same table. While the subquery
> ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the
> correct result 3 when it is run by itself, the update statement using the
> same subquery updated the column c2 to 2, instead of 3. The updated value
> always seems to be 1 less in this case.
> Here is the execution output:
> >>create schema mytest;
> --- SQL operation complete.
> >>
> >>create table mytable (c1 char(1), c2 integer);
> --- SQL operation complete.
> >>
> >>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
> --- 3 row(s) inserted.
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 100
> B 200
> C 300
> --- 3 row(s) selected.
> >>select c from (select count(distinct c2) from mytable) dt(c);
> C
>
>3
> --- 1 row(s) selected.
> >>
> >>prepare xx from update mytable set c2 =
> +>(select c from (select count(distinct c2) from mytable) dt(c))
> +>where c2 = 100;
> --- SQL command prepared.
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> -
> 12 . 13 root x 1.00E+001
> 10 11 12 tuple_flow 1.00E+001
> . . 11 trafodion_insert MYTABLE 1.00E+000
> 9 . 10 sort 1.00E+001
> 8 4 9 hybrid_hash_join 1.00E+001
> 6 7 8 nested_join 1.00E+001
> . . 7 trafodion_delete MYTABLE 1.00E+000
> 5 . 6 sort 1.00E+001
> . . 5 trafodion_scan MYTABLE 1.00E+001
> 3 . 4 sort_scalar_aggr 1.00E+000
> 2 . 3 sort_scalar_aggr 1.00E+000
> 1 . 2 hash_groupby 2.00E+000
> . . 1 trafodion_scan MYTABLE 1.00E+002
> --- SQL operation complete.
> >>execute xx;
> --- 1 row(s) updated.
> >>
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 2
> B 200
> C 300
> --- 3 row(s) selected.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>
> The value of C2 in row A above should have been updated to 3.
> This problem was found by Wei-Shiun Tsai.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1575) Self-referencing update updates the column to a wrong value
[
https://issues.apache.org/jira/browse/TRAFODION-1575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15000549#comment-15000549
]
ASF GitHub Bot commented on TRAFODION-1575:
---
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/167#discussion_r44546995
--- Diff: core/sql/optimizer/BindRelExpr.cpp ---
@@ -10543,26 +10543,19 @@ RelExpr *Update::bindNode(BindWA *bindWA)
{
xnsfrmHbaseUpdate = TRUE;
}
- else if
((CmpCommon::getDefault(HBASE_TRANSFORM_UPDATE_TO_DELETE_INSERT) == DF_SYSTEM)
&&
- (getTableDesc()->getNATable()->hasSecondaryIndexes()))
- {
- xnsfrmHbaseUpdate = TRUE;
- }
- else if (avoidHalloween())
- {
- xnsfrmHbaseUpdate = TRUE;
- }
else if (getCheckConstraints().entries())
- {
- xnsfrmHbaseUpdate = TRUE;
- }
+ {
--- End diff --
I suppose the default attribute can be removed from nadefaults.cpp and
DefaultConstants.h also?
> Self-referencing update updates the column to a wrong value
> ---
>
> Key: TRAFODION-1575
> URL: https://issues.apache.org/jira/browse/TRAFODION-1575
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.3-incubating
> Environment: Can be reproduced on a workstation
>Reporter: David Wayne Birdsall
>Assignee: Selvaganesan Govindarajan
> Fix For: 2.0-incubating
>
>
> As shown in the following execution output, the update statement tries to
> update c2 with count(distinct c2) from the same table. While the subquery
> ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the
> correct result 3 when it is run by itself, the update statement using the
> same subquery updated the column c2 to 2, instead of 3. The updated value
> always seems to be 1 less in this case.
> Here is the execution output:
> >>create schema mytest;
> --- SQL operation complete.
> >>
> >>create table mytable (c1 char(1), c2 integer);
> --- SQL operation complete.
> >>
> >>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
> --- 3 row(s) inserted.
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 100
> B 200
> C 300
> --- 3 row(s) selected.
> >>select c from (select count(distinct c2) from mytable) dt(c);
> C
>
>3
> --- 1 row(s) selected.
> >>
> >>prepare xx from update mytable set c2 =
> +>(select c from (select count(distinct c2) from mytable) dt(c))
> +>where c2 = 100;
> --- SQL command prepared.
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> -
> 12 . 13 root x 1.00E+001
> 10 11 12 tuple_flow 1.00E+001
> . . 11 trafodion_insert MYTABLE 1.00E+000
> 9 . 10 sort 1.00E+001
> 8 4 9 hybrid_hash_join 1.00E+001
> 6 7 8 nested_join 1.00E+001
> . . 7 trafodion_delete MYTABLE 1.00E+000
> 5 . 6 sort 1.00E+001
> . . 5 trafodion_scan MYTABLE 1.00E+001
> 3 . 4 sort_scalar_aggr 1.00E+000
> 2 . 3 sort_scalar_aggr 1.00E+000
> 1 . 2 hash_groupby 2.00E+000
> . . 1 trafodion_scan MYTABLE 1.00E+002
> --- SQL operation complete.
> >>execute xx;
> --- 1 row(s) updated.
> >>
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 2
> B 200
> C 300
> --- 3 row(s) selected.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>
> The value of C2 in row A above should have been updated to 3.
> This problem was found by Wei-Shiun Tsai.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1575) Self-referencing update updates the column to a wrong value
[
https://issues.apache.org/jira/browse/TRAFODION-1575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14998962#comment-14998962
]
ASF GitHub Bot commented on TRAFODION-1575:
---
GitHub user selvaganesang opened a pull request:
https://github.com/apache/incubator-trafodion/pull/167
[TRAFODION-1575] Avoid transforming update into delete and insert
commands to improve performance of update statements. When a table has
check constraint the update command is transformed into insert and delete.
Added code to evaluate constraint expressions in all TCBs so that
constraint checking is done correctly. But, the subtask [TRAFODION-1610]
needs to implemented before this change is exercised.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/selvaganesang/incubator-trafodion workspace
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/167.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #167
commit c69be87dde510728fafdbe9520df2602573e9522
Author: selvaganesang
Date: 2015-11-10T17:16:46Z
[TRAFODION-1575] Avoid transforming update into delete and insert
commands to improve performance of update statements. When a table has
check constraint the update command is transformed into insert and delete.
Added code to evaluate constraint expressions in all TCBs so that
constraint checking is done correctly. But, the subtask [TRAFODION-1610]
needs to implemented before this change is exercised.
> Self-referencing update updates the column to a wrong value
> ---
>
> Key: TRAFODION-1575
> URL: https://issues.apache.org/jira/browse/TRAFODION-1575
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.3-incubating
> Environment: Can be reproduced on a workstation
>Reporter: David Wayne Birdsall
>Assignee: Selvaganesan Govindarajan
> Fix For: 2.0-incubating
>
>
> As shown in the following execution output, the update statement tries to
> update c2 with count(distinct c2) from the same table. While the subquery
> ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the
> correct result 3 when it is run by itself, the update statement using the
> same subquery updated the column c2 to 2, instead of 3. The updated value
> always seems to be 1 less in this case.
> Here is the execution output:
> >>create schema mytest;
> --- SQL operation complete.
> >>
> >>create table mytable (c1 char(1), c2 integer);
> --- SQL operation complete.
> >>
> >>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
> --- 3 row(s) inserted.
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 100
> B 200
> C 300
> --- 3 row(s) selected.
> >>select c from (select count(distinct c2) from mytable) dt(c);
> C
>
>3
> --- 1 row(s) selected.
> >>
> >>prepare xx from update mytable set c2 =
> +>(select c from (select count(distinct c2) from mytable) dt(c))
> +>where c2 = 100;
> --- SQL command prepared.
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> -
> 12 . 13 root x 1.00E+001
> 10 11 12 tuple_flow 1.00E+001
> . . 11 trafodion_insert MYTABLE 1.00E+000
> 9 . 10 sort 1.00E+001
> 8 4 9 hybrid_hash_join 1.00E+001
> 6 7 8 nested_join 1.00E+001
> . . 7 trafodion_delete MYTABLE 1.00E+000
> 5 . 6 sort 1.00E+001
> . . 5 trafodion_scan MYTABLE 1.00E+001
> 3 . 4 sort_scalar_aggr 1.00E+000
> 2 . 3 sort_scalar_aggr 1.00E+000
> 1 . 2 hash_groupby 2.00E+000
> . . 1 trafodion_scan MYTABLE 1.00E+002
> --- SQL operation complete.
> >>execute xx;
> --- 1 row(s) updated.
> >>
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 2
> B 200
> C 300
> --- 3 row(s) selected.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>
> The value of C2 in row A above should have been updated to 3.
> This problem was found by Wei-Shiun Tsai.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1575) Self-referencing update updates the column to a wrong value
[
https://issues.apache.org/jira/browse/TRAFODION-1575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14997846#comment-14997846
]
Selvaganesan Govindarajan commented on TRAFODION-1575:
--
Currently self-referencing update or the table having check constraint is
updated, the update command is transformed into delete and insert. This
transformation has problems that it can produce wrong result. Also, it is
important that the transformation is avoided to improve the performance of
update commands in such a case. This process involves two steps:
Ensure that the constraint expresssion is evaluated in all the operators
(insert, update and merge) and to ensure that the constraint expression is
generated correctly. This involves changes in both in executor and compiler
component of Trafodion. Creating a subwork jira to track tehe compiler work
> Self-referencing update updates the column to a wrong value
> ---
>
> Key: TRAFODION-1575
> URL: https://issues.apache.org/jira/browse/TRAFODION-1575
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.3-incubating
> Environment: Can be reproduced on a workstation
>Reporter: David Wayne Birdsall
>Assignee: Selvaganesan Govindarajan
> Fix For: 2.0-incubating
>
>
> As shown in the following execution output, the update statement tries to
> update c2 with count(distinct c2) from the same table. While the subquery
> ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the
> correct result 3 when it is run by itself, the update statement using the
> same subquery updated the column c2 to 2, instead of 3. The updated value
> always seems to be 1 less in this case.
> Here is the execution output:
> >>create schema mytest;
> --- SQL operation complete.
> >>
> >>create table mytable (c1 char(1), c2 integer);
> --- SQL operation complete.
> >>
> >>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
> --- 3 row(s) inserted.
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 100
> B 200
> C 300
> --- 3 row(s) selected.
> >>select c from (select count(distinct c2) from mytable) dt(c);
> C
>
>3
> --- 1 row(s) selected.
> >>
> >>prepare xx from update mytable set c2 =
> +>(select c from (select count(distinct c2) from mytable) dt(c))
> +>where c2 = 100;
> --- SQL command prepared.
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> -
> 12 . 13 root x 1.00E+001
> 10 11 12 tuple_flow 1.00E+001
> . . 11 trafodion_insert MYTABLE 1.00E+000
> 9 . 10 sort 1.00E+001
> 8 4 9 hybrid_hash_join 1.00E+001
> 6 7 8 nested_join 1.00E+001
> . . 7 trafodion_delete MYTABLE 1.00E+000
> 5 . 6 sort 1.00E+001
> . . 5 trafodion_scan MYTABLE 1.00E+001
> 3 . 4 sort_scalar_aggr 1.00E+000
> 2 . 3 sort_scalar_aggr 1.00E+000
> 1 . 2 hash_groupby 2.00E+000
> . . 1 trafodion_scan MYTABLE 1.00E+002
> --- SQL operation complete.
> >>execute xx;
> --- 1 row(s) updated.
> >>
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 2
> B 200
> C 300
> --- 3 row(s) selected.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>
> The value of C2 in row A above should have been updated to 3.
> This problem was found by Wei-Shiun Tsai.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1575) Self-referencing update updates the column to a wrong value
[
https://issues.apache.org/jira/browse/TRAFODION-1575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14986153#comment-14986153
]
David Wayne Birdsall commented on TRAFODION-1575:
-
The solution seems to be to look for subquery expressions referencing the
target table in the SET clause of the UPDATE statement, and if any are found,
to create a RelRoot node between the Scan and the Delete node and move the
subquery expression into the select list of this new node. This will cause the
subquery to be evaluated before the Delete node.
> Self-referencing update updates the column to a wrong value
> ---
>
> Key: TRAFODION-1575
> URL: https://issues.apache.org/jira/browse/TRAFODION-1575
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.3-incubating
> Environment: Can be reproduced on a workstation
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.0-incubating
>
>
> As shown in the following execution output, the update statement tries to
> update c2 with count(distinct c2) from the same table. While the subquery
> ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the
> correct result 3 when it is run by itself, the update statement using the
> same subquery updated the column c2 to 2, instead of 3. The updated value
> always seems to be 1 less in this case.
> Here is the execution output:
> >>create schema mytest;
> --- SQL operation complete.
> >>
> >>create table mytable (c1 char(1), c2 integer);
> --- SQL operation complete.
> >>
> >>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
> --- 3 row(s) inserted.
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 100
> B 200
> C 300
> --- 3 row(s) selected.
> >>select c from (select count(distinct c2) from mytable) dt(c);
> C
>
>3
> --- 1 row(s) selected.
> >>
> >>prepare xx from update mytable set c2 =
> +>(select c from (select count(distinct c2) from mytable) dt(c))
> +>where c2 = 100;
> --- SQL command prepared.
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> -
> 12 . 13 root x 1.00E+001
> 10 11 12 tuple_flow 1.00E+001
> . . 11 trafodion_insert MYTABLE 1.00E+000
> 9 . 10 sort 1.00E+001
> 8 4 9 hybrid_hash_join 1.00E+001
> 6 7 8 nested_join 1.00E+001
> . . 7 trafodion_delete MYTABLE 1.00E+000
> 5 . 6 sort 1.00E+001
> . . 5 trafodion_scan MYTABLE 1.00E+001
> 3 . 4 sort_scalar_aggr 1.00E+000
> 2 . 3 sort_scalar_aggr 1.00E+000
> 1 . 2 hash_groupby 2.00E+000
> . . 1 trafodion_scan MYTABLE 1.00E+002
> --- SQL operation complete.
> >>execute xx;
> --- 1 row(s) updated.
> >>
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 2
> B 200
> C 300
> --- 3 row(s) selected.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>
> The value of C2 in row A above should have been updated to 3.
> This problem was found by Wei-Shiun Tsai.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-1575) Self-referencing update updates the column to a wrong value
[
https://issues.apache.org/jira/browse/TRAFODION-1575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14986149#comment-14986149
]
David Wayne Birdsall commented on TRAFODION-1575:
-
The problem is that the subquery is being done as a child of the
hybrid_hash_join node, which is above the delete node. So the subquery in
effect races the delete. In this case, it loses the race so it sees a changed
copy of the source table, MYTABLE. The correct semantics would be to evaluate
the subquery before any delete is done.
This problem is a latent bug which became visible with the DELETE costing
changes in JIRA TRAFODION-25. Formerly a different plan was chosen, and the
race turned out more fortunately.
To see this, one can turn off the DELETE costing code using CQD
HBASE_DELETE_COSTING 'OFF', to get the stub costing that was formerly in place.
> Self-referencing update updates the column to a wrong value
> ---
>
> Key: TRAFODION-1575
> URL: https://issues.apache.org/jira/browse/TRAFODION-1575
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.3-incubating
> Environment: Can be reproduced on a workstation
>Reporter: David Wayne Birdsall
>Assignee: David Wayne Birdsall
> Fix For: 2.0-incubating
>
>
> As shown in the following execution output, the update statement tries to
> update c2 with count(distinct c2) from the same table. While the subquery
> ‘select c from (select count(distinct c2) from mytable) dt(c)’ returns the
> correct result 3 when it is run by itself, the update statement using the
> same subquery updated the column c2 to 2, instead of 3. The updated value
> always seems to be 1 less in this case.
> Here is the execution output:
> >>create schema mytest;
> --- SQL operation complete.
> >>
> >>create table mytable (c1 char(1), c2 integer);
> --- SQL operation complete.
> >>
> >>insert into mytable values ('A', 100), ('B', 200), ('C', 300);
> --- 3 row(s) inserted.
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 100
> B 200
> C 300
> --- 3 row(s) selected.
> >>select c from (select count(distinct c2) from mytable) dt(c);
> C
>
>3
> --- 1 row(s) selected.
> >>
> >>prepare xx from update mytable set c2 =
> +>(select c from (select count(distinct c2) from mytable) dt(c))
> +>where c2 = 100;
> --- SQL command prepared.
> >>explain options 'f' xx;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> -
> 12 . 13 root x 1.00E+001
> 10 11 12 tuple_flow 1.00E+001
> . . 11 trafodion_insert MYTABLE 1.00E+000
> 9 . 10 sort 1.00E+001
> 8 4 9 hybrid_hash_join 1.00E+001
> 6 7 8 nested_join 1.00E+001
> . . 7 trafodion_delete MYTABLE 1.00E+000
> 5 . 6 sort 1.00E+001
> . . 5 trafodion_scan MYTABLE 1.00E+001
> 3 . 4 sort_scalar_aggr 1.00E+000
> 2 . 3 sort_scalar_aggr 1.00E+000
> 1 . 2 hash_groupby 2.00E+000
> . . 1 trafodion_scan MYTABLE 1.00E+002
> --- SQL operation complete.
> >>execute xx;
> --- 1 row(s) updated.
> >>
> >>select * from mytable order by 1;
> C1 C2
> -- ---
> A 2
> B 200
> C 300
> --- 3 row(s) selected.
> >>
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>
> The value of C2 in row A above should have been updated to 3.
> This problem was found by Wei-Shiun Tsai.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
