[
https://issues.apache.org/jira/browse/TRAFODION-3066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16472521#comment-16472521
]
ASF GitHub Bot commented on TRAFODION-3066:
-------------------------------------------
Github user asfgit closed the pull request at:
https://github.com/apache/trafodion/pull/1558
> 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)
