Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 19358 invoked from network); 18 Jun 2010 22:33:49 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 18 Jun 2010 22:33:49 -0000 Received: (qmail 65680 invoked by uid 500); 18 Jun 2010 22:33:49 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 65653 invoked by uid 500); 18 Jun 2010 22:33:48 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 65646 invoked by uid 99); 18 Jun 2010 22:33:48 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Jun 2010 22:33:48 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Jun 2010 22:33:44 +0000 Received: from thor (localhost [127.0.0.1]) by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id o5IMXNw2002506 for ; Fri, 18 Jun 2010 22:33:23 GMT Message-ID: <19710455.87031276900403143.JavaMail.jira@thor> Date: Fri, 18 Jun 2010 18:33:23 -0400 (EDT) From: "Dag H. Wanvik (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-4695) Internal assignment of tablenumer, columnnumber looks wrong in query tree, although no ill effects are seen. In-Reply-To: <31310066.11011275943133643.JavaMail.jira@thor> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ 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.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.