[
https://issues.apache.org/jira/browse/DERBY-4695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12880347#action_12880347
]
Dag H. Wanvik commented on DERBY-4695:
--------------------------------------
Uploading why-loj-needs-2nd-bind.txt, which shows graphically what is wrong in
query tree just prior to code generation if we skip the rebinding block for
this query.
By way of comparison, I also upload a similar analysis for an inner join which
can do without this 2nd binding: why-ij-can-do-without-2nd-bind.txt
My current thinking is that this hoisting of the column references to point to
the RC in the RCL of the join node, is (sometimes) necessary, but can happens
too early, cf. the problem addressed in DERBY-4679, since other rewriting can
be thwarted by it - we lose the information about which original point in the
tree the column reference pointed to. As we have seen it also can (will
always?) lead to wrong assignment of table number and column number in the
column reference.
Note that at code generation time, the (tn, cn) information in a column
reference can be wrong - it is not used - as long as the source points to the
correct result column. In deed it *is* wrong in the loj sample query, cf. note
in why-loj-needs-2nd-bind.txt.
Note that this doesn't mean that I have concluded that inner join can always do
without this 2nd bind, nor that outer join always needs it, this is just a case
study to understand what this 2nd bind is doing.
> Internal assignment of tablenumer, columnnumber looks wrong in query tree,
> although no ill effects are seen.
> ------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4695
> URL: https://issues.apache.org/jira/browse/DERBY-4695
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.0.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Priority: Minor
> Attachments: derby.log, why-ij-can-do-without-2nd-bind.txt,
> why-loj-needs-2nd-bind.txt
>
>
> 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.fromsubqu...@12789d2
> correlation Name: BVW
> null
> tableNumber 1 <------------------------------------------- Note!
> level 0
> resultSetNumber: 0
> referencedTableMap: null
> statementResultSet: false
> resultColumns:
> org.apache.derby.impl.sql.compile.resultcolumnl...@c943d1
> indexRow: false
> orderBySelect: 0
> [0]:
> org.apache.derby.impl.sql.compile.resultcol...@d3c6a3
> ***truncated***
> [1]:
> org.apache.derby.impl.sql.compile.resultcol...@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.virtualcolumnn...@b40ec4
> dataTypeServices: null
> sourceColumn:
> org.apache.derby.impl.sql.compile.resultcol...@1d95da8
> ***truncated***
> sourceResultSet:
> org.apache.derby.impl.sql.compile.unionn...@14d7745
> ***truncated***
> [2]:
> org.apache.derby.impl.sql.compile.resultcol...@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.virtualcolumnn...@ff8c74
> dataTypeServices: null
> sourceColumn:
> org.apache.derby.impl.sql.compile.resultcol...@61736e
> ***truncated***
> sourceResultSet:
> org.apache.derby.impl.sql.compile.unionn...@14d7745
> ***truncated***
> [3]:
> org.apache.derby.impl.sql.compile.resultcol...@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.virtualcolumnn...@1cf7491
> dataTypeServices: null
> sourceColumn:
> org.apache.derby.impl.sql.compile.resultcol...@11946c2
> ***truncated***
> sourceResultSet:
> org.apache.derby.impl.sql.compile.unionn...@14d7745
> ***truncated***
> [4]: <----------------------------------------- highest column
> number is 5 (index is zero-based)
> org.apache.derby.impl.sql.compile.resultcol...@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.virtualcolumnn...@ff94b1
> dataTypeServices: null
> sourceColumn:
> org.apache.derby.impl.sql.compile.resultcol...@17a4989
> ***truncated***
> sourceResultSet:
> org.apache.derby.impl.sql.compile.unionn...@14d7745
> ***truncated***
> subquery:
> org.apache.derby.impl.sql.compile.unionn...@14d7745
> ***truncated***
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
