Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-3066

Deeply nested subqueries may have warning 2053 at compile time

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.3
    • Fix Version/s: 2.3
    • Component/s: sql-cmp
    • Labels:
      None

      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

        Attachments

        1. ReproJira.sql
          1 kB
          Dave Birdsall

          Issue Links

            Activity

              People

              • Assignee:
                dbirdsall Dave Birdsall
                Reporter:
                dbirdsall Dave Birdsall
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: