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

        Issue Links

          Activity

            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