Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <27026783.14051277217237865.JavaMail.jira@thor>
Date: Tue, 22 Jun 2010 10:33:57 -0400 (EDT)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
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


    [ https://issues.apache.org/jira/browse/DERBY-4695?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12881215#action_12881215 ] 

Dag H. Wanvik commented on DERBY-4695:
--------------------------------------

I found the following comment in FromList#bindColumnReference:

/* TableNumbers are set in the CR in the underlying
 * FromTable.  This ensures that they get the table
 * number from the underlying table, not the join node.
 * This is important for beging able to push predicates
 * down through join nodes.

So it appears that by design, the ColumnReference's source RC is set
to that of the join, whereas the table number and the column number is
set to that of the underlying FromTable.

Referring to  the "why-loj-needs-2nd-bind.txt" example:

select * from t1 left outer join (t2 left outer "
                + "join t3 on t2.c1=t3.c1) on t1.c1=t3.c1

The 2nd bind sets up the corrects table number
and column number: although the returned RC from
JoinNode#getMatchingColumn is that of the JoinNode, the table number
has been set to the basetable's (not the join's), the column number is
*also* set to the position in the base table, since the
columndescriptor of the returned RC contains the correct column number
(which is different from the column poosition of the returned rc from
the join (2nd bind) and is here:

ColumnReference#bindExpression:

		/* Set the columnNumber from the base table.
 		 * Useful for optimizer and generation.
		 */
		columnNumber = matchingRC.getColumnPosition();

If the FromTable is a base table, matchingRC.getColumnPosition will
pick it up from the RC's columnDescriptor. This position is the column
position of the column in the base table,  so it is consistent with
the set table number.

However, if the underlying FromTable is a subquery, we get the
virtual column id of the matchingRC, cf. this code for
getColumnPosition:

	public int getColumnPosition()
	{
	    if (columnDescriptor != null) {
                return columnDescriptor.getPosition();
            } else {
                return virtualColumnId;

so, the columnNumber ends up pointing to the RC's position in the Join
node, whereas the table number still points to the FromTable, hence
the observed inconsistency.

If this is intentional or wrong, I can't yet say, but it looks weird.
The comment in ColumnReference#bindExpression says 

    "Set the columnNumber from the base table".

but what we get here is definitely not that :)

In the original query posted for this issue, the column number "6"
observed to look strange, stems from using virtualColumnId
above, (BVW is a FromSubquery).


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