[TRAFODION-3066] Deeply nested subqueries may have warning 2053 at compile time

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 09/May/18 21:53
• Updated: 13/Aug/18 22:34
• Resolved: 11/May/18 19:33

Description

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.

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

— 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

Issue Links

• links to: GitHub Pull Request #1558