Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 44573 invoked from network); 28 Jun 2010 20:28:45 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 28 Jun 2010 20:28:45 -0000 Received: (qmail 51139 invoked by uid 500); 28 Jun 2010 20:28:45 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 51082 invoked by uid 500); 28 Jun 2010 20:28:45 -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 51075 invoked by uid 99); 28 Jun 2010 20:28:45 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Jun 2010 20:28:45 +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; Mon, 28 Jun 2010 20:28:42 +0000 Received: from thor (localhost [127.0.0.1]) by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id o5SKKp4a021761 for ; Mon, 28 Jun 2010 20:20:51 GMT Message-ID: <1298084.98191277756451261.JavaMail.jira@thor> Date: Mon, 28 Jun 2010 16:20:51 -0400 (EDT) From: "Dag H. Wanvik (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Updated: (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:all-tabpanel ] Dag H. Wanvik updated DERBY-4695: --------------------------------- Attachment: query-b-after.log > 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, 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.