You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2010/08/20 01:08:21 UTC
[jira] Commented: (DERBY-4695) Internal assignment of tablenumer,
columnnumber looks wrong in query tree, although no ill effects are seen.
[ https://issues.apache.org/jira/browse/DERBY-4695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12900520#action_12900520 ]
Dag H. Wanvik commented on DERBY-4695:
--------------------------------------
Since this patch makes the intermediate (tableNumber, columnNumber) states (more) consistent and doesn't break anything as far as I can verify it, I propose to commit this patch soon unless somebody has objections.
> 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-4695-fixcolumnno-1a.diff, derby-4695-fixcolumnno-1a.stat, derby.log, query-a-after.log, query-a-before.log, query-b-after.log, query-b-before.log, trace-remapping.diff, 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.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***
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.