Derby
  1. Derby
  2. DERBY-4695

Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None

      Description

      While looking into derby-4679, I also looked at the query in
      derby-2526 to validate that my changes also worked for that variant of
      the problem.

      During inspection of the query trees after the bind phase, I found one
      instance in which the pair (tablenumber, columnnumber) of a column
      reference was wrong. Although it did not seem to impact the query
      result, I note it here as as something we should probably investiate
      as it may be a symptom an underlying problem, or a potential for
      future problems.

      The query looks like this:

      select b3.* from b3 join bvw on (b3.c8 = bvw.c5) join b4 on (bvw.c1 = b4.c7) where b4.c4 = 42"

      and the underlying DDL is this:

      create table b2 (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
      create table b4 (c7 int, c4 int, c6 int);
      create table b3 (c8 int, c9 int, c5 int, c6 int);
      create table b (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
      create view bvw (c5, c1 ,c2 ,c3 ,c4) as
      select c5, c1 ,c2 ,c3 ,c4 from b2 union
      select c5, c1 ,c2 ,c3 ,c4 from b;
      create view bvw2 (c1 ,c2 ,c3 ,c4 ,c5) as

      After the bind phase, the join clause "bvw.c1 = b4.c7" has the
      following entry for the column reference bvw.C1:

      tableNumber: 1
      columnNumber: 6

      The problem is that the node with tablenumber 1 is bvw, which is the
      view with the subquery for the union, which has only 5 resulting
      columns, so 6 must be wrong. Although both the view participant tables
      (b, b2) both have six column, the view does not. In any case, C1 is
      column 2 in the view and column 2 in the two union selects from both b
      and b2.

      C1 is however, column 6 of the join node resulting from "select b3.*
      from b3 join bvw on (b3.c8 = bvw.c5)", but the correct table number for
      that would be 5, not 1.

      So, it would seem the table number has been bound to the bvw view's
      result set, but the column number has been bound to the innermost join
      node's result set. This looks worrying to me.

      See derby.log attached for the full dump of the query tree after the
      bind phase.

      sourceResultSet:
      org.apache.derby.impl.sql.compile.FromSubquery@12789d2
      correlation Name: BVW
      null
      tableNumber 1 <------------------------------------------- Note!
      level 0
      resultSetNumber: 0
      referencedTableMap: null
      statementResultSet: false
      resultColumns:
      org.apache.derby.impl.sql.compile.ResultColumnList@c943d1
      indexRow: false
      orderBySelect: 0
      [0]:
      org.apache.derby.impl.sql.compile.ResultColumn@d3c6a3
      **truncated**
      [1]:
      org.apache.derby.impl.sql.compile.ResultColumn@18352d8
      exposedName: C1
      name: C1
      tableName: null
      isDefaultColumn: false
      wasDefaultColumn: false
      isNameGenerated: false
      sourceTableName: B2
      type: INTEGER
      columnDescriptor: null
      isGenerated: false
      isGeneratedForUnmatchedColumnInInsert: false
      isGroupingColumn: false
      isReferenced: true
      isRedundant: false
      virtualColumnId: 2
      resultSetNumber: -1
      dataTypeServices: INTEGER
      expression:
      org.apache.derby.impl.sql.compile.VirtualColumnNode@b40ec4
      dataTypeServices: null
      sourceColumn:
      org.apache.derby.impl.sql.compile.ResultColumn@1d95da8
      **truncated**
      sourceResultSet:
      org.apache.derby.impl.sql.compile.UnionNode@14d7745
      **truncated**
      [2]:
      org.apache.derby.impl.sql.compile.ResultColumn@13576a2
      exposedName: C2
      name: C2
      tableName: null
      isDefaultColumn: false
      wasDefaultColumn: false
      isNameGenerated: false
      sourceTableName: B2
      type: INTEGER
      columnDescriptor: null
      isGenerated: false
      isGeneratedForUnmatchedColumnInInsert: false
      isGroupingColumn: false
      isReferenced: true
      isRedundant: false
      virtualColumnId: 3
      resultSetNumber: -1
      dataTypeServices: INTEGER
      expression:
      org.apache.derby.impl.sql.compile.VirtualColumnNode@ff8c74
      dataTypeServices: null
      sourceColumn:
      org.apache.derby.impl.sql.compile.ResultColumn@61736e
      **truncated**
      sourceResultSet:
      org.apache.derby.impl.sql.compile.UnionNode@14d7745
      **truncated**
      [3]:
      org.apache.derby.impl.sql.compile.ResultColumn@15e2ccd
      exposedName: C3
      name: C3
      tableName: null
      isDefaultColumn: false
      wasDefaultColumn: false
      isNameGenerated: false
      sourceTableName: B2
      type: CHAR(1)
      columnDescriptor: null
      isGenerated: false
      isGeneratedForUnmatchedColumnInInsert: false
      isGroupingColumn: false
      isReferenced: true
      isRedundant: false
      virtualColumnId: 4
      resultSetNumber: -1
      dataTypeServices: CHAR(1)
      expression:
      org.apache.derby.impl.sql.compile.VirtualColumnNode@1cf7491
      dataTypeServices: null
      sourceColumn:
      org.apache.derby.impl.sql.compile.ResultColumn@11946c2
      **truncated**
      sourceResultSet:
      org.apache.derby.impl.sql.compile.UnionNode@14d7745
      **truncated**
      [4]: <----------------------------------------- highest column number is 5 (index is zero-based)
      org.apache.derby.impl.sql.compile.ResultColumn@edf730
      exposedName: C4
      name: C4
      tableName: null
      isDefaultColumn: false
      wasDefaultColumn: false
      isNameGenerated: false
      sourceTableName: B2
      type: INTEGER
      columnDescriptor: null
      isGenerated: false
      isGeneratedForUnmatchedColumnInInsert: false
      isGroupingColumn: false
      isReferenced: true
      isRedundant: false
      virtualColumnId: 5
      resultSetNumber: -1
      dataTypeServices: INTEGER
      expression:
      org.apache.derby.impl.sql.compile.VirtualColumnNode@ff94b1
      dataTypeServices: null
      sourceColumn:
      org.apache.derby.impl.sql.compile.ResultColumn@17a4989
      **truncated**
      sourceResultSet:
      org.apache.derby.impl.sql.compile.UnionNode@14d7745
      **truncated**
      subquery:
      org.apache.derby.impl.sql.compile.UnionNode@14d7745
      **truncated**

      1. why-loj-needs-2nd-bind.txt
        67 kB
        Dag H. Wanvik
      2. why-ij-can-do-without-2nd-bind.txt
        50 kB
        Dag H. Wanvik
      3. trace-remapping.diff
        0.8 kB
        Dag H. Wanvik
      4. query-b-before.log
        277 kB
        Dag H. Wanvik
      5. query-b-after.log
        277 kB
        Dag H. Wanvik
      6. query-a-before.log
        144 kB
        Dag H. Wanvik
      7. query-a-after.log
        144 kB
        Dag H. Wanvik
      8. derby-4695-fixcolumnno-1b.stat
        0.5 kB
        Dag H. Wanvik
      9. derby-4695-fixcolumnno-1b.diff
        8 kB
        Dag H. Wanvik
      10. derby-4695-fixcolumnno-1a.stat
        0.5 kB
        Dag H. Wanvik
      11. derby-4695-fixcolumnno-1a.diff
        8 kB
        Dag H. Wanvik
      12. derby.log
        79 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 12512785 ] Default workflow, editable Closed status [ 12801986 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-5933 [ DERBY-5933 ]
          Rick Hillegas made changes -
          Affects Version/s 10.7.1.1 [ 12315564 ]
          Affects Version/s 10.7.1.0 [ 12314971 ]
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Fix Version/s 10.7.1.0 [ 12314971 ]
          Dag H. Wanvik made changes -
          Issue Type Bug [ 1 ] Improvement [ 4 ]
          Dag H. Wanvik made changes -
          Fix Version/s 10.7.0.0 [ 12314971 ]
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          Status Open [ 1 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Dag H. Wanvik made changes -
          Attachment derby-4695-fixcolumnno-1b.diff [ 12452822 ]
          Attachment derby-4695-fixcolumnno-1b.stat [ 12452823 ]
          Dag H. Wanvik made changes -
          Attachment trace-remapping.diff [ 12448239 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          Attachment query-b-after.log [ 12448237 ]
          Dag H. Wanvik made changes -
          Attachment query-a-before.log [ 12448234 ]
          Attachment query-a-after.log [ 12448235 ]
          Attachment query-b-before.log [ 12448236 ]
          Dag H. Wanvik made changes -
          Attachment derby-4695-fixcolumnno-1a.diff [ 12448232 ]
          Attachment derby-4695-fixcolumnno-1a.stat [ 12448233 ]
          Dag H. Wanvik made changes -
          Attachment why-loj-needs-2nd-bind.txt [ 12447498 ]
          Attachment why-ij-can-do-without-2nd-bind.txt [ 12447499 ]
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Dag H. Wanvik made changes -
          Description While looking into derby-4679, I also looked at the query in
          derby-2526 to validate that my changes also worked for that variant of
          the problem.

          During inspection of the query trees after the bind phase, I found one
          instance in which the pair (tablenumber, columnnumber) of a column
          reference was wrong. Although it did not seem to impact the query
          result, I note it here as as something we should probably investiate
          as it may be a symptom an underlying problem, or a potential for
          future problems.

          The query looks like this:

          select b3.* from b3 join bvw on (b3.c8 = bvw.c5) join b4 on (bvw.c1 = b4.c7) where b4.c4 = 42"

          and the underlying DDL is this:

          create table b2 (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
          create table b4 (c7 int, c4 int, c6 int);
          create table b3 (c8 int, c9 int, c5 int, c6 int);
          create table b (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
          create view bvw (c5, c1 ,c2 ,c3 ,c4) as
          select c5, c1 ,c2 ,c3 ,c4 from b2 union
          select c5, c1 ,c2 ,c3 ,c4 from b;
          create view bvw2 (c1 ,c2 ,c3 ,c4 ,c5) as

          After the bind phase, the join clause "bvw.c1 = b4.c7" has the
          following entry for the column reference bvw.C1:
                    
                    tableNumber: 1
                    columnNumber: 6

          The problem is that the node with tablenumber 1 is bvw, which is the
          view with the subquery for the union, which has only 5 resulting
          columns, so 6 must be wrong. Although both the view participant tables
          (b, b2) both have six column, the view does not. In any case, C1 is
          column 2 in the view and column 2 in both b and b2.

          C1 is however, column 6 of the join node resulting from "select b3.*
          from b3 join bvw on (b3.c8 = bvw.c5)", but the correct table number for
          that would be 5, not 1.

          So, it would seem the table number has been bound to the bvw view's
          result set, but the column number has been bound to the innermost join
          node's result set. This looks worrying to me.

          See derby.log attached for the full dump of the query tree after the
          bind phase.

          sourceResultSet:
              org.apache.derby.impl.sql.compile.FromSubquery@12789d2
              correlation Name: BVW
              null
              tableNumber 1 <------------------------------------------- Note!
              level 0
              resultSetNumber: 0
              referencedTableMap: null
              statementResultSet: false
              resultColumns:
                  org.apache.derby.impl.sql.compile.ResultColumnList@c943d1
                  indexRow: false
                  orderBySelect: 0
                  [0]:
                  org.apache.derby.impl.sql.compile.ResultColumn@d3c6a3
                  ***truncated***
                  [1]:
                  org.apache.derby.impl.sql.compile.ResultColumn@18352d8
                  exposedName: C1
                  name: C1
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: INTEGER
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 2
                  resultSetNumber: -1
                  dataTypeServices: INTEGER
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@b40ec4
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@1d95da8
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
                  [2]:
                  org.apache.derby.impl.sql.compile.ResultColumn@13576a2
                  exposedName: C2
                  name: C2
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: INTEGER
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 3
                  resultSetNumber: -1
                  dataTypeServices: INTEGER
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@ff8c74
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@61736e
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
                  [3]:
                  org.apache.derby.impl.sql.compile.ResultColumn@15e2ccd
                  exposedName: C3
                  name: C3
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: CHAR(1)
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 4
                  resultSetNumber: -1
                  dataTypeServices: CHAR(1)
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@1cf7491
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@11946c2
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
                  [4]: <----------------------------------------- highest column number is 5 (index is zero-based)
                  org.apache.derby.impl.sql.compile.ResultColumn@edf730
                  exposedName: C4
                  name: C4
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: INTEGER
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 5
                  resultSetNumber: -1
                  dataTypeServices: INTEGER
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@ff94b1
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@17a4989
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
              subquery:
                  org.apache.derby.impl.sql.compile.UnionNode@14d7745
                  ***truncated***
          While looking into derby-4679, I also looked at the query in
          derby-2526 to validate that my changes also worked for that variant of
          the problem.

          During inspection of the query trees after the bind phase, I found one
          instance in which the pair (tablenumber, columnnumber) of a column
          reference was wrong. Although it did not seem to impact the query
          result, I note it here as as something we should probably investiate
          as it may be a symptom an underlying problem, or a potential for
          future problems.

          The query looks like this:

          select b3.* from b3 join bvw on (b3.c8 = bvw.c5) join b4 on (bvw.c1 = b4.c7) where b4.c4 = 42"

          and the underlying DDL is this:

          create table b2 (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
          create table b4 (c7 int, c4 int, c6 int);
          create table b3 (c8 int, c9 int, c5 int, c6 int);
          create table b (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int);
          create view bvw (c5, c1 ,c2 ,c3 ,c4) as
          select c5, c1 ,c2 ,c3 ,c4 from b2 union
          select c5, c1 ,c2 ,c3 ,c4 from b;
          create view bvw2 (c1 ,c2 ,c3 ,c4 ,c5) as

          After the bind phase, the join clause "bvw.c1 = b4.c7" has the
          following entry for the column reference bvw.C1:
                    
                    tableNumber: 1
                    columnNumber: 6

          The problem is that the node with tablenumber 1 is bvw, which is the
          view with the subquery for the union, which has only 5 resulting
          columns, so 6 must be wrong. Although both the view participant tables
          (b, b2) both have six column, the view does not. In any case, C1 is
          column 2 in the view and column 2 in the two union selects from both b
          and b2.

          C1 is however, column 6 of the join node resulting from "select b3.*
          from b3 join bvw on (b3.c8 = bvw.c5)", but the correct table number for
          that would be 5, not 1.

          So, it would seem the table number has been bound to the bvw view's
          result set, but the column number has been bound to the innermost join
          node's result set. This looks worrying to me.

          See derby.log attached for the full dump of the query tree after the
          bind phase.

          sourceResultSet:
              org.apache.derby.impl.sql.compile.FromSubquery@12789d2
              correlation Name: BVW
              null
              tableNumber 1 <------------------------------------------- Note!
              level 0
              resultSetNumber: 0
              referencedTableMap: null
              statementResultSet: false
              resultColumns:
                  org.apache.derby.impl.sql.compile.ResultColumnList@c943d1
                  indexRow: false
                  orderBySelect: 0
                  [0]:
                  org.apache.derby.impl.sql.compile.ResultColumn@d3c6a3
                  ***truncated***
                  [1]:
                  org.apache.derby.impl.sql.compile.ResultColumn@18352d8
                  exposedName: C1
                  name: C1
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: INTEGER
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 2
                  resultSetNumber: -1
                  dataTypeServices: INTEGER
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@b40ec4
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@1d95da8
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
                  [2]:
                  org.apache.derby.impl.sql.compile.ResultColumn@13576a2
                  exposedName: C2
                  name: C2
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: INTEGER
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 3
                  resultSetNumber: -1
                  dataTypeServices: INTEGER
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@ff8c74
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@61736e
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
                  [3]:
                  org.apache.derby.impl.sql.compile.ResultColumn@15e2ccd
                  exposedName: C3
                  name: C3
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: CHAR(1)
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 4
                  resultSetNumber: -1
                  dataTypeServices: CHAR(1)
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@1cf7491
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@11946c2
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
                  [4]: <----------------------------------------- highest column number is 5 (index is zero-based)
                  org.apache.derby.impl.sql.compile.ResultColumn@edf730
                  exposedName: C4
                  name: C4
                  tableName: null
                  isDefaultColumn: false
                  wasDefaultColumn: false
                  isNameGenerated: false
                  sourceTableName: B2
                  type: INTEGER
                  columnDescriptor: null
                  isGenerated: false
                  isGeneratedForUnmatchedColumnInInsert: false
                  isGroupingColumn: false
                  isReferenced: true
                  isRedundant: false
                  virtualColumnId: 5
                  resultSetNumber: -1
                  dataTypeServices: INTEGER
                  expression:
                      org.apache.derby.impl.sql.compile.VirtualColumnNode@ff94b1
                      dataTypeServices: null
                      sourceColumn:
                          org.apache.derby.impl.sql.compile.ResultColumn@17a4989
                          ***truncated***
                      sourceResultSet:
                          org.apache.derby.impl.sql.compile.UnionNode@14d7745
                          ***truncated***
              subquery:
                  org.apache.derby.impl.sql.compile.UnionNode@14d7745
                  ***truncated***
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-2526 [ DERBY-2526 ]
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-4679 [ DERBY-4679 ]
          Dag H. Wanvik made changes -
          Summary Internal assignment of tablenumer, columnnumber looks wrong in query, although no ill effetcs are seen. Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen.
          Dag H. Wanvik made changes -
          Affects Version/s 10.7.0.0 [ 12314971 ]
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Attachment derby.log [ 12446520 ]
          Dag H. Wanvik created issue -

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development