[DERBY-4695] Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen.

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 10.7.1.1
• Fix Version/s: 10.7.1.1
• Component/s: SQL
• Assignee: Dag H. Wanvik
• Reporter: Dag H. Wanvik
• Created: 07/Jun/10 20:37
• Updated: 25/Sep/12 15:46
• Resolved: 23/Aug/10 18:18

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

Issue Links

• is related to: DERBY-2526 Wrong results with queries that use the JOIN ... ON syntax to join with views or other non-base table expressions.
• is related to: DERBY-4679 Several left outer joins causes unstable query with incorrect results
• relates to: DERBY-5933 SQL sorting error