[
https://issues.apache.org/jira/browse/TRAFODION-3066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16469664#comment-16469664
]
ASF GitHub Bot commented on TRAFODION-3066:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1558
[TRAFODION-3066] Fix bug that causes 2053 warning on deeply nested
subqueries
When a query has at least three levels of subquery, and when the subqueries
are transformed from semi-joins to inner-joins, we may see an Optimizer
assertion failure warning (2053) when preparing the query. This bug has been
there for a long time but is more likely now due to the changes in
https://github.com/apache/trafodion/pull/1530.
The problem is that when transforming a semi-join to an inner-join + group
by, the new GroupByAgg node was getting its GroupAttributes as a copy of the
child node. If the child node happened to also be a Join (as would happen with
nested subqueries), the numJoinedTables_ field for the GroupByAgg's
GroupAttributes would remain 2 or more. If during optimization, the
GroupByOnJoinRule fired, pushing the GroupByAgg down, we end up with another
Join expression in the same group. But having numJoinedTables_ being 2 or more
in that Group would allow the LeftShiftJoinRule to fire, with the result that
we might get multiple GroupBy criteria in the same join backbone. The
Analysis.cpp module does not expect this expansion of the join back bone, and
raises the assertion, which results in the 2053 warning.
The fix is that when Join::transformSemiJoin creates the new GroupByAgg
node, it must reset numJoinedTables_ in its new GroupAttributes object to 1.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion Trafodion3066
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1558.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 #1558
----
commit 3d54d5ac8ab797335e32bd6bbefff4150452bd15
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-05-09T23:15:17Z
[TRAFODION-3066] Fix bug that causes 2053 warning on deeply nested
subqueries
----
> Deeply nested subqueries may have warning 2053 at compile time
> --------------------------------------------------------------
>
> Key: TRAFODION-3066
> URL: https://issues.apache.org/jira/browse/TRAFODION-3066
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Attachments: ReproJira.sql
>
>
> The following sqlci session demonstrates the problem. A query with three
> levels of nested subqueries gets a 2053 warning when compiled. An equivalent
> query (where the subqueries have been rewritten as joins + group bys) does
> not show this problem. The attached script is the one used to create this
> sqlci session.
> {quote}>>obey reproJira.sql;
> >>?section setup
> >>
> >>drop schema if exists mytest cascade;
> --- SQL operation complete.
> >>create schema mytest;
> --- SQL operation complete.
> >>set schema mytest;
> --- SQL operation complete.
> >>
> >>create table t1 (pic_x_1 char(1) not null not droppable) no partition;
> --- SQL operation complete.
> >>showddl t1;
> CREATE TABLE TRAFODION.MYTEST.T1
> (
> PIC_X_1 CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> )
> ATTRIBUTES ALIGNED FORMAT
> ;
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.MYTEST.T1 TO
> DB__ROOT WITH GRANT OPTION;
> --- SQL operation complete.
> >>
> >>insert into t1 values ('B'), ('C'), ('B'), ('Q'), ('B'), ('C'), ('Q'),
> >>('Q');
> --- 8 row(s) inserted.
> >>update statistics for table t1 on every column;
> --- SQL operation complete.
> >>
> >>create table t2 like t1;
> --- SQL operation complete.
> >>insert into t2 select * From t1;
> --- 8 row(s) inserted.
> >>update statistics for table t2 on every column;
> --- SQL operation complete.
> >>
> >>create table t3 like t1;
> --- SQL operation complete.
> >>insert into t3 select * From t1;
> --- 8 row(s) inserted.
> >>update statistics for table t3 on every column;
> --- SQL operation complete.
> >>
> >>create table t4 like t1;
> --- SQL operation complete.
> >>insert into t4 select * From t1;
> --- 8 row(s) inserted.
> >>update statistics for table t4 on every column;
> --- SQL operation complete.
> >>
> >>?section testit
> >>
> >>-- reproduces problem
> >>
> >>set schema mytest;
> --- SQL operation complete.
> >>
> >>prepare s1 from
> +>--display
> +>select * from mytest.t1
> +>where pic_x_1 in (select pic_x_1 from mytest.t2
> +>where pic_x_1 in (select pic_x_1 from mytest.t3
> +>where pic_x_1 in (select pic_x_1 from mytest.t4
> +>))) ;
> *** WARNING[2053] Optimizer pass two assertion failure ((gb_ == NULL_CA_ID)
> || (gb_ == othersGB)) in file ../optimizer/Analyzer.cpp at line 4907.
> Attempting to recover and produce a plan.
> --- SQL command prepared.
> >>
> >>?section rewrite
> >>
> >>set schema mytest;
> {quote}
> --- SQL operation complete.
> >>
> >>-- a rewrite of the query, equivalent to what the semi-join
> >>-- to inner-join transformation accomplishes
> >>
> >>prepare s2 from
> +>select t1.* from mytest.t1 join
> +> (select distinct t2.pic_x_1 from mytest.t2 join
> +> (select distinct t3.pic_x_1 from mytest.t3 join
> +> (select distinct t4.pic_x_1 from mytest.t4) as t4
> +> on t3.pic_x_1 = t4.pic_x_1) as t3
> +> on t2.pic_x_1 = t3.pic_x_1) as t2
> +>on t1.pic_x_1 = t2.pic_x_1;
> --- SQL command prepared.
> >>
> >>
> >>exit;
> End of MXCI Session
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
